ASO Quote Data Template

SELECT 
            DECODE(ASO_QUOTES.RESOURCE_ID, null, sales.name, sales1.name) sales_rep_name,
  aso_quotes.quote_name,
  aso_quotes.contract_id,
  aso_quotes.quote_number||decode(aso_quotes.quote_version,NULL,'',' - ') ||   aso_quotes.quote_version      quote_name_version,
  to_char(aso_quotes.quote_expiration_date, nvl(FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'), 'DD-MON-RRRR'))     quote_expiration_date,
  TO_NUMBER(TO_CHAR(aso_quotes.quote_expiration_date,'J')) - TO_NUMBER(TO_CHAR(Sysdate,'J')) duration,
  I_HEADER_CONTACT_PARTY.PARTY_NAME sold_to_contact_name,
  I_CUST_PARTIES.party_name customer_name,
  I_PHONE.PHONE_AREA_CODE || DECODE(I_PHONE.PHONE_AREA_CODE , NULL, '',' ' ) ||  I_PHONE.PHONE_NUMBER  full_phone_number,
  I_INVOICE_REL_PARTY.PARTY_NAME invoice_to_contact_name,
  I_ACCTS_INV_PARTY.PARTY_NAME invoice_to_cust_party_name,
  I_INVOICE_LOCATIONS.ADDRESS1 invoice_to_address1,
  I_INVOICE_LOCATIONS.ADDRESS2 invoice_to_address2,
  I_INVOICE_LOCATIONS.ADDRESS3 invoice_to_address3,
  I_INVOICE_LOCATIONS.ADDRESS4 invoice_to_address4,
  decode(I_INVOICE_LOCATIONS.city, null,'',I_INVOICE_LOCATIONS.city || decode(I_INVOICE_LOCATIONS.county,null,'',',' || I_INVOICE_LOCATIONS.county)) invoice_to_city,
  I_INVOICE_LOCATIONS.state ||     decode(I_INVOICE_LOCATIONS.postal_code, null,'',' '|| 
  I_INVOICE_LOCATIONS.postal_code   ||       decode(I_INVOICE_LOCATIONS.province,null,'',',' ||I_INVOICE_LOCATIONS.province ))  invoice_to_county,
  I_INVOICE_LOCATIONS.country invoice_to_country,
  aso_quotes.currency_code Currency_Code,
  (SELECT hzcp.email_address 
   FROM  hz_contact_points hzcp
   WHERE  aso_quotes.party_id = hzcp.owner_table_id
   AND   hzcp.contact_point_type = 'EMAIL'
   AND    hzcp.owner_table_name = 'HZ_PARTIES'
   AND    hzcp.primary_flag='Y'
  ) Sales_Rep_Email,
  aso_quotes.quote_header_id quote_header_id,  
  oav.name||decode(oav.agreement_num,NULL,'',',')||oav.agreement_num||decode(oav.agreement_num,NULL,'',',')||oav.revision Agreement_name,
  oav.name||oav.agreement_num||oav.revision Agreement_check,
  to_char(oav.revision_date, nvl(FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'), 'DD-MON-RRRR')) revision_date,
  (select payment_term_name 
   FROM
   aso_payments_v
   WHERE 
    quote_line_id is null and
    quote_header_id = aso_quotes.quote_header_id) payment_terms,
    to_char(sysdate, nvl(FND_PROFILE.VALUE('ICX_DATE_FORMAT_MASK'), 'DD-MON-RRRR')) curr_date,
    aso_quotes.total_list_price total_list_price,
    aso_quotes.total_adjusted_amount total_adjusted_amount,
    (aso_quotes.total_list_price + aso_quotes.total_adjusted_amount) total_net_price, 
    aso_quotes.total_shipping_charge charges_total, 
    aso_quotes.total_tax taxes_total, 
    aso_quotes.total_quote_price total_quote_price,
  (select cust_po_number 
   FROM
   aso_payments
   WHERE 
    quote_line_id is null and
    quote_header_id = aso_quotes.quote_header_id ) cust_po_number,   
  (select fct.name
   from fnd_currencies_tl fct
   where aso_quotes.currency_code = fct.currency_code
   and fct.language = userenv('lang')) Currency_Name,
  (select fcurr.symbol
   from fnd_currencies fcurr
   where aso_quotes.currency_code = fcurr.currency_code) Currency_Symbol,
   (Select meaning 
    From   oe_lookups
    Where  lookup_type = 'FREIGHT_TERMS'
    And    lookup_code = (select aship.freight_terms_code
                              from aso_shipments aship
                              where aship.quote_header_id =  aso_quotes.quote_header_id
                              and aship.quote_line_id is null)) Freight_Terms, 
   (Select  fl.meaning
    From    fnd_lookup_values fl
    where   fl.lookup_type = 'SHIP_METHOD'
    And     fl.lookup_code = (select aship.ship_method_code
                              from aso_shipments aship
                              where aship.quote_header_id =  aso_quotes.quote_header_id
                              and aship.quote_line_id is null)
    And     fl.language =  userenv('LANG')) shipping_method ,
aso_quotes.org_id ,
aso_quotes.quote_number ,
aso_quotes.Quote_version VersionNumber,
I_ACCTS.ACCOUNT_NUMBER CustomerNumber,
oav.agreement_num Agreement_Number,
aso_quotes.minisite_id minisite_id,
(select imt.msite_name from ibe_msites_tl imt where imt.msite_id = aso_quotes.minisite_id 
and imt.language = userenv('LANG')) msite_name,
(select asl.meaning from oe_lookups asl 
where asl.lookup_type = 'SHIPMENT_PRIORITY'
 and  asl.lookup_code in  (select ash.shipment_priority_code from aso_shipments 
 ash where ash.quote_header_id = aso_quotes.quote_header_id and ash.quote_line_id is null)) shipment_priority_name,
(select OE_SYS_PARAMETERS.VALUE('INSTALLMENT_OPTIONS',aso_quotes.org_id) from dual) enable_pay_now,
 aso_quotes.attribute1 header_attribute1,
 aso_quotes.attribute2 header_attribute2,
 aso_quotes.attribute3 header_attribute3,
 aso_quotes.attribute4 header_attribute4, 
 aso_quotes.attribute5 header_attribute5,
 aso_quotes.attribute6 header_attribute6,
 aso_quotes.attribute7 header_attribute7,
 aso_quotes.attribute8 header_attribute8,
 aso_quotes.attribute9 header_attribute9,
 aso_quotes.attribute10 header_attribute10,
 aso_quotes.attribute11 header_attribute11,
 aso_quotes.attribute12 header_attribute12,
 aso_quotes.attribute13 header_attribute13,
 aso_quotes.attribute14 header_attribute14,
 aso_quotes.attribute15 header_attribute15,
 aso_quotes.attribute16 header_attribute16,
 aso_quotes.attribute17 header_attribute17,
 aso_quotes.attribute18 header_attribute18,
 aso_quotes.attribute19 header_attribute19,
 aso_quotes.attribute20 header_attribute20
from  
  hz_CONTACT_POINTS I_PHONE , 
  HZ_RELATIONSHIPS I_HEADER_CONTACT_REL, 
  HZ_PARTIES I_HEADER_CONTACT_PARTY, 
  ASO_QUOTE_HEADERS ASO_QUOTES, 
  HZ_RELATIONSHIPS I_INVOICE_REL, 
  HZ_PARTIES I_INVOICE_REL_PARTY,
  HZ_PARTY_sites I_INVOICE_PARTY_SITES, 
  HZ_LOCATIONS I_INVOICE_LOCATIONS, 
  HZ_PARTIES I_CUST_PARTIES, 
  HZ_CUST_ACCOUNTS I_ACCTS, 
  HZ_CUST_ACCOUNTS I_ACCTS_INV, 
  HZ_PARTIES I_ACCTS_INV_PARTY, 
  JTF_RS_SALESREPS_MO_V SALES, 
  JTF_RS_SALESREPS_MO_V  SALES1, 
  JTF_RS_RESOURCE_EXTNS_VL RES,
  FND_TERRITORIES_VL I_TERRITORIES,  
  oe_agreements_vl oav
  where
    ASO_QUOTES.PARTY_ID = I_HEADER_CONTACT_REL.PARTY_ID(+) 
    AND I_HEADER_CONTACT_REL.SUBJECT_ID = I_HEADER_CONTACT_PARTY.PARTY_ID(+) 
    AND I_HEADER_CONTACT_REL.SUBJECT_TYPE(+) = 'PERSON' 
    AND I_HEADER_CONTACT_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' 
    and i_header_contact_rel.object_id(+) = aso_quotes.cust_party_id
    AND ASO_QUOTES .INVOICE_TO_PARTY_SITE_ID = I_INVOICE_PARTY_SITES.PARTY_SITE_ID (+) 
    AND I_INVOICE_PARTY_SITES.LOCATION_ID = I_INVOICE_LOCATIONS.LOCATION_ID(+) 
    AND I_INVOICE_LOCATIONS.country = I_TERRITORIES.territory_code(+) 
    AND ASO_QUOTES.INVOICE_TO_PARTY_ID = I_INVOICE_REL.PARTY_ID(+) 
    AND I_INVOICE_REL.SUBJECT_ID = I_INVOICE_REL_PARTY.PARTY_ID(+) 
    AND I_INVOICE_REL.SUBJECT_TYPE(+) = 'PERSON' 
    AND I_INVOICE_REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' 
    AND i_invoice_rel.object_id (+) = aso_quotes.invoice_to_cust_party_id 
    AND ASO_QUOTES.ORG_ID = SALES.ORG_ID(+) 
    AND ASO_QUOTES.EMPLOYEE_PERSON_ID = SALES.PERSON_ID (+) 
    AND ASO_QUOTES.ORG_ID = SALES1.ORG_ID  (+)
    AND ASO_QUOTES.RESOURCE_ID = SALES1.RESOURCE_ID  (+)
    AND sales1.resource_id = res.resource_id
    AND ASO_QUOTES.CUST_ACCOUNT_ID =I_ACCTS.CUST_ACCOUNT_ID(+) 
    AND ASO_QUOTES.CUST_PARTY_ID = I_CUST_PARTIES.PARTY_ID 
    AND ASO_QUOTES.INVOICE_TO_CUST_ACCOUNT_ID = I_ACCTS_INV.CUST_ACCOUNT_ID(+) 
    AND ASO_QUOTES.INVOICE_TO_CUST_PARTY_ID = I_ACCTS_INV_PARTY.PARTY_ID(+) 
    AND aso_quotes.phone_id = i_phone.contact_point_Id(+)
    AND oav.agreement_id(+) = aso_quotes.contract_id 
    AND aso_quotes.quote_header_id = :DocumentId