Help Me Build report for the Below Query....

[richiearora]

Hi Frineds,

Can you Please give me little insight how to co0nvert this below Query to a Report.

SELECT
            LOAN.*,
            TRUNC(STAT.CREATION_DATE) AS STATUS_DATE,
            STATDESC.STATUS_DESC AD_STATUS,
            EMP.FIRSTNAME || ' ' || EMP.LASTNAME AS STATUS_CHANGED_BY,
            EMP.PAYROLLID EMP_SID,
            WMEMP.SUP_NM,
            WMEMP.AVP_NM,
            ROUND(TRUNC(SYSDATE) - TRUNC(STAT.CREATION_DATE),0) AS AD_STATUS_DAYS
        FROM
        (
                /********** hWaMu Data **********/
                SELECT
                    'WAMU' AS HERITAGE,
                    'OWNED' AS OWNED_RISK,
                    NULL AS OWNED_RISK_SUB,
                    CASE
                        WHEN LOB = 'OPTION_ARM' THEN
                            'PRIME'
                        ELSE
                            LOB
                    END AS LOB,
                    LOAN_NUMBER_TXT AS LOAN_NUMBER,
                    FIRST_PRINCIPAL_BALANCE,
                    NEXT_PAYMENT_DUE_DATE,
                    LM AS LM_STATUS,
                    LM_TEMPLATE_ID,
                    LOSS_MIT_STAGE_CODE AS STAGE,
                    LOSS_MIT_PROCESSOR_ID AS PROC_ID,
                    RM.SUP_NM AS RM_SECTION_MGR,
                    RM.EMP_NAME AS RM_NAME,
                    LOSS_MIT_FILE_OWNER_CODE AS FILE_LOC,
                    UW.SUP_NM AS UW_SECTION_MGR,
                    UW.EMP_NAME AS UW_NAME,
                    LOSS_MIT_TYPE_CODE AS TYPE,
                    HMP_STAT,
                    CMP_STAT,
                    TT.TASK_ID,
                    TT.TASK_ACTUAL_CLOSE_DATE
                   
                FROM
                    BOMMGR.TB_HWAMU_MASTER_D LOAN
                    LEFT JOIN (SELECT * FROM BOMMGR.TB_COMB_EMP_ORG_D WHERE CLIENT = 156) RM ON LOAN.LOSS_MIT_PROCESSOR_ID = RM.MSP_ID
                    LEFT JOIN (SELECT * FROM BOMMGR.TB_COMB_EMP_ORG_D WHERE CLIENT = 156) UW ON LOAN.LOSS_MIT_FILE_OWNER_CODE = UW.MSP_ID
                    LEFT JOIN (SELECT A01.* FROM MC156MGR.MSP_TASK_TRACKING_D@INFODB A01
                                JOIN BOMMGR.MSP_LOAN_D_156 MSP_156 ON A01.LOAN_NUMBER= MSP_156.LOAN_NUMBER )TT ON LOAN.LOAN_NUMBER=TT.LOAN_NUMBER                   
                WHERE
                    OS_IND = 'Y'
                AND INV_TYPE = 'Y'
                AND LM = 'A'
                AND LM_TEMPLATE_ID = 'GENMOD'
               AND TT.TASK_ID IN ('INTPKG','PKGESC')
               AND TT.TASK_ACTUAL_CLOSE_DATE IS NULL
                 
                UNION
                /********** hChase Prime Data **********/
                SELECT
                    'CHASE' AS HERITAGE,
                    PRIME_OWNED_AT_RISK_IND AS OWNED_RISK,
                    PRIME_OWNED_AT_RISK_SUB_CAT AS OWNED_RISK_SUB,
                    SYS_SOURCE AS LOB,
                    ACCT_NBR_TXT AS LOAN_NUMBER,
                    UPB AS FIRST_PRINCIPAL_BALANCE,
                    NXT_DUE_DT AS NEXT_PAYMENT_DUE_DATE,
                    LOAN.LOAN_LOSS_MIT_STATUS_CODE AS LM_STATUS,
                    LM_TEMPLATE_ID,
                    LOSS_MIT_STAGE_CODE AS STAGE,
                    LOSS_MIT_PROCESSOR_ID AS PROC_ID,
                    RM.SUP_NM AS RM_SECTION_MGR,
                    RM.EMP_NAME AS RM_NAME,
                    LOSS_MIT_FILE_OWNER_CODE AS FILE_LOC,
                    UW.SUP_NM AS UW_SECTION_MGR,
                    UW.EMP_NAME AS UW_NAME,
                    LOSS_MIT_TYPE_CODE AS TYPE,
                    USER_07_POSITION_FIELD_6A AS MHA,
                    USER_07_POSITION_FIELD_7A AS BAU_PLANS,
                    TT.TASK_ID,
                    TT.TASK_ACTUAL_CLOSE_DATE
                   
                FROM
                    COAMGR.PR_TBL_D LOAN
                    JOIN BOMMGR.TB_HCHASE_LOSS_MIT_D LMIT ON LOAN.ACCT_NBR = LMIT.LOAN_NUMBER
                    JOIN MORTGMGR.MSP_USER_DEFINED_D@INFODB USR ON LMIT.LOAN_NUMBER = USR.LOAN_NUMBER
                    LEFT JOIN (SELECT * FROM BOMMGR.TB_COMB_EMP_ORG_D WHERE CLIENT = 465) RM ON LMIT.LOSS_MIT_PROCESSOR_ID = RM.MSP_ID
                    LEFT JOIN (SELECT * FROM BOMMGR.TB_COMB_EMP_ORG_D WHERE CLIENT = 465) UW ON LMIT.LOSS_MIT_FILE_OWNER_CODE = UW.MSP_ID
                    LEFT JOIN (SELECT * FROM MORTGMGR.MSP_TASK_TRACKING_D@INFODB)TT ON ACCT_NBR_TXT=TT.LOAN_NUMBER
                WHERE
                    OS_IND = 'Y'
                AND SYS_SOURCE IN ('PRIME','PRIMU')
                AND ((PRIME_OWNED_AT_RISK_IND = 'OWNED RISK')
                        OR (PRIME_OWNED_AT_RISK_IND = 'OWNED NO RISK'
                            AND PRIME_OWNED_AT_RISK_SUB_CAT = 'HELD FOR SALE'))
          AND  LOAN.LOAN_LOSS_MIT_STATUS_CODE = 'A'
         AND LM_TEMPLATE_ID = 'GENMOD'
        AND TT.TASK_ID IN ('INTPKG','PKGESC')
         AND TT.TASK_ACTUAL_CLOSE_DATE IS NULL
               

                UNION
                /********** hChase Non Prime Data **********/
                SELECT
                    'CHASE' AS HERITAGE,
                    'OWNED' AS OWNED_RISK,
                    NULL AS OWNED_RISK_SUB,
                    SYS_SOURCE AS LOB,
                    ACCT_NBR_TXT AS LOAN_NUMBER,
                    UPB AS FIRST_PRINCIPAL_BALANCE,
                    NXT_DUE_DT AS NEXT_PAYMENT_DUE_DATE,
                    LOAN.LOAN_LOSS_MIT_STATUS_CODE AS LM_STATUS,
                    LM_TEMPLATE_ID,
                    LOSS_MIT_STAGE_CODE AS STAGE,
                    LOSS_MIT_PROCESSOR_ID AS PROC_ID,
                    RM.SUP_NM AS RM_SECTION_MGR,
                    RM.EMP_NAME AS RM_NAME,
                    LOSS_MIT_FILE_OWNER_CODE AS FILE_LOC,
                    UW.SUP_NM AS UW_SECTION_MGR,
                    UW.EMP_NAME AS UW_NAME,
                    LOSS_MIT_TYPE_CODE AS TYPE,
                    USER_07_POSITION_FIELD_6A AS MHA,
                    USER_07_POSITION_FIELD_7A AS BAU_PLANS,
                    TT.TASK_ID,
                    TT.TASK_ACTUAL_CLOSE_DATE
                   
                FROM
                    COAMGR.SP_TBL_D LOAN
                    JOIN BOMMGR.TB_HCHASE_LOSS_MIT_D LMIT ON LOAN.ACCT_NBR = LMIT.LOAN_NUMBER
                    JOIN MORTGMGR.MSP_USER_DEFINED_D@INFODB USR ON LMIT.LOAN_NUMBER = USR.LOAN_NUMBER
                    LEFT JOIN (SELECT * FROM BOMMGR.TB_COMB_EMP_ORG_D WHERE CLIENT = 465) RM ON LMIT.LOSS_MIT_PROCESSOR_ID = RM.MSP_ID
                    LEFT JOIN (SELECT * FROM BOMMGR.TB_COMB_EMP_ORG_D WHERE CLIENT = 465) UW ON LMIT.LOSS_MIT_FILE_OWNER_CODE = UW.MSP_ID
                    LEFT JOIN (SELECT * FROM MORTGMGR.MSP_TASK_TRACKING_D@INFODB)TT ON ACCT_NBR_TXT=TT.LOAN_NUMBER
                WHERE
                    OS_IND = 'Y'
                AND INVOWNED = 'Y'
                 AND SYS_SOURCE IN ('SUBPRM')
                 AND  LOAN.LOAN_LOSS_MIT_STATUS_CODE = 'A'
                 AND LM_TEMPLATE_ID = 'GENMOD'
            ) LOAN
            LEFT JOIN 

(   SELECT
                                *
                            FROM
                                (   SELECT
                                        ROW_NUMBER() OVER (PARTITION BY INBOX.ACCT_NBR ORDER BY INBOX.CREATION_DATE DESC NULLS LAST) AS MAX_LOAN,
                                        INBOX.*
                                    FROM
                                        CHAPSAPP.MOD_INBOX INBOX
                                     )
                            WHERE
                                MAX_LOAN = 1) INBOX ON LOAN.LOAN_NUMBER = INBOX.ACCT_NBR
            LEFT JOIN CHAPSAPP.MOD_FORM FRM ON FRM.INBOX_ID = INBOX.INBOX_ID
            LEFT JOIN

( SELECT    *
                        FROM
                        (
                        SELECT
                            ROW_NUMBER() OVER (PARTITION BY STAT.FORM_ID ORDER BY STAT.CREATION_DATE DESC NULLS LAST) AS MAX_STAT,
                            STAT.*
                        FROM
                            CHAPSAPP.MOD_FORM_STATUS_JOIN STAT
                        WHERE
                            STAT.STATUS_ACTIVE = 'Y'
      )
                       



WHERE
                            MAX_STAT = 1) STAT ON STAT.FORM_ID = FRM.FORM_ID
            LEFT JOIN CHAPSAPP.MOD_STATUS STATDESC ON STATDESC.STATUS_ID = STAT.STATUS_ID
            LEFT JOIN DFLTEQTYANLMGR.ORG_EMPLOYEE@BUSG01DB EMP ON EMP.EMPLOYEEID = STAT.CREATEDBYID
            LEFT JOIN (SELECT DISTINCT EMP_NAME, SUP_NM, AVP_NM, VP_NM, EMP_SID FROM BOMMGR.TB_COMB_EMP_ORG_D) WMEMP ON WMEMP.EMP_SID = EMP.PAYROLLID
        WHERE
            LM_STATUS = 'A'
        AND LM_TEMPLATE_ID = 'GENMOD'
        AND TASK_ID IN ('INTPKG','PKGESC')
        AND TASK_ACTUAL_CLOSE_DATE IS NULL

Example I need to understand what can be query subjects and all..

[blom0344]

Even if you could rewrite this to fit into a model based on query subjects, then it would still be too report-specific to be ever be reused for anything else.
I would rewrite the  * parts to full projection lists and then add it as a standalone - native - query subject  'as is' within a model. Alternative is to rewrite it to a view definition and add it as a view to the database. You also would need to add a lot of comments to the code for future reference (like why you add certain filters to the where clauses)

[richiearora]

Hi blom ,

thanks for your Reply.

I totoally get your Point.

Here is my Requirement, The SQL generated the Records querying respective databases.

All i want to undetstand is the Joins and Union how can it be implemented.

yes we will have to model the Package Accordingly..

I am just looking at a an idea to understand what does this Query actually Do?

thanks

[blom0344]

We can't tell exactly what this query does. I have no clue what the tables contain or why filters are added pointing to certain values in the where clauses. The query itself is the ultimate product of 'some' business requirement. Understanding the requirement will give you the clues to interpreting the SQL generated