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..
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)
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
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