Skip to Content
0
Former Member
Jul 02, 2015 at 05:34 AM

Security on Standalone tables in Universe

20 Views

Hi All,

I have table on an existing universe named as Report Data which is having column Transref .When I am pulling this column on report .it generates Following type of query :

SELECT

PSD_REPORT_DATA_MG_TB_DERIVED.TRANSACTION_REFERENCE

FROM

PSD_PRODUCT_TYPE_REF,

( SELECT

USER_KEY_SEQ,

USER_ID,

SUPER_USER_FLAG,

ADMIN_USER_FLAG,

PSD_USER_FLAG,

MLAR_USER_FLAG,

RMAR_USER_FLAG,

START_DATE,

ACTIVE_FLAG,

SUBMITTER_FLAG,

TO_DATE(TO_CHAR(LAST_UPDATED,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS') AS LAST_UPDATED,

END_DATE,

FIRST_NAME,

LAST_NAME

FROM IRR_USER_TB

WHERE PSD_USER_FLAG = 'Y' AND ACTIVE_FLAG = 'Y' AND END_DATE IS NULL) IRR_USER_TB_DERIVED,

( SELECT

FIRM_FSA_REFERENCE,

FIRM_S_NAME,

FIRM_NAME,

SUBMITTING_DEPT

FROM IRR_FIRM_REF) IRR_FIRM_REF_DERIVED,

( SELECT PSD_USER_FIRM_TB.USER_KEY,PSD_USER_FIRM_TB.FIRM_FSA_REFERENCE,PSD_USER_FIRM_TB.PRODUCT_ID,TO_DATE(TO_CHAR(PSD_USER_FIRM_TB.LAST_UPDATED,'DD/MM/YYYY'),'DD/MM/YYYY') AS LAST_UPDATED_DATE FROM PSD_USER_FIRM_TB) PSD_USER_FIRM_TB_DERIVED,

( SELECT

FSA_YEAR, FSA_PERIOD, TRANSACTION_REFERENCE,

VERSION, PRODUCT_ID, SOURCE_SYSTEM, nvl(BRAND_ID, 'NA') AS BRAND_ID,

REPORT_IDENTIFIER, SUBMITTING_FIRM, SUBMITTING_DEPARTMENT,

FIRM_FSA_REFERENCE, CANCELLATION, PRINCIPAL_OR_NETWORK_FSA_REF,

ACCT_OPEN_DATE, ADVISED_SALE_FLAG, TYPE_INT_RATE_IND,

MTGE_CHAR1_IND, MTGE_CHAR2_IND, MTGE_CHAR3_IND,

MTGE_CHAR4_IND, MTGE_CHAR5_IND, PROPERTY_POST_CODE,

TYPE_BORROWER_IND, REPAY_METHOD_IND, MTGE_TERM,

LOAN_AMT, PROPERTY_VALUE, INCOME_BASIS_IND,

MAIN_DOB_DATE, REMTGE_PURPOSE_IND, MAIN_EMP_STATUS_IND,

TOTAL_GROSS_INCOME, INCOME_VERIFICATION_FLAG, MAIN_BORROWER_CCJ_VALUE,

SECOND_BORROWER_CCJ_VALUE, MAIN_BORR_IMP_CRED_HIST1_IND, MAIN_BORR_IMP_CRED_HIST2_IND,

MAIN_BORR_IMP_CRED_HIST3_IND, SECOND_BORR_IMP_CRED_HIST1_IND, SECOND_BORR_IMP_CRED_HIST2_IND,

SECOND_BORR_IMP_CRED_HIST3_IND, INITIAL_GROSS_INT_RATE, INCENTIVE_RATE_ENDS_DATE,

ERC_ENDS_DATE, PURCHASE_PRICE, TYPE_DWELLING_IND,

NUM_HABITABLE_ROOMS, NUM_BEDROOMS, NEW_DWELLING_FLAG,

GARAGE_INC_FLAG, MTGE_PROTECTION_PLAN_INC_FLAG, IS_VALID_FLAG,

COMMENT_FLD, ACTIVE_VERSION_FLAG, SENT_FLAG,

READY_TO_SEND_FLAG,

UPDATED_BY,

TO_DATE(TO_CHAR(SENT_DATE,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS') AS SENT_DATE,

TO_DATE(TO_CHAR(ADDED_DATE,'DD/MM/YYYY HH24:MI:SS'),'DD/MM/YYYY HH24:MI:SS') AS ADDED_DATE

FROM IRRPSD_OWNER.PSD_REPORT_DATA_MG_TB) PSD_REPORT_DATA_MG_TB_DERIVED

WHERE

( PSD_USER_FIRM_TB_DERIVED.PRODUCT_ID=PSD_REPORT_DATA_MG_TB_DERIVED.PRODUCT_ID and PSD_USER_FIRM_TB_DERIVED.FIRM_FSA_REFERENCE=PSD_REPORT_DATA_MG_TB_DERIVED.SUBMITTING_FIRM )

AND ( IRR_USER_TB_DERIVED.USER_KEY_SEQ=PSD_USER_FIRM_TB_DERIVED.USER_KEY )

AND ( IRR_FIRM_REF_DERIVED.FIRM_FSA_REFERENCE=PSD_USER_FIRM_TB_DERIVED.FIRM_FSA_REFERENCE )

AND ( IRR_USER_TB_DERIVED.USER_ID = @Variable('BOUSER') )

AND ( PSD_PRODUCT_TYPE_REF.PRODUCT_ID=PSD_USER_FIRM_TB_DERIVED.PRODUCT_ID )

************************

However I have another table named as Report Data007 having column like Transref .When I pull this column on report ..It generates following SQL :

Select Transref from Report Data 007

But I need t generate the Above SQL as in case 1 .

Please help me resolving this issue ...How the query in case 1 is created ..

Appreciate your help.