PJC_PAYROLL_TC_DIST_DETAILS_V

Details

• Schema: FUSION

• Object owner: PJC

• Object type: VIEW

Columns

Name
PAYROLL_TC_DIST_ID PAYROLL_DIST_GROUP_ID GROUP_STATUS DISTRIBUTION_STATUS RUN_TARGET_ID ALLOC_TXN_ID TIMECARD_RUN_STATUS RUN_ID TIMECARD_DIST_RUN_STATUS_CODE TIMECARD_DIST_MESSAGE_NUMBER TIMECARD_DIST_MESSAGE_NAME TIMECARD_DIST_MESSAGE_TYPE TIMECARD_DIST_MESSAGE_TEXT TIMECARD_DIST_MESSAGE_DETAILS

Query

SQL_Statement

SELECT tcdist.payroll_tc_dist_id, tcdist.payroll_dist_group_id, tcdist.group_status, tcdist.distribution_status, txn.run_target_id, txn.alloc_txn_id, run.timecard_run_status, run.run_id, ( CASE WHEN run.timecard_run_status = 'ORA_FAILED' THEN 'PJC_LD_TC_DIST_NOT_EXECUTED' WHEN run.timecard_run_status = 'ORA_ON_HOLD' THEN 'PJC_LD_OTHER_TC_DIST_IN_PROG' WHEN run.timecard_run_status = 'ORA_PARTIAL_ON_HOLD' THEN 'PJC_LD_OTHER_TC_DIST_IN_PROG' WHEN tcdist.distribution_status = 'ORA_DRAFT' THEN 'PJC_LD_TC_DIST_RUNNING' WHEN tcdist.distribution_status = 'ORA_PENDING' THEN 'PJC_LD_TC_DIST_RUNNING' WHEN tcdist.distribution_status = 'ORA_IN_PROGRESS' THEN 'PJC_LD_TC_DIST_RUNNING' WHEN tcdist.distribution_status = 'ORA_PARTIAL_SUCCESS' THEN 'PJC_LD_TC_DIST_TARGET_ERRORS' WHEN tcdist.distribution_status = 'ORA_REJECT_PENDING_TXN' THEN 'PJC_LD_UPC_OR_ADJ_PENDING' WHEN tcdist.distribution_status = 'ORA_REJECT_NO_EI' THEN 'PJC_LD_TC_DIST_NO_TARGETS' ELSE null END ) timecard_dist_run_status_code, msg.message_number timecard_dist_message_number, msg.message_name timecard_dist_message_name, msg.type timecard_dist_message_type, msg.message_text timecard_dist_message_text, msg.message_user_details timecard_dist_message_details FROM pjc_alloc_runs_all run, pjc_alloc_txn_details txn, (SELECT out_tc.payroll_tc_dist_id, out_tc.payroll_dist_group_id, out_tc.alloc_txn_id, out_tc.group_status, out_tc.distribution_status FROM pjc_payroll_tc_distributions out_tc WHERE ((out_tc.group_status = 'ORA_DRAFT') or (( out_tc.group_status <> 'ORA_DRAFT') and (out_tc.new_payroll_dist_group_id is null) AND NOT EXISTS ( SELECT 1 FROM pjc_payroll_tc_distributions in_tc WHERE out_tc.alloc_txn_id = in_tc.alloc_txn_id AND in_tc.group_status = 'ORA_DRAFT' )))) tcdist, fnd_messages msg WHERE run.run_id = txn.run_id AND txn.timecard_dist_flag = 'Y' AND txn.alloc_txn_id = tcdist.alloc_txn_id (+) and msg.application_id(+) = 10036 AND ( CASE WHEN run.timecard_run_status = 'ORA_FAILED' THEN 'PJC_LD_TC_DIST_NOT_EXECUTED' WHEN run.timecard_run_status = 'ORA_ON_HOLD' THEN 'PJC_LD_OTHER_TC_DIST_IN_PROG' WHEN run.timecard_run_status = 'ORA_PARTIAL_ON_HOLD' THEN 'PJC_LD_OTHER_TC_DIST_IN_PROG' WHEN tcdist.distribution_status = 'ORA_DRAFT' THEN 'PJC_LD_TC_DIST_RUNNING' WHEN tcdist.distribution_status = 'ORA_PENDING' THEN 'PJC_LD_TC_DIST_RUNNING' WHEN tcdist.distribution_status = 'ORA_IN_PROGRESS' THEN 'PJC_LD_TC_DIST_RUNNING' WHEN tcdist.distribution_status = 'ORA_PARTIAL_SUCCESS' THEN 'PJC_LD_TC_DIST_TARGET_ERRORS' WHEN tcdist.distribution_status = 'ORA_REJECT_PENDING_TXN' THEN 'PJC_LD_UPC_OR_ADJ_PENDING' WHEN tcdist.distribution_status = 'ORA_REJECT_NO_EI' THEN 'PJC_LD_TC_DIST_NO_TARGETS' ELSE null END ) = msg.message_name(+)