Code Block

language	java

Provider Address:
($V{issuerName}!=null ? $V{issuerName} + "\n" : "" )+
"Student Accounts\n" +
($F{billing_property_name} != null ? $F{billing_property_name}  + "\n" : "") +
($F{billing_postal_subaddress} != null ? $F{billing_postal_subaddress} + "\n" : 
($F{billing_street_subaddress} != null ? $F{billing_street_subaddress} + "/" : "") +
($F{billing_street_number} != null ? $F{billing_street_number} + " " : "") +
($F{billing_street_name} != null ? $F{billing_street_name} + "\n" : "")) +
($F{billing_city} != null ? $F{billing_city} + " " : "") +
($F{billing_state_name} != null ? $F{billing_state_name} + " " : "") +
($F{billing_postal_code} != null ? $F{billing_postal_code} + "\n" : "") +
($F{billing_country_name} != null && !$F{billing_country_name}.equals("AUS") && !$F{billing_country_name}.equals("AUST") && !$F{billing_country_name}.equals("AUSTRALIA") && !$F{billing_country_name}.equals("Australia")? $F{billing_country_name} : "")
+"\nPhone: " + ($F{billing_fixed_phone_number}!=null ? $F{billing_fixed_phone_number} : ($F{billing_other_phone_number}!=null ? $F{billing_other_phone_number} : ($F{billing_mobile_phone_number}!=null ? $F{billing_mobile_phone_number} : "" )))


Residential Address:
$F{first_name}+" "+$F{last_name}+"\n"+
($F{residential_property_name} != null ? $F{residential_property_name}  + "\n" : "") +
($F{residential_postal_subaddress} != null ? $F{residential_postal_subaddress} + "\n" : 
($F{residential_street_subaddress} != null && !$F{residential_street_subaddress}.equals("") ? $F{residential_street_subaddress}.replaceAll("\\\\'", "'") + "/" : "") +
($F{residential_street_number} != null && !$F{residential_street_number}.equals("") ? $F{residential_street_number}.replaceAll("\\\\'", "'") + " " : "") +
($F{residential_street_name} != null && !$F{residential_street_name}.equals("") ? $F{residential_street_name}.replaceAll("\\\\'", "'") + '\n' : "")) +
($F{residential_city} != null && !$F{residential_city}.equals("") ? $F{residential_city}.replaceAll("\\\\'", "'") + ", \t" : "") + 
($F{residential_state_abbreviation}!= null && !$F{residential_state_abbreviation}.equals("") ? $F{residential_state_abbreviation}.replaceAll("\\\\'", "'") + " ": "") +
($F{residential_postal_code}!= null && !$F{residential_postal_code}.equals("") ? $F{residential_postal_code}.replaceAll("\\\\'", "'") + " ": "") +
($F{residential_country_geo_id} != null && !$F{residential_country_geo_id}.equals("") && !$F{residential_country_geo_id}.equals("AUSTRALIA") && !$F{residential_country_geo_id}.equals("AUS") ? "\n" + $F{residential_country_geo_id}.replaceAll("\\\\'", "'") : "")


Billing Address:
$F{first_name}+" "+$F{last_name}+"\n"+
($F{billing_property_name} != null ? $F{billing_property_name}  + "\n" : "") +
($F{billing_postal_subaddress} != null ? $F{billing_postal_subaddress} + "\n" : 
($F{billing_street_subaddress} != null && !$F{billing_street_subaddress}.equals("") ? $F{billing_street_subaddress}.replaceAll("\\\\'", "'") + "/" : "") +
($F{billing_street_number} != null && !$F{billing_street_number}.equals("") ? $F{billing_street_number}.replaceAll("\\\\'", "'") + " " : "") +
($F{billing_street_name} != null && !$F{billing_street_name}.equals("") ? $F{billing_street_name}.replaceAll("\\\\'", "'") + '\n' : "")) +
($F{billing_city} != null && !$F{billing_city}.equals("") ? $F{billing_city}.replaceAll("\\\\'", "'") + ", \t" : "") + 
($F{billing_state_abbreviation}!= null && !$F{billing_state_abbreviation}.equals("") ? $F{billing_state_abbreviation}.replaceAll("\\\\'", "'") + " ": "") +
($F{billing_postal_code}!= null && !$F{billing_postal_code}.equals("") ? $F{billing_postal_code}.replaceAll("\\\\'", "'") + " ": "") +
($F{billing_country_geo_id} != null && !$F{billing_country_geo_id}.equals("") && !$F{billing_country_geo_id}.equals("AUSTRALIA") && !$F{billing_country_geo_id}.equals("AUS") ? "\n" + $F{billing_country_geo_id}.replaceAll("\\\\'", "'") : "")


($F{billing_property_name} != null ? $F{billing_property_name}  + "\n" : "") +
($F{billing_postal_subaddress} != null ? $F{billing_postal_subaddress} + "\n" : 
($F{billing_street_subaddress} != null ? $F{billing_street_subaddress} + "/" : "") +
($F{billing_street_number} != null ? $F{billing_street_number} + " " : "") +
($F{billing_street_name} != null ? $F{billing_street_name} + "\n" : "")) +
($F{billing_city} != null ? $F{billing_city} + " " : "") +
($F{billing_state_province_geo_id} != null ? $F{billing_state_province_geo_id} + " " : ($F{billing_state_abbreviation} != null ? $F{billing_state_abbreviation} + " " : " ")) +
($F{billing_postal_code} != null ? $F{billing_postal_code} + "\n" : "") +
($F{billing_country_name} != null ? $F{billing_country_name} + "\n" : "")


($F{billing_address1} != null ? $F{billing_address1}.replaceAll("\\\\'", "'") + '\n' : "") +
($F{billing_address2} != null ? $F{billing_address2}.replaceAll("\\\\'", "'") + '\n' : "") +
($F{billing_city} != null ? $F{billing_city}.replaceAll("\\\\'", "'") + '\t' : "") + "  " +
($F{billing_state} != null ? $F{billing_state_abbrev}.replaceAll("\\\\'", "'") + "   " : ($F{billing_state}!=null ? $F{billing_state}.replaceAll("\\\\'", "'") + "   " : "")) +
($F{billing_postal_code} != null ? $F{billing_postal_code}.replaceAll("\\\\'", "'") : "") +
($F{billing_country} != null && !$F{billing_country}.equals("AUSTRALIA") ? "\n" + $F{billing_country}.replaceAll("\\\\'", "'") : "")


($F{postal_address1} != null ? $F{postal_address1}.replaceAll("\\\\'", "'") + '\n' : "") +
($F{postal_address2} != null ? $F{postal_address2}.replaceAll("\\\\'", "'") + '\n' : "") +
($F{postal_city} != null ? $F{postal_city}.replaceAll("\\\\'", "'") + '\t' : "") + "  " +
($F{postal_state} != null ? $F{postal_state_abbrev}.replaceAll("\\\\'", "'") + "   " : ($F{postal_state}!=null ? $F{postal_state}.replaceAll("\\\\'", "'") + "   " : "")) +
($F{postal_state_abbrev} != null ? $F{postal_state_abbrev}.replaceAll("\\\\'", "'") : "") +
($F{postal_country} != null && !$F{postal_country}.equals("AUSTRALIA") ? "\n" + $F{postal_country}.replaceAll("\\\\'", "'") : "")


Provider ABN, CRICOS and TEQSA provider code: 
($F{provider_abn} != null ? "ABN: " + $F{provider_abn}.replaceAll("\\\\'", "'") + "\n" : "") + 
($F{provider_hedest_provider_code} != null ? "TEQSA Prov " + $F{provider_hedest_provider_code}.replaceAll("\\\\'", "'")+ "\n" : "") + 
($F{provider_cricos_code} != null ? "CRICOS Code: " + $F{provider_cricos_code}.replaceAll("\\\\'", "'") + "\n" : "")

Selecting postal address

Code Block

language	sql


SELECT
    p.student_number,
    p.personal_title,
    p.first_name,
    p.middle_name,
    p.last_name,
    p.suffix,
    TRIM(COALESCE(ecd.property_name,'') || ' ' || COALESCE(ecd.street_subaddress,'')) AS postal_address1,
    TRIM(COALESCE(ecd.street_number,'') || ' ' || COALESCE(ecd.street_name,'')) AS postal_address2,
    ecd.city,
    CASE 
        WHEN ecd.state_province_geo_id IS NULL THEN ecd.state
        ELSE sg.geo_name
    END AS state,
    ecd.postal_code AS postal_state_code,
    CASE 
        WHEN ecd.country_geo_id IS NULL THEN ''
        ELSE cg.geo_name
    END AS postal_country
FROM
    person p
JOIN edu_contact_details ecd ON ecd.party_id = p.party_id AND ecd.role_type_id = 'RESIDENTIAL'
LEFT JOIN geo sg ON sg.geo_type_id = 'STATE' AND sg.geo_id = ecd.state_province_geo_id
LEFT JOIN geo cg ON cg.geo_type_id = 'COUNTRY' AND cg.geo_id = ecd.country_geo_id
WHERE TRUE

Versions Compared

Old Version 25

New Version 26

Key

Selecting postal address

Page Comparison

Versions Compared

Old Version 25

New Version 26

Key

Selecting postal address