7 Replies Latest reply: Mar 12, 2012 7:41 PM by DanielB RSS

    Add Where Clause in "Invoice Aging Report" ==> APXINAGE.rdf

    915031
      Hi,

      I want to add where clause 'Invoice_Date' from table ap_invoices_all in report APXINAGE.rdf. But it not work. The result is the same as when I before added where clause
      Invoice_Date.

      ====BEFORE====
      SELECT hp.party_name C_VENDOR_NAME,
      v.segment1 C_VENDOR_NUMBER,
      decode(upper(:P_SORT_OPTION),
      'VENDOR NAME',
      decode(:C_VENDOR_NAME_SELECT,
      '%',
      upper(hp.party_name),
      hp.party_name),
      i.invoice_type_lookup_code) C_PRIMARY_BRK,
      decode(upper(:P_SORT_OPTION),
      'VENDOR NAME',
      decode(:C_VENDOR_NAME_SELECT,
      '%',
      decode(:SORT_BY_ALTERNATE,
      'Y',
      upper(hp.organization_name_phonetic),
      upper(hp.party_name)),
      decode(:SORT_BY_ALTERNATE,
      'Y',
      hp.organization_name_phonetic,
      hp.party_name)),
      i.invoice_type_lookup_code) C_PRIMARY_BRK_REAL,
      hp.party_name C_SHORT_VENDOR_NAME,
      v.vendor_id C_VENDOR_ID,
      i.vendor_site_id C_CONTACT_SITE_ID,
      vs.vendor_site_code C_VENDOR_SITE_CODE,
      decode(:SORT_BY_ALTERNATE,
      'Y',
      vs.vendor_site_code_alt,
      vs.vendor_site_code) C_VENDOR_SITE_CODE_BRK,
      nvl(vs.state, ' ') C_VENDOR_STATE,
      nvl(substr(vs.city, 1, 15), ' ') C_VENDOR_CITY,
      ps.payment_num C_REFERENCE_NUMBER,
      i.vendor_site_id C_ADDRESS_ID,
      nvl(substr(i.invoice_type_lookup_code, 1, 20), ' ') C_INVOICE_TYPE,
      i.invoice_id C_PAYMENT_SCHED_ID,
      nvl(to_char(ps.due_date, 'DD-MON-RR'), ' ') C_DUE_DATE,
      /* Bug: 1549982 */
      /* Bug 2083419 For foreign currencies, show amounts in functional currency */
      decode(i.invoice_currency_code,
      :C_BASE_CURRENCY_CODE,
      decode(:C_BASE_MIN_ACCT_UNIT,
      0,
      round(((nvl(ps.amount_remaining, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)),
      :C_BASE_PRECISION),
      round(((nvl(ps.amount_remaining, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)) / :C_BASE_MIN_ACCT_UNIT) *
      :C_BASE_MIN_ACCT_UNIT),
      decode(i.exchange_rate,
      NULL,
      0,
      decode(:C_BASE_MIN_ACCT_UNIT,
      0,
      round(((nvl(ps.amount_remaining, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)),
      :C_BASE_PRECISION),
      round(((nvl(ps.amount_remaining, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)) /
      :C_BASE_MIN_ACCT_UNIT) *
      :C_BASE_MIN_ACCT_UNIT))) C_AMT_DUE_REMAINING,
      decode(i.invoice_currency_code,
      :C_BASE_CURRENCY_CODE,
      decode(:C_BASE_MIN_ACCT_UNIT,
      0,
      round(((nvl(ps.gross_amount, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)),
      :C_BASE_PRECISION),
      round(((nvl(ps.gross_amount, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)) / :C_BASE_MIN_ACCT_UNIT) *
      :C_BASE_MIN_ACCT_UNIT),
      decode(i.exchange_rate,
      NULL,
      0,
      decode(:C_BASE_MIN_ACCT_UNIT,
      0,
      round(((nvl(ps.gross_amount, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)),
      :C_BASE_PRECISION),
      round(((nvl(ps.gross_amount, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)) /
      :C_BASE_MIN_ACCT_UNIT) *
      :C_BASE_MIN_ACCT_UNIT))) C_AMT_DUE_ORIGINAL,
      i.accts_pay_code_combination_id C_VENDOR_TRX_ID,
      i.invoice_num C_INVOICE_NUMBER,
      i.invoice_num C_INVOICE_NUM_SHORT,
      to_char(i.invoice_date, 'DD-MON-RR') C_INVOICE_DATE,
      ceil(to_date(to_char(SYSDATE, 'DD-MON-RR'), 'DD-MON-RR') -
      ps.due_date) C_DAYS_PAST_DUE,
      decode(i.invoice_currency_code,
      :C_BASE_CURRENCY_CODE,
      ' ',
      decode(i.exchange_rate, NULL, '*', ' ')) C_DATA_CONVERTED,
      nvl(i.exchange_rate, 1) C_EXCHANGE_RATE,
      decode(:C_VENDOR_NAME_SELECT,
      '%',
      decode(:SORT_BY_ALTERNATE,
      'Y',
      upper(hp.organization_name_phonetic),
      upper(hp.party_name)),
      decode(:SORT_BY_ALTERNATE,
      'Y',
      hp.organization_name_phonetic,
      hp.party_name)) C_VENDOR_NAME_BRK,
      g.segment4 acc
      FROM ap_payment_schedules_all ps,
      ap_invoices_all i,
      hz_parties hp,
      ap_suppliers v,
      po_vendor_sites vs,
      GL_CODE_COMBINATIONS g
      WHERE i.invoice_id = ps.invoice_id
      AND i.party_id = hp.party_id
      AND i.Accts_Pay_Code_Combination_Id = g.code_combination_id
      AND hp.party_id = v.party_id(+)
      AND i.vendor_site_id = vs.vendor_site_id(+)
      &P_PARTY_PREDICATE
      AND ((to_date(to_char(SYSDATE, 'DD-MON-RR'), 'DD-MON-RR') - ps.due_date) between :C_MINDAYS and :C_MAXDAYS)
      AND i.invoice_type_lookup_code like :C_INVOICE_TYPE_SELECT
      AND i.cancelled_date IS NULL
      AND (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate, 1)) != 0
      &P_AMOUNT_PREDICATE
      AND nvl(i.payment_status_flag, 'N') in ('N', 'P') /* Added the NVL for Bug#7115261 */
      &P_ORDER_BY

      ====E N D====

      to
      ====AFTER====
      SELECT hp.party_name C_VENDOR_NAME,
      v.segment1 C_VENDOR_NUMBER,
      decode(upper(:P_SORT_OPTION),
      'VENDOR NAME',
      decode(:C_VENDOR_NAME_SELECT,
      '%',
      upper(hp.party_name),
      hp.party_name),
      i.invoice_type_lookup_code) C_PRIMARY_BRK,
      decode(upper(:P_SORT_OPTION),
      'VENDOR NAME',
      decode(:C_VENDOR_NAME_SELECT,
      '%',
      decode(:SORT_BY_ALTERNATE,
      'Y',
      upper(hp.organization_name_phonetic),
      upper(hp.party_name)),
      decode(:SORT_BY_ALTERNATE,
      'Y',
      hp.organization_name_phonetic,
      hp.party_name)),
      i.invoice_type_lookup_code) C_PRIMARY_BRK_REAL,
      hp.party_name C_SHORT_VENDOR_NAME,
      v.vendor_id C_VENDOR_ID,
      i.vendor_site_id C_CONTACT_SITE_ID,
      vs.vendor_site_code C_VENDOR_SITE_CODE,
      decode(:SORT_BY_ALTERNATE,
      'Y',
      vs.vendor_site_code_alt,
      vs.vendor_site_code) C_VENDOR_SITE_CODE_BRK,
      nvl(vs.state, ' ') C_VENDOR_STATE,
      nvl(substr(vs.city, 1, 15), ' ') C_VENDOR_CITY,
      ps.payment_num C_REFERENCE_NUMBER,
      i.vendor_site_id C_ADDRESS_ID,
      nvl(substr(i.invoice_type_lookup_code, 1, 20), ' ') C_INVOICE_TYPE,
      i.invoice_id C_PAYMENT_SCHED_ID,
      nvl(to_char(ps.due_date, 'DD-MON-RR'), ' ') C_DUE_DATE,
      /* Bug: 1549982 */
      /* Bug 2083419 For foreign currencies, show amounts in functional currency */
      decode(i.invoice_currency_code,
      :C_BASE_CURRENCY_CODE,
      decode(:C_BASE_MIN_ACCT_UNIT,
      0,
      round(((nvl(ps.amount_remaining, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)),
      :C_BASE_PRECISION),
      round(((nvl(ps.amount_remaining, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)) / :C_BASE_MIN_ACCT_UNIT) *
      :C_BASE_MIN_ACCT_UNIT),
      decode(i.exchange_rate,
      NULL,
      0,
      decode(:C_BASE_MIN_ACCT_UNIT,
      0,
      round(((nvl(ps.amount_remaining, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)),
      :C_BASE_PRECISION),
      round(((nvl(ps.amount_remaining, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)) /
      :C_BASE_MIN_ACCT_UNIT) *
      :C_BASE_MIN_ACCT_UNIT))) C_AMT_DUE_REMAINING,
      decode(i.invoice_currency_code,
      :C_BASE_CURRENCY_CODE,
      decode(:C_BASE_MIN_ACCT_UNIT,
      0,
      round(((nvl(ps.gross_amount, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)),
      :C_BASE_PRECISION),
      round(((nvl(ps.gross_amount, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)) / :C_BASE_MIN_ACCT_UNIT) *
      :C_BASE_MIN_ACCT_UNIT),
      decode(i.exchange_rate,
      NULL,
      0,
      decode(:C_BASE_MIN_ACCT_UNIT,
      0,
      round(((nvl(ps.gross_amount, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)),
      :C_BASE_PRECISION),
      round(((nvl(ps.gross_amount, 0) /
      (nvl(i.payment_cross_rate, 1))) *
      nvl(i.exchange_rate, 1)) /
      :C_BASE_MIN_ACCT_UNIT) *
      :C_BASE_MIN_ACCT_UNIT))) C_AMT_DUE_ORIGINAL,
      i.accts_pay_code_combination_id C_VENDOR_TRX_ID,
      i.invoice_num C_INVOICE_NUMBER,
      i.invoice_num C_INVOICE_NUM_SHORT,
      to_char(i.invoice_date, 'DD-MON-RR') C_INVOICE_DATE,
      ceil(to_date(to_char(SYSDATE, 'DD-MON-RR'), 'DD-MON-RR') -
      ps.due_date) C_DAYS_PAST_DUE,
      decode(i.invoice_currency_code,
      :C_BASE_CURRENCY_CODE,
      ' ',
      decode(i.exchange_rate, NULL, '*', ' ')) C_DATA_CONVERTED,
      nvl(i.exchange_rate, 1) C_EXCHANGE_RATE,
      decode(:C_VENDOR_NAME_SELECT,
      '%',
      decode(:SORT_BY_ALTERNATE,
      'Y',
      upper(hp.organization_name_phonetic),
      upper(hp.party_name)),
      decode(:SORT_BY_ALTERNATE,
      'Y',
      hp.organization_name_phonetic,
      hp.party_name)) C_VENDOR_NAME_BRK,
      g.segment4 acc
      FROM ap_payment_schedules_all ps,
      ap_invoices_all i,
      hz_parties hp,
      ap_suppliers v,
      po_vendor_sites vs,
      GL_CODE_COMBINATIONS g
      WHERE i.invoice_id = ps.invoice_id
      AND i.party_id = hp.party_id
      AND i.Accts_Pay_Code_Combination_Id = g.code_combination_id
      AND hp.party_id = v.party_id(+)
      AND i.vendor_site_id = vs.vendor_site_id(+)
      &P_PARTY_PREDICATE
      AND ((to_date(to_char(SYSDATE, 'DD-MON-RR'), 'DD-MON-RR') - ps.due_date) between :C_MINDAYS and :C_MAXDAYS)
      AND to_char(i.invoice_date, 'DD-MON-RR') <= :P_MAXDAY
      AND i.invoice_type_lookup_code like :C_INVOICE_TYPE_SELECT
      AND i.cancelled_date IS NULL
      AND (nvl(ps.amount_remaining, 0) * nvl(i.exchange_rate, 1)) != 0
      &P_AMOUNT_PREDICATE
      AND nvl(i.payment_status_flag, 'N') in ('N', 'P') /* Added the NVL for Bug#7115261 */
      &P_ORDER_BY

      ====E N D====
        • 1. Re: Add Where Clause in "Invoice Aging Report" ==> APXINAGE.rdf
          DanielB
          how is the value and type of :P_MAXDAY
          ?

          Edited by: DanielB on Mar 7, 2012 8:48 AM
          • 2. Re: Add Where Clause in "Invoice Aging Report" ==> APXINAGE.rdf
            915031
            Hi,

            Value *:P_MAXDAYS* = date (exp. sysdate)
            • 3. Re: Add Where Clause in "Invoice Aging Report" ==> APXINAGE.rdf
              DanielB
              if p_maxdays is a date value don't use to_char for invoice_date

              invoice_date is a date field of ap_invoices_all

              p_maxdays sounds as a number de days, then what do you want to compare?
              • 4. Re: Add Where Clause in "Invoice Aging Report" ==> APXINAGE.rdf
                918454
                If your :p_maxday is the number i.e. 30,60 then

                add
                AND TRUNC(SYSDATE) - trunc(i.invoice_date) <= :P_MAXDAY
                so if you have invoice date is 28-FEB-12 and you pass p_maxday value as 30 then today, above statement will be converted as
                10 <=30

                (SYSDATE - 28-FEB-12 = 10)
                • 5. Re: Add Where Clause in "Invoice Aging Report" ==> APXINAGE.rdf
                  915031
                  Hi Daniel,
                  we've tried in accordance with the instructions. But we instead found a new error.
                  i change in where clause to :

                  AND ((to_date(to_char(SYSDATE, 'DD-MON-RR'), 'DD-MON-RR') - ps.due_date) between :C_MINDAYS and :C_MAXDAYS)
                  AND i.invoice_date <= to_date(:CP_1,'DD-MON-RR')

                  and the result is error. see below:

                  +-----------------------------
                  | Starting concurrent program execution...
                  +-----------------------------

                  Arguments
                  ------------
                  P_SORT_OPTION='Vendor Name'
                  P_SUMMARY_OPTION='Y'
                  P_FORMAT_OPTION='N'
                  P_PERIOD_TYPE='MTI Overdue Monthly'
                  P_SET_OF_BOOKS_ID='2022'
                  P_TRACE_SWITCH='N'
                  P_MAXDAYS='2011/12/31 00:00:00'
                  ------------

                  APPLLCSP Environment Variable set to :

                  Current NLS_LANG and NLS_NUMERIC_CHARACTERS Environment Variables are :
                  American_America.UTF8

                  '.,'

                  Enter Password:
                  REP-0069: Internal error
                  REP-57054: In-process job terminated:Terminated with error:
                  REP-300: MSG-00001: After SRWINIT
                  MSG-00002: After Get_Company_Name
                  MSG-00003: After Get_NLS_Strings
                  MSG-00004: After Get_Base_Curr_Data
                  MSG-00005: After custom_init
                  MSG-00007: After set_order_by
                  MSG-00008: After Get_period_info
                  MSG-00009: Closing Before Report
                  ORA-01861: literal does not match format string
                  SELECT hp . party_name C_VENDOR_NAME , v . segment1 C_VENDOR_NUMBER , decode ( upper ( : P_SORT_OPTION ) , 'VENDOR

                  Report Builder: Release 10.1.2.0.2 - Production on Mon Mar 12 10:25:00 2012

                  Copyright (c) 1982, 2005, Oracle. All rights reserved.


                  ---------------------------------------------------------------------------
                  Start of log messages from FND_FILE
                  ---------------------------------------------------------------------------
                  ---------------------------------------------------------------------------
                  End of log messages from FND_FILE
                  ---------------------------------------------------------------------------
                  Program exited with status 1
                  Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 1691037.

                  Review your concurrent request log and/or report output file for more detailed information.


                  ---------------------------------------------------------------------------
                  Executing request completion options...


                  Finished executing request completion options.

                  ---------------------------------------------------------------------------
                  • 6. Re: Add Where Clause in "Invoice Aging Report" ==> APXINAGE.rdf
                    915031
                    Sorry,
                    type data *:P_MaxDays* is CHAR
                    • 7. Re: Add Where Clause in "Invoice Aging Report" ==> APXINAGE.rdf
                      DanielB
                      is a char but is a data with the format 'DD-MON-RR'??


                      then
                      AND i.invoice_date <= to_date(:P_MAXDAY, 'DD-MON-RR')

                      you must compare date and not char ok?