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) |
