BIAPPS 7961 ETL does not load on 1 task
remc0Dec 7 2010 — edited Dec 15 2010Hi all,
I have installed BI Apps 7961 with eBS R12 as a source and SQL server 2008 as a target.
Now I am loading all the data but one task does not succeed.
And all other task do load properly.
when I look in the monitor than I can see that it does not have loaded any records yet (and wil not load it as well, since you can leave it for a couple of days without any result).
However, it worked before, but for some reason not anymore...
any suggestions?
Here is the session log:
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6228 : Writing session output to log file [C:\Informatica\PowerCenter8.6.1\server\infa_shared\SessLogs\ORA_R12.DATAWAREHOUSE.SDE_ORAR12_Adaptor.SDE_ORA_PayrollFact_Full.log].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6228 : Writing session output to log file [C:\Informatica\PowerCenter8.6.1\server\infa_shared\SessLogs\ORA_R12.DATAWAREHOUSE.SDE_ORAR12_Adaptor.SDE_ORA_PayrollFact_Full.log].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : VAR_27028 : Use override value [DataWarehouse] for session parameter:[$DBConnection_OLAP].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : VAR_27028 : Use override value [ORA_R12] for session parameter:[$DBConnection_OLTP].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : VAR_27028 : Use override value [ORA_R12.DATAWAREHOUSE.SDE_ORAR12_Adaptor.SDE_ORA_PayrollFact_Full.log] for session parameter:[$PMSessionLogFile].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : VAR_27028 : Use override value [9] for mapping parameter:[$$DATASOURCE_NUM_ID].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : VAR_27028 : Use override value [] for mapping parameter:[mplt_BC_ORA_PayrollFact.$$HINT1].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : VAR_27028 : Use override value [] for mapping parameter:[mplt_BC_ORA_PayrollFact.$$HINT2].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : VAR_27028 : Use override value [] for mapping parameter:[mplt_BC_ORA_PayrollFact.$$HINT3].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : VAR_27028 : Use override value [01/01/1970] for mapping parameter:[mplt_BC_ORA_PayrollFact.$$INITIAL_EXTRACT_DATE].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : VAR_27028 : Use override value [] for mapping parameter:[mplt_BC_ORA_PayrollFact.$$LAST_EXTRACT_DATE].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : VAR_27028 : Use override value [DEFAULT] for mapping parameter:[$$TENANT_ID].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6014 : Initializing session [SDE_ORA_PayrollFact_Full] at [Tue Dec 07 18:35:34 2010].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6683 : Repository Name: [Oracle_BI_DW_Base]
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6684 : Server Name: [Oracle_BI_DW_Base_Integration_Service]
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6686 : Folder: [SDE_ORAR12_Adaptor]
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6685 : Workflow: [SDE_ORA_PayrollFact_Full] Run Instance Name: [] Run Id: [20658]
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6101 : Mapping name: SDE_ORA_PayrollFact [version 1].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6963 : Pre 85 Timestamp Compatibility is Enabled
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6964 : Date format for the Session is [MM/DD/YYYY HH24:MI:SS]
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6827 : [C:\Informatica\PowerCenter8.6.1\server\infa_shared\Storage] will be used as storage directory for session [SDE_ORA_PayrollFact_Full].
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : CMN_1805 : Recovery cache will be deleted when running in normal mode.
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : CMN_1802 : Session recovery cache initialization is complete.
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6708 : Using configuration property [OptimizeODBCRead,No]
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6703 : Session [SDE_ORA_PayrollFact_Full] is run by 64-bit Integration Service [node01_SATURANUS1], version [8.6.1 HotFix10], build [0124].
2010-12-07 18:35:34 : INFO : (14272 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : PETL_24058 : Running Partition Group [1].
2010-12-07 18:35:34 : INFO : (14272 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : PETL_24000 : Parallel Pipeline Engine initializing.
2010-12-07 18:35:34 : INFO : (14272 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : PETL_24001 : Parallel Pipeline Engine running.
2010-12-07 18:35:34 : INFO : (14272 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : PETL_24003 : Initializing session run.
2010-12-07 18:35:34 : INFO : (14272 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : CMN_1569 : Server Mode: [UNICODE]
2010-12-07 18:35:34 : INFO : (14272 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : CMN_1570 : Server Code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
2010-12-07 18:35:34 : INFO : (14272 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6151 : The session sort order is [Binary].
2010-12-07 18:35:34 : INFO : (14272 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6156 : Using low precision processing.
2010-12-07 18:35:34 : INFO : (14272 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6180 : Deadlock retry logic will not be implemented.
2010-12-07 18:35:34 : INFO : (14272 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6307 : DTM error log disabled.
2010-12-07 18:35:34 : INFO : (14272 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TE_7022 : TShmWriter: Initialized
2010-12-07 18:35:34 : INFO : (14272 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6007 : DTM initialized successfully for session [SDE_ORA_PayrollFact_Full]
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : PETL_24033 : All DTM Connection Info: [<NONE>].
2010-12-07 18:35:34 : INFO : (14272 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : PETL_24004 : PETL_24004 Starting pre-session tasks. : (Tue Dec 07 18:35:34 2010)
2010-12-07 18:35:34 : INFO : (14272 | MANAGER) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : PETL_24027 : PETL_24027 Pre-session task completed successfully. : (Tue Dec 07 18:35:34 2010)
2010-12-07 18:35:34 : INFO : (14272 | DIRECTOR) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : PETL_24006 : Starting data movement.
2010-12-07 18:35:34 : INFO : (14272 | MAPPING) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : TM_6660 : Total Buffer Pool size is 32000000 bytes and Block size is 1280000 bytes.
2010-12-07 18:35:34 : INFO : (14272 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : DBG_21438 : Reader: Source is [MASTER], user [apps]
2010-12-07 18:35:34 : INFO : (14272 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : BLKR_16051 : Source database connection [ORA_R12] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
2010-12-07 18:35:36 : INFO : (14272 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : BLKR_16003 : Initialization completed successfully.
2010-12-07 18:35:36 : INFO : (14272 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : WRT_8147 : Writer: Target is database [SATSCAURORA06\SQL6,1410@BAW], user [BAW], bulk mode [OFF]
2010-12-07 18:35:36 : INFO : (14272 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : WRT_8414 : High availability license is absent. Connection retry period specified for target connection is ignored.
2010-12-07 18:35:36 : INFO : (14272 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : WRT_8221 : Target database connection [DataWarehouse] code page: [MS Windows Latin 1 (ANSI), superset of Latin1]
2010-12-07 18:35:36 : INFO : (14272 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : WRT_8397 : Appending output data to file [C:\Informatica\PowerCenter8.6.1\server\infa_shared\BadFiles\SDE_ORA_PayrollFact_Full.bad] (initial size [484]).
2010-12-07 18:35:36 : INFO : (14272 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : WRT_8124 : Target Table W_PAYROLL_FS :SQL INSERT statement:
INSERT INTO W_PAYROLL_FS(EMPLOYEE_ID,JOB_ID,HR_POSITION_ID,PAY_GRADE_ID,EMP_LOC_ID,EMP_HR_ORG_ID,HR_BUSINESS_UNIT_ID,COMPANY_ORG_ID,EMPLOYMENT_ID,PAY_TYPE_ID,EMP_POSTN_DH_ID,PAY_CHECK_DT,PAY_PERIOD_START_DT,PAY_PERIOD_END_DT,PAY_EARN_START_DT,PAY_EARN_END_DT,PAY_DETAIL_FLG,PAY_ITEM_AMT,DELETE_FLG,REJECT_FLG,DOC_CURR_CODE,LOC_CURR_CODE,LOC_EXCH_RATE_TYPE,LOC_EXCHANGE_RATE,EXCHANGE_DT,CREATED_BY_ID,CHANGED_BY_ID,CREATED_ON_DT,CHANGED_ON_DT,AUX1_CHANGED_ON_DT,AUX2_CHANGED_ON_DT,AUX3_CHANGED_ON_DT,AUX4_CHANGED_ON_DT,DATASOURCE_NUM_ID,INTEGRATION_ID,TENANT_ID,X_CUSTOM) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2010-12-07 18:35:36 : INFO : (14272 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : WRT_8270 : Target connection group #1 consists of target(s) [W_PAYROLL_FS]
2010-12-07 18:35:36 : INFO : (14272 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : WRT_8003 : Writer initialization complete.
2010-12-07 18:35:36 : INFO : (14272 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : BLKR_16007 : Reader run started.
2010-12-07 18:35:36 : INFO : (14272 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : WRT_8005 : Writer run started.
2010-12-07 18:35:36 : INFO : (14272 | WRITER_1_*_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : WRT_8158 :
*****START LOAD SESSION*****
Load Start Time: Tue Dec 07 18:35:36 2010
Target tables:
W_PAYROLL_FS
2010-12-07 18:35:36 : INFO : (14272 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : RR_4029 : SQ Instance [mplt_BC_ORA_PayrollFact.Sq_Payroll] User specified SQL Query [SELECT PAYROLL.ASSIGNMENT_ACTION_ID,
PAYROLL.ASSIGNMENT_ID,
PAYROLL.INPUT_CURRENCY_CODE,
PAYROLL.OUTPUT_CURRENCY_CODE,
PAYROLL.END_DATE,
PAYROLL.START_DATE,
PAYROLL.PAY_ADVICE_DATE,
PAYROLL.LAST_UPDATE_DATE,
PAYROLL.LAST_UPDATED_BY,
PAYROLL.CREATED_BY,
PAYROLL.CREATION_DATE,
PAYROLL.INPUT_VALUE_ID,
PAYROLL.RUN_RESULT_ID,
PAYROLL.RESULT_VALUE,
PAYROLL.ELEMENT_TYPE_ID,
PAYROLL.LAST_UPDATE_DATE1,
PAYROLL.LAST_UPDATE_DATE2,
LKP.GRADE_ID,
LKP.POSITION_ID,
LKP.JOB_ID,
LKP.LOCATION_ID,
LKP.PERSON_ID,
LKP.ORGANIZATION_ID,
LKP.SET_OF_BOOKS_ID,
LKP.ASSIGNMENT_STATUS_TYPE_ID,
LKP.PERSON_TYPE_ID,
LKP.PAY_BASIS_ID,
LKP.EMPLOYMENT_CATEGORY,
LKP.LABOUR_UNION_MEMBER_FLAG,
LKP.BUSINESS_GROUP_ID,
LKP.CHANGED_ON_DT,
X_CUSTOM
FROM (SELECT PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID,
PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ID,
PAY_ELEMENT_TYPES_F.INPUT_CURRENCY_CODE,
PAY_ELEMENT_TYPES_F.OUTPUT_CURRENCY_CODE,
PER_TIME_PERIODS.END_DATE,
PER_TIME_PERIODS.START_DATE,
PAY_PAYROLL_ACTIONS.PAY_ADVICE_DATE,
PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE,
PAY_PAYROLL_ACTIONS.LAST_UPDATED_BY,
PAY_PAYROLL_ACTIONS.CREATED_BY,
PAY_PAYROLL_ACTIONS.CREATION_DATE,
PAY_RUN_RESULT_VALUES.INPUT_VALUE_ID,
PAY_RUN_RESULT_VALUES.RUN_RESULT_ID,
PAY_RUN_RESULT_VALUES.RESULT_VALUE,
PAY_RUN_RESULTS.ELEMENT_TYPE_ID,
PAY_INPUT_VALUES_F.LAST_UPDATE_DATE LAST_UPDATE_DATE1,
PAY_ELEMENT_TYPES_F.LAST_UPDATE_DATE LAST_UPDATE_DATE2,
'0' AS X_CUSTOM
FROM PAY_RUN_RESULT_VALUES,
PAY_RUN_RESULTS,
PAY_INPUT_VALUES_F,
PAY_ASSIGNMENT_ACTIONS,
PAY_ELEMENT_TYPES_F,
PAY_PAYROLL_ACTIONS,
PAY_ELEMENT_CLASSIFICATIONS,
PER_TIME_PERIODS
WHERE (PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE >=
TO_DATE('01/01/1970 00:00:00','MM/DD/YYYY HH24:MI:SS')
)
AND PAY_PAYROLL_ACTIONS.ACTION_STATUS = 'C'
AND PAY_PAYROLL_ACTIONS.ACTION_POPULATION_STATUS = 'C'
AND PAY_ASSIGNMENT_ACTIONS.ACTION_STATUS = 'C'
AND PAY_INPUT_VALUES_F.UOM = 'M'
AND PAY_RUN_RESULT_VALUES.RUN_RESULT_ID =
PAY_RUN_RESULTS.RUN_RESULT_ID
AND PAY_RUN_RESULT_VALUES.INPUT_VALUE_ID =
PAY_INPUT_VALUES_F.INPUT_VALUE_ID
AND PAY_RUN_RESULTS.ASSIGNMENT_ACTION_ID =
PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID
AND PAY_RUN_RESULTS.ELEMENT_TYPE_ID =
PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID
AND PAY_ASSIGNMENT_ACTIONS.PAYROLL_ACTION_ID =
PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID
AND PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE
BETWEEN PAY_INPUT_VALUES_F.EFFECTIVE_START_DATE
AND PAY_INPUT_VALUES_F.EFFECTIVE_END_DATE
AND PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE
BETWEEN PAY_ELEMENT_TYPES_F.EFFECTIVE_START_DATE
AND PAY_ELEMENT_TYPES_F.EFFECTIVE_END_DATE
AND PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_ID =
PAY_ELEMENT_TYPES_F.CLASSIFICATION_ID
AND PER_TIME_PERIODS.TIME_PERIOD_ID =
PAY_PAYROLL_ACTIONS.TIME_PERIOD_ID
AND PAY_INPUT_VALUES_F.NAME = 'Pay Value'
AND CLASSIFICATION_NAME NOT LIKE '%Information%'
AND CLASSIFICATION_NAME NOT LIKE '%Employer%'
AND CLASSIFICATION_NAME NOT LIKE '%Balance%'
AND PAY_RUN_RESULTS.SOURCE_TYPE IN ('I', 'E')
UNION ALL
SELECT
PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID,
PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ID,
PAY_ELEMENT_TYPES_F.INPUT_CURRENCY_CODE,
PAY_ELEMENT_TYPES_F.OUTPUT_CURRENCY_CODE,
PER_TIME_PERIODS.END_DATE,
PER_TIME_PERIODS.START_DATE,
PAY_PAYROLL_ACTIONS.PAY_ADVICE_DATE,
PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE,
PAY_PAYROLL_ACTIONS.LAST_UPDATED_BY,
PAY_PAYROLL_ACTIONS.CREATED_BY,
PAY_PAYROLL_ACTIONS.CREATION_DATE,
PAY_RUN_RESULT_VALUES.INPUT_VALUE_ID,
PAY_RUN_RESULT_VALUES.RUN_RESULT_ID,
PAY_RUN_RESULT_VALUES.RESULT_VALUE,
PAY_RUN_RESULTS.ELEMENT_TYPE_ID,
PAY_INPUT_VALUES_F.LAST_UPDATE_DATE LAST_UPDATE_DATE1,
PAY_ELEMENT_TYPES_F.LAST_UPDATE_DATE LAST_UPDATE_DATE2,
'0' AS X_CUSTOM
FROM PAY_RUN_RESULT_VALUES,
PAY_RUN_RESULTS,
PAY_INPUT_VALUES_F,
PAY_ASSIGNMENT_ACTIONS,
PAY_ELEMENT_TYPES_F,
PAY_PAYROLL_ACTIONS,
PAY_ELEMENT_CLASSIFICATIONS,
PER_TIME_PERIODS
WHERE (PAY_PAYROLL_ACTIONS.LAST_UPDATE_DATE >=
TO_DATE('01/01/1970 00:00:00','MM/DD/YYYY HH24:MI:SS')
)
AND PAY_PAYROLL_ACTIONS.ACTION_STATUS = 'C'
AND PAY_PAYROLL_ACTIONS.ACTION_POPULATION_STATUS = 'C'
AND PAY_ASSIGNMENT_ACTIONS.ACTION_STATUS = 'C'
AND PAY_INPUT_VALUES_F.UOM = 'M'
AND PAY_RUN_RESULT_VALUES.RUN_RESULT_ID =
PAY_RUN_RESULTS.RUN_RESULT_ID
AND PAY_RUN_RESULT_VALUES.INPUT_VALUE_ID =
PAY_INPUT_VALUES_F.INPUT_VALUE_ID
AND PAY_RUN_RESULTS.ASSIGNMENT_ACTION_ID =
PAY_ASSIGNMENT_ACTIONS.ASSIGNMENT_ACTION_ID
AND PAY_RUN_RESULTS.ELEMENT_TYPE_ID =
PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID
AND PAY_ASSIGNMENT_ACTIONS.PAYROLL_ACTION_ID =
PAY_PAYROLL_ACTIONS.PAYROLL_ACTION_ID
AND PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE
BETWEEN PAY_INPUT_VALUES_F.EFFECTIVE_START_DATE
AND PAY_INPUT_VALUES_F.EFFECTIVE_END_DATE
AND PAY_PAYROLL_ACTIONS.EFFECTIVE_DATE
BETWEEN PAY_ELEMENT_TYPES_F.EFFECTIVE_START_DATE
AND PAY_ELEMENT_TYPES_F.EFFECTIVE_END_DATE
AND PAY_ELEMENT_CLASSIFICATIONS.CLASSIFICATION_ID =
PAY_ELEMENT_TYPES_F.CLASSIFICATION_ID
AND PAY_PAYROLL_ACTIONS.DATE_EARNED
BETWEEN PER_TIME_PERIODS.START_DATE
AND PER_TIME_PERIODS.END_DATE
AND PER_TIME_PERIODS.PAYROLL_ID = PAY_PAYROLL_ACTIONS.PAYROLL_ID
AND PAY_INPUT_VALUES_F.NAME = 'Pay Value'
AND CLASSIFICATION_NAME NOT LIKE '%Information%'
AND CLASSIFICATION_NAME NOT LIKE '%Employer%'
AND CLASSIFICATION_NAME NOT LIKE '%Balance%'
AND PAY_RUN_RESULTS.SOURCE_TYPE IN ('V', 'R')) PAYROLL,
(SELECT
PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID ASSIGNMENT_ID,
PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE
EFFECTIVE_START_DATE,
PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE EFFECTIVE_END_DATE,
PER_ALL_ASSIGNMENTS_F.GRADE_ID GRADE_ID,
PER_ALL_ASSIGNMENTS_F.POSITION_ID POSITION_ID,
PER_ALL_ASSIGNMENTS_F.JOB_ID JOB_ID,
PER_ALL_ASSIGNMENTS_F.LOCATION_ID LOCATION_ID,
PER_ALL_ASSIGNMENTS_F.PERSON_ID PERSON_ID,
PER_ALL_ASSIGNMENTS_F.ORGANIZATION_ID ORGANIZATION_ID,
PER_ALL_ASSIGNMENTS_F.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,
PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_STATUS_TYPE_ID,
PER_ALL_PEOPLE_F.PERSON_TYPE_ID,
PER_ALL_ASSIGNMENTS_F.PAY_BASIS_ID,
PER_ALL_ASSIGNMENTS_F.EMPLOYMENT_CATEGORY,
PER_ALL_ASSIGNMENTS_F.LABOUR_UNION_MEMBER_FLAG,
PER_ALL_ASSIGNMENTS_F.BUSINESS_GROUP_ID BUSINESS_GROUP_ID,
PER_ALL_ASSIGNMENTS_F.LAST_UPDATE_DATE AS CHANGED_ON_DT
FROM PER_ALL_ASSIGNMENTS_F,
PER_ALL_PEOPLE_F
WHERE PER_ALL_ASSIGNMENTS_F.PERSON_ID = PER_ALL_PEOPLE_F.PERSON_ID
AND ( PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE <=
PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE
AND PER_ALL_PEOPLE_F.EFFECTIVE_END_DATE >=
PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE
OR PER_ALL_PEOPLE_F.EFFECTIVE_START_DATE <=
PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE
AND PER_ALL_PEOPLE_F.EFFECTIVE_END_DATE >=
PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE
)) LKP
WHERE LKP.ASSIGNMENT_ID(+) = TO_CHAR (PAYROLL.ASSIGNMENT_ID)
AND LKP.EFFECTIVE_START_DATE <= PAYROLL.END_DATE
AND LKP.EFFECTIVE_END_DATE > PAYROLL.END_DATE]
2010-12-07 18:35:36 : INFO : (14272 | READER_1_1_1) : (IS | Oracle_BI_DW_Base_Integration_Service) : node01_SATURANUS1 : RR_4049 : RR_4049 SQL Query issued to database : (Tue Dec 07 18:35:36 2010)