Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Custom column not getting populated in W_INT_ORG_D
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
Answers
-
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).
0 -
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
0 -
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!
0