Custom column not getting populated in W_INT_ORG_D

Question

Hi Team,

We are implementing BIAPPS 11.1.1.8.1 with ODI 11.1.1.7.0 as our ETL.  As part of our requirement we have added a new custom column to W_INT_ORG_DS and W_INT_ORG_D table. The column name X_Process.

We have done the reverse engineering and we have used out of the box Universal adaptor SIL mapping to load W_INT_ORG_D table. I have created a copy of the interface and added the expression to new column as well in  the OOTB ODI interface. After executing the interface, we are seeing all columns in W_INT_ORG_D are getting populated except new column which has NULL values. Also I need to mention that this column has data in W_INT_ORG_DS table.

Please advise why the new custom column is getting populated, am I missing something.

Thanks,

Dany

Thomas Dodds · Answer

You'll have to modify the mapping that handles the outer query to have it selected as well.   Usually these things are like onions - many layers!

User_6LJME · Answer

Hi Thomas, Yes I have made the changes to the SILOS mapping also. But what I observed is that in the step Insert flow into I$ table, the column is part of the sub-query, but not getting selected in the outer query, Please advise what might be causing this. /* DETECTION_STRATEGY = NOT_EXISTS */ insert /*+ append */ into DEV_DW.I$_7171500_1 ( ......... select DISTINCT SQ_W_INT_ORG_DS.ORG_NUM ORG_NUM, SQ_W_INT_ORG_DS.W_CURR_CODE W_CURR_CODE, SQ_W_INT_ORG_DS.ST_ADDRESS1 ST_ADDRESS1, SQ_W_INT_ORG_DS.ST_ADDRESS2 ST_ADDRESS2, SQ_W_INT_ORG_DS.CITY_CODE CITY_CODE, SQ_W_INT_ORG_DS.POSTAL_CODE POSTAL_CODE, SQ_W_INT_ORG_DS.STATE_PROV_CODE STATE_PROV_CODE, SQ_W_INT_ORG_DS.COUNTRY_REGION_CODE COUNTRY_REGION_CODE, SQ_W_INT_ORG_DS.PHONE_NUM PHONE_NUM, SQ_W_INT_ORG_DS.FAX_NUM FAX_NUM, SQ_W_INT_ORG_DS.EMAIL_ADDRESS EMAIL_ADDRESS, SQ_W_INT_ORG_DS.WEB_ADDRESS WEB_ADDRESS, SQ_W_INT_ORG_DS.DIVN_FLG DIVN_FLG, SQ_W_INT_ORG_DS.BU_FLG BU_FLG, SQ_W_INT_ORG_DS.SALES_GROUP_FLG SALES_GROUP_FLG, SQ_W_INT_ORG_DS.PRTNR_FLG PRTNR_FLG, SQ_W_INT_ORG_DS.INTERNAL_FLG INTERNAL_FLG, SQ_W_INT_ORG_DS.CNTCT_REP_ORG_FLG CNTCT_REP_ORG_FLG, SQ_W_INT_ORG_DS.BUSINESS_AREA_FLG BUSINESS_AREA_FLG, SQ_W_INT_ORG_DS.COMPANY_FLG COMPANY_FLG, SQ_W_INT_ORG_DS.LEGAL_ENTITY_FLG LEGAL_ENTITY_FLG, SQ_W_INT_ORG_DS.OPERATING_UNIT_FLG OPERATING_UNIT_FLG, SQ_W_INT_ORG_DS.BUSINESS_GROUP_FLG BUSINESS_GROUP_FLG, SQ_W_INT_ORG_DS.INV_ORG_FLG INV_ORG_FLG, SQ_W_INT_ORG_DS.HR_ORG_FLG HR_ORG_FLG, SQ_W_INT_ORG_DS.GOVT_REPT_ENTITY_FLG GOVT_REPT_ENTITY_FLG, SQ_W_INT_ORG_DS.BALANCING_ENTITY_FLG BALANCING_ENTITY_FLG, SQ_W_INT_ORG_DS.ASSET_ORG_FLG ASSET_ORG_FLG, SQ_W_INT_ORG_DS.PROJECT_ORG_FLG PROJECT_ORG_FLG, SQ_W_INT_ORG_DS.CONTROL_AREA_FLG CONTROL_AREA_FLG, SQ_W_INT_ORG_DS.FIN_AREA_FLG FIN_AREA_FLG, SQ_W_INT_ORG_DS.VALUATION_AREA_FLG VALUATION_AREA_FLG, SQ_W_INT_ORG_DS.SALES_AREA_FLG SALES_AREA_FLG, SQ_W_INT_ORG_DS.MARKETING_ORG_FLG MARKETING_ORG_FLG, SQ_W_INT_ORG_DS.PURCH_ORG_FLG PURCH_ORG_FLG, SQ_W_INT_ORG_DS.SALES_ORG_FLG SALES_ORG_FLG, SQ_W_INT_ORG_DS.PAYABLES_ORG_FLG PAYABLES_ORG_FLG, SQ_W_INT_ORG_DS.RECEIVABLES_ORG_FLG RECEIVABLES_ORG_FLG, SQ_W_INT_ORG_DS.SERVICE_ORG_FLG SERVICE_ORG_FLG, SQ_W_INT_ORG_DS.BRANCH_FLG BRANCH_FLG, SQ_W_INT_ORG_DS.ORG_TYPE_CODE ORG_TYPE_CODE, SQ_W_INT_ORG_DS.MGR_NAME MGR_NAME, SQ_W_INT_ORG_DS.PRTNR_TIER_CODE PRTNR_TIER_CODE, SQ_W_INT_ORG_DS.PRTNR_TYPE_CODE PRTNR_TYPE_CODE, SQ_W_INT_ORG_DS.PTSHP_STAGE_CODE PTSHP_STAGE_CODE, SQ_W_INT_ORG_DS.PR_PTSHP_MKTSEG PR_PTSHP_MKTSEG, SQ_W_INT_ORG_DS.PR_ORG_TRGT_MKT PR_ORG_TRGT_MKT, SQ_W_INT_ORG_DS.PRTNR_SALES_RANK PRTNR_SALES_RANK, SQ_W_INT_ORG_DS.PRTNRSHP_START_DT PRTNRSHP_START_DT, SQ_W_INT_ORG_DS.PTSHP_END_DT PTSHP_END_DT, SQ_W_INT_ORG_DS.PTSHP_PRTNR_ACCNT PTSHP_PRTNR_ACCNT, SQ_W_INT_ORG_DS.PTSHP_RENEWAL_DT PTSHP_RENEWAL_DT, SQ_W_INT_ORG_DS.PTSHP_SAT_INDEX PTSHP_SAT_INDEX, SQ_W_INT_ORG_DS.VIS_PR_BU_ID VIS_PR_BU_ID, SQ_W_INT_ORG_DS.VIS_PR_POS_ID VIS_PR_POS_ID, SQ_W_INT_ORG_DS.POSITION_DH_WID POSITION_DH_WID, SQ_W_INT_ORG_DS.INT_ORG_ATTR1_CHAR INT_ORG_ATTR1_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR2_CHAR INT_ORG_ATTR2_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR3_CHAR INT_ORG_ATTR3_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR4_CHAR INT_ORG_ATTR4_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR5_CHAR INT_ORG_ATTR5_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR6_CHAR INT_ORG_ATTR6_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR7_CHAR INT_ORG_ATTR7_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR8_CHAR INT_ORG_ATTR8_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR9_CHAR INT_ORG_ATTR9_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR10_CHAR INT_ORG_ATTR10_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR11_CHAR INT_ORG_ATTR11_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR12_CHAR INT_ORG_ATTR12_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR13_CHAR INT_ORG_ATTR13_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR14_CHAR INT_ORG_ATTR14_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR15_CHAR INT_ORG_ATTR15_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR16_CHAR INT_ORG_ATTR16_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR17_CHAR INT_ORG_ATTR17_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR18_CHAR INT_ORG_ATTR18_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR19_CHAR INT_ORG_ATTR19_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR20_CHAR INT_ORG_ATTR20_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR21_CHAR INT_ORG_ATTR21_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR22_CHAR INT_ORG_ATTR22_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR23_CHAR INT_ORG_ATTR23_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR24_CHAR INT_ORG_ATTR24_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR25_CHAR INT_ORG_ATTR25_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR26_CHAR INT_ORG_ATTR26_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR27_CHAR INT_ORG_ATTR27_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR28_CHAR INT_ORG_ATTR28_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR29_CHAR INT_ORG_ATTR29_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR30_CHAR INT_ORG_ATTR30_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR1_NUM INT_ORG_ATTR1_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR2_NUM INT_ORG_ATTR2_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR3_NUM INT_ORG_ATTR3_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR4_NUM INT_ORG_ATTR4_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR5_NUM INT_ORG_ATTR5_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR6_NUM INT_ORG_ATTR6_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR7_NUM INT_ORG_ATTR7_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR8_NUM INT_ORG_ATTR8_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR9_NUM INT_ORG_ATTR9_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR10_NUM INT_ORG_ATTR10_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR11_NUM INT_ORG_ATTR11_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR12_NUM INT_ORG_ATTR12_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR13_NUM INT_ORG_ATTR13_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR14_NUM INT_ORG_ATTR14_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR15_NUM INT_ORG_ATTR15_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR16_NUM INT_ORG_ATTR16_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR17_NUM INT_ORG_ATTR17_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR18_NUM INT_ORG_ATTR18_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR19_NUM INT_ORG_ATTR19_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR20_NUM INT_ORG_ATTR20_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR1_DATE INT_ORG_ATTR1_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR2_DATE INT_ORG_ATTR2_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR3_DATE INT_ORG_ATTR3_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR4_DATE INT_ORG_ATTR4_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR5_DATE INT_ORG_ATTR5_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR6_DATE INT_ORG_ATTR6_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR7_DATE INT_ORG_ATTR7_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR8_DATE INT_ORG_ATTR8_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR9_DATE INT_ORG_ATTR9_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR10_DATE INT_ORG_ATTR10_DATE, SQ_W_INT_ORG_DS.CREATED_ON_DT CREATED_ON_DT, SQ_W_INT_ORG_DS.CHANGED_ON_DT CHANGED_ON_DT, SQ_W_INT_ORG_DS.AUX1_CHANGED_ON_DT AUX1_CHANGED_ON_DT, SQ_W_INT_ORG_DS.AUX2_CHANGED_ON_DT AUX2_CHANGED_ON_DT, SQ_W_INT_ORG_DS.AUX3_CHANGED_ON_DT AUX3_CHANGED_ON_DT, SQ_W_INT_ORG_DS.AUX4_CHANGED_ON_DT AUX4_CHANGED_ON_DT, SQ_W_INT_ORG_DS.SRC_EFF_FROM_DT SRC_EFF_FROM_DT, SQ_W_INT_ORG_DS.SRC_EFF_TO_DT SRC_EFF_TO_DT, 'N' DELETE_FLG, SYSDATE W_INSERT_DT, SQ_W_INT_ORG_DS.DATASOURCE_NUM_ID DATASOURCE_NUM_ID, #BIAPPS.ETL_PROC_WID ETL_PROC_WID, SQ_W_INT_ORG_DS.INTEGRATION_ID INTEGRATION_ID, SQ_W_INT_ORG_DS.TENANT_ID TENANT_ID, SQ_W_INT_ORG_DS.X_CUSTOM X_CUSTOM, SQ_W_INT_ORG_DS.REGISTRATION_NUM REGISTRATION_NUM, SQ_W_INT_ORG_DS.PROJECT_EXP_ORG_FLG PROJECT_EXP_ORG_FLG, SQ_W_INT_ORG_DS.PROJECT_BU_FLG PROJECT_BU_FLG, SQ_W_INT_ORG_DS.SET_ID SET_ID, SQ_W_INT_ORG_DS.CONTRACT_BU_FLG CONTRACT_BU_FLG, SQ_W_INT_ORG_DS.C_CITY_CODE C_CITY_CODE, SQ_W_INT_ORG_DS.C_COUNTY_CODE C_COUNTY_CODE, SQ_W_INT_ORG_DS.COUNTY_CODE COUNTY_CODE, SQ_W_INT_ORG_DS.C_STATE_PROV_CODE C_STATE_PROV_CODE, SQ_W_INT_ORG_DS.W_COUNTRY_CODE W_COUNTRY_CODE, SQ_W_INT_ORG_DS.C_COUNTRY_REGION_CODE C_COUNTRY_REGION_CODE, SQ_W_INT_ORG_DS.ORGANIZATION_CODE ORGANIZATION_CODE, SQ_W_INT_ORG_DS.SCD1_WID SCD1_WID, SQ_W_INT_ORG_DS.RETAIL_FLG RETAIL_FLG, SQ_W_INT_ORG_DS.ENTERPRISE_FLG ENTERPRISE_FLG, SQ_W_INT_ORG_DS.REPORTING_EST_FLG REPORTING_EST_FLG, SQ_W_INT_ORG_DS.EXPENDITURE_ORG_FLG EXPENDITURE_ORG_FLG, SQ_W_INT_ORG_DS.PAYROLL_STATUTORY_UNIT_FLG PAYROLL_STATUTORY_UNIT_FLG, SQ_W_INT_ORG_DS.CUSTOMER_PAYMENTS_BU_FLG CUSTOMER_PAYMENTS_BU_FLG, SQ_W_INT_ORG_DS.EXPENSE_BU_FLG EXPENSE_BU_FLG, SQ_W_INT_ORG_DS.COLLECTIONS_BU_FLG COLLECTIONS_BU_FLG, SQ_W_INT_ORG_DS.MATERIALS_MANAGEMENT_BU_FLG MATERIALS_MANAGEMENT_BU_FLG, SQ_W_INT_ORG_DS.PRC_CONTRACT_MANAGEMENT_BU_FLG PRC_CONTRACT_MANAGEMENT_BU_FLG, SQ_W_INT_ORG_DS.RECEIVING_BU_FLG RECEIVING_BU_FLG, SQ_W_INT_ORG_DS.FINANCIAL_BU_FLG FINANCIAL_BU_FLG, SQ_W_INT_ORG_DS.REQUISITION_BU_FLG REQUISITION_BU_FLG, SQ_W_INT_ORG_DS.COST_ORG_FLG COST_ORG_FLG, SQ_W_INT_ORG_DS.PROJECT_UNIT_FLG PROJECT_UNIT_FLG, SQ_W_INT_ORG_DS.VIS_PRTNR_MGR_ID VIS_PRTNR_MGR_ID, SQ_W_INT_ORG_DS.W_PTSHP_STAGE_CODE W_PTSHP_STAGE_CODE, SQ_W_INT_ORG_DS.MFG_DEPT_FLG MFG_DEPT_FLG, SQ_W_INT_ORG_DS.SIA_BU_FLG SIA_BU_FLG, SQ_W_INT_ORG_DS.QUALITY_ENABLED_FLG QUALITY_ENABLED_FLG, COALESCE(SQ_W_INT_ORG_DS.SRC_EFF_FROM_DT,TO_DATE(SUBSTR('#BIAPPS.LOW_DATE',0,19),'YYYY-MM-DD HH24:MI:SS')) EFFECTIVE_FROM_DT, TO_DATE(SUBSTR('#BIAPPS.HI_DT',0,19),'YYYY-MM-DD HH24:MI:SS') EFFECTIVE_TO_DT, 'I' IND_UPDATE from ( /* Subselect from SIL_InternalOrganizationDimension.W_INT_ORG_D_SQ_W_INT_ORG_DS */ select DISTINCT SQ_W_INT_ORG_DS.ORG_NUM ORG_NUM, COALESCE(SQ_W_INT_ORG_DS.CURR_CODE,'__NOT_APPLICABLE__') W_CURR_CODE, SQ_W_INT_ORG_DS.ST_ADDRESS1 ST_ADDRESS1, SQ_W_INT_ORG_DS.ST_ADDRESS2 ST_ADDRESS2, COALESCE(SQ_W_INT_ORG_DS.POSTAL_CODE,'__NOT_APPLICABLE__') POSTAL_CODE, COALESCE( TRIM( SUBSTR( SQ_W_INT_ORG_DS.STATE_PROV_CODE, INSTR(SQ_W_INT_ORG_DS.STATE_PROV_CODE,':')+1, LENGTH(SQ_W_INT_ORG_DS.STATE_PROV_CODE) ) ), '#BIAPPS.SOURCE_CODE_NOT_SUPPLIED' ) STATE_PROV_CODE, COALESCE(SQ_W_INT_ORG_DS.COUNTRY_REGION_CODE,'__NOT_APPLICABLE__') COUNTRY_REGION_CODE, SQ_W_INT_ORG_DS.FAX_NUM FAX_NUM, SQ_W_INT_ORG_DS.PHONE_NUM PHONE_NUM, COALESCE(SQ_W_INT_ORG_DS.DIVN_FLG,'N') DIVN_FLG, SQ_W_INT_ORG_DS.WEB_ADDRESS WEB_ADDRESS, SQ_W_INT_ORG_DS.EMAIL_ADDRESS EMAIL_ADDRESS, COALESCE(SQ_W_INT_ORG_DS.BU_FLG,'N') BU_FLG, COALESCE(SQ_W_INT_ORG_DS.SALES_GROUP_FLG,'N') SALES_GROUP_FLG, SQ_W_INT_ORG_DS.CITY_CODE CITY_CODE, SQ_W_INT_ORG_DS.COUNTY_CODE COUNTY_CODE, COALESCE(SQ_W_INT_ORG_DS.PRTNR_FLG,'N') PRTNR_FLG, COALESCE(SQ_W_INT_ORG_DS.INTERNAL_FLG,'N') INTERNAL_FLG, COALESCE(SQ_W_INT_ORG_DS.CNTCT_REP_ORG_FLG,'N') CNTCT_REP_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.BUSINESS_AREA_FLG,'N') BUSINESS_AREA_FLG, COALESCE(SQ_W_INT_ORG_DS.COMPANY_FLG,'N') COMPANY_FLG, COALESCE(SQ_W_INT_ORG_DS.LEGAL_ENTITY_FLG,'N') LEGAL_ENTITY_FLG, COALESCE(SQ_W_INT_ORG_DS.OPERATING_UNIT_FLG,'N') OPERATING_UNIT_FLG, COALESCE(SQ_W_INT_ORG_DS.BUSINESS_GROUP_FLG,'N') BUSINESS_GROUP_FLG, COALESCE(SQ_W_INT_ORG_DS.INV_ORG_FLG,'N') INV_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.PROJECT_ORG_FLG,'N') PROJECT_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.HR_ORG_FLG,'N') HR_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.GOVT_REPT_ENTITY_FLG,'N') GOVT_REPT_ENTITY_FLG, COALESCE(SQ_W_INT_ORG_DS.BALANCING_ENTITY_FLG,'N') BALANCING_ENTITY_FLG, COALESCE(SQ_W_INT_ORG_DS.ASSET_ORG_FLG,'N') ASSET_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.CONTROL_AREA_FLG,'N') CONTROL_AREA_FLG, COALESCE(SQ_W_INT_ORG_DS.FIN_AREA_FLG,'N') FIN_AREA_FLG, COALESCE(SQ_W_INT_ORG_DS.VALUATION_AREA_FLG,'N') VALUATION_AREA_FLG, COALESCE(SQ_W_INT_ORG_DS.SALES_AREA_FLG,'N') SALES_AREA_FLG, COALESCE(SQ_W_INT_ORG_DS.MARKETING_ORG_FLG,'N') MARKETING_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.PURCH_ORG_FLG,'N') PURCH_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.SALES_ORG_FLG,'N') SALES_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.PAYABLES_ORG_FLG,'N') PAYABLES_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.RECEIVABLES_ORG_FLG,'N') RECEIVABLES_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.SERVICE_ORG_FLG,'N') SERVICE_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.BRANCH_FLG,'N') BRANCH_FLG, COALESCE(SQ_W_INT_ORG_DS.ORG_TYPE_CODE,'__NOT_APPLICABLE__') ORG_TYPE_CODE, SQ_W_INT_ORG_DS.MGR_NAME MGR_NAME, COALESCE(SQ_W_INT_ORG_DS.PRTNR_TIER_CODE,'__NOT_APPLICABLE__') PRTNR_TIER_CODE, COALESCE(SQ_W_INT_ORG_DS.PRTNR_TYPE_CODE,'__NOT_APPLICABLE__') PRTNR_TYPE_CODE, COALESCE(SQ_W_INT_ORG_DS.PTSHP_STAGE_CODE,'__NOT_APPLICABLE__') PTSHP_STAGE_CODE, SQ_W_INT_ORG_DS.PR_PTSHP_MKTSEG PR_PTSHP_MKTSEG, SQ_W_INT_ORG_DS.PR_ORG_TRGT_MKT PR_ORG_TRGT_MKT, SQ_W_INT_ORG_DS.PRTNR_SALES_RANK PRTNR_SALES_RANK, SQ_W_INT_ORG_DS.PRTNRSHP_START_DT PRTNRSHP_START_DT, SQ_W_INT_ORG_DS.PTSHP_END_DT PTSHP_END_DT, SQ_W_INT_ORG_DS.PTSHP_PRTNR_ACCNT PTSHP_PRTNR_ACCNT, SQ_W_INT_ORG_DS.PTSHP_RENEWAL_DT PTSHP_RENEWAL_DT, SQ_W_INT_ORG_DS.PTSHP_SAT_INDEX PTSHP_SAT_INDEX, SQ_W_INT_ORG_DS.VIS_PR_BU_ID VIS_PR_BU_ID, SQ_W_INT_ORG_DS.VIS_PR_POS_ID VIS_PR_POS_ID, COALESCE(LKP_W_USER_D_CREATED_BY_WID.ROW_WID,0) CREATED_BY_ID, COALESCE(LKP_W_USER_D_CHANGED_BY_WID.ROW_WID,0) CHANGED_BY_ID, SQ_W_INT_ORG_DS.CREATED_ON_DT CREATED_ON_DT, SQ_W_INT_ORG_DS.CHANGED_ON_DT CHANGED_ON_DT, SQ_W_INT_ORG_DS.AUX1_CHANGED_ON_DT AUX1_CHANGED_ON_DT, SQ_W_INT_ORG_DS.AUX2_CHANGED_ON_DT AUX2_CHANGED_ON_DT, SQ_W_INT_ORG_DS.AUX3_CHANGED_ON_DT AUX3_CHANGED_ON_DT, SQ_W_INT_ORG_DS.AUX4_CHANGED_ON_DT AUX4_CHANGED_ON_DT, COALESCE(SQ_W_INT_ORG_DS.SRC_EFF_FROM_DT,TO_DATE(SUBSTR('#BIAPPS.LOW_DATE',0,19),'YYYY-MM-DD HH24:MI:SS')) SRC_EFF_FROM_DT, TO_DATE(SUBSTR('#BIAPPS.HI_DATE',0,19),'YYYY-MM-DD HH24:MI:SS') SRC_EFF_TO_DT, (CASE WHEN SQ_W_INT_ORG_DS.DELETE_FLG='Y' THEN 'Y' ELSE 'N' END ) DELETE_FLG, SQ_W_INT_ORG_DS.DATASOURCE_NUM_ID DATASOURCE_NUM_ID, SQ_W_INT_ORG_DS.INTEGRATION_ID INTEGRATION_ID, SQ_W_INT_ORG_DS.TENANT_ID TENANT_ID, SQ_W_INT_ORG_DS.X_CUSTOM X_CUSTOM, SQ_W_INT_ORG_DS.REGISTRATION_NUM REGISTRATION_NUM, COALESCE(SQ_W_INT_ORG_DS.PROJECT_BU_FLG,'N') PROJECT_BU_FLG, COALESCE(SQ_W_INT_ORG_DS.PROJECT_EXP_ORG_FLG,'N') PROJECT_EXP_ORG_FLG, SQ_W_INT_ORG_DS.SET_ID SET_ID, COALESCE(SQ_W_INT_ORG_DS.CONTRACT_BU_FLG,'N') CONTRACT_BU_FLG, SQ_W_INT_ORG_DS.C_CITY_CODE C_CITY_CODE, SQ_W_INT_ORG_DS.C_COUNTY_CODE C_COUNTY_CODE, SQ_W_INT_ORG_DS.C_STATE_PROV_CODE C_STATE_PROV_CODE, SQ_W_INT_ORG_DS.W_COUNTRY_CODE W_COUNTRY_CODE, SQ_W_INT_ORG_DS.C_COUNTRY_REGION_CODE C_COUNTRY_REGION_CODE, SQ_W_INT_ORG_DS.ORGANIZATION_CODE ORGANIZATION_CODE, COALESCE(SQ_W_INT_ORG_DS.RETAIL_FLG,'N') RETAIL_FLG, COALESCE(SQ_W_INT_ORG_DS.ENTERPRISE_FLG,'N') ENTERPRISE_FLG, COALESCE(SQ_W_INT_ORG_DS.REPORTING_EST_FLG,'N') REPORTING_EST_FLG, COALESCE(SQ_W_INT_ORG_DS.EXPENDITURE_ORG_FLG,'N') EXPENDITURE_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.PAYROLL_STATUTORY_UNIT_FLG,'N') PAYROLL_STATUTORY_UNIT_FLG, COALESCE(SQ_W_INT_ORG_DS.CUSTOMER_PAYMENTS_BU_FLG,'N') CUSTOMER_PAYMENTS_BU_FLG, COALESCE(SQ_W_INT_ORG_DS.EXPENSE_BU_FLG,'N') EXPENSE_BU_FLG, COALESCE(SQ_W_INT_ORG_DS.COLLECTIONS_BU_FLG,'N') COLLECTIONS_BU_FLG, COALESCE(SQ_W_INT_ORG_DS.MATERIALS_MANAGEMENT_BU_FLG,'N') MATERIALS_MANAGEMENT_BU_FLG, COALESCE(SQ_W_INT_ORG_DS.PRC_CONTRACT_MANAGEMENT_BU_FLG,'N') PRC_CONTRACT_MANAGEMENT_BU_FLG, COALESCE(SQ_W_INT_ORG_DS.RECEIVING_BU_FLG,'N') RECEIVING_BU_FLG, COALESCE(SQ_W_INT_ORG_DS.FINANCIAL_BU_FLG,'N') FINANCIAL_BU_FLG, COALESCE(SQ_W_INT_ORG_DS.REQUISITION_BU_FLG,'N') REQUISITION_BU_FLG, COALESCE(SQ_W_INT_ORG_DS.COST_ORG_FLG,'N') COST_ORG_FLG, COALESCE(SQ_W_INT_ORG_DS.PROJECT_UNIT_FLG,'N') PROJECT_UNIT_FLG, SQ_W_INT_ORG_DS.VIS_PRTNR_MGR_ID VIS_PRTNR_MGR_ID, SQ_W_INT_ORG_DS.W_PTSHP_STAGE_CODE W_PTSHP_STAGE_CODE, COALESCE(SQ_W_INT_ORG_DS.MFG_DEPT_FLG,'N') MFG_DEPT_FLG, COALESCE(LKP_W_POSITION_DH_WID.ROW_WID,0) POSITION_DH_WID, COALESCE(SQ_W_INT_ORG_DS.SRC_EFF_FROM_DT,TO_DATE(SUBSTR('#BIAPPS.LOW_DATE',0,19),'YYYY-MM-DD HH24:MI:SS')) EFFECTIVE_FROM_DT, 0 SCD1_WID, #BIAPPS.ETL_PROC_WID ETL_PROC_WID, COALESCE(SQ_W_INT_ORG_DS.SIA_BU_FLG,'N') SIA_BU_FLG, SQ_W_INT_ORG_DS.INT_ORG_ATTR10_CHAR INT_ORG_ATTR10_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR6_CHAR INT_ORG_ATTR6_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR5_CHAR INT_ORG_ATTR5_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR4_CHAR INT_ORG_ATTR4_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR7_CHAR INT_ORG_ATTR7_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR9_CHAR INT_ORG_ATTR9_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR8_CHAR INT_ORG_ATTR8_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR2_CHAR INT_ORG_ATTR2_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR3_CHAR INT_ORG_ATTR3_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR1_CHAR INT_ORG_ATTR1_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR8_NUM INT_ORG_ATTR8_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR29_CHAR INT_ORG_ATTR29_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR14_CHAR INT_ORG_ATTR14_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR3_NUM INT_ORG_ATTR3_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR9_NUM INT_ORG_ATTR9_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR7_DATE INT_ORG_ATTR7_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR11_CHAR INT_ORG_ATTR11_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR21_CHAR INT_ORG_ATTR21_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR16_NUM INT_ORG_ATTR16_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR3_DATE INT_ORG_ATTR3_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR13_CHAR INT_ORG_ATTR13_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR19_NUM INT_ORG_ATTR19_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR10_DATE INT_ORG_ATTR10_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR28_CHAR INT_ORG_ATTR28_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR13_NUM INT_ORG_ATTR13_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR4_DATE INT_ORG_ATTR4_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR14_NUM INT_ORG_ATTR14_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR26_CHAR INT_ORG_ATTR26_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR15_CHAR INT_ORG_ATTR15_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR19_CHAR INT_ORG_ATTR19_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR20_CHAR INT_ORG_ATTR20_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR17_CHAR INT_ORG_ATTR17_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR16_CHAR INT_ORG_ATTR16_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR22_CHAR INT_ORG_ATTR22_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR2_DATE INT_ORG_ATTR2_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR24_CHAR INT_ORG_ATTR24_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR10_NUM INT_ORG_ATTR10_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR2_NUM INT_ORG_ATTR2_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR15_NUM INT_ORG_ATTR15_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR6_DATE INT_ORG_ATTR6_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR12_NUM INT_ORG_ATTR12_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR8_DATE INT_ORG_ATTR8_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR23_CHAR INT_ORG_ATTR23_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR7_NUM INT_ORG_ATTR7_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR20_NUM INT_ORG_ATTR20_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR30_CHAR INT_ORG_ATTR30_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR1_NUM INT_ORG_ATTR1_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR27_CHAR INT_ORG_ATTR27_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR1_DATE INT_ORG_ATTR1_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR6_NUM INT_ORG_ATTR6_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR12_CHAR INT_ORG_ATTR12_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR11_NUM INT_ORG_ATTR11_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR5_NUM INT_ORG_ATTR5_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR4_NUM INT_ORG_ATTR4_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR17_NUM INT_ORG_ATTR17_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR18_NUM INT_ORG_ATTR18_NUM, SQ_W_INT_ORG_DS.INT_ORG_ATTR18_CHAR INT_ORG_ATTR18_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR5_DATE INT_ORG_ATTR5_DATE, SQ_W_INT_ORG_DS.INT_ORG_ATTR25_CHAR INT_ORG_ATTR25_CHAR, SQ_W_INT_ORG_DS.INT_ORG_ATTR9_DATE INT_ORG_ATTR9_DATE, COALESCE(SQ_W_INT_ORG_DS.QUALITY_ENABLED_FLG,'N') QUALITY_ENABLED_FLG, SQ_W_INT_ORG_DS.X_ATTACHED_PROCESS_QTY X_ATTACHED_PROCESS_QTY, --it is getting selected in sub query but not in the outer query SYSDATE W_INSERT_DT from ((DEV_DW.W_INT_ORG_DS SQ_W_INT_ORG_DS LEFT OUTER JOIN ( /* Subselect from LKP_W_USER_D */ select W_USER_D.ROW_WID ROW_WID, W_USER_D.DATASOURCE_NUM_ID DATASOURCE_NUM_ID, W_USER_D.INTEGRATION_ID INTEGRATION_ID, W_USER_D.EFFECTIVE_FROM_DT EFFECTIVE_FROM_DT, W_USER_D.EFFECTIVE_TO_DT EFFECTIVE_TO_DT from DEV_DW.W_USER_D W_USER_D where (1=1) ) LKP_W_USER_D_CREATED_BY_WID ON SQ_W_INT_ORG_DS.DATASOURCE_NUM_ID = LKP_W_USER_D_CREATED_BY_WID.DATASOURCE_NUM_ID AND SQ_W_INT_ORG_DS.CREATED_BY_ID = LKP_W_USER_D_CREATED_BY_WID.INTEGRATION_ID AND SQ_W_INT_ORG_DS.CREATED_ON_DT >= LKP_W_USER_D_CREATED_BY_WID.EFFECTIVE_FROM_DT AND SQ_W_INT_ORG_DS.CREATED_ON_DT < LKP_W_USER_D_CREATED_BY_WID.EFFECTIVE_TO_DT) LEFT OUTER JOIN DEV_DW.W_POSITION_DH LKP_W_POSITION_DH_WID ON SQ_W_INT_ORG_DS.VIS_PR_POS_ID = LKP_W_POSITION_DH_WID.BASE_POSTN_CODE AND SQ_W_INT_ORG_DS.DATASOURCE_NUM_ID = LKP_W_POSITION_DH_WID.DATASOURCE_NUM_ID AND SQ_W_INT_ORG_DS.CREATED_ON_DT >= LKP_W_POSITION_DH_WID.EFFECTIVE_FROM_DT AND SQ_W_INT_ORG_DS.CREATED_ON_DT < LKP_W_POSITION_DH_WID.EFFECTIVE_TO_DT) LEFT OUTER JOIN ( /* Subselect from LKP_W_USER_D */ select W_USER_D.ROW_WID ROW_WID, W_USER_D.DATASOURCE_NUM_ID DATASOURCE_NUM_ID, W_USER_D.INTEGRATION_ID INTEGRATION_ID, W_USER_D.EFFECTIVE_FROM_DT EFFECTIVE_FROM_DT, W_USER_D.EFFECTIVE_TO_DT EFFECTIVE_TO_DT from DEV_DW.W_USER_D W_USER_D where (1=1) ) LKP_W_USER_D_CHANGED_BY_WID ON SQ_W_INT_ORG_DS.DATASOURCE_NUM_ID = LKP_W_USER_D_CHANGED_BY_WID.DATASOURCE_NUM_ID AND SQ_W_INT_ORG_DS.CHANGED_BY_ID = LKP_W_USER_D_CHANGED_BY_WID.INTEGRATION_ID AND SQ_W_INT_ORG_DS.CHANGED_ON_DT >= LKP_W_USER_D_CHANGED_BY_WID.EFFECTIVE_FROM_DT AND SQ_W_INT_ORG_DS.CHANGED_ON_DT < LKP_W_USER_D_CHANGED_BY_WID.EFFECTIVE_TO_DT where (1=1) ) SQ_W_INT_ORG_DS where (1=1) ) S Thanks, Dany

Thomas Dodds · Answer

Did you make your changes in SDE, SILO & PLP for W_INT_ORG_D?  You might be missing a mapping in SILO or PLP if you are seeing data in _DS (stage) but not in _D (dim).