1: SELECT MVADMP_LENDER.SELR_SRVR_NO AS [Lender Number], MVADMP_LENDER.NAME AS [Lender Name], MVADMP_STRUCTURED_FACILITY.DL_NME AS [CF Name], MVADMP_POOL.POOL_NO AS [Pool Number], MVADMP_POOL.SECU_FACE_VAL_AMT AS UPB, MVADMP_SECURITY.CUSIP_NO AS [CUSIP#], IIf(MVADMP_EXECUTION_TYPE.VALUE="MBS","Fixed Rate",MVADMP_EXECUTION_TYPE.VALUE) AS Execution, "fed" AS Agent, MVADMP_SECURITY.PUR_PRC_PCT AS PurchasePrice, [qry_Wiring Instruction-DVP].[DVP Wire Face Amount]-[qry_Wiring Instruction-DVP].[Proceeds Amount] AS Discount, [qry_Wiring Instruction-DVP].[Proceeds Amount] AS Proceeds, MVADMP_SECURITY.BK_ENT_DT AS [Wire Date], MVADMP_SECURITY.ISS_DT AS [Issue Date], MVADMP_POOL.LAST_LN_MTRY_DT AS [Maturity Date], MVADMP_POOL_STATISTICS.WT_AVG_MTRY_TERM AS Term, MVADMP_POOL.POOL_PRFX_CD AS Prefix, MVADMP_POOL_STATISTICS.WT_AVG_GNTY_FEE_RT AS [Guaranty Fee], Min(MVADMP_LOAN.SRVG_FEE_RT_PCT) AS Servicing, First(MVADMP_LOAN.LQDY_FEE_PCT) AS [Liquidity Fee], IIf(MVADMP_POOL_STATISTICS.LN_CNT>1,MVADMP_POOL_STATISTICS.LN_CNT & " Loans",MVADMP_LOAN.FM_LN_NO) AS [Fannie Mae Loan #], IIf(MVADMP_STRUCTURED_FACILITY_TYPE.value Is Not Null,MVADMP_STRUCTURED_FACILITY_TYPE.value,MVADMP_TRANSACTION_TYPE.value) AS Structure, [CF Info].[MSFMS Deal ID] AS Code, MVADMP_SECURITY.TRD_DT AS [Commitment Date], MVADMP_STRUCTURED_FACILITY.DL_ID, MVADMP_DEAL_TERMS.CLSNG_DT, MVADMP_POOL_STATISTICS.LN_CNT, [tbl_Security Meta Status].[Meta Status], MVADMP_SECURITY_STATUS_TYPE.VALUE
FROM (((((((((((((((MVADMP_LENDER RIGHT JOIN MVADMP_STRUCTURED_FACILITY ON MVADMP_LENDER.SELR_SRVR_NO = MVADMP_STRUCTURED_FACILITY.SELR_SRVR_NO) RIGHT JOIN MVADMP_SECURITY ON MVADMP_STRUCTURED_FACILITY.DL_ID = MVADMP_SECURITY.DL_ID) RIGHT JOIN MVADMP_POOL ON MVADMP_SECURITY.CUSIP_NO = MVADMP_POOL.CUSIP_NO) LEFT JOIN MVADMP_DEAL_TERMS ON MVADMP_STRUCTURED_FACILITY.DL_ID = MVADMP_DEAL_TERMS.DL_ID) LEFT JOIN MVADMP_TRANSACTION_TYPE ON MVADMP_STRUCTURED_FACILITY.TRAN_TYP_ID = MVADMP_TRANSACTION_TYPE.TRAN_TYP_ID) LEFT JOIN MVADMP_WIRING_INSTRUCTION ON MVADMP_SECURITY.CUSIP_NO = MVADMP_WIRING_INSTRUCTION.CUSIP_NO) LEFT JOIN MVADMP_POOL_STATISTICS ON MVADMP_POOL.POOL_NO = MVADMP_POOL_STATISTICS.POOL_NO) LEFT JOIN MVADMP_EXECUTION_TYPE ON MVADMP_SECURITY.EXCN_TYP_ID = MVADMP_EXECUTION_TYPE.EXCN_TYP_ID) LEFT JOIN MVADMP_STRUCTURED_FACILITY_TYPE ON MVADMP_STRUCTURED_FACILITY.STRCD_FAC_TYP_ID = MVADMP_STRUCTURED_FACILITY_TYPE.STRCD_FAC_TYP_ID) LEFT JOIN MVADMP_DEAL_STATUS_TYPE ON MVADMP_STRUCTURED_FACILITY.DL_STAT_TYP_ID = MVADMP_DEAL_STATUS_TYPE.DL_STAT_TYP_ID) LEFT JOIN MVADMP_SECURITY_STATUS_TYPE ON MVADMP_SECURITY.SECU_STAT_TYP_ID = MVADMP_SECURITY_STATUS_TYPE.SECU_STAT_TYP_ID) LEFT JOIN MVADMP_LOAN ON MVADMP_POOL.POOL_NO = MVADMP_LOAN.POOL_NO) LEFT JOIN [CF Info] ON MVADMP_SECURITY.DL_ID = [CF Info].[MSFMS Deal ID]) LEFT JOIN [qry_Wiring Instruction-DVP] ON MVADMP_SECURITY.CUSIP_NO = [qry_Wiring Instruction-DVP].[CUSIP Number]) LEFT JOIN [qry_Wiring Instruction-Free] ON MVADMP_SECURITY.CUSIP_NO = [qry_Wiring Instruction-Free].[CUSIP Number]) LEFT JOIN [tbl_Security Meta Status] ON MVADMP_SECURITY.SECU_STAT_TYP_ID = [tbl_Security Meta Status].SECU_STAT_TYP_ID
GROUP BY MVADMP_LENDER.SELR_SRVR_NO, MVADMP_LENDER.NAME, MVADMP_STRUCTURED_FACILITY.DL_NME, MVADMP_POOL.POOL_NO, MVADMP_POOL.SECU_FACE_VAL_AMT, MVADMP_SECURITY.CUSIP_NO, IIf(MVADMP_EXECUTION_TYPE.VALUE="MBS","Fixed Rate",MVADMP_EXECUTION_TYPE.VALUE), "fed", MVADMP_SECURITY.PUR_PRC_PCT, [qry_Wiring Instruction-DVP].[DVP Wire Face Amount]-[qry_Wiring Instruction-DVP].[Proceeds Amount], [qry_Wiring Instruction-DVP].[Proceeds Amount], MVADMP_SECURITY.BK_ENT_DT, MVADMP_SECURITY.ISS_DT, MVADMP_POOL.LAST_LN_MTRY_DT, MVADMP_POOL_STATISTICS.WT_AVG_MTRY_TERM, MVADMP_POOL.POOL_PRFX_CD, MVADMP_POOL_STATISTICS.WT_AVG_GNTY_FEE_RT, IIf(MVADMP_POOL_STATISTICS.LN_CNT>1,MVADMP_POOL_STATISTICS.LN_CNT & " Loans",MVADMP_LOAN.FM_LN_NO), IIf(MVADMP_STRUCTURED_FACILITY_TYPE.value Is Not Null,MVADMP_STRUCTURED_FACILITY_TYPE.value,MVADMP_TRANSACTION_TYPE.value), [CF Info].[MSFMS Deal ID], MVADMP_SECURITY.TRD_DT, MVADMP_STRUCTURED_FACILITY.DL_ID, MVADMP_DEAL_TERMS.CLSNG_DT, MVADMP_POOL_STATISTICS.LN_CNT, [tbl_Security Meta Status].[Meta Status], MVADMP_SECURITY_STATUS_TYPE.VALUE
ORDER BY MVADMP_POOL.POOL_NO;
3:
Name Type
dbo_CommtPlus 4
dbo_MtgLn 4
MVADMP_ACCRUAL_RATE_METHOD_TYPE 4
MVADMP_ADDITION_TEST_TYPE 4
MVADMP_ADDL_COLLATERAL_ALLOWED_TYPE 4
MVADMP_ALLOCABLE_FACILITY_FRQY_TYPE 4
MVADMP_ALLOWED_EXECUTION_TYPE 4
MVADMP_AMI_THRESHOLD_PERCENT_TYPE 4
MVADMP_AMORTIZATION_TERM_TYPE 4
MVADMP_APN_POOL_NUMBER 4
MVADMP_ASMP_ALWD_TYPE 4
MVADMP_BORROWER_LOAN_PRPS_DTL_CD_TYPE 4
MVADMP_BORROWER_LOAN_PURPOSE_TYPE 4
MVADMP_BORROWER_UP_ALLOWED_TYPE 4
MVADMP_BUILDING_TYPE 4
MVADMP_BUSINESS_EVENT_TYPE 4
MVADMP_CASH_BOND 4
MVADMP_CERTIFIED_PROP_VALUES 4
MVADMP_CF_APP_ENV 4
MVADMP_CF_CONTACT 4
MVADMP_CF_CONTACT_TYPE 4
MVADMP_CLOSED_LOAN 4
MVADMP_CLOSED_POOL 4
MVADMP_COLLATERAL 4
MVADMP_COLLATERAL_GROUP 4
MVADMP_COLLATERAL_GROUP_STATE_TYPE 4
MVADMP_COLLATERAL_PROPERTY 4
MVADMP_COLLATERAL_PURPOSE_TYPE 4
MVADMP_COLLATERAL_STATE_TYPE 4
MVADMP_COLLATERAL_TYPE 4
MVADMP_COLT_GROUP_CONTRACT_COMPLIANCE 4
MVADMP_COLT_REF_NO_RANGE 4
MVADMP_COMPLEX_FEE 4
MVADMP_COMPLEX_FEE_PARAMETER_SET 4
MVADMP_COMPLEX_FEE_TEMPLATE_TYPE 4
MVADMP_COMPLEX_FEE_TYPE 4
MVADMP_CONTACT 4
MVADMP_CONTACT_ROLES 4
MVADMP_CONTACT_TYPE 4
MVADMP_COUNTERPARTY_TYPE 4
MVADMP_CTRT_CPNC_ADDN_TEST_TYPE_POOL 4
MVADMP_CTRT_CPNC_ADDN_TEST_TYPE_PROP 4
MVADMP_CTRT_CPNC_ALCB_FAC_TYPE 4
MVADMP_DEAL_CONTACTS 4
MVADMP_DEAL_PARTICIPANT 4
MVADMP_DEAL_STATUS_TYPE 4
MVADMP_DEAL_TERMS 4
MVADMP_DEAL_TERMS_AMRT_TERM_TYPE 4
MVADMP_DEAL_TERMS_COLLATERAL_TYPE 4
MVADMP_DEAL_TERMS_EXECUTION_TYPE 4
MVADMP_DEFEASANCE_TYPE 4
MVADMP_DWELLING_UNIT 4
MVADMP_ELDERLY_PROPERTY_TYPE 4
MVADMP_ESCROW_REVALUE_FREQUENCY_TYPE 4
MVADMP_EXECUTION_TYPE 4
MVADMP_FEE_GROUP 4
MVADMP_FEE_GROUP_STATE_TYPE 4
MVADMP_FEE_LEDGER 4
MVADMP_FHA_VA_SECTION_ACT_TYPE 4
MVADMP_FIXED_PREPAY_SCHEDULE_TYPE 4
MVADMP_FM_HOLIDAY 4
MVADMP_FM_INVESTOR_BORROWER_TYPE 4
MVADMP_GUARANTY_TYPE 4
MVADMP_HEDGE 4
MVADMP_HEDGE_INDEX_TYPE 4
MVADMP_HEDGE_PROPERTY 4
MVADMP_HEDGE_STATE_TYPE 4
MVADMP_INT_RATE_HEDGE_SHARING_TYPE 4
MVADMP_INTEREST_ACCRUAL_METHOD_TYPE 4
MVADMP_INTEREST_TYPE 4
MVADMP_LAND_OWNERSHIP_RIGHTS_TYPE 4
MVADMP_LARGE_BORROWER 4
MVADMP_LEGAL_CONFORMANCE_STATUS_TYPE 4
MVADMP_LENDER 4
MVADMP_LETTER_OF_CREDIT 4
MVADMP_LIEN_PRIORITY_TYPE 4
MVADMP_LOAN 4
MVADMP_LOAN_NO_RANGE 4
MVADMP_LOAN_PLANNED_REHAB_TYPE 4
MVADMP_LOAN_PREPAYMENT 4
MVADMP_LOAN_SPCL_FEAT_TYPE 4
MVADMP_LOAN_SPCL_FEAT_TYPE_DRVD 4
MVADMP_LOAN_TYPE 4
MVADMP_LOC_COLLATERAL_GROUP 4
MVADMP_LOSS_SHARING_LEVEL_TYPE 4
MVADMP_MESSAGE_EVENT_LOG 4
MVADMP_MESSAGE_TYPE 4
MVADMP_MONITORING_REQD_FRQY_TYPE 4
MVADMP_MONTHLY_DMBS_FEES_CLCTD_TYPE 4
MVADMP_OPERATING_STATEMENT 4
MVADMP_OPERATING_STATEMENT_TYPE 4
MVADMP_OTHER_COLLATERAL 4
MVADMP_OTHER_COLLATERAL_COLT_GROUP 4
MVADMP_OTHER_COLLATERAL_TYPE 4
MVADMP_PARTICIPANT_STAT_TYPE 4
MVADMP_PAYMENT_ELECTRIC_SOURCE_TYPE 4
MVADMP_PAYMENT_FREQUENCY_TYPE 4
MVADMP_PAYMENT_GAS_SOURCE_TYPE 4
MVADMP_POOL 4
MVADMP_POOL_CLOSE_TYPE 4
MVADMP_POOL_STATISTICS 4
MVADMP_POOL_STATUS_TYPE 4
MVADMP_PRICING_TIER_TYPE 4
MVADMP_PROP_SPCL_FEAT_TYPE 4
MVADMP_PROP_SPCL_FEAT_TYPE_DRVD 4
MVADMP_PROPERTY 4
MVADMP_PROPERTY_CONSTRUCTION_PHASE 4
MVADMP_PROPERTY_FINANCIAL_TYPE 4
MVADMP_PROVIDER 4
MVADMP_PROVIDER_RECOURSE 4
MVADMP_PROVIDER_RECOURSE_TYPE 4
MVADMP_RECOURSE_TYPE 4
MVADMP_REGION_TYPE 4
MVADMP_REMITTANCE_TYPE 4
MVADMP_ROLES 4
MVADMP_RULESET 4
MVADMP_SECURITY 4
MVADMP_SECURITY_EDIT_OVERRIDE_COMMENT 4
MVADMP_SECURITY_STATUS_TYPE 4
MVADMP_SENIOR_HOUSING_TYPE 4
MVADMP_SF_AUDIT 4
MVADMP_SPCL_DELIVERY_INSTRUCTION_TYPE 4
MVADMP_SPCL_FEATURE_TYPE 4
MVADMP_SPCL_FLOOD_HZRD_INSURANCE_TYPE 4
MVADMP_SPECIAL_NEED_TYPE 4
MVADMP_STATE_TYPE 4
MVADMP_STRUCTURED_FACILITY 4
MVADMP_STRUCTURED_FACILITY_TYPE 4
MVADMP_SUB_LEDG_ACCT 4
MVADMP_SUBSEQUENT_HEDGE_TYPE 4
MVADMP_TAX_CREDIT_PERCENT_TYPE 4
MVADMP_TRANSACTION_TYPE 4
MVADMP_VALUATION_FRQY_TYPE 4
MVADMP_VALUATION_METHOD_TYPE 4
MVADMP_WIRING_INSTRUCTION 4
MVADMP_WIRING_INSTRUCTION_TYPE 4
The back end is an Oracle database.
The query is an attempt to simulate a local table that is loaded using four action queries. The word has come down from the upper management that there will be no more local tables; all local tables must be simulated with queries going against the original tables. [CF Info] is another such query.