Customer Print Statement Query.

Customer Print Statement Query.

SELECT   MIN (sh.line_cluster_id) line_cluster_id,
         MAX (sh.line_cluster_id) max_line_cluster_id,
         sh.send_to_address_id send_to_address_id,
         sh.customer_id customer_id,
         MAX (sh.site_use_id) site_use_id,
         sh.statement_type statement_type,
         sh.remit_to_address_id remit_to_address_id,
         INITCAP (sh.statement_date) statement_date,
         sh.bucket1_heading bucket1_heading,
         sh.bucket2_heading bucket2_heading,
         sh.bucket3_heading bucket3_heading,
         sh.bucket4_heading bucket4_heading,
         sh.bucket5_heading bucket5_heading,
         SUM (sh.bucket1) bucket1, SUM (sh.bucket2) bucket2,
         SUM (sh.bucket3) bucket3, SUM (sh.bucket4) bucket4,
         SUM (sh.bucket5) bucket5,
         SUM (sh.bucket1 + sh.bucket2) bucket12,
         SUM (sh.bucket3 + sh.bucket4) bucket34,
         SUM (sh.finance_charges) finance_charges,
         SUM (sh.total_amount_due) total_amount_due,
         sh.currency_code currency_code,
         sh.monthly_interest_rate monthly_interest_rate,
         sh.error_no_send_to error_no_send_to,
         sh.error_no_remit_to error_no_remit_to,
         sh.error_amount_different error_amount_different,
         SUBSTRB
            (   MIN
                   (DECODE
                       (NVL (loc1.LANGUAGE,
                             :p_base_language),
                        USERENV ('LANG'), NVL
                           (acct_site1.translated_customer_name,
                            SUBSTRB (party.party_name, 1,
                                     50)
                           ),
                        SUBSTRB (party.party_name, 1, 50)
                       )
                   )
             || DECODE (sh.statement_type,
                        'STMTS', NULL,
                        ' : ' || sh.bill_to_location
                       ),
             1,
             50
            ) send_customer_name,
         LTRIM (SUBSTRB (loc1.address1, 1, 50)
               ) send_address1,
         LTRIM (SUBSTRB (loc1.address2, 1, 50)
               ) send_address2,
         LTRIM (SUBSTRB (loc1.address3, 1, 50)
               ) send_address3,
         LTRIM (SUBSTRB (loc1.address4, 1, 50)
               ) send_address4,
         SUBSTRB (loc1.city, 1, 30) send_city,
         SUBSTRB (loc1.state, 1, 30) send_state,
         SUBSTRB (loc1.postal_code, 1, 30) send_postal_code,
         loc1.country send_country,
         SUBSTRB
                (t1.territory_short_name,
                 1,
                 30
                ) send_country_desc,
         LTRIM (SUBSTRB (loc2.address1, 1, 50)
               ) remit_address1,
         LTRIM (SUBSTRB (loc2.address2, 1, 50)
               ) remit_address2,
         LTRIM (SUBSTRB (loc2.address3, 1, 50)
               ) remit_address3,
         LTRIM (SUBSTRB (loc2.address4, 1, 50)
               ) remit_address4,
         SUBSTRB (loc2.city, 1, 30) remit_city,
         SUBSTRB (loc2.state, 1, 30) remit_state,
         SUBSTRB (loc2.postal_code, 1,
                  30) remit_postal_code,
         loc2.country remit_country,
         SUBSTRB
               (t2.territory_short_name,
                1,
                30
               ) remit_country_desc,
         c.account_number cust_num,
         SUBSTRB (party.party_name,
                  1,
                  50
                 ) send_to_customer_name,
          MAX
            (DECODE (sh.statement_type,
                     'STMTS', NULL,
                     sh.bill_to_location
                    )
            ) send_to_bill_to_location,
         loc1.address1 send_to_address1,
         loc1.address2 send_to_address2,
         loc1.address3 send_to_address3,
         loc1.address4 send_to_address4,
         loc1.city send_to_city,
         loc1.state send_to_state,
         loc1.postal_code send_to_postal_code,
         loc1.province send_to_province,
         UPPER
              (t1.territory_short_name)
                                       send_to_country_desc,
         loc2.address1 remit_to_address1,
         loc2.address2 remit_to_address2,
         loc2.address3 remit_to_address3,
         loc2.address4 remit_to_address4,
         loc2.city remit_to_city, loc2.state remit_to_state,
         loc2.postal_code remit_to_postal_code,
         loc2.province remit_to_province,
         t2.territory_short_name remit_to_country_desc
,hcpc.name cust_profile_class
    FROM ar_statement_headers sh,
         hz_cust_accounts c,
         hz_parties party,
         fnd_territories_vl t1,
         fnd_territories_vl t2,
         hz_locations loc1,
         hz_cust_acct_sites acct_site1,
         hz_party_sites party_site1,
         hz_locations loc2,
         hz_cust_acct_sites acct_site2,
         hz_party_sites party_site2
, hz_customer_profiles hzcp,
      HZ_CUST_PROFILE_CLASSES hcpc
   WHERE 1 = 1
     AND NVL (sh.total_amount_due, 1) != 0
     AND NOT EXISTS (
            SELECT 1
              FROM hz_customer_profiles hzcp
             WHERE c.cust_account_id = hzcp.cust_account_id
               AND hzcp.profile_class_id = 1043)
       AND sh.error_no_lines = 'N'
     AND sh.customer_id = c.cust_account_id
      AND hzcp.profile_class_id = hcpc.profile_class_id
AND hzcp.cust_account_id = c.cust_account_id
     AND c.party_id = party.party_id
     AND sh.send_to_address_id = acct_site1.cust_acct_site_id(+)
     AND acct_site1.party_site_id = party_site1.party_site_id(+)
     AND party_site1.location_id = loc1.location_id(+)
     AND loc1.country = t1.territory_code(+)
     AND sh.remit_to_address_id = acct_site2.cust_acct_site_id(+)
     AND acct_site2.party_site_id = party_site2.party_site_id(+)
     AND party_site2.location_id = loc2.location_id(+)
     AND loc2.country = t2.territory_code(+)
     AND sh.request_id = :p_conc_request_id
     AND :report_name IS NOT NULL
GROUP BY sh.send_to_address_id,
         sh.customer_id,
         sh.statement_type,
         sh.remit_to_address_id,
         sh.statement_date,
         sh.bucket1_heading,
         sh.bucket2_heading,
         sh.bucket3_heading,
         sh.bucket4_heading,
         sh.bucket5_heading,
         sh.currency_code,
         sh.monthly_interest_rate,
         sh.error_no_send_to,
         sh.error_no_remit_to,
         sh.error_amount_different,
         DECODE (sh.statement_type,
                 'STMTS', NULL,
                 ' : ' || sh.bill_to_location
                ),
         LTRIM (SUBSTRB (loc1.address1, 1, 50)),
         LTRIM (SUBSTRB (loc1.address2, 1, 50)),
         LTRIM (SUBSTRB (loc1.address3, 1, 50)),
         LTRIM (SUBSTRB (loc1.address4, 1, 50)),
         SUBSTRB (loc1.city, 1, 30),
         SUBSTRB (loc1.state, 1, 30),
         SUBSTRB (loc1.postal_code, 1, 30),
         loc1.country,
         SUBSTRB (t1.territory_short_name, 1, 30),
         LTRIM (SUBSTRB (loc2.address1, 1, 50)),
         LTRIM (SUBSTRB (loc2.address2, 1, 50)),
         LTRIM (SUBSTRB (loc2.address3, 1, 50)),
         LTRIM (SUBSTRB (loc2.address4, 1, 50)),
         SUBSTRB (loc2.city, 1, 30),
         SUBSTRB (loc2.state, 1, 30),
         SUBSTRB (loc2.postal_code, 1, 30),
         loc2.country,
         SUBSTRB (t2.territory_short_name, 1, 30),
         c.account_number,
         party.party_name,
         party.organization_name_phonetic,
         loc1.address1,
         loc1.address2,
         loc1.address3,
         loc1.address4,
         loc1.city,
         loc1.state,
         loc1.postal_code,
         loc1.province,
         t1.territory_short_name,
         loc2.address1,
         loc2.address2,
         loc2.address3,
         loc2.address4,
         loc2.city,
         loc2.state,
         loc2.postal_code,
         loc2.province,
         t2.territory_short_name
, hcpc.name
  HAVING (   SUM (sh.bucket5) != 0
          OR SUM (sh.bucket3 + sh.bucket4) != 0
         )
     AND EXISTS (
            SELECT 1
              FROM ar_statement_line_clusters slc,
                   ar_statement_line_clusters slc2
             WHERE 1 = 1
               AND slc.line_cluster_id IN (
                      SELECT sh2.line_cluster_id
                        FROM ar_statement_headers sh2
                       WHERE sh2.customer_id = sh2.customer_id
                         AND sh2.line_cluster_id
                                BETWEEN MIN (sh.line_cluster_id)
                                    AND MAX (sh.line_cluster_id)
                         AND sh2.request_id =
                                          :p_conc_request_id)
               AND slc.payment_schedule_sequence = slc2.payment_schedule_sequence(+)
               AND slc.request_id = :p_conc_request_id
               AND slc2.request_id(+) = :p_conc_request_id
               AND (   NVL (slc.amount_due, 0) != 0
                    OR slc.invoice_number = 'On Account'
                   )
               AND NOT EXISTS (
                      SELECT 1
                        FROM ar_cash_receipts_all acr,
                             ar_payment_schedules_all aps
                       WHERE acr.receipt_number =
                                DECODE
                                   (slc.invoice_number,
                                    'On Account', SUBSTR
                                       (slc.REFERENCE,
                                          INSTR
                                             (slc.REFERENCE,
                                              ':'
                                             )
                                        + 2,
                                        LENGTH
                                              (slc.REFERENCE)
                                       ),
                                    'Unapplied', SUBSTR
                                       (slc.REFERENCE,
                                          INSTR
                                             (slc.REFERENCE,
                                              ':'
                                             )
                                        + 2,
                                        LENGTH
                                              (slc.REFERENCE)
                                       ),
                                    slc.invoice_number
                                   )
                         AND aps.cash_receipt_id =
                                         acr.cash_receipt_id
                         AND NVL (aps.amount_due_remaining,
                                  0
                                 ) = 0
                         AND slc.TRANSACTION = 'Payment'
                         AND slc.invoice_number =
                                                 'Unapplied')
               AND slc2.trx_order_sequence(+) = 1)