Oracle MOOC: Introduction to Java Cloud Services

1. Introduction to Oracle Cloud

https://www.youtube.com/watch?v=vidrAMQeofo

2. Cloud Computing Benefits
https://www.youtube.com/watch?v=auXBWWFNqrQ

3.Introduction to Java Cloud Services

https://www.youtube.com/watch?v=9WFFcAji6Uk

4. Java Cloud Services ,Concepts,Capabilities,Architecture

https://www.youtube.com/watch?v=ANEEoCgEvP4

ACTIVE USERS EMPLOYEE AND THEIR RESPONSIBILITIES IN ORACLE APPS

ACTIVE USERS EMPLOYEE AND THEIR RESPONSIBILITIES IN ORACLE APPS

SELECT fu.user_name user_name
      ,fu.description user_description
      ,fu.email_address user_email
      ,ppf.full_name employee_name
      ,hou.name business_group
      ,fr.responsibility_name resp_name
FROM apps.fnd_user fu
    ,apps.per_all_people_f ppf
    ,apps.hr_all_organization_units hou
    ,apps.fnd_user_resp_groups_all fur
    ,apps.fnd_responsibility_tl fr
WHERE ppf.person_id = fu.employee_id
AND hou.organization_id = ppf.business_group_id
AND fu.user_id = fur.user_id
AND NVL (fur.end_date, SYSDATE + 1) > SYSDATE
AND fur.responsibility_id = fr.responsibility_id
--AND fr.responsibility_name LIKE 'Shipping User'
AND fr.LANGUAGE = 'US'
ORDER BY fu.user_name
        ,fr.responsibility_name

Active Employees and Their Jobs in oracle apps

Active Employees and Their Jobs

SELECT papf.full_name emp_name,
      papf.current_employee_flag current_emp_flag,
      papf.employee_number emp_number,
      b.d_job_id job_title,
      papf.email_address email

FROM  apps.PER_ALL_PEOPLE_F       papf,
      apps.PER_ALL_ASSIGNMENTS_F asg,
      apps.PER_ASSIGNMENTS_V7    b,
      apps.PER_JOBS              pjb
   
WHERE papf.person_id       = asg.person_id(+)
AND asg.person_id          = b.person_id
AND b.effective_start_date =
  (SELECT MAX (b2.effective_start_date)
  FROM apps.per_assignments_v7 b2
  WHERE b2.person_id = b.person_id
  )
AND NVL (papf.effective_end_date, SYSDATE + 1) > SYSDATE
AND NVL (asg.effective_end_date, SYSDATE  + 1) > SYSDATE
AND b.job_id                                   = pjb.job_id(+)
ORDER BY papf.full_name

Employee supervisor Oracle R12 SQL Query

# Oracle EBS SQL Query that will list all employees in the hr.per_all_assignments_f 
# table and their supervisor, in readable format.

SELECT DISTINCT papf1.full_name leve1_full_name
     , papf1.employee_number level1_empno
     , papf2.full_name leve2_full_name
     , papf2.employee_number level2_empno
  FROM hr.per_all_people_f papf1
     , hr.per_all_assignments_f paaf1
     , hr.per_all_assignments_f paaf2
     , hr.per_all_people_f papf2
  WHERE  papf1.person_id = paaf1.person_id
  AND paaf1.supervisor_id = papf2.person_id(+)
  AND papf2.person_id = paaf2.person_id

  ORDER BY leve1_full_name;

RTF Template having more columns in oracle apps , Call Template in Layout

we can build RTF Template with more columns using <?call-template:1temp?>  

CUSTOMER ITEM INTERFACE ORACLE APPS

CUSTOMER ITEM INTERFACE ORACLE APPS CODE:

PACKAGE SPEC:
CREATE OR REPLACE PACKAGE xxrs_cust_item_int_pkg
AS
PROCEDURE main(retcode OUT NUMBER
              ,errbuff OUT VARCHAR2
 ,p_cust_no IN NUMBER);
PROCEDURE cust_item_iface(p_cust_no IN NUMBER);
PROCEDURE cust_item_iface_xref(p_cust_no IN NUMBER);
END xxrs_cust_item_int_pkg;

PACKAGE BODY:

create or replace PACKAGE BODY xxrs_cust_item_int_pkg
AS
 PROCEDURE main( retcode   OUT NUMBER
                ,errbuff   OUT VARCHAR2
         ,p_cust_no  IN NUMBER
)
 AS
  --local variables
  l_req_id            NUMBER;
  lc_phase            VARCHAR2(50);
  lc_status           VARCHAR2(50);
  lc_dev_phase        VARCHAR2(50);
  lc_dev_status       VARCHAR2(50);
  lc_message          VARCHAR2(50);
  l_req_return_status BOOLEAN;
 BEGIN

   l_req_id := fnd_request.submit_request( application   => 'XXRS'
                                          ,program       => 'XXRSCUITEL'
                                          ,description   => ''
                                          ,start_time    => SYSDATE
                                          ,sub_request   => FALSE
                       );

      fnd_file.put_line(fnd_file.log,'Request Id for loading Customer Items to temp table'||l_req_id);

      COMMIT;

     l_req_return_status :=fnd_concurrent.wait_for_request (request_id      => l_req_id
                                                           ,INTERVAL        => 5
                                                           ,max_wait        => 1
                                                           ,phase           => lc_phase
                                                           ,STATUS          => lc_status
                                                           ,dev_phase       => lc_dev_phase
                                                           ,dev_status      => lc_dev_status
                                                           ,message         => lc_message
                                                          );
        xxrs_cust_item_int_pkg.cust_item_iface(p_cust_no);
        xxrs_cust_item_int_pkg.cust_item_iface_xref(p_cust_no);
 EXCEPTION
  WHEN OTHERS THEN
   fnd_File.put_line(fnd_file.log,'Unhandled exception occurred in package.procedure'||SQLERRM);
   retcode :=2;
 END main;

 PROCEDURE cust_item_iface(p_cust_no IN NUMBER
                          )
 AS
 --local variables
  v_user_id           NUMBER;
  v_acct_id           NUMBER :=p_cust_no;
  l_req_id            NUMBER;
  lc_phase            VARCHAR2(50);
  lc_status           VARCHAR2(50);
  lc_dev_phase        VARCHAR2(50);
  lc_dev_status       VARCHAR2(50);
  lc_message          VARCHAR2(50);
  l_req_return_status BOOLEAN;
  -- cursor is fetching the data from staging table to validate
 CURSOR c1 IS
 SELECT vendor_item
       ,medline_item
 FROM xx_cus_item_temp;

 BEGIN
  fnd_file.put_line(fnd_file.log,'v_acct_id '||v_acct_id);
   select user_id
   into v_user_id
   from fnd_user
   where user_name='ANONYMOUS';

   FOR i IN c1
   LOOP
   INSERT INTO mtl_ci_interface(process_flag
                                ,process_mode
                                ,lock_flag
                                ,last_updated_by
                                ,last_update_date
                                ,last_update_login
                                ,created_by
                                ,creation_date
                                ,transaction_type
                                ,customer_id
                                ,address_id
                                ,customer_item_number
                                ,customer_item_desc
                                ,item_definition_level
                                ,commodity_code_id
                                ,inactive_flag
                                )
                       VALUES (1              
                              ,1              
                              ,'N'            
                              ,v_user_id      
                              ,SYSDATE        
                              ,v_user_id      
                              ,v_user_id      
                              ,SYSDATE
                              ,'CREATE'
                              ,v_acct_id
                              ,NULL
                              ,i.medline_item
                              ,NULL
                              ,'1'
                              ,1
                              ,2
                              );
                       COMMIT;
   END LOOP;
             
l_req_id := fnd_request.submit_request(application    => 'INV'
                                           ,program       => 'INVCIINT'
                                           ,description   => ''
                                           ,start_time    => SYSDATE
                                           ,sub_request   => FALSE
                                           ,argument1  => 'N'
                                           ,argument2  => 'Y'
  );
     fnd_file.put_line(fnd_file.log,'request id for import customer items '||l_req_id);

     COMMIT;
      l_req_return_status :=fnd_concurrent.wait_for_request (request_id      => l_req_id
                                                            ,INTERVAL        => 5
                                                            ,max_wait        => 1
                                                            ,phase           => lc_phase
                                                            ,STATUS          => lc_status
                                                            ,dev_phase       => lc_dev_phase
                                                            ,dev_status      => lc_dev_status
                                                            ,message         => lc_message
                                                            );
 END cust_item_iface;
 

 PROCEDURE cust_item_iface_xref(p_cust_no IN NUMBER
                               )
 AS

  v_user_id    NUMBER;
  V_ITEM_ID    NUMBER;
  l_req_id     NUMBER;
  V_ACCT_ID    NUMBER :=p_cust_no;

   -- cursor is fetching the data from staging table to validate
  CURSOR c1 IS
  SELECT vendor_item
        ,medline_item
  FROM xx_cus_item_temp;

  BEGIN
    select user_id
    into v_user_id
    from fnd_user
    where user_name='ANONYMOUS';


   FOR i IN c1
   LOOP
     BEGIN
      SELECT DISTINCT inventory_item_id
      INTO v_item_id
      FROM mtl_system_items_b
      WHERE segment1=i.vendor_item;--'040-00';
     EXCEPTION
      WHEN no_data_found THEN
       v_item_id:=NULL;
       fnd_file.put_line(fnd_file.log,'for vendor_item'||i.vendor_item ||'inventory_item_id doesnot exists');
     END;

             INSERT INTO mtl_ci_xrefs_interface (process_flag
                                                ,process_mode
                                                ,lock_flag
                                                ,last_update_date
                                                ,last_updated_by
                                                ,creation_date
                                                ,created_by
                                                ,last_update_login
                                                ,transaction_type
                                                ,customer_id
                                                ,address_id
                                                ,customer_item_number
                                                ,item_definition_level
                                                ,inventory_item_id
                                                ,master_organization_id
                                                ,preference_number
                                                ,inactive_flag
                                                )
                                        VALUES (1
                                               ,1
                                               ,'N'
                                               ,SYSDATE
                                               ,v_user_id
                                               ,SYSDATE
                                               ,v_user_id
                                               ,v_user_id
                                               ,'CREATE'
                                               ,v_acct_id
                                               ,NULL
                                               ,i.medline_item
                                               ,'1'
                                               ,v_item_id
                                               ,83
                                               ,1
                                               ,2
                                              );
                                      COMMIT;

  END LOOP;

l_req_id := fnd_request.submit_request(application   => 'INV'
                                          ,program       => 'INVCIINTX'
                                          ,description   => ''
                                          ,start_time    => SYSDATE
                                          ,sub_request   => FALSE
                                          ,argument1   => 'N'
                                          ,argument2 => 'Y'
 );
    fnd_file.put_line(fnd_file.log,'request id for import customer item cross references'||l_req_id);
COMMIT;
 END cust_item_iface_xref;


end xxrs_cust_item_int_pkg;

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;