I have an issue related to ODI SIL interface
443 views
Skip to first unread message
Ali Mostafa
Apr 2, 2018, 10:00:43 AM4/2/18
to OBIEE Enterprise Methodology Group
Dear Team,
I have added new filed called "REGISTRATION_NUMBER" to W_SUPPLIER_ACCOUNT_D .
The issue is I can see the new filed in creation table "create table BIAPPS_DW.I$__1" , but I'm not able to see the new filed in the select statement that generated by ODI.
The result is after executed the job the new filed not updated , (check screenshot& below script)
I have executed the simulation of SIL Interface.
------------------------------------------------------------
Generated Session
Session Name:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Scenario Name:
Context Code:
GLOBAL
Step
Step Name:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Step No:
1
Step Type:
F
Ok Next Step:
Ko Next Step:
Ok Exit:
0
Ko Exit:
0
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Initialization and log header
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* ===================================================
-- IKM BIAPPS Oracle Slowly Changing Dimension
-- 11.1.1.7.2.20140613
-- ===================================================
---------------------------------------------------
SCD Dimension Column Behaviour
---------------------------------------------------
Type 1 Columns (updated to match current record if UPDATE_ALL_HISTORY is set):
[SCD Behaviour - overwrite on change, not OBI System Column (on column level flexfield)]
SPLR_ACCT_NUM
COMPANY_CODE
ALLOC_SORT_KEY
GL_RECNCL_ACCT_NUM
PAY_TERMS_CODE
OUR_ACCT_NUM
SPLR_USER_NAME
PLAN_GROUP_CODE
ACCT_CLERK_NUM
ACCT_CLERK_NAME
HEAD_OFFC_ACCT_NUM
HEAD_OFFC_ACCT_NAME
ALT_ACCT_NUM
ALT_ACCT_NAME
BOE_LIMIT_AMT
CHECK_PAID_TIME
TOLERANCE_GRP_CODE
HOUSE_BANK_CODE
PREV_SPLR_NUM
PAYMENT_GRP_CODE
SPLR_RECPT_TYPE_CODE
PERSONNEL_NUM
PERSONNEL_NAME
ACTIVE_FLG
DOC_CURR_CODE
LOC_CURR_CODE
LOC_EXCHANGE_RATE
GLOBAL1_EXCHANGE_RATE
GLOBAL2_EXCHANGE_RATE
GLOBAL3_EXCHANGE_RATE
CREATED_BY_WID
CHANGED_BY_WID
CREATED_ON_DT
CHANGED_ON_DT
AUX1_CHANGED_ON_DT
AUX2_CHANGED_ON_DT
AUX3_CHANGED_ON_DT
AUX4_CHANGED_ON_DT
SRC_EFF_FROM_DT
SRC_EFF_TO_DT
DELETE_FLG
W_INSERT_DT
W_UPDATE_DT
ETL_PROC_WID
SET_ID
TENANT_ID
X_CUSTOM
SUPPLIER_SITE_CODE
ST_ADDRESS2
CITY_CODE
STATE_PROV_CODE
COUNTRY_REGION_CODE
COUNTY_CODE
REGION_CODE
POSTAL_CODE
CONTINENT_CODE
C_CITY_CODE
C_STATE_PROV_CODE
C_COUNTY_CODE
C_REGION_CODE
W_COUNTRY_CODE
C_COUNTRY_REGION_CODE
C_CONTINENT_CODE
PHONE_NUM
FAX_NUM
CUSTOMER_NUM
SUPPLIER_ID
PARTY_ID
PARTY_SITE_ID
LOCATION_ID
BU_ID
Type 2 Columns (used to detect a change for a new record if TYPE2_FLG is set):
[SCD Behaviour - insert new row on change, not mapped on TARGET]
SPLR_ACCT_NAME
ST_ADDRESS1
Change columns (used to exclude unchanged rows from processing):
[Updateable columns, not mapped on TARGET, OBI Change Column (on column level flexfield)
or if no OBI Change Columns flagged then all updateable columns not mapped on TARGET]
CHANGED_ON_DT
AUX1_CHANGED_ON_DT
AUX2_CHANGED_ON_DT
AUX3_CHANGED_ON_DT
AUX4_CHANGED_ON_DT
ST_ADDRESS1
Has ROW_WID: Y
SCD1 column:
[OBI SCD1 column (column level flexfield), not mapped on TARGET]
Not found
Bulk Mode Option: N
---------------------------------------------------
Work Objects
---------------------------------------------------
Setting flow table name to BIAPPS_DW.I$__1
=================================================== */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Table Maintenance - Clean Truncated Entry
Exe Channel:
S
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
SUNOPSIS_API
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Table Maintenance Before
Exe Channel:
S
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
SUNOPSIS_API
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Drop flow table (I$)
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
drop table BIAPPS_DW.I$__1 purge
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Run Alter Session Commands
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
BEGIN
NULL;
END;
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Alter Session - Char Semantics
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Below statement ensure table lengths are proper for Oracle Unicode Enabled DBs. */
ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Create flow table (I$)
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Creates the flow table with the parameters specified in the FLOW_TABLE_OPTIONS option */
create table BIAPPS_DW.I$__1
(
SRC_EFF_FROM_DT DATE NULL,
DATASOURCE_NUM_ID NUMBER(10) NULL,
INTEGRATION_ID VARCHAR2(80) NULL,
ROW_WID NUMBER NULL,
SPLR_ACCT_NUM VARCHAR2(4000) NULL,
SPLR_ACCT_NAME VARCHAR2(4000) NULL,
COMPANY_CODE VARCHAR2(4000) NULL,
ALLOC_SORT_KEY VARCHAR2(4000) NULL,
GL_RECNCL_ACCT_NUM VARCHAR2(4000) NULL,
PAY_TERMS_CODE VARCHAR2(4000) NULL,
OUR_ACCT_NUM VARCHAR2(4000) NULL,
SPLR_USER_NAME VARCHAR2(4000) NULL,
PLAN_GROUP_CODE VARCHAR2(4000) NULL,
ACCT_CLERK_NUM VARCHAR2(4000) NULL,
ACCT_CLERK_NAME VARCHAR2(4000) NULL,
HEAD_OFFC_ACCT_NUM VARCHAR2(4000) NULL,
HEAD_OFFC_ACCT_NAME VARCHAR2(4000) NULL,
ALT_ACCT_NUM VARCHAR2(4000) NULL,
ALT_ACCT_NAME VARCHAR2(4000) NULL,
BOE_LIMIT_AMT NUMBER NULL,
CHECK_PAID_TIME NUMBER NULL,
TOLERANCE_GRP_CODE VARCHAR2(4000) NULL,
HOUSE_BANK_CODE VARCHAR2(4000) NULL,
PREV_SPLR_NUM VARCHAR2(4000) NULL,
PAYMENT_GRP_CODE VARCHAR2(4000) NULL,
SPLR_RECPT_TYPE_CODE VARCHAR2(4000) NULL,
PERSONNEL_NUM VARCHAR2(4000) NULL,
PERSONNEL_NAME VARCHAR2(4000) NULL,
ACTIVE_FLG CHAR(1) NULL,
DOC_CURR_CODE VARCHAR2(4000) NULL,
LOC_CURR_CODE VARCHAR2(4000) NULL,
LOC_EXCHANGE_RATE NUMBER NULL,
GLOBAL1_EXCHANGE_RATE NUMBER NULL,
GLOBAL2_EXCHANGE_RATE NUMBER NULL,
GLOBAL3_EXCHANGE_RATE NUMBER NULL,
CREATED_BY_WID NUMBER NULL,
CHANGED_BY_WID NUMBER NULL,
CREATED_ON_DT DATE NULL,
CHANGED_ON_DT DATE NULL,
AUX1_CHANGED_ON_DT DATE NULL,
AUX2_CHANGED_ON_DT DATE NULL,
AUX3_CHANGED_ON_DT DATE NULL,
AUX4_CHANGED_ON_DT DATE NULL,
SRC_EFF_TO_DT DATE NULL,
EFFECTIVE_FROM_DT DATE NULL,
EFFECTIVE_TO_DT DATE NULL,
DELETE_FLG CHAR(1) NULL,
CURRENT_FLG CHAR(1) NULL,
W_INSERT_DT DATE NULL,
W_UPDATE_DT DATE NULL,
ETL_PROC_WID NUMBER NULL,
SET_ID VARCHAR2(4000) NULL,
TENANT_ID VARCHAR2(4000) NULL,
X_CUSTOM VARCHAR2(4000) NULL,
SUPPLIER_SITE_CODE VARCHAR2(4000) NULL,
ST_ADDRESS1 VARCHAR2(4000) NULL,
ST_ADDRESS2 VARCHAR2(4000) NULL,
CITY_CODE VARCHAR2(4000) NULL,
STATE_PROV_CODE VARCHAR2(4000) NULL,
COUNTRY_REGION_CODE VARCHAR2(4000) NULL,
COUNTY_CODE VARCHAR2(4000) NULL,
REGION_CODE VARCHAR2(4000) NULL,
POSTAL_CODE VARCHAR2(4000) NULL,
CONTINENT_CODE VARCHAR2(4000) NULL,
C_CITY_CODE VARCHAR2(4000) NULL,
C_STATE_PROV_CODE VARCHAR2(4000) NULL,
C_COUNTY_CODE VARCHAR2(4000) NULL,
C_REGION_CODE VARCHAR2(4000) NULL,
W_COUNTRY_CODE VARCHAR2(4000) NULL,
C_COUNTRY_REGION_CODE VARCHAR2(4000) NULL,
C_CONTINENT_CODE VARCHAR2(4000) NULL,
PHONE_NUM VARCHAR2(4000) NULL,
FAX_NUM VARCHAR2(4000) NULL,
CUSTOMER_NUM VARCHAR2(4000) NULL,
SUPPLIER_ID NUMBER NULL,
PARTY_ID NUMBER NULL,
PARTY_SITE_ID NUMBER NULL,
LOCATION_ID NUMBER NULL,
BU_ID NUMBER NULL,
REGISTRATION_NUMBER VARCHAR2(4000) NULL,
IND_UPDATE CHAR(1),
IND_CURRENT_FLG CHAR(1)
)
NOLOGGING
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Insert flow into I$ table
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* DETECTION_STRATEGY = NOT_EXISTS */
insert /*+ append */ into BIAPPS_DW.I$__1
(
SPLR_ACCT_NUM,
SPLR_ACCT_NAME,
COMPANY_CODE,
ALLOC_SORT_KEY,
GL_RECNCL_ACCT_NUM,
PAY_TERMS_CODE,
OUR_ACCT_NUM,
SPLR_USER_NAME,
PLAN_GROUP_CODE,
ACCT_CLERK_NUM,
ACCT_CLERK_NAME,
HEAD_OFFC_ACCT_NUM,
HEAD_OFFC_ACCT_NAME,
ALT_ACCT_NUM,
ALT_ACCT_NAME,
BOE_LIMIT_AMT,
CHECK_PAID_TIME,
TOLERANCE_GRP_CODE,
HOUSE_BANK_CODE,
PREV_SPLR_NUM,
PAYMENT_GRP_CODE,
SPLR_RECPT_TYPE_CODE,
PERSONNEL_NUM,
PERSONNEL_NAME,
ACTIVE_FLG,
DOC_CURR_CODE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
CREATED_BY_WID,
CHANGED_BY_WID,
CREATED_ON_DT,
CHANGED_ON_DT,
AUX1_CHANGED_ON_DT,
AUX2_CHANGED_ON_DT,
AUX3_CHANGED_ON_DT,
AUX4_CHANGED_ON_DT,
SRC_EFF_FROM_DT,
SRC_EFF_TO_DT,
DELETE_FLG,
DATASOURCE_NUM_ID,
INTEGRATION_ID,
SET_ID,
TENANT_ID,
X_CUSTOM,
SUPPLIER_SITE_CODE,
ST_ADDRESS1,
ST_ADDRESS2,
CITY_CODE,
STATE_PROV_CODE,
COUNTRY_REGION_CODE,
COUNTY_CODE,
REGION_CODE,
POSTAL_CODE,
CONTINENT_CODE,
C_CITY_CODE,
C_STATE_PROV_CODE,
C_COUNTY_CODE,
C_REGION_CODE,
W_COUNTRY_CODE,
C_COUNTRY_REGION_CODE,
C_CONTINENT_CODE,
PHONE_NUM,
FAX_NUM,
CUSTOMER_NUM,
SUPPLIER_ID,
PARTY_ID,
PARTY_SITE_ID,
LOCATION_ID,
BU_ID,
EFFECTIVE_FROM_DT,
EFFECTIVE_TO_DT,
IND_CURRENT_FLG,
IND_UPDATE
)
select
SPLR_ACCT_NUM,
SPLR_ACCT_NAME,
COMPANY_CODE,
ALLOC_SORT_KEY,
GL_RECNCL_ACCT_NUM,
PAY_TERMS_CODE,
OUR_ACCT_NUM,
SPLR_USER_NAME,
PLAN_GROUP_CODE,
ACCT_CLERK_NUM,
ACCT_CLERK_NAME,
HEAD_OFFC_ACCT_NUM,
HEAD_OFFC_ACCT_NAME,
ALT_ACCT_NUM,
ALT_ACCT_NAME,
BOE_LIMIT_AMT,
CHECK_PAID_TIME,
TOLERANCE_GRP_CODE,
HOUSE_BANK_CODE,
PREV_SPLR_NUM,
PAYMENT_GRP_CODE,
SPLR_RECPT_TYPE_CODE,
PERSONNEL_NUM,
PERSONNEL_NAME,
ACTIVE_FLG,
DOC_CURR_CODE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
CREATED_BY_WID,
CHANGED_BY_WID,
CREATED_ON_DT,
CHANGED_ON_DT,
AUX1_CHANGED_ON_DT,
AUX2_CHANGED_ON_DT,
AUX3_CHANGED_ON_DT,
AUX4_CHANGED_ON_DT,
SRC_EFF_FROM_DT,
SRC_EFF_TO_DT,
DELETE_FLG,
DATASOURCE_NUM_ID,
INTEGRATION_ID,
SET_ID,
TENANT_ID,
X_CUSTOM,
SUPPLIER_SITE_CODE,
ST_ADDRESS1,
ST_ADDRESS2,
CITY_CODE,
STATE_PROV_CODE,
COUNTRY_REGION_CODE,
COUNTY_CODE,
REGION_CODE,
POSTAL_CODE,
CONTINENT_CODE,
C_CITY_CODE,
C_STATE_PROV_CODE,
C_COUNTY_CODE,
C_REGION_CODE,
W_COUNTRY_CODE,
C_COUNTRY_REGION_CODE,
C_CONTINENT_CODE,
PHONE_NUM,
FAX_NUM,
CUSTOMER_NUM,
SUPPLIER_ID,
PARTY_ID,
PARTY_SITE_ID,
LOCATION_ID,
BU_ID,
EFFECTIVE_FROM_DT,
EFFECTIVE_TO_DT,
'Y' IND_CURRENT_FLG,
IND_UPDATE
from (
select
SQ_W_SUPPLIER_ACCOUNT_DS.SPLR_ACCT_NUM SPLR_ACCT_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.SPLR_ACCT_NAME SPLR_ACCT_NAME,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.COMPANY_CODE,'__NOT_APPLICABLE__') COMPANY_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.ALLOC_SORT_KEY ALLOC_SORT_KEY,
SQ_W_SUPPLIER_ACCOUNT_DS.GL_RECNCL_ACCT_NUM GL_RECNCL_ACCT_NUM,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.PAY_TERMS_CODE,'__NOT_APPLICABLE__') PAY_TERMS_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.OUR_ACCT_NUM OUR_ACCT_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.SPLR_USER_NAME SPLR_USER_NAME,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.PLAN_GROUP_CODE,'__NOT_APPLICABLE__') PLAN_GROUP_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.ACCT_CLERK_NUM ACCT_CLERK_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.ACCT_CLERK_NAME ACCT_CLERK_NAME,
SQ_W_SUPPLIER_ACCOUNT_DS.HEAD_OFFC_ACCT_NUM HEAD_OFFC_ACCT_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.HEAD_OFFC_ACCT_NAME HEAD_OFFC_ACCT_NAME,
SQ_W_SUPPLIER_ACCOUNT_DS.ALT_ACCT_NUM ALT_ACCT_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.ALT_ACCT_NAME ALT_ACCT_NAME,
SQ_W_SUPPLIER_ACCOUNT_DS.BOE_LIMIT_AMT BOE_LIMIT_AMT,
SQ_W_SUPPLIER_ACCOUNT_DS.CHECK_PAID_TIME CHECK_PAID_TIME,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.TOLERANCE_GRP_CODE,'__NOT_APPLICABLE__') TOLERANCE_GRP_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.HOUSE_BANK_CODE,'__NOT_APPLICABLE__') HOUSE_BANK_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.PREV_SPLR_NUM PREV_SPLR_NUM,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.PAYMENT_GRP_CODE,'__NOT_APPLICABLE__') PAYMENT_GRP_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.SPLR_RECPT_TYPE_CODE,'__NOT_APPLICABLE__') SPLR_RECPT_TYPE_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.PERSONNEL_NUM PERSONNEL_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.PERSONNEL_NAME PERSONNEL_NAME,
SQ_W_SUPPLIER_ACCOUNT_DS.ACTIVE_FLG ACTIVE_FLG,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.DOC_CURR_CODE,'__NOT_APPLICABLE__') DOC_CURR_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.LOC_CURR_CODE,'__NOT_APPLICABLE__') LOC_CURR_CODE,
LKP_W_EXCH_RATE_G_LOC.EXCH_RATE LOC_EXCHANGE_RATE,
LKP_W_GLOBAL_EXCH_RATE_G.GLBL1_EXCH_RATE GLOBAL1_EXCHANGE_RATE,
LKP_W_GLOBAL_EXCH_RATE_G.GLBL2_EXCH_RATE GLOBAL2_EXCHANGE_RATE,
LKP_W_GLOBAL_EXCH_RATE_G.GLBL3_EXCH_RATE GLOBAL3_EXCHANGE_RATE,
COALESCE(LKP_W_USER_D_CREATED_BY_WID.ROW_WID,0) CREATED_BY_WID,
COALESCE(LKP_W_USER_D_CHANGED_BY_WID.ROW_WID,0) CHANGED_BY_WID,
SQ_W_SUPPLIER_ACCOUNT_DS.CREATED_ON_DT CREATED_ON_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.CHANGED_ON_DT CHANGED_ON_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.AUX1_CHANGED_ON_DT AUX1_CHANGED_ON_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.AUX2_CHANGED_ON_DT AUX2_CHANGED_ON_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.AUX3_CHANGED_ON_DT AUX3_CHANGED_ON_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.AUX4_CHANGED_ON_DT AUX4_CHANGED_ON_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.SRC_EFF_FROM_DT SRC_EFF_FROM_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.SRC_EFF_TO_DT SRC_EFF_TO_DT,
(CASE
WHEN SQ_W_SUPPLIER_ACCOUNT_DS.DELETE_FLG='Y' THEN 'Y'
ELSE 'N'
END ) DELETE_FLG,
SQ_W_SUPPLIER_ACCOUNT_DS.DATASOURCE_NUM_ID DATASOURCE_NUM_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.INTEGRATION_ID INTEGRATION_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.SET_ID SET_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.TENANT_ID TENANT_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.X_CUSTOM X_CUSTOM,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.SUPPLIER_SITE_CODE,'__NOT_APPLICABLE__') SUPPLIER_SITE_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.ST_ADDRESS1 ST_ADDRESS1,
SQ_W_SUPPLIER_ACCOUNT_DS.ST_ADDRESS2 ST_ADDRESS2,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.CITY_CODE,'__NOT_APPLICABLE__') CITY_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.STATE_PROV_CODE,'__NOT_APPLICABLE__') STATE_PROV_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.COUNTRY_REGION_CODE,'__NOT_APPLICABLE__') COUNTRY_REGION_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.COUNTY_CODE,'__NOT_APPLICABLE__') COUNTY_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.REGION_CODE,'__NOT_APPLICABLE__') REGION_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.POSTAL_CODE,'__NOT_APPLICABLE__') POSTAL_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.CONTINENT_CODE,'__NOT_APPLICABLE__') CONTINENT_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.C_CITY_CODE,'__NOT_APPLICABLE__') C_CITY_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.C_STATE_PROV_CODE,'__NOT_APPLICABLE__') C_STATE_PROV_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.C_COUNTY_CODE,'__NOT_APPLICABLE__') C_COUNTY_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.C_REGION_CODE,'__NOT_APPLICABLE__') C_REGION_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.W_COUNTRY_CODE,'__NOT_APPLICABLE__') W_COUNTRY_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.C_COUNTRY_REGION_CODE,'__NOT_APPLICABLE__') C_COUNTRY_REGION_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.C_CONTINENT_CODE,'__NOT_APPLICABLE__') C_CONTINENT_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.PHONE_NUM PHONE_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.FAX_NUM FAX_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.CUSTOMER_NUM CUSTOMER_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.SUPPLIER_ID SUPPLIER_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.PARTY_ID PARTY_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.PARTY_SITE_ID PARTY_SITE_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.LOCATION_ID LOCATION_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.BU_ID BU_ID,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_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 (((BIAPPS_DW.W_SUPPLIER_ACCOUNT_DS SQ_W_SUPPLIER_ACCOUNT_DS LEFT OUTER JOIN
( /* Subselect from LKP_W_EXCH_RATE_G
*/
select
#BIAPPS.ETL_HINT_NESTED
W_EXCH_RATE_G.EXCH_RATE EXCH_RATE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_FROM_CURCY_CODE,
W_EXCH_RATE_G.W_TO_CURCY_CODE W_TO_CURCY_CODE,
W_EXCH_RATE_G.START_DT START_DT,
W_EXCH_RATE_G.END_DT END_DT,
W_EXCH_RATE_G.RATE_TYPE RATE_TYPE,
W_EXCH_RATE_G.DATASOURCE_NUM_ID DATASOURCE_NUM_ID
from BIAPPS_DW.W_EXCH_RATE_G W_EXCH_RATE_G
where (1=1)
And (W_EXCH_RATE_G.W_FROM_CURCY_CODE <> W_EXCH_RATE_G.W_TO_CURCY_CODE)
UNION
select
#BIAPPS.ETL_HINT_NESTED
1.0 EXCH_RATE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_FROM_CURCY_CODE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_TO_CURCY_CODE,
TO_DATE('18990101000000','YYYYMMDDHH24MISS') START_DT,
TO_DATE('37140101000000','YYYYMMDDHH24MISS') END_DT,
W_EXCH_RATE_G.RATE_TYPE RATE_TYPE,
W_EXCH_RATE_G.DATASOURCE_NUM_ID DATASOURCE_NUM_ID
from BIAPPS_DW.W_EXCH_RATE_G W_EXCH_RATE_G
where (1=1)
UNION
select
#BIAPPS.ETL_HINT_NESTED
1.0 EXCH_RATE,
'STAT' W_FROM_CURCY_CODE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_TO_CURCY_CODE,
TO_DATE('18990101000000','YYYYMMDDHH24MISS') START_DT,
TO_DATE('37140101000000','YYYYMMDDHH24MISS') END_DT,
W_EXCH_RATE_G.RATE_TYPE RATE_TYPE,
W_EXCH_RATE_G.DATASOURCE_NUM_ID DATASOURCE_NUM_ID
from BIAPPS_DW.W_EXCH_RATE_G W_EXCH_RATE_G
where (1=1)
UNION
select
#BIAPPS.ETL_HINT_NESTED
W_EXCH_RATE_G.EXCH_RATE EXCH_RATE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_FROM_CURCY_CODE,
W_EXCH_RATE_G.W_TO_CURCY_CODE W_TO_CURCY_CODE,
W_EXCH_RATE_G.START_DT START_DT,
W_EXCH_RATE_G.END_DT END_DT,
'BIAPPS_DEFAULT' RATE_TYPE,
W_EXCH_RATE_G.DATASOURCE_NUM_ID DATASOURCE_NUM_ID
from BIAPPS_DW.W_EXCH_RATE_G W_EXCH_RATE_G
where (1=1)
And (W_EXCH_RATE_G.W_FROM_CURCY_CODE <> W_EXCH_RATE_G.W_TO_CURCY_CODE)
And (W_EXCH_RATE_G.RATE_TYPE =
(SELECT COALESCE((SELECT W_DOMAIN_MEMBER_MAP_G.SRC_DOMAIN_MEMBER_CODE
FROM BIAPPS_DW.W_DOMAIN_MEMBER_MAP_G
WHERE SRC_DOMAIN_CODE = 'RATE_TYPE'
AND TRG_DOMAIN_MEMBER_CODE = '#BIAPPS.DEFAULT_LOC_RATE_TYPE_DOMAIN'
AND SRC_DATASOURCE_NUM_ID = W_EXCH_RATE_G.DATASOURCE_NUM_ID
AND TRG_DOMAIN_CODE = 'W_RATE_TYPE'),'#BIAPPS.DEFAULT_LOC_RATE_TYPE_DOMAIN')FROM BIAPPS_DW.W_DUAL_G ))
UNION
select
#BIAPPS.ETL_HINT_NESTED
1.0 EXCH_RATE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_FROM_CURCY_CODE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_TO_CURCY_CODE,
TO_DATE('18990101000000','YYYYMMDDHH24MISS') START_DT,
TO_DATE('37140101000000','YYYYMMDDHH24MISS') END_DT,
'BIAPPS_DEFAULT' RATE_TYPE,
W_EXCH_RATE_G.DATASOURCE_NUM_ID DATASOURCE_NUM_ID
from BIAPPS_DW.W_EXCH_RATE_G W_EXCH_RATE_G
where (1=1)
) LKP_W_EXCH_RATE_G_LOC ON SQ_W_SUPPLIER_ACCOUNT_DS.DOC_CURR_CODE = LKP_W_EXCH_RATE_G_LOC.W_FROM_CURCY_CODE
AND SQ_W_SUPPLIER_ACCOUNT_DS.LOC_CURR_CODE = LKP_W_EXCH_RATE_G_LOC.W_TO_CURCY_CODE
AND SQ_W_SUPPLIER_ACCOUNT_DS.EXCHANGE_DT >= LKP_W_EXCH_RATE_G_LOC.START_DT
AND SQ_W_SUPPLIER_ACCOUNT_DS.EXCHANGE_DT < LKP_W_EXCH_RATE_G_LOC.END_DT
AND COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.LOC_EXCH_RATE_TYPE,'#BIAPPS.DEFAULT_LOC_RATE_TYPE') = LKP_W_EXCH_RATE_G_LOC.RATE_TYPE
AND SQ_W_SUPPLIER_ACCOUNT_DS.DATASOURCE_NUM_ID = LKP_W_EXCH_RATE_G_LOC.DATASOURCE_NUM_ID) LEFT OUTER JOIN
( /* Subselect from LKP_W_USER_D
*/
select
#BIAPPS.ETL_HINT_NESTED
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 BIAPPS_DW.W_USER_D W_USER_D
where (1=1)
) LKP_W_USER_D_CHANGED_BY_WID ON SQ_W_SUPPLIER_ACCOUNT_DS.DATASOURCE_NUM_ID = LKP_W_USER_D_CHANGED_BY_WID.DATASOURCE_NUM_ID
AND SQ_W_SUPPLIER_ACCOUNT_DS.CHANGED_BY_ID = LKP_W_USER_D_CHANGED_BY_WID.INTEGRATION_ID
AND SQ_W_SUPPLIER_ACCOUNT_DS.CHANGED_ON_DT >= LKP_W_USER_D_CHANGED_BY_WID.EFFECTIVE_FROM_DT
AND SQ_W_SUPPLIER_ACCOUNT_DS.CHANGED_ON_DT < LKP_W_USER_D_CHANGED_BY_WID.EFFECTIVE_TO_DT) LEFT OUTER JOIN
( /* Subselect from LKP_W_GLOBAL_EXCH_RATE_G
*/
select
#BIAPPS.ETL_HINT_NESTED
W_GLOBAL_EXCH_RATE_G.DATASOURCE_NUM_ID DATASOURCE_NUM_ID,
W_GLOBAL_EXCH_RATE_G.W_FROM_CURRENCY_CODE W_FROM_CURRENCY_CODE,
W_GLOBAL_EXCH_RATE_G.TENANT_ID TENANT_ID,
TRUNC(W_GLOBAL_EXCH_RATE_G.EXCH_DATE) EXCH_DATE,
W_GLOBAL_EXCH_RATE_G.GLBL1_EXCH_RATE GLBL1_EXCH_RATE,
W_GLOBAL_EXCH_RATE_G.GLBL2_EXCH_RATE GLBL2_EXCH_RATE,
W_GLOBAL_EXCH_RATE_G.GLBL3_EXCH_RATE GLBL3_EXCH_RATE,
W_GLOBAL_EXCH_RATE_G.GLBL4_EXCH_RATE GLBL4_EXCH_RATE,
W_GLOBAL_EXCH_RATE_G.GLBL5_EXCH_RATE GLBL5_EXCH_RATE
from BIAPPS_DW.W_GLOBAL_EXCH_RATE_G W_GLOBAL_EXCH_RATE_G
where (1=1)
) LKP_W_GLOBAL_EXCH_RATE_G ON SQ_W_SUPPLIER_ACCOUNT_DS.DOC_CURR_CODE = LKP_W_GLOBAL_EXCH_RATE_G.W_FROM_CURRENCY_CODE
AND SQ_W_SUPPLIER_ACCOUNT_DS.EXCHANGE_DT = LKP_W_GLOBAL_EXCH_RATE_G.EXCH_DATE
AND SQ_W_SUPPLIER_ACCOUNT_DS.DATASOURCE_NUM_ID = LKP_W_GLOBAL_EXCH_RATE_G.DATASOURCE_NUM_ID
AND SQ_W_SUPPLIER_ACCOUNT_DS.TENANT_ID = LKP_W_GLOBAL_EXCH_RATE_G.TENANT_ID) LEFT OUTER JOIN
( /* Subselect from LKP_W_USER_D
*/
select
#BIAPPS.ETL_HINT_NESTED
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 BIAPPS_DW.W_USER_D W_USER_D
where (1=1)
) LKP_W_USER_D_CREATED_BY_WID ON SQ_W_SUPPLIER_ACCOUNT_DS.DATASOURCE_NUM_ID = LKP_W_USER_D_CREATED_BY_WID.DATASOURCE_NUM_ID
AND SQ_W_SUPPLIER_ACCOUNT_DS.CREATED_BY_ID = LKP_W_USER_D_CREATED_BY_WID.INTEGRATION_ID
AND SQ_W_SUPPLIER_ACCOUNT_DS.CREATED_ON_DT >= LKP_W_USER_D_CREATED_BY_WID.EFFECTIVE_FROM_DT
AND SQ_W_SUPPLIER_ACCOUNT_DS.CREATED_ON_DT < LKP_W_USER_D_CREATED_BY_WID.EFFECTIVE_TO_DT
where (1=1)
) S
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Create flow table UK index
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Creates index on flow table unique key */
create index I$__1_UK
on BIAPPS_DW.I$__1
(DATASOURCE_NUM_ID, INTEGRATION_ID,
EFFECTIVE_FROM_DT,
IND_UPDATE
)
NOLOGGING
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Analyze integration table
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
begin
dbms_stats.gather_table_stats(
ownname => 'BIAPPS_DW',
tabname => 'I$__1',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
degree => DBMS_STATS.DEFAULT_DEGREE
);
end;
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Fix effective dates (full)
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Not required for type 1 dimensions */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Flag rows for update
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Sets the flow table update indicator to U(pdate) if the record exists in the target table */
/* Also points the update at the latest target SCD timestamp */
/* Not required for full load */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Detect type 2 changes
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Sets update indicator to S (insert new type 2 record for type 2 change to current record) */
/* Only required for type 2 dimension in incremental load */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Insert new type 1 values
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* No SCD1_WID value to set */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Run Alter Session1 Commands
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
BEGIN
NULL;
END;
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Soft delete preprocess
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Identify deleted records by comparing primary extract keys with target table */
/* Full load or soft delete options not set */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Soft delete on target
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Update target table soft delete flag for records in the delete queue */
/* Full load or soft delete disabled */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Insert Unspecified record
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Skipping for incremental load */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Insert new dimension records
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Inserts new dimension records */
insert into BIAPPS_DW.W_SUPPLIER_ACCOUNT_D
(
ROW_WID,
DATASOURCE_NUM_ID,
INTEGRATION_ID,
SPLR_ACCT_NAME,
ST_ADDRESS1,
SPLR_ACCT_NUM,
COMPANY_CODE,
ALLOC_SORT_KEY,
GL_RECNCL_ACCT_NUM,
PAY_TERMS_CODE,
OUR_ACCT_NUM,
SPLR_USER_NAME,
PLAN_GROUP_CODE,
ACCT_CLERK_NUM,
ACCT_CLERK_NAME,
HEAD_OFFC_ACCT_NUM,
HEAD_OFFC_ACCT_NAME,
ALT_ACCT_NUM,
ALT_ACCT_NAME,
BOE_LIMIT_AMT,
CHECK_PAID_TIME,
TOLERANCE_GRP_CODE,
HOUSE_BANK_CODE,
PREV_SPLR_NUM,
PAYMENT_GRP_CODE,
SPLR_RECPT_TYPE_CODE,
PERSONNEL_NUM,
PERSONNEL_NAME,
DOC_CURR_CODE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
SET_ID,
TENANT_ID,
X_CUSTOM,
SUPPLIER_SITE_CODE,
ST_ADDRESS2,
CITY_CODE,
STATE_PROV_CODE,
COUNTRY_REGION_CODE,
COUNTY_CODE,
REGION_CODE,
POSTAL_CODE,
CONTINENT_CODE,
C_CITY_CODE,
C_STATE_PROV_CODE,
C_COUNTY_CODE,
C_REGION_CODE,
W_COUNTRY_CODE,
C_COUNTRY_REGION_CODE,
C_CONTINENT_CODE,
PHONE_NUM,
FAX_NUM,
CUSTOMER_NUM,
SUPPLIER_ID,
PARTY_ID,
PARTY_SITE_ID,
LOCATION_ID,
BU_ID,
ACTIVE_FLG,
CREATED_BY_WID,
CHANGED_BY_WID,
CREATED_ON_DT,
CHANGED_ON_DT,
AUX1_CHANGED_ON_DT,
AUX2_CHANGED_ON_DT,
AUX3_CHANGED_ON_DT,
AUX4_CHANGED_ON_DT,
SRC_EFF_FROM_DT,
SRC_EFF_TO_DT,
DELETE_FLG,
W_INSERT_DT,
W_UPDATE_DT,
ETL_PROC_WID,
CURRENT_FLG,
EFFECTIVE_FROM_DT,
EFFECTIVE_TO_DT
)
select
BIAPPS_DW.W_SUPPLIER_ACCOUNT_D_SEQ.NEXTVAL ROW_WID,
S.DATASOURCE_NUM_ID,
S.INTEGRATION_ID,
S.SPLR_ACCT_NAME,
S.ST_ADDRESS1,
S.SPLR_ACCT_NUM,
S.COMPANY_CODE,
S.ALLOC_SORT_KEY,
S.GL_RECNCL_ACCT_NUM,
S.PAY_TERMS_CODE,
S.OUR_ACCT_NUM,
S.SPLR_USER_NAME,
S.PLAN_GROUP_CODE,
S.ACCT_CLERK_NUM,
S.ACCT_CLERK_NAME,
S.HEAD_OFFC_ACCT_NUM,
S.HEAD_OFFC_ACCT_NAME,
S.ALT_ACCT_NUM,
S.ALT_ACCT_NAME,
S.BOE_LIMIT_AMT,
S.CHECK_PAID_TIME,
S.TOLERANCE_GRP_CODE,
S.HOUSE_BANK_CODE,
S.PREV_SPLR_NUM,
S.PAYMENT_GRP_CODE,
S.SPLR_RECPT_TYPE_CODE,
S.PERSONNEL_NUM,
S.PERSONNEL_NAME,
S.DOC_CURR_CODE,
S.LOC_CURR_CODE,
S.LOC_EXCHANGE_RATE,
S.GLOBAL1_EXCHANGE_RATE,
S.GLOBAL2_EXCHANGE_RATE,
S.GLOBAL3_EXCHANGE_RATE,
S.SET_ID,
S.TENANT_ID,
S.X_CUSTOM,
S.SUPPLIER_SITE_CODE,
S.ST_ADDRESS2,
S.CITY_CODE,
S.STATE_PROV_CODE,
S.COUNTRY_REGION_CODE,
S.COUNTY_CODE,
S.REGION_CODE,
S.POSTAL_CODE,
S.CONTINENT_CODE,
S.C_CITY_CODE,
S.C_STATE_PROV_CODE,
S.C_COUNTY_CODE,
S.C_REGION_CODE,
S.W_COUNTRY_CODE,
S.C_COUNTRY_REGION_CODE,
S.C_CONTINENT_CODE,
S.PHONE_NUM,
S.FAX_NUM,
S.CUSTOMER_NUM,
S.SUPPLIER_ID,
S.PARTY_ID,
S.PARTY_SITE_ID,
S.LOCATION_ID,
S.BU_ID,
S.ACTIVE_FLG,
S.CREATED_BY_WID,
S.CHANGED_BY_WID,
S.CREATED_ON_DT,
S.CHANGED_ON_DT,
S.AUX1_CHANGED_ON_DT,
S.AUX2_CHANGED_ON_DT,
S.AUX3_CHANGED_ON_DT,
S.AUX4_CHANGED_ON_DT,
S.SRC_EFF_FROM_DT,
S.SRC_EFF_TO_DT,
S.DELETE_FLG,
SYSDATE W_INSERT_DT,
SYSDATE W_UPDATE_DT,
#BIAPPS.ETL_PROC_WID ETL_PROC_WID,
S.IND_CURRENT_FLG CURRENT_FLG,
S.EFFECTIVE_FROM_DT,
S.EFFECTIVE_TO_DT
from BIAPPS_DW.I$__1 S
where S.IND_UPDATE IN ('I','S')
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Update existing rows
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Updates changes to existing dimension records */
/* Not required for full load */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Historize old rows
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Update existing records end dates and current flag to make room for new inserts */
/* Not required for full load */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Fix effective dates
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Not required for full load */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Update all history
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Update all history only when #BIAPPS.UPDATE_ALL_HISTORY is set to Y */
/* Columns with the OBI_SYSTEM_COLUMN flexfield set will NOT be updated */
/* Also, any field mapped to the target will have that value used */
/* Update History is not set */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Insert reference dates
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
--Check if records exists in W_ETL_LOAD_DATE TABLE
--Query run
--select count(1),MAX(LAST_MAX_DATE) from BIAPPS_DW.W_ETL_LOAD_DATES where DATASOURCE_NUM_ID = #BIAPPS.DATASOURCE_NUM_ID and PACKAGE_NAME = '' and ETL_USAGE_CODE = '#BIAPPS.ETL_USAGE_CODE'
--Result:0,
--Insert/Update reference dates
/* Insert details of completed task to the load dates table */
insert into BIAPPS_DW.W_ETL_LOAD_DATES
(DATASOURCE_NUM_ID,
PACKAGE_NAME,
TARGET_TABLE_NAME,
ETL_USAGE_CODE,
ETL_PROC_WID,
LOAD_PLAN_ID,
WIP_LOAD_START_DATE,
LAST_MAX_DATE,
ETL_LOAD_DATE,
COMMITTED
)
select
:BIAPPS.DATASOURCE_NUM_ID,
'',
'W_SUPPLIER_ACCOUNT_D',
:BIAPPS.ETL_USAGE_CODE,
:BIAPPS.ETL_PROC_WID,
:BIAPPS.EXECUTION_ID,
(SYSDATE+(-1)*#BIAPPS.PRUNE_DAYS),
(SYSDATE+(-1)*#BIAPPS.PRUNE_DAYS),
SYSDATE,
DECODE('#BIAPPS.IS_INCREMENTAL','Y','1','0')
FROM DUAL
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Insert reference dates to History Table
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Insert/Update reference dates into History Table */
insert into BIAPPS_DW.W_ETL_LOAD_DATES_LOG
(DATASOURCE_NUM_ID,
PACKAGE_NAME,
TARGET_TABLE_NAME,
ETL_USAGE_CODE,
ETL_PROC_WID,
LOAD_PLAN_ID,
SESSION_ID,
WIP_LOAD_START_DATE,
LAST_MAX_DATE,
ETL_LOAD_DATE,
COMMITTED
)
select
DATASOURCE_NUM_ID,
PACKAGE_NAME,
TARGET_TABLE_NAME,
ETL_USAGE_CODE,
ETL_PROC_WID,
LOAD_PLAN_ID,
,
WIP_LOAD_START_DATE,
LAST_MAX_DATE,
ETL_LOAD_DATE,
COMMITTED
FROM BIAPPS_DW.W_ETL_LOAD_DATES
where DATASOURCE_NUM_ID = :BIAPPS.DATASOURCE_NUM_ID
and PACKAGE_NAME = ''
and ETL_USAGE_CODE = :BIAPPS.ETL_USAGE_CODE
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Commit Transaction
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
1
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/*commit*/
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Table Maintenance After
Exe Channel:
S
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
SUNOPSIS_API
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Table Maintenance - Clean Truncated Entry
Exe Channel:
S
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
SUNOPSIS_API
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Drop flow table (I$)
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Optionally drops flow table */
drop table BIAPPS_DW.I$__1 purge
appreciate your help
Thanks,
Ali
I have added new filed called "REGISTRATION_NUMBER" to W_SUPPLIER_ACCOUNT_D .
The issue is I can see the new filed in creation table "create table BIAPPS_DW.I$__1" , but I'm not able to see the new filed in the select statement that generated by ODI.
The result is after executed the job the new filed not updated , (check screenshot& below script)
I have executed the simulation of SIL Interface.
------------------------------------------------------------
Generated Session
Session Name:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Scenario Name:
Context Code:
GLOBAL
Step
Step Name:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Step No:
1
Step Type:
F
Ok Next Step:
Ko Next Step:
Ok Exit:
0
Ko Exit:
0
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Initialization and log header
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* ===================================================
-- IKM BIAPPS Oracle Slowly Changing Dimension
-- 11.1.1.7.2.20140613
-- ===================================================
---------------------------------------------------
SCD Dimension Column Behaviour
---------------------------------------------------
Type 1 Columns (updated to match current record if UPDATE_ALL_HISTORY is set):
[SCD Behaviour - overwrite on change, not OBI System Column (on column level flexfield)]
SPLR_ACCT_NUM
COMPANY_CODE
ALLOC_SORT_KEY
GL_RECNCL_ACCT_NUM
PAY_TERMS_CODE
OUR_ACCT_NUM
SPLR_USER_NAME
PLAN_GROUP_CODE
ACCT_CLERK_NUM
ACCT_CLERK_NAME
HEAD_OFFC_ACCT_NUM
HEAD_OFFC_ACCT_NAME
ALT_ACCT_NUM
ALT_ACCT_NAME
BOE_LIMIT_AMT
CHECK_PAID_TIME
TOLERANCE_GRP_CODE
HOUSE_BANK_CODE
PREV_SPLR_NUM
PAYMENT_GRP_CODE
SPLR_RECPT_TYPE_CODE
PERSONNEL_NUM
PERSONNEL_NAME
ACTIVE_FLG
DOC_CURR_CODE
LOC_CURR_CODE
LOC_EXCHANGE_RATE
GLOBAL1_EXCHANGE_RATE
GLOBAL2_EXCHANGE_RATE
GLOBAL3_EXCHANGE_RATE
CREATED_BY_WID
CHANGED_BY_WID
CREATED_ON_DT
CHANGED_ON_DT
AUX1_CHANGED_ON_DT
AUX2_CHANGED_ON_DT
AUX3_CHANGED_ON_DT
AUX4_CHANGED_ON_DT
SRC_EFF_FROM_DT
SRC_EFF_TO_DT
DELETE_FLG
W_INSERT_DT
W_UPDATE_DT
ETL_PROC_WID
SET_ID
TENANT_ID
X_CUSTOM
SUPPLIER_SITE_CODE
ST_ADDRESS2
CITY_CODE
STATE_PROV_CODE
COUNTRY_REGION_CODE
COUNTY_CODE
REGION_CODE
POSTAL_CODE
CONTINENT_CODE
C_CITY_CODE
C_STATE_PROV_CODE
C_COUNTY_CODE
C_REGION_CODE
W_COUNTRY_CODE
C_COUNTRY_REGION_CODE
C_CONTINENT_CODE
PHONE_NUM
FAX_NUM
CUSTOMER_NUM
SUPPLIER_ID
PARTY_ID
PARTY_SITE_ID
LOCATION_ID
BU_ID
Type 2 Columns (used to detect a change for a new record if TYPE2_FLG is set):
[SCD Behaviour - insert new row on change, not mapped on TARGET]
SPLR_ACCT_NAME
ST_ADDRESS1
Change columns (used to exclude unchanged rows from processing):
[Updateable columns, not mapped on TARGET, OBI Change Column (on column level flexfield)
or if no OBI Change Columns flagged then all updateable columns not mapped on TARGET]
CHANGED_ON_DT
AUX1_CHANGED_ON_DT
AUX2_CHANGED_ON_DT
AUX3_CHANGED_ON_DT
AUX4_CHANGED_ON_DT
ST_ADDRESS1
Has ROW_WID: Y
SCD1 column:
[OBI SCD1 column (column level flexfield), not mapped on TARGET]
Not found
Bulk Mode Option: N
---------------------------------------------------
Work Objects
---------------------------------------------------
Setting flow table name to BIAPPS_DW.I$__1
=================================================== */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Table Maintenance - Clean Truncated Entry
Exe Channel:
S
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
SUNOPSIS_API
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Table Maintenance Before
Exe Channel:
S
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
SUNOPSIS_API
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Drop flow table (I$)
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
drop table BIAPPS_DW.I$__1 purge
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Run Alter Session Commands
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
BEGIN
NULL;
END;
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Alter Session - Char Semantics
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Below statement ensure table lengths are proper for Oracle Unicode Enabled DBs. */
ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Create flow table (I$)
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Creates the flow table with the parameters specified in the FLOW_TABLE_OPTIONS option */
create table BIAPPS_DW.I$__1
(
SRC_EFF_FROM_DT DATE NULL,
DATASOURCE_NUM_ID NUMBER(10) NULL,
INTEGRATION_ID VARCHAR2(80) NULL,
ROW_WID NUMBER NULL,
SPLR_ACCT_NUM VARCHAR2(4000) NULL,
SPLR_ACCT_NAME VARCHAR2(4000) NULL,
COMPANY_CODE VARCHAR2(4000) NULL,
ALLOC_SORT_KEY VARCHAR2(4000) NULL,
GL_RECNCL_ACCT_NUM VARCHAR2(4000) NULL,
PAY_TERMS_CODE VARCHAR2(4000) NULL,
OUR_ACCT_NUM VARCHAR2(4000) NULL,
SPLR_USER_NAME VARCHAR2(4000) NULL,
PLAN_GROUP_CODE VARCHAR2(4000) NULL,
ACCT_CLERK_NUM VARCHAR2(4000) NULL,
ACCT_CLERK_NAME VARCHAR2(4000) NULL,
HEAD_OFFC_ACCT_NUM VARCHAR2(4000) NULL,
HEAD_OFFC_ACCT_NAME VARCHAR2(4000) NULL,
ALT_ACCT_NUM VARCHAR2(4000) NULL,
ALT_ACCT_NAME VARCHAR2(4000) NULL,
BOE_LIMIT_AMT NUMBER NULL,
CHECK_PAID_TIME NUMBER NULL,
TOLERANCE_GRP_CODE VARCHAR2(4000) NULL,
HOUSE_BANK_CODE VARCHAR2(4000) NULL,
PREV_SPLR_NUM VARCHAR2(4000) NULL,
PAYMENT_GRP_CODE VARCHAR2(4000) NULL,
SPLR_RECPT_TYPE_CODE VARCHAR2(4000) NULL,
PERSONNEL_NUM VARCHAR2(4000) NULL,
PERSONNEL_NAME VARCHAR2(4000) NULL,
ACTIVE_FLG CHAR(1) NULL,
DOC_CURR_CODE VARCHAR2(4000) NULL,
LOC_CURR_CODE VARCHAR2(4000) NULL,
LOC_EXCHANGE_RATE NUMBER NULL,
GLOBAL1_EXCHANGE_RATE NUMBER NULL,
GLOBAL2_EXCHANGE_RATE NUMBER NULL,
GLOBAL3_EXCHANGE_RATE NUMBER NULL,
CREATED_BY_WID NUMBER NULL,
CHANGED_BY_WID NUMBER NULL,
CREATED_ON_DT DATE NULL,
CHANGED_ON_DT DATE NULL,
AUX1_CHANGED_ON_DT DATE NULL,
AUX2_CHANGED_ON_DT DATE NULL,
AUX3_CHANGED_ON_DT DATE NULL,
AUX4_CHANGED_ON_DT DATE NULL,
SRC_EFF_TO_DT DATE NULL,
EFFECTIVE_FROM_DT DATE NULL,
EFFECTIVE_TO_DT DATE NULL,
DELETE_FLG CHAR(1) NULL,
CURRENT_FLG CHAR(1) NULL,
W_INSERT_DT DATE NULL,
W_UPDATE_DT DATE NULL,
ETL_PROC_WID NUMBER NULL,
SET_ID VARCHAR2(4000) NULL,
TENANT_ID VARCHAR2(4000) NULL,
X_CUSTOM VARCHAR2(4000) NULL,
SUPPLIER_SITE_CODE VARCHAR2(4000) NULL,
ST_ADDRESS1 VARCHAR2(4000) NULL,
ST_ADDRESS2 VARCHAR2(4000) NULL,
CITY_CODE VARCHAR2(4000) NULL,
STATE_PROV_CODE VARCHAR2(4000) NULL,
COUNTRY_REGION_CODE VARCHAR2(4000) NULL,
COUNTY_CODE VARCHAR2(4000) NULL,
REGION_CODE VARCHAR2(4000) NULL,
POSTAL_CODE VARCHAR2(4000) NULL,
CONTINENT_CODE VARCHAR2(4000) NULL,
C_CITY_CODE VARCHAR2(4000) NULL,
C_STATE_PROV_CODE VARCHAR2(4000) NULL,
C_COUNTY_CODE VARCHAR2(4000) NULL,
C_REGION_CODE VARCHAR2(4000) NULL,
W_COUNTRY_CODE VARCHAR2(4000) NULL,
C_COUNTRY_REGION_CODE VARCHAR2(4000) NULL,
C_CONTINENT_CODE VARCHAR2(4000) NULL,
PHONE_NUM VARCHAR2(4000) NULL,
FAX_NUM VARCHAR2(4000) NULL,
CUSTOMER_NUM VARCHAR2(4000) NULL,
SUPPLIER_ID NUMBER NULL,
PARTY_ID NUMBER NULL,
PARTY_SITE_ID NUMBER NULL,
LOCATION_ID NUMBER NULL,
BU_ID NUMBER NULL,
REGISTRATION_NUMBER VARCHAR2(4000) NULL,
IND_UPDATE CHAR(1),
IND_CURRENT_FLG CHAR(1)
)
NOLOGGING
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Insert flow into I$ table
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* DETECTION_STRATEGY = NOT_EXISTS */
insert /*+ append */ into BIAPPS_DW.I$__1
(
SPLR_ACCT_NUM,
SPLR_ACCT_NAME,
COMPANY_CODE,
ALLOC_SORT_KEY,
GL_RECNCL_ACCT_NUM,
PAY_TERMS_CODE,
OUR_ACCT_NUM,
SPLR_USER_NAME,
PLAN_GROUP_CODE,
ACCT_CLERK_NUM,
ACCT_CLERK_NAME,
HEAD_OFFC_ACCT_NUM,
HEAD_OFFC_ACCT_NAME,
ALT_ACCT_NUM,
ALT_ACCT_NAME,
BOE_LIMIT_AMT,
CHECK_PAID_TIME,
TOLERANCE_GRP_CODE,
HOUSE_BANK_CODE,
PREV_SPLR_NUM,
PAYMENT_GRP_CODE,
SPLR_RECPT_TYPE_CODE,
PERSONNEL_NUM,
PERSONNEL_NAME,
ACTIVE_FLG,
DOC_CURR_CODE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
CREATED_BY_WID,
CHANGED_BY_WID,
CREATED_ON_DT,
CHANGED_ON_DT,
AUX1_CHANGED_ON_DT,
AUX2_CHANGED_ON_DT,
AUX3_CHANGED_ON_DT,
AUX4_CHANGED_ON_DT,
SRC_EFF_FROM_DT,
SRC_EFF_TO_DT,
DELETE_FLG,
DATASOURCE_NUM_ID,
INTEGRATION_ID,
SET_ID,
TENANT_ID,
X_CUSTOM,
SUPPLIER_SITE_CODE,
ST_ADDRESS1,
ST_ADDRESS2,
CITY_CODE,
STATE_PROV_CODE,
COUNTRY_REGION_CODE,
COUNTY_CODE,
REGION_CODE,
POSTAL_CODE,
CONTINENT_CODE,
C_CITY_CODE,
C_STATE_PROV_CODE,
C_COUNTY_CODE,
C_REGION_CODE,
W_COUNTRY_CODE,
C_COUNTRY_REGION_CODE,
C_CONTINENT_CODE,
PHONE_NUM,
FAX_NUM,
CUSTOMER_NUM,
SUPPLIER_ID,
PARTY_ID,
PARTY_SITE_ID,
LOCATION_ID,
BU_ID,
EFFECTIVE_FROM_DT,
EFFECTIVE_TO_DT,
IND_CURRENT_FLG,
IND_UPDATE
)
select
SPLR_ACCT_NUM,
SPLR_ACCT_NAME,
COMPANY_CODE,
ALLOC_SORT_KEY,
GL_RECNCL_ACCT_NUM,
PAY_TERMS_CODE,
OUR_ACCT_NUM,
SPLR_USER_NAME,
PLAN_GROUP_CODE,
ACCT_CLERK_NUM,
ACCT_CLERK_NAME,
HEAD_OFFC_ACCT_NUM,
HEAD_OFFC_ACCT_NAME,
ALT_ACCT_NUM,
ALT_ACCT_NAME,
BOE_LIMIT_AMT,
CHECK_PAID_TIME,
TOLERANCE_GRP_CODE,
HOUSE_BANK_CODE,
PREV_SPLR_NUM,
PAYMENT_GRP_CODE,
SPLR_RECPT_TYPE_CODE,
PERSONNEL_NUM,
PERSONNEL_NAME,
ACTIVE_FLG,
DOC_CURR_CODE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
CREATED_BY_WID,
CHANGED_BY_WID,
CREATED_ON_DT,
CHANGED_ON_DT,
AUX1_CHANGED_ON_DT,
AUX2_CHANGED_ON_DT,
AUX3_CHANGED_ON_DT,
AUX4_CHANGED_ON_DT,
SRC_EFF_FROM_DT,
SRC_EFF_TO_DT,
DELETE_FLG,
DATASOURCE_NUM_ID,
INTEGRATION_ID,
SET_ID,
TENANT_ID,
X_CUSTOM,
SUPPLIER_SITE_CODE,
ST_ADDRESS1,
ST_ADDRESS2,
CITY_CODE,
STATE_PROV_CODE,
COUNTRY_REGION_CODE,
COUNTY_CODE,
REGION_CODE,
POSTAL_CODE,
CONTINENT_CODE,
C_CITY_CODE,
C_STATE_PROV_CODE,
C_COUNTY_CODE,
C_REGION_CODE,
W_COUNTRY_CODE,
C_COUNTRY_REGION_CODE,
C_CONTINENT_CODE,
PHONE_NUM,
FAX_NUM,
CUSTOMER_NUM,
SUPPLIER_ID,
PARTY_ID,
PARTY_SITE_ID,
LOCATION_ID,
BU_ID,
EFFECTIVE_FROM_DT,
EFFECTIVE_TO_DT,
'Y' IND_CURRENT_FLG,
IND_UPDATE
from (
select
SQ_W_SUPPLIER_ACCOUNT_DS.SPLR_ACCT_NUM SPLR_ACCT_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.SPLR_ACCT_NAME SPLR_ACCT_NAME,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.COMPANY_CODE,'__NOT_APPLICABLE__') COMPANY_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.ALLOC_SORT_KEY ALLOC_SORT_KEY,
SQ_W_SUPPLIER_ACCOUNT_DS.GL_RECNCL_ACCT_NUM GL_RECNCL_ACCT_NUM,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.PAY_TERMS_CODE,'__NOT_APPLICABLE__') PAY_TERMS_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.OUR_ACCT_NUM OUR_ACCT_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.SPLR_USER_NAME SPLR_USER_NAME,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.PLAN_GROUP_CODE,'__NOT_APPLICABLE__') PLAN_GROUP_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.ACCT_CLERK_NUM ACCT_CLERK_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.ACCT_CLERK_NAME ACCT_CLERK_NAME,
SQ_W_SUPPLIER_ACCOUNT_DS.HEAD_OFFC_ACCT_NUM HEAD_OFFC_ACCT_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.HEAD_OFFC_ACCT_NAME HEAD_OFFC_ACCT_NAME,
SQ_W_SUPPLIER_ACCOUNT_DS.ALT_ACCT_NUM ALT_ACCT_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.ALT_ACCT_NAME ALT_ACCT_NAME,
SQ_W_SUPPLIER_ACCOUNT_DS.BOE_LIMIT_AMT BOE_LIMIT_AMT,
SQ_W_SUPPLIER_ACCOUNT_DS.CHECK_PAID_TIME CHECK_PAID_TIME,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.TOLERANCE_GRP_CODE,'__NOT_APPLICABLE__') TOLERANCE_GRP_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.HOUSE_BANK_CODE,'__NOT_APPLICABLE__') HOUSE_BANK_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.PREV_SPLR_NUM PREV_SPLR_NUM,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.PAYMENT_GRP_CODE,'__NOT_APPLICABLE__') PAYMENT_GRP_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.SPLR_RECPT_TYPE_CODE,'__NOT_APPLICABLE__') SPLR_RECPT_TYPE_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.PERSONNEL_NUM PERSONNEL_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.PERSONNEL_NAME PERSONNEL_NAME,
SQ_W_SUPPLIER_ACCOUNT_DS.ACTIVE_FLG ACTIVE_FLG,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.DOC_CURR_CODE,'__NOT_APPLICABLE__') DOC_CURR_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.LOC_CURR_CODE,'__NOT_APPLICABLE__') LOC_CURR_CODE,
LKP_W_EXCH_RATE_G_LOC.EXCH_RATE LOC_EXCHANGE_RATE,
LKP_W_GLOBAL_EXCH_RATE_G.GLBL1_EXCH_RATE GLOBAL1_EXCHANGE_RATE,
LKP_W_GLOBAL_EXCH_RATE_G.GLBL2_EXCH_RATE GLOBAL2_EXCHANGE_RATE,
LKP_W_GLOBAL_EXCH_RATE_G.GLBL3_EXCH_RATE GLOBAL3_EXCHANGE_RATE,
COALESCE(LKP_W_USER_D_CREATED_BY_WID.ROW_WID,0) CREATED_BY_WID,
COALESCE(LKP_W_USER_D_CHANGED_BY_WID.ROW_WID,0) CHANGED_BY_WID,
SQ_W_SUPPLIER_ACCOUNT_DS.CREATED_ON_DT CREATED_ON_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.CHANGED_ON_DT CHANGED_ON_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.AUX1_CHANGED_ON_DT AUX1_CHANGED_ON_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.AUX2_CHANGED_ON_DT AUX2_CHANGED_ON_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.AUX3_CHANGED_ON_DT AUX3_CHANGED_ON_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.AUX4_CHANGED_ON_DT AUX4_CHANGED_ON_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.SRC_EFF_FROM_DT SRC_EFF_FROM_DT,
SQ_W_SUPPLIER_ACCOUNT_DS.SRC_EFF_TO_DT SRC_EFF_TO_DT,
(CASE
WHEN SQ_W_SUPPLIER_ACCOUNT_DS.DELETE_FLG='Y' THEN 'Y'
ELSE 'N'
END ) DELETE_FLG,
SQ_W_SUPPLIER_ACCOUNT_DS.DATASOURCE_NUM_ID DATASOURCE_NUM_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.INTEGRATION_ID INTEGRATION_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.SET_ID SET_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.TENANT_ID TENANT_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.X_CUSTOM X_CUSTOM,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.SUPPLIER_SITE_CODE,'__NOT_APPLICABLE__') SUPPLIER_SITE_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.ST_ADDRESS1 ST_ADDRESS1,
SQ_W_SUPPLIER_ACCOUNT_DS.ST_ADDRESS2 ST_ADDRESS2,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.CITY_CODE,'__NOT_APPLICABLE__') CITY_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.STATE_PROV_CODE,'__NOT_APPLICABLE__') STATE_PROV_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.COUNTRY_REGION_CODE,'__NOT_APPLICABLE__') COUNTRY_REGION_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.COUNTY_CODE,'__NOT_APPLICABLE__') COUNTY_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.REGION_CODE,'__NOT_APPLICABLE__') REGION_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.POSTAL_CODE,'__NOT_APPLICABLE__') POSTAL_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.CONTINENT_CODE,'__NOT_APPLICABLE__') CONTINENT_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.C_CITY_CODE,'__NOT_APPLICABLE__') C_CITY_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.C_STATE_PROV_CODE,'__NOT_APPLICABLE__') C_STATE_PROV_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.C_COUNTY_CODE,'__NOT_APPLICABLE__') C_COUNTY_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.C_REGION_CODE,'__NOT_APPLICABLE__') C_REGION_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.W_COUNTRY_CODE,'__NOT_APPLICABLE__') W_COUNTRY_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.C_COUNTRY_REGION_CODE,'__NOT_APPLICABLE__') C_COUNTRY_REGION_CODE,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.C_CONTINENT_CODE,'__NOT_APPLICABLE__') C_CONTINENT_CODE,
SQ_W_SUPPLIER_ACCOUNT_DS.PHONE_NUM PHONE_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.FAX_NUM FAX_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.CUSTOMER_NUM CUSTOMER_NUM,
SQ_W_SUPPLIER_ACCOUNT_DS.SUPPLIER_ID SUPPLIER_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.PARTY_ID PARTY_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.PARTY_SITE_ID PARTY_SITE_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.LOCATION_ID LOCATION_ID,
SQ_W_SUPPLIER_ACCOUNT_DS.BU_ID BU_ID,
COALESCE(SQ_W_SUPPLIER_ACCOUNT_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 (((BIAPPS_DW.W_SUPPLIER_ACCOUNT_DS SQ_W_SUPPLIER_ACCOUNT_DS LEFT OUTER JOIN
( /* Subselect from LKP_W_EXCH_RATE_G
*/
select
#BIAPPS.ETL_HINT_NESTED
W_EXCH_RATE_G.EXCH_RATE EXCH_RATE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_FROM_CURCY_CODE,
W_EXCH_RATE_G.W_TO_CURCY_CODE W_TO_CURCY_CODE,
W_EXCH_RATE_G.START_DT START_DT,
W_EXCH_RATE_G.END_DT END_DT,
W_EXCH_RATE_G.RATE_TYPE RATE_TYPE,
W_EXCH_RATE_G.DATASOURCE_NUM_ID DATASOURCE_NUM_ID
from BIAPPS_DW.W_EXCH_RATE_G W_EXCH_RATE_G
where (1=1)
And (W_EXCH_RATE_G.W_FROM_CURCY_CODE <> W_EXCH_RATE_G.W_TO_CURCY_CODE)
UNION
select
#BIAPPS.ETL_HINT_NESTED
1.0 EXCH_RATE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_FROM_CURCY_CODE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_TO_CURCY_CODE,
TO_DATE('18990101000000','YYYYMMDDHH24MISS') START_DT,
TO_DATE('37140101000000','YYYYMMDDHH24MISS') END_DT,
W_EXCH_RATE_G.RATE_TYPE RATE_TYPE,
W_EXCH_RATE_G.DATASOURCE_NUM_ID DATASOURCE_NUM_ID
from BIAPPS_DW.W_EXCH_RATE_G W_EXCH_RATE_G
where (1=1)
UNION
select
#BIAPPS.ETL_HINT_NESTED
1.0 EXCH_RATE,
'STAT' W_FROM_CURCY_CODE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_TO_CURCY_CODE,
TO_DATE('18990101000000','YYYYMMDDHH24MISS') START_DT,
TO_DATE('37140101000000','YYYYMMDDHH24MISS') END_DT,
W_EXCH_RATE_G.RATE_TYPE RATE_TYPE,
W_EXCH_RATE_G.DATASOURCE_NUM_ID DATASOURCE_NUM_ID
from BIAPPS_DW.W_EXCH_RATE_G W_EXCH_RATE_G
where (1=1)
UNION
select
#BIAPPS.ETL_HINT_NESTED
W_EXCH_RATE_G.EXCH_RATE EXCH_RATE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_FROM_CURCY_CODE,
W_EXCH_RATE_G.W_TO_CURCY_CODE W_TO_CURCY_CODE,
W_EXCH_RATE_G.START_DT START_DT,
W_EXCH_RATE_G.END_DT END_DT,
'BIAPPS_DEFAULT' RATE_TYPE,
W_EXCH_RATE_G.DATASOURCE_NUM_ID DATASOURCE_NUM_ID
from BIAPPS_DW.W_EXCH_RATE_G W_EXCH_RATE_G
where (1=1)
And (W_EXCH_RATE_G.W_FROM_CURCY_CODE <> W_EXCH_RATE_G.W_TO_CURCY_CODE)
And (W_EXCH_RATE_G.RATE_TYPE =
(SELECT COALESCE((SELECT W_DOMAIN_MEMBER_MAP_G.SRC_DOMAIN_MEMBER_CODE
FROM BIAPPS_DW.W_DOMAIN_MEMBER_MAP_G
WHERE SRC_DOMAIN_CODE = 'RATE_TYPE'
AND TRG_DOMAIN_MEMBER_CODE = '#BIAPPS.DEFAULT_LOC_RATE_TYPE_DOMAIN'
AND SRC_DATASOURCE_NUM_ID = W_EXCH_RATE_G.DATASOURCE_NUM_ID
AND TRG_DOMAIN_CODE = 'W_RATE_TYPE'),'#BIAPPS.DEFAULT_LOC_RATE_TYPE_DOMAIN')FROM BIAPPS_DW.W_DUAL_G ))
UNION
select
#BIAPPS.ETL_HINT_NESTED
1.0 EXCH_RATE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_FROM_CURCY_CODE,
W_EXCH_RATE_G.W_FROM_CURCY_CODE W_TO_CURCY_CODE,
TO_DATE('18990101000000','YYYYMMDDHH24MISS') START_DT,
TO_DATE('37140101000000','YYYYMMDDHH24MISS') END_DT,
'BIAPPS_DEFAULT' RATE_TYPE,
W_EXCH_RATE_G.DATASOURCE_NUM_ID DATASOURCE_NUM_ID
from BIAPPS_DW.W_EXCH_RATE_G W_EXCH_RATE_G
where (1=1)
) LKP_W_EXCH_RATE_G_LOC ON SQ_W_SUPPLIER_ACCOUNT_DS.DOC_CURR_CODE = LKP_W_EXCH_RATE_G_LOC.W_FROM_CURCY_CODE
AND SQ_W_SUPPLIER_ACCOUNT_DS.LOC_CURR_CODE = LKP_W_EXCH_RATE_G_LOC.W_TO_CURCY_CODE
AND SQ_W_SUPPLIER_ACCOUNT_DS.EXCHANGE_DT >= LKP_W_EXCH_RATE_G_LOC.START_DT
AND SQ_W_SUPPLIER_ACCOUNT_DS.EXCHANGE_DT < LKP_W_EXCH_RATE_G_LOC.END_DT
AND COALESCE(SQ_W_SUPPLIER_ACCOUNT_DS.LOC_EXCH_RATE_TYPE,'#BIAPPS.DEFAULT_LOC_RATE_TYPE') = LKP_W_EXCH_RATE_G_LOC.RATE_TYPE
AND SQ_W_SUPPLIER_ACCOUNT_DS.DATASOURCE_NUM_ID = LKP_W_EXCH_RATE_G_LOC.DATASOURCE_NUM_ID) LEFT OUTER JOIN
( /* Subselect from LKP_W_USER_D
*/
select
#BIAPPS.ETL_HINT_NESTED
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 BIAPPS_DW.W_USER_D W_USER_D
where (1=1)
) LKP_W_USER_D_CHANGED_BY_WID ON SQ_W_SUPPLIER_ACCOUNT_DS.DATASOURCE_NUM_ID = LKP_W_USER_D_CHANGED_BY_WID.DATASOURCE_NUM_ID
AND SQ_W_SUPPLIER_ACCOUNT_DS.CHANGED_BY_ID = LKP_W_USER_D_CHANGED_BY_WID.INTEGRATION_ID
AND SQ_W_SUPPLIER_ACCOUNT_DS.CHANGED_ON_DT >= LKP_W_USER_D_CHANGED_BY_WID.EFFECTIVE_FROM_DT
AND SQ_W_SUPPLIER_ACCOUNT_DS.CHANGED_ON_DT < LKP_W_USER_D_CHANGED_BY_WID.EFFECTIVE_TO_DT) LEFT OUTER JOIN
( /* Subselect from LKP_W_GLOBAL_EXCH_RATE_G
*/
select
#BIAPPS.ETL_HINT_NESTED
W_GLOBAL_EXCH_RATE_G.DATASOURCE_NUM_ID DATASOURCE_NUM_ID,
W_GLOBAL_EXCH_RATE_G.W_FROM_CURRENCY_CODE W_FROM_CURRENCY_CODE,
W_GLOBAL_EXCH_RATE_G.TENANT_ID TENANT_ID,
TRUNC(W_GLOBAL_EXCH_RATE_G.EXCH_DATE) EXCH_DATE,
W_GLOBAL_EXCH_RATE_G.GLBL1_EXCH_RATE GLBL1_EXCH_RATE,
W_GLOBAL_EXCH_RATE_G.GLBL2_EXCH_RATE GLBL2_EXCH_RATE,
W_GLOBAL_EXCH_RATE_G.GLBL3_EXCH_RATE GLBL3_EXCH_RATE,
W_GLOBAL_EXCH_RATE_G.GLBL4_EXCH_RATE GLBL4_EXCH_RATE,
W_GLOBAL_EXCH_RATE_G.GLBL5_EXCH_RATE GLBL5_EXCH_RATE
from BIAPPS_DW.W_GLOBAL_EXCH_RATE_G W_GLOBAL_EXCH_RATE_G
where (1=1)
) LKP_W_GLOBAL_EXCH_RATE_G ON SQ_W_SUPPLIER_ACCOUNT_DS.DOC_CURR_CODE = LKP_W_GLOBAL_EXCH_RATE_G.W_FROM_CURRENCY_CODE
AND SQ_W_SUPPLIER_ACCOUNT_DS.EXCHANGE_DT = LKP_W_GLOBAL_EXCH_RATE_G.EXCH_DATE
AND SQ_W_SUPPLIER_ACCOUNT_DS.DATASOURCE_NUM_ID = LKP_W_GLOBAL_EXCH_RATE_G.DATASOURCE_NUM_ID
AND SQ_W_SUPPLIER_ACCOUNT_DS.TENANT_ID = LKP_W_GLOBAL_EXCH_RATE_G.TENANT_ID) LEFT OUTER JOIN
( /* Subselect from LKP_W_USER_D
*/
select
#BIAPPS.ETL_HINT_NESTED
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 BIAPPS_DW.W_USER_D W_USER_D
where (1=1)
) LKP_W_USER_D_CREATED_BY_WID ON SQ_W_SUPPLIER_ACCOUNT_DS.DATASOURCE_NUM_ID = LKP_W_USER_D_CREATED_BY_WID.DATASOURCE_NUM_ID
AND SQ_W_SUPPLIER_ACCOUNT_DS.CREATED_BY_ID = LKP_W_USER_D_CREATED_BY_WID.INTEGRATION_ID
AND SQ_W_SUPPLIER_ACCOUNT_DS.CREATED_ON_DT >= LKP_W_USER_D_CREATED_BY_WID.EFFECTIVE_FROM_DT
AND SQ_W_SUPPLIER_ACCOUNT_DS.CREATED_ON_DT < LKP_W_USER_D_CREATED_BY_WID.EFFECTIVE_TO_DT
where (1=1)
) S
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Create flow table UK index
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Creates index on flow table unique key */
create index I$__1_UK
on BIAPPS_DW.I$__1
(DATASOURCE_NUM_ID, INTEGRATION_ID,
EFFECTIVE_FROM_DT,
IND_UPDATE
)
NOLOGGING
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Analyze integration table
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
begin
dbms_stats.gather_table_stats(
ownname => 'BIAPPS_DW',
tabname => 'I$__1',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
degree => DBMS_STATS.DEFAULT_DEGREE
);
end;
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Fix effective dates (full)
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Not required for type 1 dimensions */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Flag rows for update
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Sets the flow table update indicator to U(pdate) if the record exists in the target table */
/* Also points the update at the latest target SCD timestamp */
/* Not required for full load */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Detect type 2 changes
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Sets update indicator to S (insert new type 2 record for type 2 change to current record) */
/* Only required for type 2 dimension in incremental load */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Insert new type 1 values
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* No SCD1_WID value to set */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Run Alter Session1 Commands
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
BEGIN
NULL;
END;
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Soft delete preprocess
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Identify deleted records by comparing primary extract keys with target table */
/* Full load or soft delete options not set */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Soft delete on target
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Update target table soft delete flag for records in the delete queue */
/* Full load or soft delete disabled */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Insert Unspecified record
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Skipping for incremental load */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Insert new dimension records
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Inserts new dimension records */
insert into BIAPPS_DW.W_SUPPLIER_ACCOUNT_D
(
ROW_WID,
DATASOURCE_NUM_ID,
INTEGRATION_ID,
SPLR_ACCT_NAME,
ST_ADDRESS1,
SPLR_ACCT_NUM,
COMPANY_CODE,
ALLOC_SORT_KEY,
GL_RECNCL_ACCT_NUM,
PAY_TERMS_CODE,
OUR_ACCT_NUM,
SPLR_USER_NAME,
PLAN_GROUP_CODE,
ACCT_CLERK_NUM,
ACCT_CLERK_NAME,
HEAD_OFFC_ACCT_NUM,
HEAD_OFFC_ACCT_NAME,
ALT_ACCT_NUM,
ALT_ACCT_NAME,
BOE_LIMIT_AMT,
CHECK_PAID_TIME,
TOLERANCE_GRP_CODE,
HOUSE_BANK_CODE,
PREV_SPLR_NUM,
PAYMENT_GRP_CODE,
SPLR_RECPT_TYPE_CODE,
PERSONNEL_NUM,
PERSONNEL_NAME,
DOC_CURR_CODE,
LOC_CURR_CODE,
LOC_EXCHANGE_RATE,
GLOBAL1_EXCHANGE_RATE,
GLOBAL2_EXCHANGE_RATE,
GLOBAL3_EXCHANGE_RATE,
SET_ID,
TENANT_ID,
X_CUSTOM,
SUPPLIER_SITE_CODE,
ST_ADDRESS2,
CITY_CODE,
STATE_PROV_CODE,
COUNTRY_REGION_CODE,
COUNTY_CODE,
REGION_CODE,
POSTAL_CODE,
CONTINENT_CODE,
C_CITY_CODE,
C_STATE_PROV_CODE,
C_COUNTY_CODE,
C_REGION_CODE,
W_COUNTRY_CODE,
C_COUNTRY_REGION_CODE,
C_CONTINENT_CODE,
PHONE_NUM,
FAX_NUM,
CUSTOMER_NUM,
SUPPLIER_ID,
PARTY_ID,
PARTY_SITE_ID,
LOCATION_ID,
BU_ID,
ACTIVE_FLG,
CREATED_BY_WID,
CHANGED_BY_WID,
CREATED_ON_DT,
CHANGED_ON_DT,
AUX1_CHANGED_ON_DT,
AUX2_CHANGED_ON_DT,
AUX3_CHANGED_ON_DT,
AUX4_CHANGED_ON_DT,
SRC_EFF_FROM_DT,
SRC_EFF_TO_DT,
DELETE_FLG,
W_INSERT_DT,
W_UPDATE_DT,
ETL_PROC_WID,
CURRENT_FLG,
EFFECTIVE_FROM_DT,
EFFECTIVE_TO_DT
)
select
BIAPPS_DW.W_SUPPLIER_ACCOUNT_D_SEQ.NEXTVAL ROW_WID,
S.DATASOURCE_NUM_ID,
S.INTEGRATION_ID,
S.SPLR_ACCT_NAME,
S.ST_ADDRESS1,
S.SPLR_ACCT_NUM,
S.COMPANY_CODE,
S.ALLOC_SORT_KEY,
S.GL_RECNCL_ACCT_NUM,
S.PAY_TERMS_CODE,
S.OUR_ACCT_NUM,
S.SPLR_USER_NAME,
S.PLAN_GROUP_CODE,
S.ACCT_CLERK_NUM,
S.ACCT_CLERK_NAME,
S.HEAD_OFFC_ACCT_NUM,
S.HEAD_OFFC_ACCT_NAME,
S.ALT_ACCT_NUM,
S.ALT_ACCT_NAME,
S.BOE_LIMIT_AMT,
S.CHECK_PAID_TIME,
S.TOLERANCE_GRP_CODE,
S.HOUSE_BANK_CODE,
S.PREV_SPLR_NUM,
S.PAYMENT_GRP_CODE,
S.SPLR_RECPT_TYPE_CODE,
S.PERSONNEL_NUM,
S.PERSONNEL_NAME,
S.DOC_CURR_CODE,
S.LOC_CURR_CODE,
S.LOC_EXCHANGE_RATE,
S.GLOBAL1_EXCHANGE_RATE,
S.GLOBAL2_EXCHANGE_RATE,
S.GLOBAL3_EXCHANGE_RATE,
S.SET_ID,
S.TENANT_ID,
S.X_CUSTOM,
S.SUPPLIER_SITE_CODE,
S.ST_ADDRESS2,
S.CITY_CODE,
S.STATE_PROV_CODE,
S.COUNTRY_REGION_CODE,
S.COUNTY_CODE,
S.REGION_CODE,
S.POSTAL_CODE,
S.CONTINENT_CODE,
S.C_CITY_CODE,
S.C_STATE_PROV_CODE,
S.C_COUNTY_CODE,
S.C_REGION_CODE,
S.W_COUNTRY_CODE,
S.C_COUNTRY_REGION_CODE,
S.C_CONTINENT_CODE,
S.PHONE_NUM,
S.FAX_NUM,
S.CUSTOMER_NUM,
S.SUPPLIER_ID,
S.PARTY_ID,
S.PARTY_SITE_ID,
S.LOCATION_ID,
S.BU_ID,
S.ACTIVE_FLG,
S.CREATED_BY_WID,
S.CHANGED_BY_WID,
S.CREATED_ON_DT,
S.CHANGED_ON_DT,
S.AUX1_CHANGED_ON_DT,
S.AUX2_CHANGED_ON_DT,
S.AUX3_CHANGED_ON_DT,
S.AUX4_CHANGED_ON_DT,
S.SRC_EFF_FROM_DT,
S.SRC_EFF_TO_DT,
S.DELETE_FLG,
SYSDATE W_INSERT_DT,
SYSDATE W_UPDATE_DT,
#BIAPPS.ETL_PROC_WID ETL_PROC_WID,
S.IND_CURRENT_FLG CURRENT_FLG,
S.EFFECTIVE_FROM_DT,
S.EFFECTIVE_TO_DT
from BIAPPS_DW.I$__1 S
where S.IND_UPDATE IN ('I','S')
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Update existing rows
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Updates changes to existing dimension records */
/* Not required for full load */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Historize old rows
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Update existing records end dates and current flag to make room for new inserts */
/* Not required for full load */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Fix effective dates
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Not required for full load */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Update all history
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Update all history only when #BIAPPS.UPDATE_ALL_HISTORY is set to Y */
/* Columns with the OBI_SYSTEM_COLUMN flexfield set will NOT be updated */
/* Also, any field mapped to the target will have that value used */
/* Update History is not set */
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Insert reference dates
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
--Check if records exists in W_ETL_LOAD_DATE TABLE
--Query run
--select count(1),MAX(LAST_MAX_DATE) from BIAPPS_DW.W_ETL_LOAD_DATES where DATASOURCE_NUM_ID = #BIAPPS.DATASOURCE_NUM_ID and PACKAGE_NAME = '' and ETL_USAGE_CODE = '#BIAPPS.ETL_USAGE_CODE'
--Result:0,
--Insert/Update reference dates
/* Insert details of completed task to the load dates table */
insert into BIAPPS_DW.W_ETL_LOAD_DATES
(DATASOURCE_NUM_ID,
PACKAGE_NAME,
TARGET_TABLE_NAME,
ETL_USAGE_CODE,
ETL_PROC_WID,
LOAD_PLAN_ID,
WIP_LOAD_START_DATE,
LAST_MAX_DATE,
ETL_LOAD_DATE,
COMMITTED
)
select
:BIAPPS.DATASOURCE_NUM_ID,
'',
'W_SUPPLIER_ACCOUNT_D',
:BIAPPS.ETL_USAGE_CODE,
:BIAPPS.ETL_PROC_WID,
:BIAPPS.EXECUTION_ID,
(SYSDATE+(-1)*#BIAPPS.PRUNE_DAYS),
(SYSDATE+(-1)*#BIAPPS.PRUNE_DAYS),
SYSDATE,
DECODE('#BIAPPS.IS_INCREMENTAL','Y','1','0')
FROM DUAL
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Insert reference dates to History Table
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
0
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Insert/Update reference dates into History Table */
insert into BIAPPS_DW.W_ETL_LOAD_DATES_LOG
(DATASOURCE_NUM_ID,
PACKAGE_NAME,
TARGET_TABLE_NAME,
ETL_USAGE_CODE,
ETL_PROC_WID,
LOAD_PLAN_ID,
SESSION_ID,
WIP_LOAD_START_DATE,
LAST_MAX_DATE,
ETL_LOAD_DATE,
COMMITTED
)
select
DATASOURCE_NUM_ID,
PACKAGE_NAME,
TARGET_TABLE_NAME,
ETL_USAGE_CODE,
ETL_PROC_WID,
LOAD_PLAN_ID,
,
WIP_LOAD_START_DATE,
LAST_MAX_DATE,
ETL_LOAD_DATE,
COMMITTED
FROM BIAPPS_DW.W_ETL_LOAD_DATES
where DATASOURCE_NUM_ID = :BIAPPS.DATASOURCE_NUM_ID
and PACKAGE_NAME = ''
and ETL_USAGE_CODE = :BIAPPS.ETL_USAGE_CODE
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Commit Transaction
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
1
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/*commit*/
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Table Maintenance After
Exe Channel:
S
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
SUNOPSIS_API
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Table Maintenance - Clean Truncated Entry
Exe Channel:
S
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
Target Technology Internal Name:
SUNOPSIS_API
Task
Task Type :
J
Task Name 1:
Integration
Task Name 2:
SIL_SupplierAccountDimension.W_SUPPLIER_ACCOUNT_D
Task Name 3:
Drop flow table (I$)
Exe Channel:
J
Source Context Code:
GLOBAL
Source Logical Schema Name:
DW_BIAPPS11G
Source Connection Name:
Source Commit Indicator:
Source Isolation Level:
Source Plan Comp:
0
Source Technology Internal Name:
ORACLE
Target Context Code:
GLOBAL
Target Logical Schema Name:
DW_BIAPPS11G
Target Connection Name:
Target Commit Indicator:
Target Isolation Level:
Target Plan Comp:
0
Target Technology Internal Name:
ORACLE
Default Command (Destination):
/* Optionally drops flow table */
drop table BIAPPS_DW.I$__1 purge
appreciate your help
Thanks,
Ali
Reply all
Reply to author
Forward
0 new messages