XTR Settlements - Status
Description
Categories: BI Publisher
Application: Treasury
Source: Settlements - Status Report (XML)
Short Name: XTRSESTA_XML
DB package: XTR_XTRSESTA_XMLP_PKG
Source: Settlements - Status Report (XML)
Short Name: XTRSESTA_XML
DB package: XTR_XTRSESTA_XMLP_PKG
Run
XTR Settlements - Status and other Oracle EBS reports with Blitz Report™ on our demo environment
select a.company_code COMPANY, a.deal_subtype D_SUB, a.product_type PROD, a.amount_date AMT_DATE, a.currency CCY, a.account_no ACCT, a.cparty_code CPARTY, nvl(a.settlement_party, decode(sign(a.cashflow_amount) ,-1,a.cparty_code ,a.company_code)) BENEFICIARY, a.deal_type D_TYPE, a.deal_number DEAL_NO, a.transaction_number TRANS_NO, a.amount_type AMT_TYPE, a.cashflow_amount CFLOW, a.settlement_authorised_by AUTH_BY, decode(nvl(a.chq_reqd,'N') ,'Y','CQ' ,decode(nvl(a.direct_debit,'N') ,'Y','DD' ,decode(nvl(a.trans_mts,'N') ,'Y','FT'))) METHOD, XTR_XTRSESTA_XMLP_PKG.cflow1formula(a.cashflow_amount, a.currency) CFLOW1 from XTR_DEAL_DATE_AMOUNTS_V a where a.COMPANY_CODE like nvl(upper(:P_COMPANY),a.COMPANY_CODE) and a.CASHFLOW_AMOUNT != 0 and a.STATUS_CODE != 'CANCELLED' and a.CURRENCY like nvl(upper(:P_CURRENCY),a.CURRENCY) and nvl(a.MULTIPLE_SETTLEMENTS,'N') != 'Y' and a.ACCOUNT_NO like nvl(upper(:P_BANK_ACCOUNT_NUMBER),a.ACCOUNT_NO) and a.AMOUNT_DATE >= nvl(:FROM_DATE2,a.AMOUNT_DATE) and a.AMOUNT_DATE <= nvl(:TO_DATE2,a.AMOUNT_DATE) and a.SETTLE = decode(:P_AUTH_UNAUTH,'AUTHORIZED','Y','UNAUTHORIZED','N',a.SETTLE) and ((nvl(a.SETTLEMENT_PARTY,a.COMPANY_CODE) like nvl(upper(:P_BENEFICIARY),'%') and a.CASHFLOW_AMOUNT > 0) or (nvl(a.SETTLEMENT_PARTY,CPARTY_CODE) like nvl(upper(:P_BENEFICIARY),'%') and a.CASHFLOW_AMOUNT < 0)) order by COMPANY_CODE,AMOUNT_DATE,CURRENCY |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Company |
|
LOV Oracle | |
Currency |
|
LOV Oracle | |
Bank Account Number |
|
LOV Oracle | |
Settlement Date From |
|
Date | |
Settlement Date To |
|
Date | |
Authorised/Unauthorised |
|
LOV Oracle | |
Beneficiary |
|
LOV Oracle |