AR Customer Balance Statement Letter

Description
Categories: BI Publisher
Application: Receivables
Source: AR Customer Balance Statement Letter
Short Name: ARCUSBALSL
DB package: AR_CUST_BAL_RPT_PKG
               SELECT MIN(ASH.line_cluster_id)    LINE_CLUSTER_ID
                     ,MAX(ASH.line_cluster_id)    MAX_LINE_CLUSTER_ID
                     ,ASH.send_to_address_id      SEND_TO_ADDRESS_ID
                     ,ASH.customer_id             CUSTOMER_ID
                     ,MAX(ASH.site_use_id)        SITE_USE_ID
                     ,ASH.statement_type          STATEMENT_TYPE
                     ,ASH.remit_to_address_id     REMIT_TO_ADDRESS_ID
                     ,ASH.statement_date          STATEMENT_DATE
                     ,ASH.bucket1_heading         BUCKET1_HEADING
                     ,ASH.bucket2_heading         BUCKET2_HEADING
                     ,ASH.bucket3_heading         BUCKET3_HEADING
                     ,ASH.bucket4_heading         BUCKET4_HEADING
                     ,ASH.bucket5_heading         BUCKET5_HEADING
                     ,SUM(ASH.bucket1)            BUCKET1
                     ,SUM(ASH.bucket2)            BUCKET2
                     ,SUM(ASH.bucket3)            BUCKET3
                     ,SUM(ASH.bucket4)            BUCKET4
                     ,SUM(ASH.bucket5)            BUCKET5
                     ,SUM(ASH.finance_charges)    FINANCE_CHARGES
                     ,SUM(ASH.total_amount_due)   TOTAL_AMOUNT_DUE
                     ,ASH.currency_code           CURRENCY_CODE
                     ,ASH.monthly_interest_rate   MONTHLY_INTEREST_RATE
                     ,ASH.error_no_send_to        ERROR_NO_SEND_TO
                     ,ASH.error_no_remit_to       ERROR_NO_REMIT_TO
                     ,ASH.error_amount_different  ERROR_AMOUNT_DIFFERENT
                     ,HCA.ACCOUNT_NUMBER          CUST_NUM
                     ,HZP.party_name              SEND_TO_CUSTOMER_NAME
					 ,HZP.party_id                CUST_ID
                     ,MAX(DECODE(ASH.statement_type
					            ,'STMTS'
								,NULL
								,ASH.bill_to_location)
                         )                        SEND_TO_BILL_TO_LOCATION
                     ,HZL1.address1               SEND_TO_ADDRESS1
                     ,HZL1.address2               SEND_TO_ADDRESS2
                     ,HZL1.address3               SEND_TO_ADDRESS3
                     ,HZL1.address4               SEND_TO_ADDRESS4
                     ,HZL1.city                   SEND_TO_CITY
                     ,HZL1.state                  SEND_TO_STATE
                     ,HZL1.postal_code            SEND_TO_POSTAL_CODE
                     ,FTV1.territory_short_name   SEND_TO_COUNTRY_DESC
                     ,HZL1.country                SEND_COUNTRY
                     ,HZL2.address1               REMIT_TO_ADDRESS1
                     ,HZL2.address2               REMIT_TO_ADDRESS2
                     ,HZL2.address3               REMIT_TO_ADDRESS3
                     ,HZL2.address4               REMIT_TO_ADDRESS4
                     ,HZL2.city                   REMIT_TO_CITY
                     ,HZL2.state                  REMIT_TO_STATE
                     ,HZL2.postal_code            REMIT_TO_POSTAL_CODE
                     ,FTV2.territory_short_name   REMIT_TO_COUNTRY_DESC
                     ,HZL2.country                REMIT_COUNTRY
                     ,MSG.TEXT                    MESSAGE_TEXT
               FROM   ar_statement_headers        ASH
                     ,hz_cust_accounts            HCA
                     ,hz_parties                  HZP
                     ,fnd_territories_vl          FTV1
                     ,fnd_territories_vl          FTV2
                     ,hz_locations                HZL1
		     ,hz_cust_acct_sites          HCAS1
		     ,hz_party_sites              HPS1
                     ,hz_locations                HZL2
		     ,hz_cust_acct_sites          HCAS2
                     ,hz_party_sites              HPS2
                     ,ar_standard_text            MSG
               WHERE  ASH.error_no_lines        = 'N'
               AND    ASH.customer_id           = HCA.cust_account_id
               AND    HCA.party_id              = HZP.party_id
               AND    ASH.send_to_address_id    = HCAS1.cust_acct_site_id (+)
               AND    HCAS1.party_site_id       = HPS1.party_site_id(+)
               AND    HPS1.location_id          = HZL1.location_id (+)
               AND    HZL1.country              = FTV1.territory_code (+)
               AND    ASH.remit_to_address_id   = HCAS2.cust_acct_site_id (+)
               AND    HCAS2.party_site_id       = HPS2.party_site_id(+)
               AND    HPS2.location_id          = HZL2.location_id (+)
               AND    HZL2.country              = FTV2.territory_code (+)
               AND    ASH.request_id            = :PARENT_REQUEST_ID
               AND    :REPORT_NAME IS NOT NULL
               AND    NVL(bucket1,0)+ NVL(buckeT2,0)+ NVL(bucket3,0)+ 
                      NVL(bucket5,0)+ NVL(bucket4,0) <> 0
               AND  ash.message1                = msg.standard_text_id (+)       
               GROUP BY
	              ASH.send_to_address_id
                     ,ASH.customer_id
                     ,ASH.statement_type
                     ,ASH.remit_to_address_id
                     ,ASH.statement_date
                     ,ASH.bucket1_heading
                     ,ASH.bucket2_heading
                     ,ASH.bucket3_heading
                     ,ASH.bucket4_heading
                     ,ASH.bucket5_heading
                     ,ASH.currency_code
                     ,ASH.monthly_interest_rate
                     ,ASH.error_no_send_to
                     ,ASH.error_no_remit_to
                     ,ASH.error_amount_different
                     ,DECODE(ASH.statement_type, 'STMTS',NULL,' : '||ASH.bill_to_location)
                     ,HZL1.country
                     ,HZL2.country
                     ,HCA.account_number
		     ,HZP.party_id
                     ,HZP.party_name
		     ,HZP.organization_name_phonetic
                     ,HZL1.address1
                     ,HZL1.address2
                     ,HZL1.address3
                     ,HZL1.address4
                     ,HZL1.city
                     ,HZL1.state
                     ,HZL1.postal_code
                     ,FTV1.territory_short_name
                     ,HZL2.address1
                     ,HZL2.address2
                     ,HZL2.address3
                     ,HZL2.address4
                     ,HZL2.city
                     ,HZL2.state
                     ,HZL2.postal_code
                     ,FTV2.territory_short_name
                     ,MSG.TEXT
               ORDER BY
	              &P_SORT
                     ,ASH.currency_code
                     ,DECODE(ASH.statement_type
                     ,'STMTS'       ,1
                     ,'ONACC_UNAPP' ,2
                     ,'SUMMARY'     ,3
                     ,'BILL_TO'     ,4)
                     ,DECODE(ASH.statement_type, 'STMTS',NULL,' : '||ASH.bill_to_location)