create or replace procedure get_idoc_report (
   pstart_date        in date,
   pend_date          in date,
   pprepmethod        in varchar2,
   panalyticalmethod  in varchar2,
   pinstru            in varchar2,
   pmatrix            in varchar2,
   pprepanalyst       in varchar2,
   panalyticalanalyst in varchar2,
   pwo                in number,
   pwo_id             in varchar2,
   par                in varchar2,
   p_cursor           out sys_refcursor
) is
begin
   open p_cursor for select distinct to_char(
                                          sysdate,
                                          'MM/DD/YYYY'
                                       ) as current_date,
                                     to_char(
                                          pstart_date,
                                          'MM/DD/YYYY'
                                       ) as wo_start,
                                     to_char(
                                          pend_date,
                                          'MM/DD/YYYY'
                                       ) as wo_end,
                                     pprepmethod as tqsprepmethod,
                                     panalyticalmethod as tqsanalyticalmethod,
                                     pinstru as tqsinstru,
                                     pmatrix as tqsmatrix,
                                     pprepanalyst as tqsprepanalyst,
                                     panalyticalanalyst as tqsanalyticalanalyst,
                                     pwo as tqswo,
                                     pwo_id as tqswo_id,
                                     par as tqsar,
                                     proj.project_seq as wo,
                                     proj.project_id,
                                     (
                                          select max(proc1.method)
                                            from schedules sch1,
                                                 procedures proc1
                                           where sch1.schedule_seq = sch.thread
                                             and proc1.proc_code = sch1.proc_code
                                       ) as prep_method_code,
                                     (
                                          select max(nvl(
                                             chl5.method_desc,
                                             'N/A'
                                          ))
                                            from schedules sch1,
                                                 procedures proc1,
                                                 hv$method chl5
                                           where sch1.schedule_seq = sch.thread
                                             and proc1.proc_code = sch1.proc_code
                                             and chl5.method = proc1.method
                                       ) as prep_method_desc,
                                     cl1.method as analytical_method_code,
                                     cl1.method_desc as analytical_method_desc,
                                     ad.aux_data as idoc_start,
                                     ad1.aux_data as idoc_end,
                                     mtr.create_user as analytical_id,
                                     formatname(lu.addr_seq) as analytical_name,
                                     (
                                          select max(mtr1.create_user)
                                            from map_to_runs mtr1
                                           where mtr1.schedule_seq = sch.thread
                                       ) as prep_id,
                                     (
                                          select max(nvl(
                                             formatname(lu1.addr_seq),
                                             'N/A'
                                          ))
                                            from map_to_runs mtr1,
                                                 lims_users lu1
                                           where mtr1.schedule_seq = sch.thread
                                             and lu1.user_nbr = mtr1.create_user
                                       ) as prep_name,
                                     cl2.instrument as instru_id,
                                     cl2.instrument_desc as instrument,
                                     sa.matrix as matrix_code,
                                     cl3.matrix_desc as matrix_desc,
                                     cl.units_desc as report_units,
                                     to_char(
                                          mtr.run_date,
                                          'MM/DD/YYYY'
                                       ) as run_date,
                                     rr.cmp,
                                     comp.name as comp_name,
                                     (
                                          select round(
                                             nvl(
                                                rr1.adjusted_result,
                                                0
                                             ),
                                             4
                                          )
                                            from reportable_results rr1,
                                                 project_samples ps1
                                           where rr1.cmp = rr.cmp
                                             and ps1.project_seq = proj.project_seq
                                             and rr1.hsn = ps1.hsn
                                             and ps1.hsn_pos = 1
                                             and substr(
                                             rr1.flags,
                                             10,
                                             1
                                          ) not in ( 'C',
                                                     'X' )
                                             and substr(
                                             rr1.flags,
                                             9,
                                             1
                                          ) != 'X'
                                             and substr(
                                             rr1.flags,
                                             10,
                                             1
                                          ) in ( 'F',
                                                 'O' )
                                             and ( panalyticalmethod is null
                                              or rr1.method = panalyticalmethod )
                                       ) as idoc1,
                                     case
                                        when par = 'N' then
                                                case
                                                   when rr.cmp = 'pH' then
                                                      (
                                                         select round(
                                                            abs(nvl(
                                                               rr1.adjusted_result,
                                                               0
                                                            ) - qp.theoretical_result),
                                                            2
                                                         )
                                                           from reportable_results rr1,
                                                                project_samples ps1
                                                          where rr1.cmp = rr.cmp
                                                            and ps1.project_seq = proj.project_seq
                                                            and rr1.hsn = ps1.hsn
                                                            and ps1.hsn_pos = 1
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) not in ( 'C',
                                                                    'X' )
                                                            and substr(
                                                            rr1.flags,
                                                            9,
                                                            1
                                                         ) != 'X'
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) in ( 'F',
                                                                'O' )
                                                            and ( panalyticalmethod is null
                                                             or rr1.method = panalyticalmethod )
                                                      )
                                                   else
                                                      (
                                                         select qc1.qc_result
                        --round(nvl(rr1.adjusted_result,0)/iqs.concentration * 100, 1)
                        --round(nvl(rr1.adjusted_result,0)/qp.theoretical_result * 100, 1)
                                                           from reportable_results rr1,
                                                                qc_parameters qc1,
                                                                project_samples ps1
                                                          where rr1.cmp = rr.cmp
                                                            and rr.cmp = qc1.cmp
                                                            and qc1.schedule_seq = rr1.schedule_seq
                                                            and ps1.project_seq = proj.project_seq
                                                            and rr1.hsn = ps1.hsn
                                                            and ps1.hsn_pos = 1
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) not in ( 'C',
                                                                    'X' )
                                                            and substr(
                                                            rr1.flags,
                                                            9,
                                                            1
                                                         ) != 'X'
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) in ( 'F',
                                                                'O' )
                                                            and ( panalyticalmethod is null
                                                             or rr1.method = panalyticalmethod )
                                                      )
                                                end
                                        else
                                           null
                                     end as idoc1_rec,
                                     (
                                          select round(
                                             nvl(
                                                rr1.adjusted_result,
                                                0
                                             ),
                                             4
                                          )
                                            from reportable_results rr1,
                                                 project_samples ps1
                                           where rr1.cmp = rr.cmp
                                             and ps1.project_seq = proj.project_seq
                                             and rr1.hsn = ps1.hsn
                                             and ps1.hsn_pos = 2
                                             and substr(
                                             rr1.flags,
                                             10,
                                             1
                                          ) not in ( 'C',
                                                     'X' )
                                             and substr(
                                             rr1.flags,
                                             9,
                                             1
                                          ) != 'X'
                                             and substr(
                                             rr1.flags,
                                             10,
                                             1
                                          ) in ( 'F',
                                                 'O' )
                                             and ( panalyticalmethod is null
                                              or rr1.method = panalyticalmethod )
                                       ) as idoc2,
                                     case
                                        when par = 'N' then
                                                case
                                                   when rr.cmp = 'pH' then
                                                      (
                                                         select round(
                                                            abs(nvl(
                                                               rr1.adjusted_result,
                                                               0
                                                            ) - qp.theoretical_result),
                                                            2
                                                         )
                                                           from reportable_results rr1,
                                                                project_samples ps1
                                                          where rr1.cmp = rr.cmp
                                                            and ps1.project_seq = proj.project_seq
                                                            and rr1.hsn = ps1.hsn
                                                            and ps1.hsn_pos = 2
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) not in ( 'C',
                                                                    'X' )
                                                            and substr(
                                                            rr1.flags,
                                                            9,
                                                            1
                                                         ) != 'X'
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) in ( 'F',
                                                                'O' )
                                                            and ( panalyticalmethod is null
                                                             or rr1.method = panalyticalmethod )
                                                      )
                                                   else
                                                      (
                                                         select qc1.qc_result
                        --round(nvl(rr1.adjusted_result,0)/iqs.concentration * 100, 1)
                        --round(nvl(rr1.adjusted_result,0)/qp.theoretical_result * 100, 1)
                                                           from reportable_results rr1,
                                                                qc_parameters qc1,
                                                                project_samples ps1
                                                          where rr1.cmp = rr.cmp
                                                            and rr.cmp = qc1.cmp
                                                            and qc1.schedule_seq = rr1.schedule_seq
                                                            and ps1.project_seq = proj.project_seq
                                                            and rr1.hsn = ps1.hsn
                                                            and ps1.hsn_pos = 2
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) not in ( 'C',
                                                                    'X' )
                                                            and substr(
                                                            rr1.flags,
                                                            9,
                                                            1
                                                         ) != 'X'
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) in ( 'F',
                                                                'O' )
                                                            and ( panalyticalmethod is null
                                                             or rr1.method = panalyticalmethod )
                                                      )
                                                end
                                        else
                                           null
                                     end as idoc2_rec,
                                     (
                                          select round(
                                             nvl(
                                                rr1.adjusted_result,
                                                0
                                             ),
                                             4
                                          )
                                            from reportable_results rr1,
                                                 project_samples ps1
                                           where rr1.cmp = rr.cmp
                                             and ps1.project_seq = proj.project_seq
                                             and rr1.hsn = ps1.hsn
                                             and ps1.hsn_pos = 3
                                             and substr(
                                             rr1.flags,
                                             10,
                                             1
                                          ) not in ( 'C',
                                                     'X' )
                                             and substr(
                                             rr1.flags,
                                             9,
                                             1
                                          ) != 'X'--add 081821 RAS
                                             and substr(
                                             rr1.flags,
                                             10,
                                             1
                                          ) in ( 'F',
                                                 'O' )
                                             and ( panalyticalmethod is null
                                              or rr1.method = panalyticalmethod )
                                       ) as idoc3,
                                     case
                                        when par = 'N' then
                                                case
                                                   when rr.cmp = 'pH' then
                                                      (
                                                         select round(
                                                            abs(nvl(
                                                               rr1.adjusted_result,
                                                               0
                                                            ) - qp.theoretical_result),
                                                            2
                                                         )
                                                           from reportable_results rr1,
                                                                project_samples ps1
                                                          where rr1.cmp = rr.cmp
                                                            and ps1.project_seq = proj.project_seq
                                                            and rr1.hsn = ps1.hsn
                                                            and ps1.hsn_pos = 3
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) not in ( 'C',
                                                                    'X' )
                                                            and substr(
                                                            rr1.flags,
                                                            9,
                                                            1
                                                         ) != 'X'
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) in ( 'F',
                                                                'O' )
                                                            and ( panalyticalmethod is null
                                                             or rr1.method = panalyticalmethod )
                                                      )
                                                   else
                                                      (
                                                         select                         
                        --to_number(lacsd_sigfigs(nvl(qc1.qc_result,0), substr(iqa.clflags,13,1), 'E'))--removed 081821 RAS
                                                          qc1.qc_result
                        --round(nvl(rr1.adjusted_result,0)/iqs.concentration * 100, 1)
                        --round(nvl(rr1.adjusted_result,0)/qp.theoretical_result * 100, 1)
                                                           from reportable_results rr1,
                                                                qc_parameters qc1,
                                                                project_samples ps1
                                                          where rr1.cmp = rr.cmp
                                                            and rr.cmp = qc1.cmp
                                                            and qc1.schedule_seq = rr1.schedule_seq
                                                            and ps1.project_seq = proj.project_seq
                                                            and rr1.hsn = ps1.hsn
                                                            and ps1.hsn_pos = 3
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) not in ( 'C',
                                                                    'X' )
                                                            and substr(
                                                            rr1.flags,
                                                            9,
                                                            1
                                                         ) != 'X'
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) in ( 'F',
                                                                'O' )
                                                            and ( panalyticalmethod is null
                                                             or rr1.method = panalyticalmethod )
                                                      )
                                                end
                                        else
                                           null
                                     end as idoc3_rec,
                                     (
                                          select round(
                                             nvl(
                                                rr1.adjusted_result,
                                                0
                                             ),
                                             4
                                          )
                                            from reportable_results rr1,
                                                 project_samples ps1
                                           where rr1.cmp = rr.cmp
                                             and ps1.project_seq = proj.project_seq
                                             and rr1.hsn = ps1.hsn
                                             and ps1.hsn_pos = 4
                                             and substr(
                                             rr1.flags,
                                             10,
                                             1
                                          ) not in ( 'C',
                                                     'X' )
                                             and substr(
                                             rr1.flags,
                                             9,
                                             1
                                          ) != 'X'
                                             and substr(
                                             rr1.flags,
                                             10,
                                             1
                                          ) in ( 'F',
                                                 'O' )
                                             and ( panalyticalmethod is null
                                              or rr1.method = panalyticalmethod )
                                       ) as idoc4,
                                     case
                                        when par = 'N' then
                                                case
                                                   when rr.cmp = 'pH' then
                                                      (
                                                         select round(
                                                            abs(nvl(
                                                               rr1.adjusted_result,
                                                               0
                                                            ) - qp.theoretical_result),
                                                            2
                                                         )
                                                           from reportable_results rr1,
                                                                project_samples ps1
                                                          where rr1.cmp = rr.cmp
                                                            and ps1.project_seq = proj.project_seq
                                                            and rr1.hsn = ps1.hsn
                                                            and ps1.hsn_pos = 4
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) not in ( 'C',
                                                                    'X' )
                                                            and substr(
                                                            rr1.flags,
                                                            9,
                                                            1
                                                         ) != 'X'
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) in ( 'F',
                                                                'O' )
                                                            and ( panalyticalmethod is null
                                                             or rr1.method = panalyticalmethod )
                                                      )
                                                   else
                                                      (
                                                         select qc1.qc_result
                        --round(nvl(rr1.adjusted_result,0)/iqs.concentration * 100, 1)
                        --round(nvl(rr1.adjusted_result,0)/qp.theoretical_result * 100, 1)
                                                           from reportable_results rr1,
                                                                qc_parameters qc1,
                                                                project_samples ps1
                                                          where rr1.cmp = rr.cmp
                                                            and rr.cmp = qc1.cmp
                                                            and qc1.schedule_seq = rr1.schedule_seq
                                                            and ps1.project_seq = proj.project_seq
                                                            and rr1.hsn = ps1.hsn
                                                            and ps1.hsn_pos = 4
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) not in ( 'C',
                                                                    'X' )
                                                            and substr(
                                                            rr1.flags,
                                                            9,
                                                            1
                                                         ) != 'X'
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) in ( 'F',
                                                                'O' )
                                                            and ( panalyticalmethod is null
                                                             or rr1.method = panalyticalmethod )
                                                      )
                                                end
                                        else
                                           null
                                     end as idoc4_rec,

            --lacsd_charstrip(lacsd_bsht_default_spk((select qc_rule from batches where batch_seq = qp.batch_seq),sa.sample_type,substr(sch.queue,4,1),qp.cmp)) as Spike_Amount,
                                     nvl(
                                          qp.theoretical_result,
                                          0
                                       ) as spike_amount,
            --iqs.concentration as Spike_Amount,                
                                     (
                                          select round(
                                             avg(nvl(
                                                rr1.adjusted_result,
                                                0
                                             )),
                                             4
                                          )
                                            from reportable_results rr1
                                           where rr1.cmp = rr.cmp
                                             and rr1.hsn in (
                                             select ps1.hsn
                                               from project_samples ps1,
                                                    samples sa1
                                              where ps1.project_seq = proj.project_seq
                                                and sa1.hsn = ps1.hsn
                                                and sa1.sample_type = 'IDOCR'
                                          )
                                             and substr(
                                             rr1.flags,
                                             10,
                                             1
                                          ) not in ( 'C',
                                                     'X' )
                                             and substr(
                                             rr1.flags,
                                             9,
                                             1
                                          ) != 'X'
                                             and substr(
                                             flags,
                                             10,
                                             1
                                          ) in ( 'F',
                                                 'O' )
                                             and ( panalyticalmethod is null
                                              or rr1.method = panalyticalmethod )
                                       ) as average_result,
                                     case
                                        when par = 'N' then
                                                case
                                                   when rr.cmp = 'pH' then
                                                      round(
                                                         abs(((
                                                            select avg(nvl(
                                                               rr1.adjusted_result,
                                                               0
                                                            ))
                                                              from reportable_results rr1
                                                             where rr1.cmp = rr.cmp
                                                               and rr1.hsn in(
                                                               select ps1.hsn
                                                                 from project_samples ps1,
                                                                      samples sa1
                                                                where ps1.project_seq = proj.project_seq
                                                                  and sa1.hsn = ps1.hsn
                                                                  and sa1.sample_type = 'IDOCR'
                                                            )
                                                               and substr(
                                                               rr1.flags,
                                                               10,
                                                               1
                                                            ) not in('C',
                                                                     'X')
                                                               and substr(
                                                               rr1.flags,
                                                               9,
                                                               1
                                                            ) != 'X'
                                                               and substr(
                                                               rr1.flags,
                                                               10,
                                                               1
                                                            ) in('F',
                                                                 'O')
                                                               and(panalyticalmethod is null
                                                                or rr1.method = panalyticalmethod)
                                                         ) - qp.theoretical_result)),
                                                         2
                                                      )
                                                   else
                                                      round(
                                                         (
                                                            select 
                                --avg(nvl(rr1.adjusted_result,0))
                                                             avg(to_number(lacsd_sigfigs(
                                                               nvl(
                                                                  qcp.qc_result,
                                                                  0
                                                               ),
                                                               substr(
                                                                   iqa.clflags,
                                                                   13,
                                                                   1
                                                                ),
                                                               'E'
                                                            )))
                                                              from reportable_results rr1,
                                                                   qc_parameters qcp
                                                             where rr1.cmp = rr.cmp
                                                               and rr1.cmp = qcp.cmp
                                                               and rr1.schedule_seq = qcp.schedule_seq
                                                               and rr1.hsn in(
                                                               select ps1.hsn
                                                                 from project_samples ps1,
                                                                      samples sa1
                                                                where ps1.project_seq = proj.project_seq
                                                                  and sa1.hsn = ps1.hsn
                                                                  and sa1.sample_type = 'IDOCR'
                                                            )
                                                               and substr(
                                                               rr1.flags,
                                                               10,
                                                               1
                                                            ) not in('C',
                                                                     'X')
                                                               and substr(
                                                               rr1.flags,
                                                               9,
                                                               1
                                                            ) != 'X'
                                                               and substr(
                                                               rr1.flags,
                                                               10,
                                                               1
                                                            ) in('F',
                                                                 'O')
                                                               and(panalyticalmethod is null
                                                                or rr1.method = panalyticalmethod)
                                                         ),
                                                         1
                                                      )
                                --/iqs.concentration) * 100, 1)
                                --/(select concentration from iqp_spikecmps where cmp = rr.cmp and endingscheduleseq= sch.schedule_seq)) * 100, 1)
                                --/qp.theoretical_result) * 100, 1)
                                                end
                                        else
                                           null
                                     end as ave_recovery,
                                     case
                                        when par = 'N' then
                                                case
                                                   when rr.cmp = 'pH' then
                                                      round(
                                                         abs(((
                                                            select avg(nvl(
                                                               rr1.adjusted_result,
                                                               0
                                                            ))
                                                              from reportable_results rr1
                                                             where rr1.cmp = rr.cmp
                                                               and rr1.hsn in(
                                                               select ps1.hsn
                                                                 from project_samples ps1,
                                                                      samples sa1
                                                                where ps1.project_seq = proj.project_seq
                                                                  and sa1.hsn = ps1.hsn
                                                                  and sa1.sample_type = 'IDOCR'
                                                            )
                                                               and substr(
                                                               rr1.flags,
                                                               10,
                                                               1
                                                            ) not in('C',
                                                                     'X')
                                                               and substr(
                                                               rr1.flags,
                                                               9,
                                                               1
                                                            ) != 'X'
                                                               and substr(
                                                               rr1.flags,
                                                               10,
                                                               1
                                                            ) in('F',
                                                                 'O')
                                                               and(panalyticalmethod is null
                                                                or rr1.method = panalyticalmethod)
                                                         ) - qp.theoretical_result)),
                                                         2
                                                      )
                                                   else
                                                      (
                                                         select round(
                                                            (stddev(nvl(
                                                               rr1.adjusted_result,
                                                               0
                                                            )) / avg(nvl(
                                                               rr1.adjusted_result,
                                                               0
                                                            ))) * 100,
                                                            3
                                                         )
                                                           from reportable_results rr1
                                                          where rr1.cmp = rr.cmp
                                                            and rr1.hsn in (
                                                            select ps1.hsn
                                                              from project_samples ps1,
                                                                   samples sa1
                                                             where ps1.project_seq = proj.project_seq
                                                               and sa1.hsn = ps1.hsn
                                                               and sa1.sample_type = 'IDOCR'
                                                         )
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) not in ( 'C',
                                                                    'X' )
                                                            and substr(
                                                            rr1.flags,
                                                            9,
                                                            1
                                                         ) != 'X'
                                                            and substr(
                                                            rr1.flags,
                                                            10,
                                                            1
                                                         ) in ( 'F',
                                                                'O' )
                                                            and ( panalyticalmethod is null
                                                             or rr1.method = panalyticalmethod )
                                                      )
                                                end
                                        else
                                           (
                                                select round(
                                                   stddev(nvl(
                                                      rr1.adjusted_result,
                                                      0
                                                   )),
                                                   3
                                                )
                                                  from reportable_results rr1
                                                 where rr1.cmp = rr.cmp
                                                   and rr1.hsn in (
                                                   select ps1.hsn
                                                     from project_samples ps1,
                                                          samples sa1
                                                    where ps1.project_seq = proj.project_seq
                                                      and sa1.hsn = ps1.hsn
                                                      and sa1.sample_type = 'IDOCR'
                                                )
                                                   and substr(
                                                   rr1.flags,
                                                   10,
                                                   1
                                                ) not in ( 'C',
                                                           'X' )
                                                   and substr(
                                                   rr1.flags,
                                                   9,
                                                   1
                                                ) != 'X'
                                                   and substr(
                                                   rr1.flags,
                                                   10,
                                                   1
                                                ) in ( 'F',
                                                       'O' )
                                                   and ( panalyticalmethod is null
                                                    or rr1.method = panalyticalmethod )
                                             )
                                     end as rsd,
                                     case
                                        when par = 'N' then
                                             lacsd_idoc_limits(
                                                proc.method,
                                                proc2.method,
                                                substr(
                                                   proc.flags,
                                                   4,
                                                   1
                                                ),
                                                sa.matrix,
                                                mtr.run_instru,
                                                ar.cmp,
                                                ad1.aux_data,
                                                ad.aux_data,
                                                proj.create_date,
                                                'Lower'
                                             )
                                        else
                                           lacsd_idoc_limits(
                                                proc.method,
                                                proc2.method,
                                                substr(
                                                   proc.flags,
                                                   4,
                                                   1
                                                ),
                                                sa.matrix,
                                                mtr.run_instru,
                                                ar.cmp,
                                                ad1.aux_data,
                                                ad.aux_data,
                                                proj.create_date,
                                                'LowerAR'
                                             )
                                     end as lo_limit,
                                     case
                                        when par = 'N' then
                                             lacsd_idoc_limits(
                                                proc.method,
                                                proc2.method,
                                                substr(
                                                   proc.flags,
                                                   4,
                                                   1
                                                ),
                                                sa.matrix,
                                                mtr.run_instru,
                                                ar.cmp,
                                                ad1.aux_data,
                                                ad.aux_data,
                                                proj.create_date,
                                                'Upper'
                                             )
                                        else
                                           lacsd_idoc_limits(
                                                proc.method,
                                                proc2.method,
                                                substr(
                                                   proc.flags,
                                                   4,
                                                   1
                                                ),
                                                sa.matrix,
                                                mtr.run_instru,
                                                ar.cmp,
                                                ad1.aux_data,
                                                ad.aux_data,
                                                proj.create_date,
                                                'UpperAR'
                                             )
                                     end as hi_limit,
                                     case
                                        when par = 'N' then
                                             lacsd_idoc_limits(
                                                proc.method,
                                                proc2.method,
                                                substr(
                                                   proc.flags,
                                                   4,
                                                   1
                                                ),
                                                sa.matrix,
                                                mtr.run_instru,
                                                ar.cmp,
                                                ad1.aux_data,
                                                ad.aux_data,
                                                proj.create_date,
                                                'RSD'
                                             )
                                        else
                                           lacsd_idoc_limits(
                                                proc.method,
                                                proc2.method,
                                                substr(
                                                   proc.flags,
                                                   4,
                                                   1
                                                ),
                                                sa.matrix,
                                                mtr.run_instru,
                                                ar.cmp,
                                                ad1.aux_data,
                                                ad.aux_data,
                                                proj.create_date,
                                                'SD'
                                             )
                                     end as rsd_limit
                                         from projects proj,
                                              project_samples ps,
                                              samples sa,
                                              permanent_ids pi,
                                              schedules sch,
                                              schedules sch2,
                                              procedures proc,
                                              procedures proc2,
                                              reportable_results rr,
                                              analytical_results ar,
                                              hv$units cl,
                                              hv$method cl1,
                                              hv$instrument cl2,
                                              hv$matrix cl3,
                                              map_to_runs mtr,
                                              map_to_runs mtr2,
                                              qc_parameters qp,
                                              compounds comp,
                                              lims_users lu,
                                              aux_data ad,
                                              aux_data ad1,
                                              iqp_spikecmps iqs,
                                              iqp_arinfo iqa
                      where ( proj.project_id like ( '%'
                                                     || pwo_id
                                                     || '%' )
                         or pwo_id is null )
                        and ( proc.method = panalyticalmethod
                         or panalyticalmethod is null )
                        and ( mtr.run_instru = pinstru
                         or pinstru is null )
                        and ( proc2.method = pprepmethod
                         or pprepmethod is null )
                        and ( sa.matrix = pmatrix
                         or pmatrix is null )
                        and ( mtr.create_user = panalyticalanalyst
                         or panalyticalanalyst is null )
                        and ( mtr2.create_user = pprepanalyst
                         or pprepanalyst is null )
                        and ( proj.project_seq = pwo
                         or pwo is null )
--            and (to_char(pEnd_Date + 1 ,'YYYYMMDDHH24MISS') > to_char(nvl(to_date(ad.aux_data, 'MM/DD/YYYY'), proj.create_date), 'YYYYMMDDHH24MISS') or pEnd_Date is Null)
--            and ((ad.aux_data is null AND pEnd_Date+1>proj.create_date ) OR (pEnd_Date+1 > to_date(ad.aux_data, 'MM/DD/YYYY')))
                        and ( to_char(
                        pstart_date,
                        'YYYYMMDDHH24MISS'
                     ) <= to_char(
                        nvl(
                           to_date(ad1.aux_data,
                            'MM/DD/YYYY'),
                           proj.create_date
                        ),
                        'YYYYMMDDHH24MISS'
                     )
                         or pstart_date is null )
                        and ps.project_seq = proj.project_seq
                        and ps.hsn_pos = 1
                        and pi.hsn = ps.hsn
                        and sa.hsn = ps.hsn
                        and sa.sample_type = 'IDOCR'
                        and sch.schedule_id = ps.hsn
                        and rr.schedule_seq = sch.schedule_seq
                        and iqs.endingscheduleseq = sch.schedule_seq
                        and iqs.endingscheduleseq = iqa.endingscheduleseq
                        and iqa.cmp = iqs.cmp
                        and rr.cmp = iqs.cmp
                        and substr(
                        rr.flags,
                        10,
                        1
                     ) not in ( 'C',
                                'X' )
                        and substr(
                        rr.flags,
                        9,
                        1
                     ) != 'X'
                        and cl.units = substr(
                        rr.flags,
                        3,
                        1
                     )
                        and proc.proc_code = sch.proc_code
                        and substr(
                        proc.flags,
                        4,
                        1
                     ) in ( 'A',
                            'B' )
                        and cl1.method = proc.method
                        and mtr.schedule_seq = sch.schedule_seq
                        and cl2.instrument = mtr.run_instru
                        and cl3.matrix = sa.matrix
                        and ar.schedule_seq = rr.schedule_seq
                        and ar.cmp = rr.cmp
                        and qp.schedule_seq = sch.schedule_seq
                        and qp.cmp = rr.cmp
                        and qp.theoretical_result <> 0
                        and rr.cmp = comp.cmp
                        and rr.adjusted_result <> 0
                        and lu.user_nbr = mtr.create_user
                        and ad.aux_data_type = 'P'
                        and ad.aux_data_format = 'IDOS'
                        and ad.aux_data_id = proj.project_seq
                        and ad1.aux_data_type = 'P'
                        and ad1.aux_data_format = 'IDOE'
                        and ad1.aux_data_id = proj.project_seq
                        and sch2.schedule_seq (+) = sch.thread
                        and ( ( substr(
                           proc.flags,
                           4,
                           1
                        ) = 'A'
                        and proc2.proc_code = sch2.proc_code )
                         or substr(
                        proc.flags,
                        4,
                        1
                     ) = 'B' )
                        and mtr2.schedule_seq (+) = sch.thread
                      order by wo,
                               comp_name;

exception
   when others then
      open p_cursor for select to_char(
                           sysdate,
                           'MM/DD/YYYY'
                        ) as current_date,
                               to_char(
                                  pstart_date,
                                  'MM/DD/YYYY'
                               ) as wo_start,
                               to_char(
                                  pend_date,
                                  'MM/DD/YYYY'
                               ) as wo_end,
                               pprepmethod as tqsprepmethod,
                               panalyticalmethod as tqsanalyticalmethod,
                               pinstru as tqsinstru,
                               pmatrix as tqsmatrix,
                               pprepanalyst as tqsprepanalyst,
                               panalyticalanalyst as tqsanalyticalanalyst,
                               pwo as tqswo,
                               pwo_id as tqswo_id,
                               par as tqsar,
                               null as wo,
                               null as project_id,
                               null as prep_method_code,
                               null as prep_method_desc,
                               null as analytical_method_code,
                               null as analytical_method_desc,
                               null as idoc_start,
                               null as idoc_end,
                               null as analytical_id,
                               null as analytical_name,
                               null as prep_id,
                               null as prep_name,
                               null as instru_id,
                               null as instrument,
                               null as matrix_code,
                               null as matrix_desc,
                               null as report_units,
                               null as run_date,
                               null as cmp,
                               null as comp_name,
                               null as idoc1,
                               null as idoc1_rec,
                               null as idoc2,
                               null as idoc2_rec,
                               null as idoc3,
                               null as idoc3_rec,
                               null as idoc4,
                               null as idoc4_rec,
                               null as spike_amount,
                               null as average_result,
                               null as ave_recovery,
                               null as rsd,
                               null as lo_limit,
                               null as hi_limit,
                               null as rsd_limit
                          from dual;
end;