Generating Aging report as a TEXT file in oracle apps using utl_file

Generating Aging report as a TEXT file in oracle apps using utl_file in server location

package spec

CREATE OR REPLACE PACKAGE XXRS_AGING_4_BUCKETS_PKG
AS
  PROCEDURE XXRS_AGING_4_BUCKETS_PROC(
     errbuff OUT VARCHAR2,
    retcode  OUT NUMBER,
    P_REPORTING_LEVEL     IN VARCHAR2,
    P_REPORTING_ENTITY_ID IN VARCHAR2,
    --p_ca_set_of_books_id number,
    P_COA                  IN VARCHAR2,
    P_BAL_SEGMENT_LOW      IN VARCHAR2,
    P_BAL_SEGMENT_HIGH     IN VARCHAR2,
     P_AS_OF_DATE           IN VARCHAR2,
    p_order_by             IN VARCHAR2,
    P_SUMMARY_OPTION_LOW   IN VARCHAR2,
    P_FORMAT_OPTION_LOW    IN VARCHAR2,
    P_BUCKET_TYPE_LOW      IN VARCHAR2,
    P_CREDIT_OPTION        IN VARCHAR2,
    p_risk_option          IN VARCHAR2,
    p_curr_code            IN VARCHAR2,
    p_customer_name_low    IN VARCHAR2,
    p_customer_name_high   IN VARCHAR2,
    p_customer_number_low  IN VARCHAR2,
    p_customer_number_high IN VARCHAR2,
    P_AMT_DUE_LOW          IN VARCHAR2,
    P_AMT_DUE_HIGH         IN VARCHAR2,
    p_invoice_type_low     IN VARCHAR2,
    p_invoice_type_high    IN VARCHAR2);
END;

package body
CREATE OR REPLACE PACKAGE BODY XXRS_AGING_4_BUCKETS_PKG
AS
PROCEDURE XXRS_AGING_4_BUCKETS_PROC(
    --XXRS_AGING_4_BUCKETS_PKG.XXRS_AGING_4_BUCKETS_PROC
    errbuff OUT VARCHAR2,
    retcode OUT NUMBER,
    P_REPORTING_LEVEL     IN VARCHAR2,
    P_REPORTING_ENTITY_ID IN VARCHAR2,
    --p_ca_set_of_books_id number,
    P_COA                  IN VARCHAR2,
    P_BAL_SEGMENT_LOW      IN VARCHAR2,
    P_BAL_SEGMENT_HIGH     IN VARCHAR2,
    P_AS_OF_DATE           IN VARCHAR2,
    p_order_by             IN VARCHAR2,
    P_SUMMARY_OPTION_LOW   IN VARCHAR2,
    P_FORMAT_OPTION_LOW    IN VARCHAR2,
    P_BUCKET_TYPE_LOW      IN VARCHAR2,
    P_CREDIT_OPTION        IN VARCHAR2,
    p_risk_option          IN VARCHAR2,
    p_curr_code            IN VARCHAR2,
    p_customer_name_low    IN VARCHAR2,
    p_customer_name_high   IN VARCHAR2,
    p_customer_number_low  IN VARCHAR2,
    p_customer_number_high IN VARCHAR2,
    P_AMT_DUE_LOW          IN VARCHAR2,
    P_AMT_DUE_HIGH         IN VARCHAR2,
    p_invoice_type_low     IN VARCHAR2,
    p_invoice_type_high    IN VARCHAR2)
AS
  rp_convert_flag       VARCHAR2(50);
  func_curr_precision   VARCHAR2(50);
  functional_currency   VARCHAR2(50);
  rp_bucket_line_type_0 VARCHAR2(50);
  rp_bucket_days_from_0 VARCHAR2(20);
  rp_bucket_days_from_1 VARCHAR2(20);
  rp_bucket_days_to_1   VARCHAR2(20);
  rp_bucket_category    VARCHAR2(50);
  rp_bucket_days_to_0   VARCHAR2(20);
  rp_bucket_line_type_1 DATE;
  rp_bucket_days_to_2   VARCHAR2(20);
  rp_short_unid_phrase  VARCHAR2(50);
  c_claim_meaning       VARCHAR2(50);
  c_payment_meaning     VARCHAR2(50);
  rp_bucket_line_type_2 VARCHAR2(50);
  rp_bucket_days_from_2 VARCHAR2(20);
  rp_bucket_line_type_3 VARCHAR2(50);
  rp_bucket_days_from_3 VARCHAR2(20);
  rp_bucket_days_to_3   VARCHAR2(20);
  c_risk_meaning        VARCHAR2(50);
  --fnd_date.canonical_to_date(P_AS_OF_DATE)         VARCHAR2(50);
  --v_customer_name_inv VARCHAR2(150);
  CURSOR c1
  IS
    SELECT ps.org_id invoice_org_id,
      DECODE (UPPER(p_order_by),'CUSTOMER',NULL, types.cust_trx_type_id ) dummy_id_inv,
      DECODE (UPPER(p_order_by),'CUSTOMER',NULL,types.name) dummy_type_inv,
      /* bug2820114 Unidentified payment must be at end of report. */
      DECODE(party.party_name, NULL , '2', rtrim(rpad(substrb(
      party.party_name,1,50),36) ) ) customer_name_inv,
      cust_acct.cust_account_id customer_id_inv,
      cust_acct.account_number customer_number_inv,
      DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) contact_site_id_inv,
      DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.state,2))) cust_state_inv ,
      DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.city,25))) cust_city_inv ,
      DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) addr_id_inv ,
      rtrim(rpad(trx.purchase_order,12)) reference_number ,
      ps.payment_schedule_id payment_sched_id_inv,
      ps.class class_inv,
      ps.due_date due_date_inv ,
      DECODE( rp_convert_flag, 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining ) amt_due_remaining_inv ,
      DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) amt_due_original_inv ,
      ps.trx_number invnum ,
      types.name invoice_type_inv,
      ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date) days_past_due_inv,
      ps.amount_adjusted amount_adjusted_inv,
      ps.amount_applied amount_applied_inv,
      ps.amount_credited amount_credited_inv,
      ps.gl_date gl_date_inv,
      DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) data_converted_inv,
      NVL(ps.exchange_rate, 1) ps_exchange_rate_inv,
      DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
      DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
      DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
      DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
      rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36)) customer_name_inv_dsp
    FROM ra_cust_trx_types_all types,
      hz_cust_accounts cust_acct,
      hz_parties party,
      --**  --    lp_ar_payment_schedules_all  ps,
      AR_PAYMENT_SCHEDULES_ALL ps, --SV
      --**  --    lp_table_show_bill
      --**  --    lp_ra_customer_trx_all   trx,
      RA_CUSTOMER_TRX_ALL trx, --SV
      hz_cust_site_uses_all site,
      hz_cust_acct_sites_all addr,
      hz_party_sites party_site,
      hz_locations loc,
      ra_cust_trx_line_gl_dist_all gld,
      /* Enhancement 7346488.
      xla_distribution_links       lk,
      xla_ae_lines                 ae, */
      gl_code_combinations cc
    WHERE TRUNC(ps.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
    AND ps.customer_trx_id+0 = trx.customer_trx_id
    AND ps.customer_id       = cust_acct.cust_account_id
    AND cust_acct.party_id   = party.party_id
    AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)--SV
    AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
    AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
    AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
      --**  --    lp_customer_name_low1
      --**  --    lp_customer_name_high1
      --**  --    lp_customer_number_low1
      --**  --    lp_customer_number_high1
    AND ps.cust_trx_type_id = types.cust_trx_type_id
      -- bug1960831
    AND NVL(ps.org_id,-99) = NVL(types.org_id,-99)
    AND types.name LIKE NVL(p_invoice_type_low,types.name)--SV
    AND types.name LIKE NVL(p_invoice_type_high,types.name)
      --**  --    lp_invoice_type_low
      --**  --    lp_invoice_type_high
    AND ps.customer_site_use_id+0 = site.site_use_id(+)
    AND site.cust_acct_site_id    = addr.cust_acct_site_id(+)
    AND addr.party_site_id        = party_site.party_site_id(+)
    AND loc.location_id (+)       = party_site.location_id
    AND ps.gl_date_closed         > fnd_date.canonical_to_date(P_AS_OF_DATE)
      --**  --    lp_curr_code
    AND ps.invoice_currency_code LIKE NVL(p_curr_code,ps.invoice_currency_code)
    AND ps.customer_trx_id+0 = gld.customer_trx_id
    AND gld.account_class    = 'REC'
    AND gld.latest_rec_flag  = 'Y'
      /* Enhancement 7346488.
      and gld.cust_trx_line_gl_dist_id      = lk.source_distribution_id_num_1 (+)
      and lk.source_distribution_type (+)   = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
      and lk.application_id (+)             = 222
      and ae.application_id (+)             = 222
      and lk.ae_header_id                   = ae.ae_header_id (+)
      and lk.ae_line_num                    = ae.ae_line_num (+)
      and decode(lk.accounting_line_code, 'CM_EXCH_GAIN_LOSS', 'N',
      'AUTO_GEN_GAIN_LOSS', 'N', 'Y') = 'Y'
      and decode(ae.ledger_id,'',decode(gld.posting_control_id,-3,-999999,gld.code_combination_id),gld.set_of_books_id,ae.code_combination_id,-999999)= cc.code_combination_id
      */
    AND gld.code_combination_id = cc.code_combination_id
  --**  --    lp_bal_segment_low-doubt
  --**  --    lp_bal_segment_high--doubt
  -- Bug# 1233291
  -- and ps.payment_schedule_id+0 < :rp_ps_max_id
  --**  --    lp_where_show_bill
  -- AND PS.cons_inv_id LIKE NVL(NULL,PS.cons_inv_id)--doubt
  --**  --    P_ORG_WHERE_CTT
  --**  --    P_ORG_WHERE_PS
  --  --**  --    P_ORG_WHERE_TRX
  --  --**  --    P_ORG_WHERE_SITE
  --**  --    P_ORG_WHERE_ADDR
  --**  --    P_ORG_WHERE_GLD
  UNION ALL
  SELECT ps.org_id,
    DECODE (UPPER(p_order_by),'CUSTOMER',NULL,                                         -999) ,
    DECODE (UPPER(p_order_by),'CUSTOMER',NULL, DECODE(app.applied_payment_schedule_id, -4, c_claim_meaning, c_payment_meaning) ),
    /* bug2820114 Unidentified payment must be at end of report */
    DECODE(party.party_name , NULL , '2' , rtrim(rpad(substrb(
    party.party_name,1,50),36))),
    NVL(cust_acct.cust_account_id, -999),
    cust_acct.account_number,
    DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) ,
    DECODE(UPPER(p_format_option_low),'B',NULL,SUBSTR(loc.state,1,2)) ,
    DECODE(UPPER(p_format_option_low),'B',NULL,SUBSTR(loc.city,1,25)) ,
    DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) ,
    NULL,
    ps.payment_schedule_id,
    ps.class,
    ps.due_date,
    -SUM ( DECODE( rp_convert_flag, 'Y', app.acctd_amount_applied_from, app.amount_applied ) ),
    ps.amount_due_original,
    ps.trx_number invnum ,
    DECODE (UPPER(p_order_by),'XXX',NULL, DECODE(app.applied_payment_schedule_id, -4, c_claim_meaning, c_payment_meaning)) ,
    ceil(fnd_date.canonical_to_date(P_AS_OF_DATE)                                 - ps.due_date),
    ps.amount_adjusted,
    ps.amount_applied,
    ps.amount_credited,
    ps.gl_date,
    DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)),
    NVL(ps.exchange_rate, 1),
    DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
    DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
    DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
    DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
    --**  --    lp_bal_segment                                                           bal_segment_value_inv,
    --**  --    lp_query_show_bill cons_billing_number,
    rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
  FROM                           --**  --    lp_ar_payment_schedules_all  ps,
    AR_PAYMENT_SCHEDULES_ALL ps, --SV
    --**  --    lp_table_show_bill
    ar_cons_inv_all, --SV
    --**  --    lp_ar_receivable_apps_all  app,
    AR_RECEIVABLE_APPLICATIONS_ALL app, --SV
    gl_code_combinations cc,
    hz_cust_accounts cust_acct,
    hz_parties party,
    hz_cust_site_uses_all site,
    hz_cust_acct_sites_all addr,
    hz_party_sites party_site,
    hz_locations loc
  WHERE app.gl_date+0   <= fnd_date.canonical_to_date(P_AS_OF_DATE)
  AND ps.customer_id     = cust_acct.cust_account_id(+)
  AND cust_acct.party_id = party.party_id(+)
    /* Bug fix 5376284
    Replacing 2 parameters with 1 parameters */
  AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
  AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
  AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
  AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
    --**  --    lp_customer_name_low1
    --**  --    lp_customer_name_high1
    --**  --    lp_customer_number_low1
    --**  --    lp_customer_number_high1
  AND ps.cash_receipt_id     +0    = app.cash_receipt_id
  AND ps.customer_site_use_id+0    = site.site_use_id(+)
  AND site.cust_acct_site_id       = addr.cust_acct_site_id(+)
  AND addr.party_site_id           = party_site.party_site_id(+)
  AND loc.location_id(+)           = party_site.location_id
  AND app.code_combination_id      = cc.code_combination_id
  AND app.status                  IN ( 'ACC', 'UNAPP', 'UNID', 'OTHER ACC')
  AND NVL(app.confirmed_flag, 'Y') = 'Y'
  AND ps.gl_date_closed            > fnd_date.canonical_to_date(P_AS_OF_DATE)
  AND ((app.reversal_gl_date      IS NOT NULL
  AND ps.gl_date                  <= fnd_date.canonical_to_date(P_AS_OF_DATE))
  OR app.reversal_gl_date         IS NULL )
    --**  --    lp_curr_code
  AND ps.invoice_currency_code LIKE NVL(p_curr_code,ps.invoice_currency_code)
  AND NVL( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
    --**  --    lp_bal_segment_low
    --**  --    lp_bal_segment_high
    -- Bug# 1233291
    -- and    app.receivable_application_id+0 < :rp_app_max_id
    -- and    ps.payment_schedule_id+0 < :rp_ps_max_id
    --**  --    lp_where_show_bill
    --AND PS.cons_inv_id LIKE NVL(NULL,PS.cons_inv_id) SV doubt
    --**  --    p_org_where_ps
    --**  --    p_org_where_app
    --  --**  --    p_org_where_site
    --**  --    p_org_where_addr
  GROUP BY ps.org_id,
    party.party_name,
    site.site_use_id,
    loc.state,
    loc.city,
    addr.cust_acct_site_id,
    cust_acct.cust_account_id,
    cust_acct.account_number,
    ps.payment_schedule_id,
    ps.class,
    ps.due_date,
    ps.amount_due_original,
    ps.trx_number,
    ps.amount_adjusted,
    ps.amount_applied,
    ps.amount_credited,
    ps.gl_date,
    ps.amount_in_dispute,
    ps.amount_adjusted_pending,
    ps.invoice_currency_code,
    ps.exchange_rate,
    --**  --    lp_bal_segment ,
    DECODE( app.status, 'UNID', 'UNID', 'UNAPP'),
    --**  --    lp_query_show_bill,
    app.applied_payment_schedule_id--10,444 records
  UNION ALL
  SELECT ps.org_id,
    DECODE (UPPER(p_order_by), 'CUSTOMER', NULL, -999) ,
    DECODE (UPPER(p_order_by), 'CUSTOMER', NULL, c_risk_meaning) ,
    /* bug2820114 Unidentified payment must be at end of report */
    DECODE(party.party_name, NULL, '2' , rtrim(rpad(substrb(
     party.party_name,1,50),36) )) ,
    cust_acct.cust_account_id ,
    cust_acct.account_number ,
    DECODE(UPPER(p_format_option_low),'B', 0, site.site_use_id) ,
    DECODE(UPPER(p_format_option_low),'B', NULL, rtrim(rpad(loc.state,2))) ,
    DECODE(UPPER(p_format_option_low),'B', NULL, rtrim(rpad(loc.city,25))) ,
    DECODE(UPPER(p_format_option_low),'B', 0, addr.cust_acct_site_id ) ,
    NULL ,
    ps.payment_schedule_id ,
    DECODE (UPPER(p_order_by),'XXX',NULL,c_risk_meaning) ,
    ps.due_date ,
    DECODE( rp_convert_flag, 'Y', crh.acctd_amount , crh.amount ) ,
    DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) ,
    ps.trx_number ,
    DECODE (UPPER(p_order_by),'XXX',NULL,c_risk_meaning) ,
    ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
    ps.amount_adjusted ,
    ps.amount_applied ,
    ps.amount_credited ,
    crh.gl_date ,
    DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) ,
    NVL(ps.exchange_rate, 1) ,
    DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b0_inv,
    DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b1_inv,
    DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b2_inv,
    DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b3_inv,
    --**  --    lp_bal_segment ,
    --**  --    lp_query_show_bill cons_billing_number,
    rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
  FROM hz_cust_accounts cust_acct,
    hz_parties party,
    --**  --    lp_ar_payment_schedules_all  ps,
    AR_PAYMENT_SCHEDULES_ALL ps, --SV
    --**  --    lp_table_show_bill
    ar_cons_inv_all, --SV
    hz_cust_site_uses_all site,
    hz_cust_acct_sites_all addr,
    hz_party_sites party_site,
    hz_locations loc,
    --**  --    lp_ar_cash_receipts_all   cr,
    AR_CASH_RECEIPTS_ALL cr, --SV
    --**  --    lp_ar_cash_receipt_history_all  crh,
    AR_CASH_RECEIPT_HISTORY_ALL crh, --SV
    gl_code_combinations cc
  WHERE TRUNC(crh.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
  AND ps.trx_number        IS NOT NULL
  AND upper(p_risk_option) != 'NONE'
  AND ps.customer_id        = cust_acct.cust_account_id(+)
  AND cust_acct.party_id    = party.party_id(+)
    --**  --    lp_customer_name_low1
    --**  --    lp_customer_name_high1
    --**  --    lp_customer_number_low1
    --**  --    lp_customer_number_high1
  AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
  AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
  AND cust_acct.account_number       >= NVL(p_customer_number_low,cust_acct.account_number)
  AND cust_acct.account_number       <= NVL(p_customer_number_high,cust_acct.account_number)
  AND ps.cash_receipt_id              = cr.cash_receipt_id
  AND cr.cash_receipt_id              = crh.cash_receipt_id
  AND crh.account_code_combination_id = cc.code_combination_id
  AND ps.customer_site_use_id         = site.site_use_id(+)
  AND site.cust_acct_site_id          = addr.cust_acct_site_id(+)
  AND addr.party_site_id              = party_site.party_site_id(+)
  AND loc.location_id(+)              = party_site.location_id
    --**  --    lp_curr_code
  AND ps.invoice_currency_code LIKE NVL(p_curr_code,ps.invoice_currency_code)
  AND ( crh.current_record_flag = 'Y'
  OR crh.reversal_gl_date       > fnd_date.canonical_to_date(P_AS_OF_DATE) )
  AND crh.status NOT           IN ( DECODE(crh.factor_flag, 'Y','RISK_ELIMINATED', 'N','CLEARED'), 'REVERSED')
    /* Bug 4127480 : exclude receipts applied to short term debt */
  AND NOT EXISTS
    (SELECT 'x'
    FROM                                --**  --    lp_ar_receivable_apps_all ra
      AR_RECEIVABLE_APPLICATIONS_ALL ra --SV
    WHERE ra.cash_receipt_id        = cr.cash_receipt_id
    AND ra.status                   = 'ACTIVITY'
    AND applied_payment_schedule_id = -2
    )--10,444 records
  --**  --    lp_bal_segment_low
  --**  --    lp_bal_segment_high
  --Bug 2124653
  --and    ps.payment_schedule_id+0 < :rp_ps_max_id
  --**  --    lp_where_show_bill
  --AND PS.cons_inv_id LIKE NVL(NULL,PS.cons_inv_id) doubt
  --**  --    p_org_where_ps
  --        --**  --    p_org_where_site
  --**  --    p_org_where_addr
  --**  --    p_org_where_cr
  --**  --    p_org_where_crh
  UNION ALL
  SELECT ps.org_id invoice_org_id,
    DECODE (UPPER(p_order_by),'CUSTOMER',NULL, types.cust_trx_type_id ) dummy_id_inv,
    DECODE (UPPER(p_order_by),'CUSTOMER',NULL,types.name) dummy_type_inv,
    /* bug2820114 Unidentified payment must be at end of report. */
    DECODE(party.party_name, NULL , '2', rtrim(rpad(substrb(
     party.party_name,1,50),36) ) ) customer_name_inv,
    cust_acct.cust_account_id customer_id_inv,
    cust_acct.account_number customer_number_inv,
    DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) contact_site_id_inv,
    DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.state,2))) cust_state_inv ,
    DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.city,25))) cust_city_inv ,
    DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) addr_id_inv ,
    rtrim(rpad(trx.purchase_order,12)) reference_number ,
    ps.payment_schedule_id payment_sched_id_inv,
    ps.class class_inv,
    ps.due_date due_date_inv ,
    DECODE( rp_convert_flag, 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining ) amt_due_remaining_inv ,
    DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) amt_due_original_inv ,
    ps.trx_number invnum ,
    types.name invoice_type_inv,
    ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date) days_past_due_inv,
    ps.amount_adjusted amount_adjusted_inv,
    ps.amount_applied amount_applied_inv,
    ps.amount_credited amount_credited_inv,
    ps.gl_date gl_date_inv,
    DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) data_converted_inv,
    NVL(ps.exchange_rate, 1) ps_exchange_rate_inv,
    DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
    DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
    DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
    DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
    --**  --    lp_bal_segment                                                           bal_segment_value_inv,
    --**  --    lp_query_show_bill cons_billing_number,
    rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36)) customer_name_inv_dsp
  FROM ra_cust_trx_types_all types,
    hz_cust_accounts cust_acct,
    hz_parties party,
    --**  --    lp_ar_payment_schedules_all  ps,
    AR_PAYMENT_SCHEDULES_ALL ps, --SV
    --**  --    lp_table_show_bill
    --**  --    lp_ra_customer_trx_all   trx,
    RA_CUSTOMER_TRX_ALL trx, --SV
    hz_cust_site_uses_all site,
    hz_cust_acct_sites_all addr,
    hz_party_sites party_site,
    hz_locations loc,
    ra_cust_trx_line_gl_dist_all gld,
    /* Enhancement 7346488.
    xla_distribution_links       lk,
    xla_ae_lines                 ae, */
    gl_code_combinations cc
  WHERE TRUNC(ps.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
  AND ps.customer_trx_id+0 = trx.customer_trx_id
  AND ps.customer_id       = cust_acct.cust_account_id
  AND cust_acct.party_id   = party.party_id
    --**  --    lp_customer_name_low1
    --**  --    lp_customer_name_high1
    --**  --    lp_customer_number_low1
    --**  --    lp_customer_number_high1
  AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
  AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
  AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
  AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
  AND ps.cust_trx_type_id       = types.cust_trx_type_id
    -- bug1960831
  AND NVL(ps.org_id,-99) = NVL(types.org_id,-99)
    --**  --    lp_invoice_type_low
    --**  --    lp_invoice_type_high
  AND types.name LIKE NVL(p_invoice_type_low,types.name)
  AND types.name LIKE NVL(p_invoice_type_high,types.name)
  AND ps.customer_site_use_id+0 = site.site_use_id(+)
  AND site.cust_acct_site_id    = addr.cust_acct_site_id(+)
  AND addr.party_site_id        = party_site.party_site_id(+)
  AND loc.location_id (+)       = party_site.location_id
  AND ps.gl_date_closed         > fnd_date.canonical_to_date(P_AS_OF_DATE)
    --**  --    lp_curr_code
  AND ps.invoice_currency_code LIKE NVL(p_curr_code,ps.invoice_currency_code)
  AND ps.customer_trx_id+0 = gld.customer_trx_id
  AND gld.account_class    = 'REC'
  AND gld.latest_rec_flag  = 'Y'
    /* Enhancement 7346488.
    and gld.cust_trx_line_gl_dist_id      = lk.source_distribution_id_num_1 (+)
    and lk.source_distribution_type (+)   = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
    and lk.application_id (+)             = 222
    and ae.application_id (+)             = 222
    and lk.ae_header_id                   = ae.ae_header_id (+)
    and lk.ae_line_num                    = ae.ae_line_num (+)
    and decode(lk.accounting_line_code, 'CM_EXCH_GAIN_LOSS', 'N',
    'AUTO_GEN_GAIN_LOSS', 'N', 'Y') = 'Y'
    and decode(ae.ledger_id,'',decode(gld.posting_control_id,-3,-999999,gld.code_combination_id),gld.set_of_books_id,ae.code_combination_id,-999999)= cc.code_combination_id
    */
  AND gld.code_combination_id = cc.code_combination_id
  --**  --    lp_bal_segment_low
  --**  --    lp_bal_segment_high
  -- Bug# 1233291
  -- and ps.payment_schedule_id+0 < :rp_ps_max_id
  --**  --    lp_where_show_bill
  -- AND PS.cons_inv_id LIKE NVL(NULL,PS.cons_inv_id); doubt
  --**  --    P_ORG_WHERE_CTT
  --**  --    P_ORG_WHERE_PS
  --  --**  --    P_ORG_WHERE_TRX
  --  --**  --    P_ORG_WHERE_SITE
  --**  --    P_ORG_WHERE_ADDR
  --**  --    P_ORG_WHERE_GLD
  UNION ALL
  SELECT ps.org_id,
    DECODE (UPPER(p_order_by),'CUSTOMER',NULL,                                         -999) ,
    DECODE (UPPER(p_order_by),'CUSTOMER',NULL, DECODE(app.applied_payment_schedule_id, -4, c_claim_meaning, c_payment_meaning) ),
    /* bug2820114 Unidentified payment must be at end of report */
    DECODE(party.party_name , NULL , '2' , rtrim(rpad(substrb(
     party.party_name,1,50),36))),
    NVL(cust_acct.cust_account_id, -999),
    cust_acct.account_number,
    DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) ,
    DECODE(UPPER(p_format_option_low),'B',NULL,SUBSTR(loc.state,1,2)) ,
    DECODE(UPPER(p_format_option_low),'B',NULL,SUBSTR(loc.city,1,25)) ,
    DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) ,
    NULL,
    ps.payment_schedule_id,
    ps.class,
    ps.due_date,
    -SUM ( DECODE( rp_convert_flag, 'Y', app.acctd_amount_applied_from, app.amount_applied ) ),
    ps.amount_due_original,
    ps.trx_number invnum ,
    DECODE (UPPER(p_order_by),'XXX',NULL, DECODE(app.applied_payment_schedule_id, -4, c_claim_meaning, c_payment_meaning)) ,
    ceil(fnd_date.canonical_to_date(P_AS_OF_DATE)                                 - ps.due_date),
    ps.amount_adjusted,
    ps.amount_applied,
    ps.amount_credited,
    ps.gl_date,
    DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)),
    NVL(ps.exchange_rate, 1),
    DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
    DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
    DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
    DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
    --**  --    lp_bal_segment                                                           bal_segment_value_inv,
    --**  --    lp_query_show_bill cons_billing_number,
    rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
  FROM                           --**  --    lp_ar_payment_schedules_all  ps,
    AR_PAYMENT_SCHEDULES_ALL ps, --SV
    --**  --    lp_table_show_bill
    ar_cons_inv_all, --SV
    --**  --    lp_ar_receivable_apps_all  app,
    AR_RECEIVABLE_APPLICATIONS_ALL app, --SV
    gl_code_combinations cc,
    hz_cust_accounts cust_acct,
    hz_parties party,
    hz_cust_site_uses_all site,
    hz_cust_acct_sites_all addr,
    hz_party_sites party_site,
    hz_locations loc
  WHERE app.gl_date+0   <= fnd_date.canonical_to_date(P_AS_OF_DATE)
  AND ps.customer_id     = cust_acct.cust_account_id(+)
  AND cust_acct.party_id = party.party_id(+)
    /* Bug fix 5376284
    Replacing 2 parameters with 1 parameters */
    --**  --    lp_customer_name_low1
    --**  --    lp_customer_name_high1
    --**  --    lp_customer_number_low1
    --**  --    lp_customer_number_high1
  AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
  AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
  AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
  AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
  AND ps.cash_receipt_id     +0      = app.cash_receipt_id
  AND ps.customer_site_use_id+0      = site.site_use_id(+)
  AND site.cust_acct_site_id         = addr.cust_acct_site_id(+)
  AND addr.party_site_id             = party_site.party_site_id(+)
  AND loc.location_id(+)             = party_site.location_id
  AND app.code_combination_id        = cc.code_combination_id
  AND app.status                    IN ( 'ACC', 'UNAPP', 'UNID', 'OTHER ACC')
  AND NVL(app.confirmed_flag, 'Y')   = 'Y'
  AND ps.gl_date_closed              > fnd_date.canonical_to_date(P_AS_OF_DATE)
  AND ((app.reversal_gl_date        IS NOT NULL
  AND ps.gl_date                    <= fnd_date.canonical_to_date(P_AS_OF_DATE))
  OR app.reversal_gl_date           IS NULL )
    --**  --    lp_curr_code
  AND NVL( ps.receipt_confirmed_flag, 'Y' ) = 'Y'
    --**  --    lp_bal_segment_low
    --**  --    lp_bal_segment_high
    -- Bug# 1233291
    -- and    app.receivable_application_id+0 < :rp_app_max_id
    -- and    ps.payment_schedule_id+0 < :rp_ps_max_id
    --**  --    lp_where_show_bill
    --**  --    p_org_where_ps
    --**  --    p_org_where_app
    --  --**  --    p_org_where_site
    --**  --    p_org_where_addr
  GROUP BY ps.org_id,
    party.party_name,
    site.site_use_id,
    loc.state,
    loc.city,
    addr.cust_acct_site_id,
    cust_acct.cust_account_id,
    cust_acct.account_number,
    ps.payment_schedule_id,
    ps.class,
    ps.due_date,
    ps.amount_due_original,
    ps.trx_number,
    ps.amount_adjusted,
    ps.amount_applied,
    ps.amount_credited,
    ps.gl_date,
    ps.amount_in_dispute,
    ps.amount_adjusted_pending,
    ps.invoice_currency_code,
    ps.exchange_rate,
    --**  --    lp_bal_segment ,
    DECODE( app.status, 'UNID', 'UNID', 'UNAPP'),
    --**  --    lp_query_show_bill,
    app.applied_payment_schedule_id--10,444 records
  UNION ALL
  SELECT ps.org_id,
    DECODE (UPPER(p_order_by), 'CUSTOMER', NULL, -999) ,
    DECODE (UPPER(p_order_by), 'CUSTOMER', NULL, c_risk_meaning) ,
    /* bug2820114 Unidentified payment must be at end of report */
    DECODE(party.party_name, NULL, '2' , rtrim(rpad(substrb(
    party.party_name,1,50),36) )) ,
    cust_acct.cust_account_id ,
    cust_acct.account_number ,
    DECODE(UPPER(p_format_option_low),'B', 0, site.site_use_id) ,
    DECODE(UPPER(p_format_option_low),'B', NULL, rtrim(rpad(loc.state,2))) ,
    DECODE(UPPER(p_format_option_low),'B', NULL, rtrim(rpad(loc.city,25))) ,
    DECODE(UPPER(p_format_option_low),'B', 0, addr.cust_acct_site_id ) ,
    NULL ,
    ps.payment_schedule_id ,
    DECODE (UPPER(p_order_by),'XXX',NULL,c_risk_meaning) ,
    ps.due_date ,
    DECODE( rp_convert_flag, 'Y', crh.acctd_amount , crh.amount ) ,
    DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) ,
    ps.trx_number ,
    DECODE (UPPER(p_order_by),'XXX',NULL,c_risk_meaning) ,
    ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
    ps.amount_adjusted ,
    ps.amount_applied ,
    ps.amount_credited ,
    crh.gl_date ,
    DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) ,
    NVL(ps.exchange_rate, 1) ,
    DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b0_inv,
    DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b1_inv,
    DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b2_inv,
    DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY', 0, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) ) b3_inv,
    --**  --    lp_bal_segment ,
    --**  --    lp_query_show_bill cons_billing_number,
    rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
  FROM hz_cust_accounts cust_acct,
    hz_parties party,
    --**  --    lp_ar_payment_schedules_all  ps,
    AR_PAYMENT_SCHEDULES_ALL ps, --SV
    --**  --    lp_table_show_bill
    ar_cons_inv_all, --SV
    hz_cust_site_uses_all site,
    hz_cust_acct_sites_all addr,
    hz_party_sites party_site,
    hz_locations loc,
    --**  --    lp_ar_cash_receipts_all   cr,
    AR_CASH_RECEIPTS_ALL cr, --SV
    --**  --    lp_ar_cash_receipt_history_all  crh,
    AR_CASH_RECEIPT_HISTORY_ALL crh, --SV
    gl_code_combinations cc
  WHERE TRUNC(crh.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
  AND ps.trx_number        IS NOT NULL
  AND upper(p_risk_option) != 'NONE'
  AND ps.customer_id        = cust_acct.cust_account_id(+)
  AND cust_acct.party_id    = party.party_id(+)
    --**  --    lp_customer_name_low1
    --**  --    lp_customer_name_high1
    --**  --    lp_customer_number_low1
    --**  --    lp_customer_number_high1
  AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
  AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
  AND cust_acct.account_number       >= NVL(p_customer_number_low,cust_acct.account_number)
  AND cust_acct.account_number       <= NVL(p_customer_number_high,cust_acct.account_number)
  AND ps.cash_receipt_id              = cr.cash_receipt_id
  AND cr.cash_receipt_id              = crh.cash_receipt_id
  AND crh.account_code_combination_id = cc.code_combination_id
  AND ps.customer_site_use_id         = site.site_use_id(+)
  AND site.cust_acct_site_id          = addr.cust_acct_site_id(+)
  AND addr.party_site_id              = party_site.party_site_id(+)
  AND loc.location_id(+)              = party_site.location_id
    --**  --    lp_curr_code
  AND ( crh.current_record_flag = 'Y'
  OR crh.reversal_gl_date       > fnd_date.canonical_to_date(P_AS_OF_DATE) )
  AND crh.status NOT           IN ( DECODE(crh.factor_flag, 'Y','RISK_ELIMINATED', 'N','CLEARED'), 'REVERSED')
    /* Bug 4127480 : exclude receipts applied to short term debt */
  AND NOT EXISTS
    (SELECT 'x'
    FROM                                --**  --    lp_ar_receivable_apps_all ra
      AR_RECEIVABLE_APPLICATIONS_ALL ra --SV
    WHERE ra.cash_receipt_id        = cr.cash_receipt_id
    AND ra.status                   = 'ACTIVITY'
    AND applied_payment_schedule_id = -2
    )--10,444 records
  --**  --    lp_bal_segment_low
  --**  --    lp_bal_segment_high
  --Bug 2124653
  --and    ps.payment_schedule_id+0 < :rp_ps_max_id
  --**  --    lp_where_show_bill
  --**  --    p_org_where_ps
  --        --**  --    p_org_where_site
  --**  --    p_org_where_addr
  --**  --    p_org_where_cr
  --**  --    p_org_where_crh
  UNION ALL
  --------------------------
  -- Bills Receivable
  -----------------
  SELECT ps.org_id,
    DECODE (UPPER(p_order_by),'CUSTOMER',NULL, types.cust_trx_type_id ) dummy_id_inv,
    DECODE (UPPER(p_order_by),'CUSTOMER',NULL,types.name) dummy_type_inv,
    /* bug2820114 Unidentified payment must be at end of report */
    DECODE(party.party_name , NULL , '2' , rtrim(rpad(substrb(
     party.party_name,1,50),36)) ) customer_name_inv,
    cust_acct.cust_account_id customer_id_inv,
    cust_acct.account_number customer_number_inv,
    DECODE(UPPER(p_format_option_low),'B',0, site.site_use_id) contact_site_id_inv,
    DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.state,2))) cust_state_inv ,
    DECODE(UPPER(p_format_option_low),'B',NULL,rtrim(rpad(loc.city,25))) cust_city_inv ,
    DECODE(UPPER(p_format_option_low),'B',0,addr.cust_acct_site_id ) addr_id_inv ,
    NULL ,
    ps.payment_schedule_id payment_sched_id_inv,
    ps.class class_inv,
    ps.due_date due_date_inv ,
    DECODE( rp_convert_flag, 'Y', ps.acctd_amount_due_remaining, ps.amount_due_remaining ) amt_due_remaining_inv ,
    DECODE( rp_convert_flag, 'Y', ROUND( (ps.amount_due_original * NVL( ps.exchange_rate,1)), func_curr_precision), ps.amount_due_original ) amt_due_original_inv ,
    ps.trx_number invnum ,
    types.name invoice_type_inv,
    ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date),
    ps.amount_adjusted amount_adjusted_inv,
    ps.amount_applied amount_applied_inv,
    ps.amount_credited amount_credited_inv,
    ps.gl_date gl_date_inv,
    DECODE(ps.invoice_currency_code, functional_currency, NULL, DECODE(ps.exchange_rate, NULL, '*', NULL)) data_converted_inv,
    NVL(ps.exchange_rate, 1) ps_exchange_rate_inv,
    DECODE(rp_bucket_line_type_0, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_0, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b0_inv,
    DECODE(rp_bucket_line_type_1, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_1, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b1_inv,
    DECODE(rp_bucket_line_type_2, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_2, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b2_inv,
    DECODE(rp_bucket_line_type_3, 'DISPUTE_ONLY',DECODE(NVL(ps.amount_in_dispute,0),0,0,1), 'PENDADJ_ONLY',DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 'DISPUTE_PENDADJ',DECODE(NVL(ps.amount_in_dispute,0), 0,DECODE(NVL(ps.amount_adjusted_pending,0),0,0,1), 1), DECODE( greatest(rp_bucket_days_from_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)), least(rp_bucket_days_to_3, ceil(fnd_date.canonical_to_date(P_AS_OF_DATE) - ps.due_date)),1, 0) * DECODE(NVL(ps.amount_in_dispute,0), 0, 1, DECODE(rp_bucket_category, 'DISPUTE_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1)) * DECODE(NVL(ps.amount_adjusted_pending,0), 0, 1, DECODE(rp_bucket_category, 'PENDADJ_ONLY', 0, 'DISPUTE_PENDADJ', 0, 1))) b3_inv,
    --**  --    lp_bal_segment                                                           bal_segment_value_inv,
    --**  --    lp_query_show_bill cons_billing_number,
    rtrim(rpad(NVL(substrb(party.party_name,1,50),rp_short_unid_phrase),36))
  FROM ra_cust_trx_types_all types,
    hz_cust_accounts cust_acct,
    hz_parties party,
    --**  --    lp_ar_payment_schedules_all  ps,
    AR_PAYMENT_SCHEDULES_ALL ps, --SV
    --**  --    lp_table_show_bill
    ar_cons_inv_all, --SV
    --**  --    lp_ra_customer_trx_all   trx,
    RA_CUSTOMER_TRX_ALL trx, --SV
    hz_cust_site_uses_all site,
    hz_cust_acct_sites_all addr,
    hz_party_sites party_site,
    hz_locations loc,
    ar_transaction_history_all th,
    --**  --    lp_ar_distributions_all  dist,
    AR_DISTRIBUTIONS_ALL dist, --SV
    gl_code_combinations cc
  WHERE TRUNC(ps.gl_date) <= fnd_date.canonical_to_date(P_AS_OF_DATE)
  AND ps.class             = 'BR'
  AND ps.customer_trx_id+0 = trx.customer_trx_id
  AND ps.customer_id       = cust_acct.cust_account_id
  AND cust_acct.party_id   =party.party_id
    --**  --    lp_customer_name_low1
    --**  --    lp_customer_name_high1
    --**  --    lp_customer_number_low1
    --**  --    lp_customer_number_high1
  AND party.party_name LIKE NVL(p_customer_name_low,party.party_name)
  AND party.party_name LIKE NVL(p_customer_name_high,party.party_name)
  AND cust_acct.account_number >= NVL(p_customer_number_low,cust_acct.account_number)
  AND cust_acct.account_number <= NVL(p_customer_number_high,cust_acct.account_number)
  AND ps.cust_trx_type_id       = types.cust_trx_type_id
    -- bug1960831
  AND NVL(ps.org_id ,-99) = NVL(types.org_id,-99)
    --**  --    lp_invoice_type_low
    --**  --    lp_invoice_type_high
  AND ps.customer_site_use_id+0 = site.site_use_id(+)
  AND site.cust_acct_site_id    = addr.cust_acct_site_id(+)
  AND addr.party_site_id        = party_site.party_site_id(+)
  AND loc.location_id(+)        = party_site.location_id
  AND ps.gl_date_closed         > fnd_date.canonical_to_date(P_AS_OF_DATE)
    --**  --    lp_curr_code
  AND ps.customer_trx_id+0 = th.customer_trx_id
    --4147868   and th.current_accounted_flag = 'Y'
  AND th.transaction_history_id = dist.source_id
  AND dist.source_table         = 'TH'
    --4147868   and  dist.source_type = 'REC'
    /*4147868 : Added following code */
  AND th.transaction_history_id =
    (SELECT MAX(transaction_history_id)
    FROM ar_transaction_history_all th2,
      --**  --    lp_ar_distributions_all dist2
      AR_DISTRIBUTIONS_ALL dist2 --SV
    WHERE th2.transaction_history_id = dist2.source_id
    AND dist2.source_table           = 'TH'
    AND th2.gl_date                 <= fnd_date.canonical_to_date(P_AS_OF_DATE)
      /*GSCC Compliance*/
    AND dist2.amount_dr    IS NOT NULL
    AND th2.customer_trx_id = ps.customer_trx_id
    )
  AND dist.amount_dr IS NOT NULL
    /*4147868 end*/
  AND dist.source_table_secondary IS NULL
  AND dist.code_combination_id     = cc.code_combination_id
    --**  --    lp_bal_segment_low
    --**  --    lp_bal_segment_high
    -- Bug# 1233291
    -- and ps.payment_schedule_id+0 < :rp_ps_max_id
    --**  --    lp_where_show_bill
    --**  --    P_ORG_WHERE_CTT
    --**  --    P_ORG_WHERE_PS
    -- --**  --    P_ORG_WHERE_TRX
    -- --**  --    P_ORG_WHERE_SITE
    --**  --    P_ORG_WHERE_ADDR
    --**  --    lp_order_by_inv
  ORDER BY 4,17;
  vtext        VARCHAR2(2000);
  vfilename    VARCHAR2(2000);
  v_p_order_by VARCHAR2(2000);
  file_id utl_file.file_type;
BEGIN
  fnd_global.APPS_INITIALIZE(1151,20678,222);
  --fnd_date.canonical_to_date(P_AS_OF_DATE) :=fnd_date.canonical_to_date(P_AS_OF_DATE);
  fnd_file.put_line(FND_FILE.LOG,'P_REPORTING_LEVEL'||P_REPORTING_LEVEL);
  fnd_file.put_line(FND_FILE.LOG,'P_REPORTING_ENTITY_ID'||P_REPORTING_ENTITY_ID);
  fnd_file.put_line(FND_FILE.LOG,'P_COA'||P_COA);
  fnd_file.put_line(FND_FILE.LOG,'P_BAL_SEGMENT_LOW'||P_BAL_SEGMENT_LOW);
  fnd_file.put_line(FND_FILE.LOG,'P_BAL_SEGMENT_HIGH'||P_BAL_SEGMENT_HIGH);
  fnd_file.put_line(FND_FILE.LOG,'fnd_date.canonical_to_date(P_AS_OF_DATE)'||fnd_date.canonical_to_date(P_AS_OF_DATE));
  fnd_file.put_line(FND_FILE.LOG,'p_order_by'||p_order_by);
  fnd_file.put_line(FND_FILE.LOG,'P_SUMMARY_OPTION_LOW'||P_SUMMARY_OPTION_LOW);
  fnd_file.put_line(FND_FILE.LOG,'P_FORMAT_OPTION_LOW'||P_FORMAT_OPTION_LOW);
  fnd_file.put_line(FND_FILE.LOG,'P_BUCKET_TYPE_LOW'||P_BUCKET_TYPE_LOW);
  fnd_file.put_line(FND_FILE.LOG,'P_CREDIT_OPTION'||P_CREDIT_OPTION);
  fnd_file.put_line(FND_FILE.LOG,'p_risk_option'||p_risk_option);
  fnd_file.put_line(FND_FILE.LOG,'p_curr_code'||p_curr_code);
  fnd_file.put_line(FND_FILE.LOG,'p_customer_name_low'||p_customer_name_low);
  fnd_file.put_line(FND_FILE.LOG,'p_customer_name_high'||p_customer_name_high);
  fnd_file.put_line(FND_FILE.LOG,'p_customer_number_low'||p_customer_number_low);
  fnd_file.put_line(FND_FILE.LOG,'p_customer_number_high'||p_customer_number_high);
  fnd_file.put_line(FND_FILE.LOG,'P_AMT_DUE_LOW'||P_AMT_DUE_LOW);
  fnd_file.put_line(FND_FILE.LOG,'P_AMT_DUE_HIGH'||P_AMT_DUE_HIGH);
  fnd_file.put_line(FND_FILE.LOG,'p_invoice_type_low'||p_invoice_type_low);
  fnd_file.put_line(FND_FILE.LOG,'p_invoice_type_high'||p_invoice_type_high);
  SELECT 'Aging_Output'
    -- ||xx_file_name.nextval
    ||'.txt'
  INTO vfilename
  FROM dual;
  file_id:=utl_file.fopen('/oracle/edi/DEV/OUTBOUND',vfilename,'W');
  FOR i IN c1
  LOOP
    --vtext:=i.customer_name_inv||'~'||i.customer_number_inv||'~'||i.cust_state_inv||'~'||i.invnum||'~'||i.invoice_type_inv||'~'||i.due_date_inv||'~'||i.amt_due_remaining_inv;
    vtext:=i.customer_name_inv||'~'||i.customer_number_inv||'~'||i.cust_state_inv||'~'||i.invnum||'~'||i.invoice_type_inv||'~'||i.due_date_inv||'~'||i.amt_due_remaining_inv||'~'||i.b0_inv||'~'||i.b1_inv||'~'||i.b2_inv||'~'||i.b3_inv;
    utl_file.put_line(file_id,vtext);
    --v_customer_name_inv:=i.customer_name_inv;
  END LOOP;
  utl_file.fclose(file_id);
  fnd_file.put_line(FND_FILE.OUTPUT,'In the log file'||vfilename);
  --fnd_file.put_line(FND_FILE.OUTPUT,'v_customer_name_inv'||v_customer_name_inv);
  --fnd_file.put_line ('File in the path exists' ||vfilename );
  /*EXCEPTION
  WHEN UTL_FILE.invalid_path THEN
  fnd_file.put_line ('the path does not exists' || SQLERRM);
  WHEN UTL_FILE.invalid_operation THEN
  fnd_file.put_line ('the operation is not valid' || SQLERRM);*/
  COMMIT;
END;
END;

BARCODE IN XML PUBLISHER REPORTS ORACLE R12

BARCODE IN XML PUBLISHER

1.     Client Setup

·         Get the font IDAutomation font from idautomation

·         Place the IDAutomation font under c:\Windows\Fonts.

·         Select IDAutomation font for Barcode fields in XML Publisher Template.

·         Calling encoder in the template.(Only if vendor specific fonts and java encoder is used else ignore)

·         Add following expression in your template, It can be added directly to template or as a value to Form Field.

<?register-barcode-vendor:'oracle.apps.xdo.template.rtf.util.barcoder.BarcodeUtilaaa';'XMLPBarVendor'?>

·         Add format-barcode syntax to barcode field. Replace BARCODE in below syntax with your xml field.

*<?format-barcode:BARCODE;'code128a';'XMLPBarVendor'?>*

2.     Server Setup -- Only needed if you have vendor specific barcode fonts else ignore.

If vendor specific fonts are used, java encoder will be provided along with font which will be recognized by external device.

Below imports have to be added to the vendor provided java encoder.

  package oracle.apps.xdo.template.rtf.util.barcoder;

import java.util.Hashtable;

import java.lang.reflect.Method;

import oracle.apps.xdo.template.rtf.util.XDOBarcodeEncoder;

import oracle.apps.xdo.common.log.Logger;

// This class name will be used in the register vendor field in the template.

public class BarcodeUtil implements XDOBarcodeEncoder

// The class implements the XDOBarcodeEncoder interface

{

// This is the barcode vendor id that is used in the register vendor field and

// format-barcode fields

public static final String BARCODE_VENDOR_ID = "XMLPBarVendor";

// The hastable is used to store references to the encoding methods

public static final Hashtable ENCODERS = new Hashtable(10);

// The BarcodeUtil class needs to be instantiated

public static final BarcodeUtil mUtility = new BarcodeUtil();

// This is the main code that is executed in the class, it is loading the methods

// for the encoding into the hashtable. In this case we are loading the three code128

// encoding methods we have created.

static {

try {

Class[] clazz = new Class[] { "".getClass() } ;

ENCODERS.put("code128a",mUtility.getClass().getMethod("code128a", clazz));

ENCODERS.put("code128b",mUtility.getClass().getMethod("code128b", clazz));

ENCODERS.put("code128c",mUtility.getClass().getMethod("code128c", clazz));

} catch (Exception e) {

// This is using the XML Publisher logging class to push errors to the XMLP log file.

Logger.log(e,5);

}

}

// The getVendorID method is called from the template layer at runtime to ensure the correct

// encoding method are used

public final String getVendorID()

{

return BARCODE_VENDOR_ID;

}

// The isSupported method is called to ensure that the encoding method

// called from the template is actually present in this class. If not

// then XMLP will report this in the log.

public final boolean isSupported(String s)

{

if(s != null)

return ENCODERS.containsKey(s.trim().toLowerCase());

else

return false;

}

// The encode method is called to then call the appropriate encoding method,

// in this example the code128a/b/c methods.

public final String encode(String s, String s1)

{

if(s != null && s1 != null)

{

try

{

Method method = (Method)ENCODERS.get(s1.trim().toLowerCase());

if(method != null)

return (String)method.invoke(this, new Object[] {

s

});

else

return s;

}

catch(Exception exception)

{

Logger.log(exception,5);

}

return s;

} else

{

return s;

}

}

/** Add Vendor Method for Code128a */

public static final String code128a( String DataToEncode )

{

return Printable_string;

}

}

 

 Generate class file from java code and place it under OA_JAVA/oracle/apps/xdo/template/rtf/util/barcoder. If barcoder directory doesn't exist create one.

$ cd $OA_JAVA/oracle/apps/xdo/template/rtf/util

$ mkdir barcoder

 Change permissions of the barcoder directory to 777/755.

3.     XML Publisher Font Setup

No longer XML publisher fonts needed to be placed on the server. They can be uploaded and used from XML publisher font file and font mappings.

Navigate to XML Publisher responsibility.

Go to Administration Tab.

Click on Font Files and create font file

HERE THE FONT IS IDAutomationSHcC128M

Font Name: IDAutomationSHcC128M

File: Select IDAutomationSHcC128M.ttf from your saved location.

Click Apply.

Click on Font Mappings. Click “Create Font Mapping Set”.

Mapping Name: IDAutomationSHcC128M

Mapping Code: IDAutomationSHcC128M

Type: FO To Pdf

Click Apply.

Click on Create Font Mapping. Fill the values as below screen shot.

Font Family: IDAutomationSHcC128M

Font Value: IDAutomationSHcC128M

Click Apply.

In the next page enter

Click on Administration Tab

Expand FO Processing and enter Barcodes as Font Mapping Set value.