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
Source: AR Customer Balance Statement Letter
Short Name: ARCUSBALSL
DB package: AR_CUST_BAL_RPT_PKG
Run
AR Customer Balance Statement Letter and other Oracle EBS reports with Blitz Report™ on our demo environment
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 ,FND_DATE.DATE_TO_CHARDATE(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 ,FND_DATE.DATE_TO_CHARDATE(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) |
