Reports2017-11-18T12:27:27+00:00

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
By continuing to use the site, you agree to the use of cookies. Accept