sql - Joining data from 5 tables without duplicate values and getting result with custom status in Oracle 10g

Question

I have 5 tables which I have to use to get the set of pending documents for a theft claim. I am using Oracle 10g. The table structures are given below.

1. mo_documents_master    -- master table for all document types
    CREATE TABLE "MO_DOCUMENT_MASTER"
    (
        "DOC_ID"   NUMBER NOT NULL ENABLE,
        "DOC_TYPE_NAME" VARCHAR2(100 BYTE),
        "STATUS"   VARCHAR2(50 BYTE),
        CONSTRAINT "MO_DOCUMENT_MASTER_PK" PRIMARY KEY ("DOC_ID") 
    )

2. mo_theft_pvt_documents   -- Contains list of documents applicable for a theft claim; they can be mandatory or optional
    CREATE TABLE "MO_THEFT_PVT_DOCUMENTS"
    (
        "DOC_ID"       NUMBER NOT NULL ENABLE,
        "STAGE"        VARCHAR2(20 BYTE),
        "IS_MANDATORY" VARCHAR2(10 BYTE),   -- true or false; true means document is mandatory
        CONSTRAINT "MO_THEFT_DOCUMENTS_PK" PRIMARY KEY ("DOC_ID") 
        CONSTRAINT "MO_THEFT_PVT_DOCUMENTS_MO_FK1" FOREIGN KEY ("DOC_ID") REFERENCES "MO_DOCUMENT_MASTER" ("DOC_ID") ENABLE
    )

3. mo_theft_pvt_doc_queue  -- Mapping of which documents are applicable for a particular queue
    CREATE TABLE "MO_THEFT_PVT_DOC_QUEUE"
    (
        "DOC_ID"   NUMBER NOT NULL ENABLE,
        "QUEUE_ID" NUMBER NOT NULL ENABLE,
        CONSTRAINT "MO_THEFT_DOC_QUEUE_PK" PRIMARY KEY ("DOC_ID", "QUEUE_ID") 
        CONSTRAINT "MO_THEFT_DOC_QUEUE_MO_QUE_FK1" FOREIGN KEY ("QUEUE_ID") REFERENCES "MO_QUEUE_MASTER" ("ID") ENABLE,
        CONSTRAINT "MO_THEFT_PVT_DOC_QUEUE_MO_FK1" FOREIGN KEY ("DOC_ID") REFERENCES "MO_THEFT_PVT_DOCUMENTS" ("DOC_ID") ENABLE
    )

4. mo_claim_documents    -- list of uploaded documents for a claim. 
    CREATE TABLE "MO_CLAIM_DOCUMENTS"
    (
        "ID"           NUMBER NOT NULL ENABLE,
        "CLAIM_REF_NO" VARCHAR2(50 BYTE),
        "DOC_ID"       NUMBER,
        "DOCUMENTURL"  VARCHAR2(255 BYTE),
        "FILENAME"     VARCHAR2(255 BYTE),
        "UPLOADED_ON" TIMESTAMP (6),
        "UPLOADED_BY" VARCHAR2(100 BYTE),
        "STATUS"      VARCHAR2(30 BYTE),    -- STATUS can be 'Active' or 'Inactive'
        CONSTRAINT "M0_CLAIM_DOCUMENTS_PK" PRIMARY KEY ("ID") 
        CONSTRAINT "MO_OD_CLAIM_DOCUMENTS_MO__FK1" FOREIGN KEY ("CLAIM_REF_NO") REFERENCES "MO_CLAIM_MASTER" ("CLAIM_REF_NO") ENABLE,
        CONSTRAINT "MO_OD_CLAIM_DOCUMENTS_MO__FK2" FOREIGN KEY ("DOC_ID") REFERENCES "MO_DOCUMENT_MASTER" ("DOC_ID") ENABLE
    )

5. mo_claim_doc_waiver;  -- list of document waiver requests for a claim
    CREATE TABLE "MO_CLAIM_DOC_WAIVER"
    (
        "ID"           NUMBER NOT NULL ENABLE,
        "CLAIM_REF_NO" VARCHAR2(50 BYTE),
        "DOC_ID"       NUMBER,
        "REQUESTED_DATE" TIMESTAMP (6),
        "REQUSETED_BY" VARCHAR2(200 BYTE),
        "ACTION_DATE" TIMESTAMP (6),
        "ACTION_BY"       VARCHAR2(200 BYTE),
        "REQUEST_REMARKS" VARCHAR2(255 BYTE),
        "ACTION_REMARKS"  VARCHAR2(255 BYTE),
        "STATUS"          VARCHAR2(50 BYTE),   -- STATUS can be 'Waiver Requested', 'Waived' or 'Rejected'
        CONSTRAINT "MO_CLAIM_DOC_WAIVER_PK" PRIMARY KEY ("ID") 
        CONSTRAINT "MO_CLAIM_DOC_WAIVER_MO_CL_FK1" FOREIGN KEY ("CLAIM_REF_NO") REFERENCES "MO_CLAIM_MASTER" ("CLAIM_REF_NO") ENABLE,
        CONSTRAINT "MO_CLAIM_DOC_WAIVER_MO_DO_FK1" FOREIGN KEY ("DOC_ID") REFERENCES "MO_DOCUMENT_MASTER" ("DOC_ID") ENABLE
    )

My requirement is to find the list of mandatory documents that are 'Remaining' for a particular QUEUE_ID and CLAIM_REF_NO (these 2 are the inputs for the query).

'Remaining' documents of a claim ("CLAIM_REF_NO") are those documents that are "Mandatory" for a particular "QUEUE_ID" and which have not been uploaded or waived off. Also, I need status for each 'Remaining" document type. Status can be a. 'Pending' (document is not uploaded or uploaded but made inactive or a waiver request has been rejected) b. 'Waiver Requested' (document which is not uploaded and status is Waiver Requested')

I need only one status for a particular Document Type. (For eg, if a document which has not been uploaded has 2 statuses - 'Waiver Requested' and 'Rejected' in the table "MO_CLAIM_DOC_WAIVER", then the query should return the status for that document as "Waiver Requested" The reason for such a requirement is so that, user should not be allowed to make a new waiver request if there is a waiver request that is yet to be approved or rejected.

I have created the following query. But my query fails when there are 2 records in the table MO_CLAIM_DOC_WAIVER with statuses 'Waiver Requested' and 'Rejected'. Please provide a solution either using SQL or Procedure.

    select distinct b.doc_id, c.doc_type_name, b.stage, b.is_mandatory, b.queue_id, case when a.status = 'Waiver Requested' then 'Waiver Requested' else 'Pending' end as status
    FROM 
    (SELECT tpd.doc_id, tpd.stage, tpd.is_mandatory, tpdq.queue_id
     FROM mo_theft_pvt_documents tpd , mo_theft_pvt_doc_queue tpdq
     WHERE tpd.is_mandatory='true' and tpd.doc_id  = tpdq.doc_id AND tpdq.queue_id =:queue_id AND tpd.doc_id NOT IN (SELECT doc_id FROM mo_claim_documents where status  ='Active'and claim_ref_no =:claim_ref_no UNION all SELECT doc_id from mo_claim_doc_waiver WHERE claim_ref_no =:claim_ref_no AND status = 'Waived')
    ) b
    LEFT JOIN  mo_claim_doc_waiver a on b.doc_id=a.doc_id
    left join mo_document_master c on b.doc_id=c.doc_id
    order by 1;

Answer 1

You can "rank" different statuses based on your logic and then pick the latest status out of them. You can use the logic similar to the one below, which pushes down all "rejects".

Make sure you include all other possible statuses if you care about the order for the rest of them.

with t1 as
( select 1 id, 'Waiver Requested'        status from dual union all
  select 1 id, 'Rejected'        status from dual union all
  select 2 id, 'Waiver Requested'        status from dual union all
  select 2 id, 'Waiver Requested'        status from dual union all
  select 3 id, 'Rejected'                status from dual union all
  select 3 id, 'Rejected'                status from dual union all
  select 4 id, 'Waiver Requested'        status from dual union all
  select 4 id, 'Cancelled'               status from dual
)
select id,
       status,
       max(status) KEEP (DENSE_RANK FIRST
                         order by (case when status ='Rejected' then -1
                                            else 1
                                       end) desc)
          over (partition by id) final_status
  from t1

        ID STATUS           FINAL_STATUS
---------- ---------------- ----------------
         1 Waiver Requested Waiver Requested
         1 Rejected         Waiver Requested
         2 Waiver Requested Waiver Requested
         2 Waiver Requested Waiver Requested
         3 Rejected         Rejected
         3 Rejected         Rejected
         4 Waiver Requested Waiver Requested
         4 Cancelled        Waiver Requested