select /* [[[SOP.WorksheetSearch]]] */
 W.WORKSHEET_ID WorksheetId,
 nvl(to_char(W.CLONED_FROM_ID), '--') CloneId,
 nvl((select C.CASE_NUM
       from ARV_SOP_CASE C
      where C.CASE_ID = ARFN_GET_SOP_CASE_ID(W.WORKSHEET_ID)),
     '--') CaseNumber,
 (select trim(FA.NAME)
    from AV_SERVICE_TEAM STA, AV_SERVICE_CENTER SCA, AV_FACILITY FA
   where STA.SERVICE_TEAM_ID = W.OWNING_TEAM_ID and
         STA.SERVICE_CENTER_ID = SCA.SERVICE_CENTER_ID and
         SCA.FACILITY_ID = FA.FACILITY_ID) AgencyName,
 decode(W.IS_ATTEMPTED, 'Y', 'Yes', 'N', 'No', '--') Attempted,
 decode(W.IS_CONSOLIDATED, 'Y', 'Yes', 'N', 'No', '--') Consolidated,
 decode(W.IS_BANKRUPTCY, 'Y', 'Yes', 'N', 'No', '--') Bankruptcy,
 decode(W.IS_MULTIPLE, 'Y', 'Yes', 'N', 'No', '--') Multiple,
 decode(W.REJECT_REASON_CD, null, 'No', 'Yes') Rejected,
 W.RECEIVED_DATE Received,
 W.STATUS_CD StatusCd,
 TRUNC(W.PURGE_DATE) PurgeDate,
 TRUNC(W.ARCHIVE_DATE) ArchiveDate,
 (select WS.STATUS
    from ARV_SOP_WORKSHEET_STATUS WS
   where WS.STATUS_CD = W.STATUS_CD) Status,
 nvl(trim(W.BUS_NAME), '--') EntityName,
 nvl((select C.PLAINTIFF
       from ARV_SOP_CASE C
      where C.CASE_ID = ARFN_GET_SOP_CASE_ID(W.WORKSHEET_ID)),
     '--') Plaintiff,
 nvl(trim(W.DEFENDANT_NAME), '--') Defendant,
 (select decode(STM.FIRST_NAME, null, '', trim(STM.FIRST_NAME) || ' ') ||
         trim(STM.LAST_NAME)
    from AV_SERVICE_TEAM_MEMBER STM
   where W.ASSIGNED_TO = STM.SERVICE_TEAM_MEMBER_ID) AssignedTo,
 W.ASSIGNED_TO AssignedToCd,
 decode((W.IS_LogPosted),
        'Y',
        'Posted',
        'N',
        case
          when (trunc(W.RECEIVED_DATE) < trunc(sysdate) and
               W.STATUS_CD = 33003 and
               decode((nullif((select count(1)
                                 from ARV_SOP_ACTION_ITEM AI
                                where AI.WORKSHEET_ID = W.WORKSHEET_ID and
                                      W.IS_ATTEMPTED = 'N' and
                                      W.REJECT_REASON_CD is null and
                                      AI.ACTION_ITEM_STATUS_CD != 38007 and
                                      ((AI.DELIVERABLE_CD = 12008 and
                                      AI.DELIVERY_METHOD_CD not in
                                      (13016, 13017)) or
                                      (AI.DELIVERABLE_CD = 12007 and
                                      AI.DELIVERY_METHOD_CD not in (13016))) AND
                                      AI.ACTION_ITEM_STATUS_CD in
                                      (38002, 38010, 38011)),
                               (select count(1)
                                  from ARV_SOP_ACTION_ITEM AI
                                 where AI.WORKSHEET_ID = W.WORKSHEET_ID and
                                       W.IS_ATTEMPTED = 'N' and
                                       W.REJECT_REASON_CD is null and
                                       AI.ACTION_ITEM_STATUS_CD != 38007 and
                                       ((AI.DELIVERABLE_CD = 12008 and
                                       AI.DELIVERY_METHOD_CD not in
                                       (13016, 13017)) or
                                       (AI.DELIVERABLE_CD = 12007 and
                                       AI.DELIVERY_METHOD_CD not in (13016)))
                                 group by AI.WORKSHEET_ID
                                having count(1) > 0))),
                       null,
                       'Y',
                       'N') = 'Y') then
           'Forced'
          else
           'UnPosted'
        end) PostStatus,
 (select decode(STM.FIRST_NAME, null, '', trim(STM.FIRST_NAME) || ' ') ||
         trim(STM.LAST_NAME)
    from AV_SERVICE_TEAM_MEMBER STM
   where W.REVIEWED_BY = STM.SERVICE_TEAM_MEMBER_ID) ReviewedBy,
 decode(W.REVIEWED_BY, null, 'No', 'Yes') ReviewStatus,
 nvl(trim(W.DOCUMENT_TYPE), '--') DocumentType,
 nvl(trim(J.JURIS_NAME), '--') JurisName
  from ARV_SOP_WORKSHEET W, ARV_JURISDICTION J, ARV_BUSINESS_NAME B
 where W.REP_JURIS_ID = J.JURIS_ID(+) and W.BUS_NAME_ID = B.BUS_NAME_ID(+) and
       rownum < 10000 
       --- Applying the filters
       and W.ASSIGNED_TO = '1132' and
       TRUNC(W.RECEIVED_DATE) BETWEEN '26-Apr-2005' and '26-Apr-2006' and
       B.ENTITY_ID = 2142500004 and exists
 (select 'x'
          from AV_SERVICE_TEAM STA
         where STA.SERVICE_TEAM_ID = W.OWNING_TEAM_ID and
               STA.SERVICE_CENTER_ID IN
               (select SCA.SERVICE_CENTER_ID
                  from AV_SERVICE_CENTER SCA
                 where SCA.FACILITY_ID = '592'))
************************************************************************************************************
select /* [[[SOP.WorksheetSearch]]] */
 W.WORKSHEET_ID WorksheetId,
 nvl(to_char(W.CLONED_FROM_ID), '--') CloneId,
 nvl((select C.CASE_NUM
       from ARV_SOP_CASE C
      where C.CASE_ID = ARFN_GET_SOP_CASE_ID(W.WORKSHEET_ID)),
     '--') CaseNumber,
 (select trim(FA.NAME)
    from AV_SERVICE_TEAM STA, AV_SERVICE_CENTER SCA, AV_FACILITY FA
   where STA.SERVICE_TEAM_ID = W.OWNING_TEAM_ID and
         STA.SERVICE_CENTER_ID = SCA.SERVICE_CENTER_ID and
         SCA.FACILITY_ID = FA.FACILITY_ID) AgencyName,
 decode(W.IS_ATTEMPTED, 'Y', 'Yes', 'N', 'No', '--') Attempted,
 decode(W.IS_CONSOLIDATED, 'Y', 'Yes', 'N', 'No', '--') Consolidated,
 decode(W.IS_BANKRUPTCY, 'Y', 'Yes', 'N', 'No', '--') Bankruptcy,
 decode(W.IS_MULTIPLE, 'Y', 'Yes', 'N', 'No', '--') Multiple,
 decode(W.REJECT_REASON_CD, null, 'No', 'Yes') Rejected,
 W.RECEIVED_DATE Received,
 W.STATUS_CD StatusCd,
 TRUNC(W.PURGE_DATE) PurgeDate,
 TRUNC(W.ARCHIVE_DATE) ArchiveDate,
 (select WS.STATUS
    from ARV_SOP_WORKSHEET_STATUS WS
   where WS.STATUS_CD = W.STATUS_CD) Status,
 nvl(trim(W.BUS_NAME), '--') EntityName,
 nvl((select C.PLAINTIFF
       from ARV_SOP_CASE C
      where C.CASE_ID = ARFN_GET_SOP_CASE_ID(W.WORKSHEET_ID)),
     '--') Plaintiff,
 nvl(trim(W.DEFENDANT_NAME), '--') Defendant,
 (select decode(STM.FIRST_NAME, null, '', trim(STM.FIRST_NAME) || ' ') ||
         trim(STM.LAST_NAME)
    from AV_SERVICE_TEAM_MEMBER STM
   where W.ASSIGNED_TO = STM.SERVICE_TEAM_MEMBER_ID) AssignedTo,
 W.ASSIGNED_TO AssignedToCd,
 decode((W.IS_LogPosted),
        'Y',
        'Posted',
        'N',
        case
          when (trunc(W.RECEIVED_DATE) < trunc(sysdate) and
               W.STATUS_CD = 33003 and
               decode((nullif((select count(1)
                                 from ARV_SOP_ACTION_ITEM AI
                                where AI.WORKSHEET_ID = W.WORKSHEET_ID and
                                      W.IS_ATTEMPTED = 'N' and
                                      W.REJECT_REASON_CD is null and
                                      AI.ACTION_ITEM_STATUS_CD != 38007 and
                                      ((AI.DELIVERABLE_CD = 12008 and
                                      AI.DELIVERY_METHOD_CD not in
                                      (13016, 13017)) or
                                      (AI.DELIVERABLE_CD = 12007 and
                                      AI.DELIVERY_METHOD_CD not in (13016))) AND
                                      AI.ACTION_ITEM_STATUS_CD in
                                      (38002, 38010, 38011)),
                               (select count(1)
                                  from ARV_SOP_ACTION_ITEM AI
                                 where AI.WORKSHEET_ID = W.WORKSHEET_ID and
                                       W.IS_ATTEMPTED = 'N' and
                                       W.REJECT_REASON_CD is null and
                                       AI.ACTION_ITEM_STATUS_CD != 38007 and
                                       ((AI.DELIVERABLE_CD = 12008 and
                                       AI.DELIVERY_METHOD_CD not in
                                       (13016, 13017)) or
                                       (AI.DELIVERABLE_CD = 12007 and
                                       AI.DELIVERY_METHOD_CD not in (13016)))
                                 group by AI.WORKSHEET_ID
                                having count(1) > 0))),
                       null,
                       'Y',
                       'N') = 'Y') then
           'Forced'
          else
           'UnPosted'
        end) PostStatus,
 (select decode(STM.FIRST_NAME, null, '', trim(STM.FIRST_NAME) || ' ') ||
         trim(STM.LAST_NAME)
    from AV_SERVICE_TEAM_MEMBER STM
   where W.REVIEWED_BY = STM.SERVICE_TEAM_MEMBER_ID) ReviewedBy,
 decode(W.REVIEWED_BY, null, 'No', 'Yes') ReviewStatus,
 nvl(trim(W.DOCUMENT_TYPE), '--') DocumentType,
 nvl(trim(J.JURIS_NAME), '--') JurisName
  from ARV_SOP_WORKSHEET W, ARV_JURISDICTION J, ARV_BUSINESS_NAME B
 where W.REP_JURIS_ID = J.JURIS_ID(+) and W.BUS_NAME_ID = B.BUS_NAME_ID(+) and
       rownum < 10000 
       --- Applying the filters
       and TRUNC(W.RECEIVED_DATE) BETWEEN '04-May-2006' and
       '04-May-2006' and B.ENTITY_ID = 2142500004 and exists
 (select 'x'
          from AV_SERVICE_TEAM STA
         where STA.SERVICE_TEAM_ID = W.OWNING_TEAM_ID and
               STA.SERVICE_CENTER_ID IN
               (select SCA.SERVICE_CENTER_ID
                  from AV_SERVICE_CENTER SCA
                 where SCA.FACILITY_ID = '572'))
********************************************************************************************************************
select /* [[[SOP.WorksheetSearch]]] */
 W.WORKSHEET_ID WorksheetId,
 nvl(to_char(W.CLONED_FROM_ID), '--') CloneId,
 nvl((select C.CASE_NUM
       from ARV_SOP_CASE C
      where C.CASE_ID = ARFN_GET_SOP_CASE_ID(W.WORKSHEET_ID)),
     '--') CaseNumber,
 (select trim(FA.NAME)
    from AV_SERVICE_TEAM STA, AV_SERVICE_CENTER SCA, AV_FACILITY FA
   where STA.SERVICE_TEAM_ID = W.OWNING_TEAM_ID and
         STA.SERVICE_CENTER_ID = SCA.SERVICE_CENTER_ID and
         SCA.FACILITY_ID = FA.FACILITY_ID) AgencyName,
 decode(W.IS_ATTEMPTED, 'Y', 'Yes', 'N', 'No', '--') Attempted,
 decode(W.IS_CONSOLIDATED, 'Y', 'Yes', 'N', 'No', '--') Consolidated,
 decode(W.IS_BANKRUPTCY, 'Y', 'Yes', 'N', 'No', '--') Bankruptcy,
 decode(W.IS_MULTIPLE, 'Y', 'Yes', 'N', 'No', '--') Multiple,
 decode(W.REJECT_REASON_CD, null, 'No', 'Yes') Rejected,
 W.RECEIVED_DATE Received,
 W.STATUS_CD StatusCd,
 TRUNC(W.PURGE_DATE) PurgeDate,
 TRUNC(W.ARCHIVE_DATE) ArchiveDate,
 (select WS.STATUS
    from ARV_SOP_WORKSHEET_STATUS WS
   where WS.STATUS_CD = W.STATUS_CD) Status,
 nvl(trim(W.BUS_NAME), '--') EntityName,
 nvl((select C.PLAINTIFF
       from ARV_SOP_CASE C
      where C.CASE_ID = ARFN_GET_SOP_CASE_ID(W.WORKSHEET_ID)),
     '--') Plaintiff,
 nvl(trim(W.DEFENDANT_NAME), '--') Defendant,
 (select decode(STM.FIRST_NAME, null, '', trim(STM.FIRST_NAME) || ' ') ||
         trim(STM.LAST_NAME)
    from AV_SERVICE_TEAM_MEMBER STM
   where W.ASSIGNED_TO = STM.SERVICE_TEAM_MEMBER_ID) AssignedTo,
 W.ASSIGNED_TO AssignedToCd,
 decode((W.IS_LogPosted),
        'Y',
        'Posted',
        'N',
        case
          when (trunc(W.RECEIVED_DATE) < trunc(sysdate) and
               W.STATUS_CD = 33003 and
               decode((nullif((select count(1)
                                 from ARV_SOP_ACTION_ITEM AI
                                where AI.WORKSHEET_ID = W.WORKSHEET_ID and
                                      W.IS_ATTEMPTED = 'N' and
                                      W.REJECT_REASON_CD is null and
                                      AI.ACTION_ITEM_STATUS_CD != 38007 and
                                      ((AI.DELIVERABLE_CD = 12008 and
                                      AI.DELIVERY_METHOD_CD not in
                                      (13016, 13017)) or
                                      (AI.DELIVERABLE_CD = 12007 and
                                      AI.DELIVERY_METHOD_CD not in (13016))) AND
                                      AI.ACTION_ITEM_STATUS_CD in
                                      (38002, 38010, 38011)),
                               (select count(1)
                                  from ARV_SOP_ACTION_ITEM AI
                                 where AI.WORKSHEET_ID = W.WORKSHEET_ID and
                                       W.IS_ATTEMPTED = 'N' and
                                       W.REJECT_REASON_CD is null and
                                       AI.ACTION_ITEM_STATUS_CD != 38007 and
                                       ((AI.DELIVERABLE_CD = 12008 and
                                       AI.DELIVERY_METHOD_CD not in
                                       (13016, 13017)) or
                                       (AI.DELIVERABLE_CD = 12007 and
                                       AI.DELIVERY_METHOD_CD not in (13016)))
                                 group by AI.WORKSHEET_ID
                                having count(1) > 0))),
                       null,
                       'Y',
                       'N') = 'Y') then
           'Forced'
          else
           'UnPosted'
        end) PostStatus,
 (select decode(STM.FIRST_NAME, null, '', trim(STM.FIRST_NAME) || ' ') ||
         trim(STM.LAST_NAME)
    from AV_SERVICE_TEAM_MEMBER STM
   where W.REVIEWED_BY = STM.SERVICE_TEAM_MEMBER_ID) ReviewedBy,
 decode(W.REVIEWED_BY, null, 'No', 'Yes') ReviewStatus,
 nvl(trim(W.DOCUMENT_TYPE), '--') DocumentType,
 nvl(trim(J.JURIS_NAME), '--') JurisName
  from ARV_SOP_WORKSHEET W, ARV_JURISDICTION J, ARV_BUSINESS_NAME B
 where W.REP_JURIS_ID = J.JURIS_ID(+) and W.BUS_NAME_ID = B.BUS_NAME_ID(+) and
       rownum < 10000
       --- Applying the filters
       and TRUNC(W.RECEIVED_DATE) BETWEEN '04-May-2006' and
       '04-May-2006' and exists
 (select 'x'
          from AV_SERVICE_TEAM STA
         where STA.SERVICE_TEAM_ID = W.OWNING_TEAM_ID and
               STA.SERVICE_CENTER_ID IN
               (select SCA.SERVICE_CENTER_ID
                  from AV_SERVICE_CENTER SCA
                 where SCA.FACILITY_ID = '572')) and
       W.WORKSHEET_ID in
       (select C.WORKSHEET_ID
          from ARV_SOP_CASE C
         where upper(C.PLAINTIFF) like ('PURITY HOLDING COMPANY%'))