XTR Settlements - Daily Payments / Receipts Letters

Description
Categories: BI Publisher
Columns: Amtdate, Ccy1, Amount1, Toc, Bkname1, Bk S Code, Location1, Forc, Account1, Cparty N1 ...
Application: Treasury
Source: Settlements - Daily Payments / Receipts Letters (XML)
Short Name: XTRLTDAP_XML
DB package: XTR_XTRLTDAP_XMLP_PKG
select
a.actual_settlement_date                      AMTDATE
,a.currency                                            CCY1
,a.settle_amount                                  AMOUNT1
,'To'                                                       TOC
,b.bank_name                                        BKNAME1
,b.bank_short_code                               BK_S_CODE
,b.location                                              LOCATION1
,'For A/C'                                                FORC
,a.cparty_account_no                            ACCOUNT1
,c.short_name                                        CPARTY_N1
,p.contact_name                                    DEALT_CONTACT
,p.short_name                                        DEALT_CPARTY
,p.address_1                                          DEALT_ADDR1
,p.address_2                                          DEALT_ADDR2
,p.address_3                                          DEALT_ADDR3
,p.address_4                                          DEALT_ADDR4
,p.address_5                                          DEALT_ADDR5
,p.address_6                                          DEALT_ADDR6
,decode(a.direct_debit
     ,'Y','By way of Direct Debit to our account:'||a.account_no
    , rtrim(b.sub_account_at)||' '||
      'A/c '||rtrim(b.bank_name)||' '||rtrim(b.street)||' '||rtrim(b.location)||decode(nvl(b.sub_account_number,
                                               '@[email protected]'),'@[email protected]',null,' A/c Number ')||' '||rtrim(b.sub_account_number)||' '||
     'Favour '||rtrim(c.short_name)||'  A/c Number '||rtrim(a.cparty_account_no))
                                                                 ACCT_DET
,to_char(a.netoff_number)                        NETOFF_NO
,a.chq_reqd                                                CHQ
,a.direct_debit                                             DD
,a.trans_mts                                                EFT
from xtr_settlement_letters_v a,
       xtr_bank_accounts_v b,
       xtr_party_info c,        xtr_party_info p where a.company_code like nvl(:COMPANY_CODE2,'%')
and a.cparty_code like nvl(:CPARTY_CODE2,'%')
and a.currency like nvl(:CCY_CODE2,'%')
and a.actual_settlement_date >= nvl(:PERIOD_FROM_DATE2, a.actual_settlement_date ) 
and c.party_code = a.settle_party
and p.party_code = a.cparty_code
and b.party_code = a.settle_party
and b.account_number = a.cparty_account_no
and b.currency = a.currency
order by 13,DEALT_ADDR5,DEALT_ADDR3,DEALT_ADDR4,DEALT_CPARTY,DEALT_ADDR2,DEALT_CONTACT,DEALT_ADDR6
Parameter Name SQL text Validation
Company
 
LOV Oracle
Counterparty
 
LOV Oracle
Currency
 
LOV Oracle
Period From
 
Date