VIEW: APPS.XTR_RECONCILIATION_MATRIX_V

Source

View Text - Preformatted

SELECT a.Verify_Method Verification_Method , a.Pass_Code Reconciliation_Pass_Code , a.Ver_Seq Sequence_Order , Decode(a.Pass_Col,d.Amount_Code,Decode(a.cross,d.match_code,d.match_meaning,sumdate_code,sumdate_meaning,sumrange_code, sumrange_meaning,a.cross),Null) Amount_Recon_Detail , Decode(a.Pass_Col,d.Bank_Acct_Code,Decode(a.cross,d.match_code,d.match_meaning,sumdate_code,sumdate_meaning,sumrange_code, sumrange_meaning,a.cross),Null) Bank_Acct_Recon_Detail , Decode(a.Pass_Col,d.Date_Code,Decode(a.cross,d.match_code,d.match_meaning,sumdate_code,sumdate_meaning,sumrange_code, sumrange_meaning,a.cross),Null) Date_Recon_Detail , Decode(a.Pass_Col,d.Deal_Num_Code,Decode(a.cross,d.match_code,d.match_meaning,sumdate_code,sumdate_meaning,sumrange_code, sumrange_meaning,a.cross),Null) Deal_Num_Recon_Detail , Decode(a.Pass_Col,d.Deal_Type_Code,Decode(a.cross,d.match_code,d.match_meaning,sumdate_code,sumdate_meaning,sumrange_code, sumrange_meaning,a.cross),Null) Deal_Type_Recon_Detail , Decode(a.Pass_Col,d.Party_Code,Decode(a.cross,d.match_code,d.match_meaning,sumdate_code,sumdate_meaning,sumrange_code, sumrange_meaning,a.cross),Null) Party_Recon_Detail , Decode(a.Pass_Col,d.Portfolio_Code,Decode(a.cross,d.match_code,d.match_meaning,sumdate_code,sumdate_meaning,sumrange_code, sumrange_meaning,a.cross),Null) Portfolio_Recon_Detail , Decode(a.Pass_Col,d.Product_Code,Decode(a.cross,d.match_code,d.match_meaning,sumdate_code,sumdate_meaning,sumrange_code, sumrange_meaning,a.cross),Null) Product_Recon_Detail , Decode(a.Pass_Col,d.Serial_Num_Code,Decode(a.cross,d.match_code,d.match_meaning,sumdate_code,sumdate_meaning,sumrange_code, sumrange_meaning,a.cross),Null) Serial_Num_Recon_Detail , Decode(a.Pass_Col,d.Subtype_Code,Decode(a.cross,d.match_code,d.match_meaning,sumdate_code,sumdate_meaning,sumrange_code, sumrange_meaning,a.cross),Null) Subtype_Recon_Detail From ( Select a.verification_method Verify_Method , a.sequence_order Ver_Seq , a.RECONCILED_PASS_CODE Pass_Code , b.RECONCILE_ON_COLUMN Pass_Col , decode(b.RECONCILE_ON_COLUMN,c.lookup_code,b.RECONCILE_DETAIL,Null) Cross From XTR_RECONCILIATION_PROCESS_V a, XTR_RECONCILIATION_PASSES_V b, Fnd_Lookups c WHERE a.reconciled_pass_code = b.reconciled_pass_code And c.Lookup_type = 'XTR_RECON_ON_COLUMN' And decode(b.RECONCILE_ON_COLUMN,c.lookup_code,b.RECONCILE_DETAIL,Null) is not null ) a, ( Select max(Decode(Lookup_Code,'AMOUNT',Lookup_Code)) Amount_Code, max(Decode(Lookup_Code,'AMOUNT',Meaning)) Amount_Meaning, max(Decode(Lookup_Code,'BANK ACCT',Lookup_Code)) Bank_Acct_Code, max(Decode(Lookup_Code,'BANK ACCT',Meaning)) Bank_Acct_Meaning, max(Decode(Lookup_Code,'DATE',Lookup_Code)) Date_Code, max(Decode(Lookup_Code,'DATE',Meaning)) Date_Meaning, max(Decode(Lookup_Code,'DEAL NUM',Lookup_Code)) Deal_Num_Code, max(Decode(Lookup_Code,'DEAL NUM',Meaning)) Deal_Num_Meaning, max(Decode(Lookup_Code,'DEAL TYPE',Lookup_Code)) Deal_Type_Code, max(Decode(Lookup_Code,'DEAL TYPE',Meaning)) Deal_Type_Meaning, max(Decode(Lookup_Code,'PARTY',Lookup_Code)) Party_Code, max(Decode(Lookup_Code,'PARTY',Meaning)) Party_Meaning, max(Decode(Lookup_Code,'PORTFOLIO',Lookup_Code)) Portfolio_Code, max(Decode(Lookup_Code,'PORTFOLIO',Meaning)) Portfolio_Meaning, max(Decode(Lookup_Code,'PRODUCT',Lookup_Code)) Product_Code, max(Decode(Lookup_Code,'PRODUCT',Meaning)) Product_Meaning, max(Decode(Lookup_Code,'SERIAL NUM',Lookup_Code)) Serial_Num_Code, max(Decode(Lookup_Code,'SERIAL NUM',Meaning)) Serial_Num_Meaning, max(Decode(Lookup_Code,'SUBTYPE',Lookup_Code)) Subtype_Code, max(Decode(Lookup_Code,'SUBTYPE',Meaning)) Subtype_Meaning, max(Decode(Lookup_Code,'MATCH',Lookup_Code)) Match_Code, max(Decode(Lookup_Code,'MATCH',Meaning)) Match_Meaning, max(Decode(Lookup_Code,'SUM DATE',Lookup_Code)) SumDate_Code, max(Decode(Lookup_Code,'SUM DATE',Meaning)) SumDate_Meaning, max(Decode(Lookup_Code,'SUM RANGE',Lookup_Code)) SumRange_Code, max(Decode(Lookup_Code,'SUM RANGE',Meaning)) SumRange_Meaning From fnd_Lookups Where Lookup_type in ('XTR_RECON_ON_COLUMN','XTR_RECON_CRITERIA') ) d where 1=1

View Text - HTML Formatted

SELECT A.VERIFY_METHOD VERIFICATION_METHOD
, A.PASS_CODE RECONCILIATION_PASS_CODE
, A.VER_SEQ SEQUENCE_ORDER
, DECODE(A.PASS_COL
, D.AMOUNT_CODE
, DECODE(A.CROSS
, D.MATCH_CODE
, D.MATCH_MEANING
, SUMDATE_CODE
, SUMDATE_MEANING
, SUMRANGE_CODE
, SUMRANGE_MEANING
, A.CROSS)
, NULL) AMOUNT_RECON_DETAIL
, DECODE(A.PASS_COL
, D.BANK_ACCT_CODE
, DECODE(A.CROSS
, D.MATCH_CODE
, D.MATCH_MEANING
, SUMDATE_CODE
, SUMDATE_MEANING
, SUMRANGE_CODE
, SUMRANGE_MEANING
, A.CROSS)
, NULL) BANK_ACCT_RECON_DETAIL
, DECODE(A.PASS_COL
, D.DATE_CODE
, DECODE(A.CROSS
, D.MATCH_CODE
, D.MATCH_MEANING
, SUMDATE_CODE
, SUMDATE_MEANING
, SUMRANGE_CODE
, SUMRANGE_MEANING
, A.CROSS)
, NULL) DATE_RECON_DETAIL
, DECODE(A.PASS_COL
, D.DEAL_NUM_CODE
, DECODE(A.CROSS
, D.MATCH_CODE
, D.MATCH_MEANING
, SUMDATE_CODE
, SUMDATE_MEANING
, SUMRANGE_CODE
, SUMRANGE_MEANING
, A.CROSS)
, NULL) DEAL_NUM_RECON_DETAIL
, DECODE(A.PASS_COL
, D.DEAL_TYPE_CODE
, DECODE(A.CROSS
, D.MATCH_CODE
, D.MATCH_MEANING
, SUMDATE_CODE
, SUMDATE_MEANING
, SUMRANGE_CODE
, SUMRANGE_MEANING
, A.CROSS)
, NULL) DEAL_TYPE_RECON_DETAIL
, DECODE(A.PASS_COL
, D.PARTY_CODE
, DECODE(A.CROSS
, D.MATCH_CODE
, D.MATCH_MEANING
, SUMDATE_CODE
, SUMDATE_MEANING
, SUMRANGE_CODE
, SUMRANGE_MEANING
, A.CROSS)
, NULL) PARTY_RECON_DETAIL
, DECODE(A.PASS_COL
, D.PORTFOLIO_CODE
, DECODE(A.CROSS
, D.MATCH_CODE
, D.MATCH_MEANING
, SUMDATE_CODE
, SUMDATE_MEANING
, SUMRANGE_CODE
, SUMRANGE_MEANING
, A.CROSS)
, NULL) PORTFOLIO_RECON_DETAIL
, DECODE(A.PASS_COL
, D.PRODUCT_CODE
, DECODE(A.CROSS
, D.MATCH_CODE
, D.MATCH_MEANING
, SUMDATE_CODE
, SUMDATE_MEANING
, SUMRANGE_CODE
, SUMRANGE_MEANING
, A.CROSS)
, NULL) PRODUCT_RECON_DETAIL
, DECODE(A.PASS_COL
, D.SERIAL_NUM_CODE
, DECODE(A.CROSS
, D.MATCH_CODE
, D.MATCH_MEANING
, SUMDATE_CODE
, SUMDATE_MEANING
, SUMRANGE_CODE
, SUMRANGE_MEANING
, A.CROSS)
, NULL) SERIAL_NUM_RECON_DETAIL
, DECODE(A.PASS_COL
, D.SUBTYPE_CODE
, DECODE(A.CROSS
, D.MATCH_CODE
, D.MATCH_MEANING
, SUMDATE_CODE
, SUMDATE_MEANING
, SUMRANGE_CODE
, SUMRANGE_MEANING
, A.CROSS)
, NULL) SUBTYPE_RECON_DETAIL
FROM ( SELECT A.VERIFICATION_METHOD VERIFY_METHOD
, A.SEQUENCE_ORDER VER_SEQ
, A.RECONCILED_PASS_CODE PASS_CODE
, B.RECONCILE_ON_COLUMN PASS_COL
, DECODE(B.RECONCILE_ON_COLUMN
, C.LOOKUP_CODE
, B.RECONCILE_DETAIL
, NULL) CROSS
FROM XTR_RECONCILIATION_PROCESS_V A
, XTR_RECONCILIATION_PASSES_V B
, FND_LOOKUPS C
WHERE A.RECONCILED_PASS_CODE = B.RECONCILED_PASS_CODE
AND C.LOOKUP_TYPE = 'XTR_RECON_ON_COLUMN'
AND DECODE(B.RECONCILE_ON_COLUMN
, C.LOOKUP_CODE
, B.RECONCILE_DETAIL
, NULL) IS NOT NULL ) A
, ( SELECT MAX(DECODE(LOOKUP_CODE
, 'AMOUNT'
, LOOKUP_CODE)) AMOUNT_CODE
, MAX(DECODE(LOOKUP_CODE
, 'AMOUNT'
, MEANING)) AMOUNT_MEANING
, MAX(DECODE(LOOKUP_CODE
, 'BANK ACCT'
, LOOKUP_CODE)) BANK_ACCT_CODE
, MAX(DECODE(LOOKUP_CODE
, 'BANK ACCT'
, MEANING)) BANK_ACCT_MEANING
, MAX(DECODE(LOOKUP_CODE
, 'DATE'
, LOOKUP_CODE)) DATE_CODE
, MAX(DECODE(LOOKUP_CODE
, 'DATE'
, MEANING)) DATE_MEANING
, MAX(DECODE(LOOKUP_CODE
, 'DEAL NUM'
, LOOKUP_CODE)) DEAL_NUM_CODE
, MAX(DECODE(LOOKUP_CODE
, 'DEAL NUM'
, MEANING)) DEAL_NUM_MEANING
, MAX(DECODE(LOOKUP_CODE
, 'DEAL TYPE'
, LOOKUP_CODE)) DEAL_TYPE_CODE
, MAX(DECODE(LOOKUP_CODE
, 'DEAL TYPE'
, MEANING)) DEAL_TYPE_MEANING
, MAX(DECODE(LOOKUP_CODE
, 'PARTY'
, LOOKUP_CODE)) PARTY_CODE
, MAX(DECODE(LOOKUP_CODE
, 'PARTY'
, MEANING)) PARTY_MEANING
, MAX(DECODE(LOOKUP_CODE
, 'PORTFOLIO'
, LOOKUP_CODE)) PORTFOLIO_CODE
, MAX(DECODE(LOOKUP_CODE
, 'PORTFOLIO'
, MEANING)) PORTFOLIO_MEANING
, MAX(DECODE(LOOKUP_CODE
, 'PRODUCT'
, LOOKUP_CODE)) PRODUCT_CODE
, MAX(DECODE(LOOKUP_CODE
, 'PRODUCT'
, MEANING)) PRODUCT_MEANING
, MAX(DECODE(LOOKUP_CODE
, 'SERIAL NUM'
, LOOKUP_CODE)) SERIAL_NUM_CODE
, MAX(DECODE(LOOKUP_CODE
, 'SERIAL NUM'
, MEANING)) SERIAL_NUM_MEANING
, MAX(DECODE(LOOKUP_CODE
, 'SUBTYPE'
, LOOKUP_CODE)) SUBTYPE_CODE
, MAX(DECODE(LOOKUP_CODE
, 'SUBTYPE'
, MEANING)) SUBTYPE_MEANING
, MAX(DECODE(LOOKUP_CODE
, 'MATCH'
, LOOKUP_CODE)) MATCH_CODE
, MAX(DECODE(LOOKUP_CODE
, 'MATCH'
, MEANING)) MATCH_MEANING
, MAX(DECODE(LOOKUP_CODE
, 'SUM DATE'
, LOOKUP_CODE)) SUMDATE_CODE
, MAX(DECODE(LOOKUP_CODE
, 'SUM DATE'
, MEANING)) SUMDATE_MEANING
, MAX(DECODE(LOOKUP_CODE
, 'SUM RANGE'
, LOOKUP_CODE)) SUMRANGE_CODE
, MAX(DECODE(LOOKUP_CODE
, 'SUM RANGE'
, MEANING)) SUMRANGE_MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE IN ('XTR_RECON_ON_COLUMN'
, 'XTR_RECON_CRITERIA') ) D
WHERE 1=1