AP Unaccounted Transactions Report- Delete - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Unaccounted Transactions Report (XML) - Delete - Not Supported: Reserved For Future Use
Short Name: APXTRSWP_XML
DB package: AP_APXTRSWP_XMLP_PKG
SELECT decode(asp.SORT_BY_ALTERNATE_FIELD, 'Y', 
                             (nvl((UPPER(v.vendor_name_alt)), (UPPER(v.vendor_name)))),
                             UPPER(v.vendor_name)) upp_vend_name_pmt,
       v.segment1 vendor_num_pmt,
       ac.check_number pmt_num,
       ac.check_date pmt_date,
       ac.currency_code pmt_curr,
       ac.amount pmt_amt,
       alc.displayed_field exception_pmt,
       ac.check_id pmt_id,
       v.vendor_name vendor_name_pmt,
       fc.precision precision
FROM ap_checks_all ac,
       po_vendors v, 
       ap_lookup_codes alc,
       ap_system_parameters_all asp,
       fnd_currencies fc
WHERE   ac.vendor_id = v.vendor_id
  AND  ac.check_id  in (select  aip.check_id
                                      from ap_invoice_payments_all aip,
                                               ap_system_parameters_all asp,
                                              &C_ORG_FROM_TABLES
                                      where  nvl(asp.org_id, -99) = nvl(aip.org_id, -99)
                                      and     asp.recon_accounting_flag = 'N'
                                      and     nvl(asp.when_to_account_pmt, 'ALWAYS') = 'ALWAYS' 
                                      and     aip.posted_flag IN ('N','S')
                                     &C_DATE_FILTER_PAY
                                     &C_AIP_MULTI_ORG_WHERE
                                     &P_AIP_ORG_WHERE)
  AND alc.lookup_type = 'NLS TRANSLATION'
  AND alc.lookup_code = decode(ac.currency_code,  :c_base_currency_code, 'UNACCOUNTED', 
                                                      decode(ac.exchange_rate, null,'NO RATE', 'UNACCOUNTED'))
  AND asp.org_id = ac.org_id
  AND asp.base_currency_code = fc.currency_code
UNION
SELECT decode(asp.SORT_BY_ALTERNATE_FIELD, 'Y', 
                            (nvl((UPPER(v.vendor_name_alt)), (UPPER(v.vendor_name)))), 
                            UPPER(v.vendor_name)) upp_vend_name_pmt,
       v.segment1  vendor_num_pmt,
       ac.check_number pmt_num,
       ac.check_date pmt_date,
       ac.currency_code pmt_curr,
       ac.amount pmt_amt,
       alc.displayed_field exception_pmt,
       ac.check_id pmt_id,
       v.vendor_name vendor_name_pmt,
       fc.precision precision
FROM ap_checks_all ac,
            po_vendors v,
           ap_lookup_codes alc,
           ap_system_parameters_all asp,
           fnd_currencies fc
 WHERE ac.vendor_id = v.vendor_id
  AND (NVL(:P_SWEEP_NOW_1, 'N') = 'Y' OR NVL(:P_UNACCT_RPT, 'Y') = 'Y')   AND  ac.check_id in (select aph.check_id
                                      from ap_payment_history_all aph,
                                               ap_system_parameters_all asp,
                                               &C_ORG_FROM_TABLES
                                      where nvl(asp.org_id, -99) = nvl(aph.org_id, -99)
                                      and     aph.posted_flag IN ('N','S')
                                      and     asp.recon_accounting_flag = 'Y'
                                      and     aph.transaction_type IN ('PAYMENT CLEARING',
                                                        'PAYMENT UNCLEARING')
                                      and     nvl(asp.when_to_account_pmt, 'ALWAYS') = 'CLEARING ONLY'
                                     &C_DATE_FILTER_PAYHIST
                                     &C_APH_MULTI_ORG_WHERE
                                     &P_APH_ORG_WHERE)
  AND alc.lookup_type = 'NLS TRANSLATION'
  AND alc.lookup_code =  decode(ac.currency_code, :c_base_currency_code, 'UNACCOUNTED', 
                                                       decode(ac.exchange_rate, null,'NO RATE', 'UNACCOUNTED'))
  AND asp.org_id = ac.org_id
  AND asp.base_currency_code = fc.currency_code
UNION
SELECT decode(asp.SORT_BY_ALTERNATE_FIELD, 'Y', 
                            (nvl((UPPER(v.vendor_name_alt)), (UPPER(v.vendor_name)))),
                            UPPER(v.vendor_name)) upp_vend_name_pmt,
       v.segment1  vendor_num_pmt,
       ac.check_number pmt_num,
       ac.check_date pmt_date,
       ac.currency_code pmt_curr,
       ac.amount pmt_amt,
       alc.displayed_field exception_pmt,
       ac.check_id pmt_id,
       v.vendor_name vendor_name_pmt,
       fc.precision precision
FROM ap_checks_all ac,
       po_vendors v,
       ap_lookup_codes alc,
       ap_system_parameters_all asp,
       fnd_currencies fc
WHERE  ac.vendor_id = v.vendor_id
 AND     (NVL(:P_SWEEP_NOW_1, 'N') = 'Y' OR NVL(:P_UNACCT_RPT, 'Y') = 'Y')  AND     ac.check_id in (select aip.check_id
                                       from ap_invoice_payments_all aip,
                                                ap_system_parameters_all asp,
                                                &C_ORG_FROM_TABLES
                                       where  nvl(asp.org_id, -99) = nvl(aip.org_id, -99)
                                       and     asp.recon_accounting_flag = 'Y'
                                       and     nvl(asp.when_to_account_pmt, 'ALWAYS') = 'ALWAYS' 
                                       and     aip.posted_flag IN ('N','S')
                                      &C_DATE_FILTER_PAY
                                      &C_AIP_MULTI_ORG_WHERE
                                      &P_AIP_ORG_WHERE)
  AND alc.lookup_type = 'NLS TRANSLATION'
  AND alc.lookup_code = decode(ac.currency_code, :c_base_currency_code, 'UNACCOUNTED', 
                                                      decode(ac.exchange_rate, null,'NO RATE', 'UNACCOUNTED'))
  AND asp.org_id = ac.org_id
  AND asp.base_currency_code = fc.currency_code
UNION
SELECT decode(asp.SORT_BY_ALTERNATE_FIELD, 'Y', 
                            (nvl((UPPER(v.vendor_name_alt)), (UPPER(v.vendor_name)))),
                            UPPER(v.vendor_name)) upp_vend_name_pmt,
       v.segment1  vendor_num_pmt,
       ac.check_number pmt_num,
       ac.check_date pmt_date,
       ac.currency_code pmt_curr,
       ac.amount pmt_amt,
       alc.displayed_field exception_pmt,
       ac.check_id pmt_id,
       v.vendor_name vendor_name_pmt,
       fc.precision  precision
FROM ap_checks_all ac,
       po_vendors v,
       ap_lookup_codes alc,
       ap_system_parameters_all asp,
       fnd_currencies fc
WHERE  ac.vendor_id = v.vendor_id
  AND    (NVL(:P_SWEEP_NOW_1, 'N') = 'Y' OR NVL(:P_UNACCT_RPT, 'Y') = 'Y')   AND     ac.check_id in (select aph.check_id
                                      from ap_payment_history_all aph,
                                               ap_system_parameters_all asp,
                                              &C_ORG_FROM_TABLES
                                      where nvl(asp.org_id, -99) = nvl(aph.org_id, -99)
                                      and     aph.posted_flag IN ('N','S')
                                      and     asp.recon_accounting_flag = 'Y'
                                      and     nvl(asp.when_to_account_pmt, 'ALWAYS') = 'ALWAYS'               
                                     &C_DATE_FILTER_PAYHIST
                                     &C_APH_MULTI_ORG_WHERE
                                     &P_APH_ORG_WHERE)
  AND alc.lookup_type = 'NLS TRANSLATION'
  AND alc.lookup_code = decode(ac.currency_code, :c_base_currency_code, 'UNACCOUNTED', 
                                                      decode(ac.exchange_rate, null,'NO RATE', 'UNACCOUNTED'))   
  AND asp.org_id = ac.org_id  
  AND asp.base_currency_code = fc.currency_code
ORDER BY 7,  1,  4, 3
Parameter Name SQL text Validation
Ledger Id
 
Number
Period Name
 
LOV Oracle
To Accounting Date
 
Date
From Accounting Date
 
Date
Reporting Context
 
LOV Oracle
Reporting Level
 
LOV Oracle