CST_UNCOSTED_TXN_ERRORS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
TRANSACTION_ID PROCESSOR_NAME SUBPROCESSOR_NAME RUN_CONTROL REQUEST_ID MESSAGE_TYPE ERROR_CODE TABLE_NAME MESSAGE REFERENCE REFERENCE_NUMBER COST_ORG_ID COST_BOOK_ID

Name

TRANSACTION_ID

PROCESSOR_NAME

SUBPROCESSOR_NAME

RUN_CONTROL

REQUEST_ID

MESSAGE_TYPE

ERROR_CODE

TABLE_NAME

MESSAGE

REFERENCE

REFERENCE_NUMBER

COST_ORG_ID

COST_BOOK_ID

Query

SQL_Statement
SELECT cte.transaction_id , flv1.meaning processor_name , cce.subprocessor_name , cce.run_control , cce.request_id , flv2.meaning message_type , cce.error_code , cce.table_name , cce.message , cte.external_system_reference REFERENCE , cte.external_system_ref_id reference_number, ct.cost_org_id cost_org_id, ct.cost_book_id cost_book_id FROM fusion.cst_transaction_errors cte , cst_cp_errors cce , cst_cp_drivers ccd , fnd_lookups flv1 , fnd_lookups flv2 , fusion.cst_all_cost_transactions_v ct WHERE cte.cp_error_id = cce.cp_error_id AND cte.request_id = cce.request_id AND cte.request_id = ccd.request_id AND ct.transaction_id = cte.transaction_id AND ccd.COST_ORG_ID = CT.COST_ORG_ID AND ccd.COST_BOOK_ID = CT.COST_BOOK_ID AND ct.cost_book_id = cce.cost_book_id AND ct.cost_org_id = cce.cost_org_id AND ccd.status IN ('C', 'E', 'I') AND flv1.lookup_type = 'CST_PROCESSOR_NAME' AND flv1.lookup_code = cce.processor_name AND flv2.lookup_type = 'MESSAGE_TYPE' AND flv2.lookup_code = cce.message_type AND cce.REQUEST_ID = ( SELECT DISTINCT FIRST_VALUE(D.REQUEST_ID) OVER(ORDER BY R.START_DATE DESC) FROM CST_CP_REQUESTS R, CST_CP_DRIVERS D WHERE R.REQUEST_ID = D.REQUEST_ID AND D.STATUS IN ('C', 'E', 'I') AND D.COST_ORG_ID = CT.COST_ORG_ID AND D.COST_BOOK_ID = CT.COST_BOOK_ID AND DECODE (cce.PROCESSOR_NAME, 'PREP', R.PREPROCESS_FLAG, 'CPROC', R.COST_PROCESSOR_FLAG, 'DPROC', R.COST_DISTRIBUTION_FLAG, 'N') = 'Y' )

SQL_Statement

SELECT

cte.transaction_id ,

flv1.meaning processor_name ,

cce.subprocessor_name ,

cce.run_control ,

cce.request_id ,

flv2.meaning message_type ,

cce.error_code ,

cce.table_name ,

cce.message ,

cte.external_system_reference REFERENCE ,

cte.external_system_ref_id reference_number,

ct.cost_org_id cost_org_id,

ct.cost_book_id cost_book_id

FROM

fusion.cst_transaction_errors cte ,

cst_cp_errors cce ,

cst_cp_drivers ccd ,

fnd_lookups flv1 ,

fnd_lookups flv2 ,

fusion.cst_all_cost_transactions_v ct

WHERE

cte.cp_error_id = cce.cp_error_id

AND cte.request_id = cce.request_id

AND cte.request_id = ccd.request_id

AND ct.transaction_id = cte.transaction_id

AND ccd.COST_ORG_ID = CT.COST_ORG_ID

AND ccd.COST_BOOK_ID = CT.COST_BOOK_ID

AND ct.cost_book_id = cce.cost_book_id

AND ct.cost_org_id = cce.cost_org_id

AND ccd.status IN ('C', 'E', 'I')

AND flv1.lookup_type = 'CST_PROCESSOR_NAME'

AND flv1.lookup_code = cce.processor_name

AND flv2.lookup_type = 'MESSAGE_TYPE'

AND flv2.lookup_code = cce.message_type

AND cce.REQUEST_ID =

(

SELECT DISTINCT

FIRST_VALUE(D.REQUEST_ID) OVER(ORDER BY R.START_DATE DESC)

FROM

CST_CP_REQUESTS R,

CST_CP_DRIVERS D

WHERE

R.REQUEST_ID = D.REQUEST_ID

AND D.STATUS IN ('C', 'E', 'I')

AND D.COST_ORG_ID = CT.COST_ORG_ID

AND D.COST_BOOK_ID = CT.COST_BOOK_ID

AND DECODE (cce.PROCESSOR_NAME, 'PREP', R.PREPROCESS_FLAG, 'CPROC',

R.COST_PROCESSOR_FLAG, 'DPROC', R.COST_DISTRIBUTION_FLAG, 'N') = 'Y'

)