Example Database Queries SQL


Tony Gallo wrote the following SQL. This is AR (Accounts Receivable) information retreived from shipped and invoiced records. This SQL was used as a test of the ETL (extract, transform, load) configured for OBIEE (Oracle Business Intelligence Enterprise Edition).

with 
  trx_lines (gl_period, gl_date, invoiced_qty, entered_amount, acctd_amount, gl_filter, gl_revenue_distribution, so_line_id, inv_description, inventory_item_id, warehouse_id, customer_trx_id) AS
    (select 
      gl.period_name
    , trunc(gldist.gl_date)
    , case
        when substr(nvl(trxl.description,'xxxxxxxxxxxxxx'),1,14) = 'MANUALOVERIDE.' then 0  -- all quantities are the same for both the item trx line record and the MANUALOVERIDE price adjustment trx line record
        when substr(trxl.description,1,4) = 'S2C ' then 0  -- all quantities are the same for both the item trx line record and the price adjustment trx line record
        when gldist.acctd_amount < 0 and nvl(trxl.quantity_invoiced,0) > 0 then trxl.quantity_invoiced * -1
        when gldist.acctd_amount < 0 and nvl(trxl.quantity_credited,0) > 0 then trxl.quantity_credited * -1  -- sometimes Step2 credit dept sets the quantity negative and sometimes they use a positive quantity and let Oracle make the unit cost negative in order for the amount to be negative on a credit memo
        when trxl.quantity_invoiced is not null then trxl.quantity_invoiced
        when trxl.quantity_credited is not null then trxl.quantity_credited  -- sometimes Step2 credit dept sets the quantity negative and sometimes they use a positive quantity and let Oracle make the unit cost negative in order for the amount to be negative on a credit memo
        else 0
      end as qty
    , gldist.amount  -- this amount will be in local currency (CAD or USD) and the acctd_amount is in USD always. Having both values in the same record allows me to ignore the timing of the currency rate change and let Oracle EBS handle it.
    , gldist.acctd_amount  -- the group function outside of this with/as/select will combine the original and adjustment on the price
    , substr(ca.segment4,1,2)
    , ca.segment1 || '.' || ca.segment2 || '.' || ca.segment3 || '.' || ca.segment4 || '.' || ca.segment5 || '.' || ca.segment6 || '.' || ca.segment7
    , case
        when trxl.interface_line_attribute6 is null then '0'
        when trxl.quantity_invoiced is null and trxl.quantity_credited is not null then '-'||trxl.interface_line_attribute6  -- use negative invoice line# for two reasons; 1) forces lines to be separate and not grouped in next step, and 2) negative value prevents accidental match to sales order line in COGS
        else trxl.interface_line_attribute6
      end as so_line_id
    , case -- when the invoice line is associated to an order, then trxl.interface_line_attribute6 contains the order line id. Isn't Oracle a wonderful thing ? Some future version of Oracle may make this an indexed field and a real foreign key relationship between AR and OM.
        when trxl.interface_line_attribute6 is null then trxl.description
        when substr(nvl(trxl.description,'xxxxxxxxxxxxxx'),1,14) = 'MANUALOVERIDE.' then null  -- description for parts will come from msib master parts list
        when substr(trxl.description,1,4) = 'S2C ' then trxl.description  -- Step2 pricing modifiers will start with S2C
        else null  -- description for parts will come from msib master parts list
      end as inv_description
    , nvl(trxl.inventory_item_id,0)
    , trxl.warehouse_id  -- leave the warehouse_id NULL at this stage of SQL, so revenue_lines alias left join to bom.cst_item_costs can nvl() and use 111 (org id = 185) as the standard item cost when warehouse is NULL
    , trxl.customer_trx_id
    from
      ar.ra_customer_trx_lines_all trxl
      inner join ar.ra_cust_trx_line_gl_dist_all gldist on trxl.org_id = 83 and gldist.customer_trx_line_id = trxl.customer_trx_line_id
      inner join gl.gl_code_combinations ca on gldist.code_combination_id = ca.code_combination_id 
      inner join gl.gl_periods gl on gl.period_set_name = 'S2H 4-4-5' and gl.adjustment_period_flag = 'N' and gldist.gl_date >= gl.start_date and gldist.gl_date < gl.end_date+1
    where gldist.gl_date > to_date('2013-08-01','yyyy-mm-dd')
      and nvl(trxl.interface_line_context,'xx') <> 'AR Conversion'
      and gl.period_name = nvl(:GL_PERIOD, gl.period_name)
    )
, grouped_man_override_lines (gl_period, gl_date, qty_invoiced, entered_amount, acctd_amount, gl_filter, gl_revenue_distribution, so_line_id, inv_description, inventory_item_id, warehouse_id, customer_trx_id) AS
    (select 
      gl_period
    , gl_date
    , sum(invoiced_qty)
    , sum(entered_amount)
    , sum(acctd_amount)
    , gl_filter
    , gl_revenue_distribution
    , so_line_id
    , inv_description
    , inventory_item_id
    , warehouse_id
    , customer_trx_id
    from trx_lines
    group by gl_period, gl_date, gl_filter, gl_revenue_distribution, so_line_id, inv_description, inventory_item_id, warehouse_id, customer_trx_id
    )
, revenue_lines (gl_period, gl_date, qty_invoiced, entered_amount, acctd_amount, revenue_type, gl_revenue_distribution, so_line_id, sku, inv_description, item_frozen_cost, inventory_item_id, warehouse_id, customer_trx_id) AS
    (select
      grouped_lines.gl_period
    , grouped_lines.gl_date
    , case
        when msib.segment1 in ('ZFFF','ZFRT','ZHND','ZTAX','D2UINS') then 0
        -- no need to check for 'S2C xxx', because they were set to 0 in trx_lines table alias above
        else grouped_lines.qty_invoiced
      end
    , grouped_lines.entered_amount
    , grouped_lines.acctd_amount
    , case
        when msib.segment1 in ('ZFFF','ZFRT','ZHND','D2UINS') 
          then 'SHIPHANDLING'
        when msib.segment1 = 'ZTAX'
          then 'ZTAX'
        when substr(grouped_lines.inv_description,1,7) in ('S2C DSF','S2C FRT') -- DSF = Drop Ship Fee
          then 'SHIPHANDLING'
        when substr(grouped_lines.inv_description,1,7) in ('S2C DEF','S2C COO') -- DEF = Defective, COO = CO-OP
          then 'ALLOWANCE'
        when substr(grouped_lines.inv_description,1,4) = 'S2C '
          then 'S2C_ADJ_OTHER'
        when grouped_lines.gl_filter not in ('30','31','32')  -- Forecasting system only uses ('30','31','32')
          then 'OTHER'
        else 'AAA-SALES'  -- prefixed with AAA, so it will be at the top of a sort order in a later query
      end
    , grouped_lines.gl_revenue_distribution
    , case
        when grouped_lines.so_line_id is null then 'xx'
        when substr(grouped_lines.so_line_id, 1, 1) = '-' then substr(grouped_lines.so_line_id, 2)  -- it was negated in the first step, so this puts it back. This will have no affect as of August 7, 2014, per COGS investigation below, but if return lines ever start showing up in the revenue COGS matching, then we can find something with this value.
        else grouped_lines.so_line_id
      end
    , msib.segment1
    , nvl(grouped_lines.inv_description, msib.description)
    , case
        when msib.segment1 in ('ZFFF','ZFRT','ZHND','ZTAX','D2UINS') then 0
        when substr(grouped_lines.inv_description,1,7) in ('S2C DSF','S2C FRT','S2C DEF','S2C COO') then 0
        else nvl(cic.item_cost,0)
      end
    , case
        when grouped_lines.inventory_item_id is null then 0
        when msib.segment1 is null then 0
        else grouped_lines.inventory_item_id
      end
    , nvl(grouped_lines.warehouse_id,0)
    , grouped_lines.customer_trx_id
    from 
      grouped_man_override_lines grouped_lines
        left join inv.mtl_system_items_b msib on nvl(grouped_lines.inventory_item_id,0) = msib.inventory_item_id and nvl(grouped_lines.warehouse_id,85) = msib.organization_id  -- 85 => '999' master parts list organization. grouped_lines.inventory_item_id is null for invoice only lines of ('ZFFF','ZFRT','ZHND','ZTAX','D2UINS'). We want the Zxxx to classify as SHIPHANDLING or ZTAX and not AAA-SALES, but keep the ShipFrom WH code (grouped_lines.warehouse_id) as null.
        left join bom.cst_item_costs cic on cic.cost_type_id = 1 and nvl(msib.inventory_item_id,0) = cic.inventory_item_id and nvl(grouped_lines.warehouse_id,185) = cic.organization_id  -- cost_type_id = 1 = 'Frozen' -- this is seeded in Oracle EBS, so don't bother spending execution time on the extra join
    )
, cogs (revenue_om_line_id, gl_cogs_distribution, cogs_amount) AS
    (select
      crcml.revenue_om_line_id
    , min(ca.segment1 || '.' || ca.segment2 || '.' || ca.segment3 || '.' || ca.segment4 || '.' || ca.segment5 || '.' || ca.segment6 || '.' || ca.segment7)  -- all aggregated rows have the same gl code_combination_id value, so min/max are valid aggregation functions to use
    , sum(nvl(mta.base_transaction_value,0))
    from
      revenue_lines trxl
      inner join bom.cst_revenue_cogs_match_lines crcml on crcml.pac_cost_type_id is null and crcml.operating_unit_id = 83 and crcml.revenue_om_line_id = trxl.so_line_id
      inner join bom.cst_cogs_events cce on crcml.cogs_om_line_id = cce.cogs_om_line_id and cce.event_type = 3   -- revenue_om_line_id field is for the kit on the sales order, and cogs_om_line_id is for the carton on the sales order
      inner join inv.mtl_material_transactions mmt on cce.mmt_transaction_id = mmt.transaction_id
      inner join inv.mtl_transaction_accounts mta on mmt.transaction_id = mta.transaction_id
      inner join gl.gl_code_combinations ca on mta.reference_account = ca.code_combination_id
    where mta.accounting_line_type = 35  -- 35 = Cost of Goods Sold, 36 = Deferred COGS
      and mta.reference_account in (4212,4213,103126)  -- 10.02.000.4010.00 and 10.03.000.4010.00 and 10.20.000.4010.30, this makes the SQL run faster by using the indexed field
      and trxl.revenue_type = 'AAA-SALES'
      and trxl.qty_invoiced > 0
    group by crcml.revenue_om_line_id   -- group this named query on only crcml.revenue_om_line_id, so that it returns only one row at most that will be joined below with left outer join. We don't want revenue lines to be duplicated for different cogs lines
    )
select
  case
    when rl.revenue_type = 'ZTAX' then 'ZTAX-' || nvl(shipto_l.state,'NULL')
    else rl.revenue_type
  end as REVENUE_TYPE
, hca.sales_channel_code
, hca.attribute5 as CUST_GROUP
, billto.demand_class_code as DEM_CLASS
, hca.account_number as ACCT_NUM
, hca.account_name as ACCT_NAME
, shipto_l.state
, shipto_l.country
, shipto_l.province
, ooh.cust_po_number as CUST_PO_NUM
, ooh.order_number as ORDER_NUM
, ool.line_number as SO_LINE_NUM
, trxtype.name as AR_TYPE
, ci.cons_inv_id as CONSOL_INV
, trxh.trx_number
, rl.sku
, rl.inv_description
, wh.organization_code as wh_code
, rl.item_frozen_cost
, rl.gl_period
, rl.gl_date
, rl.qty_invoiced
, rl.gl_revenue_distribution
, rl.entered_amount
, trxh.invoice_currency_code as CURRENCY
, rl.acctd_amount AS ACCTD_USD
, cogs.gl_cogs_distribution
, cogs.cogs_amount  -- always in USD
, rl.acctd_amount - cogs.cogs_amount as MARGIN_USD
, case
    when rl.acctd_amount = 0 then null
    when cogs.cogs_amount = 0 then null
    else round((rl.acctd_amount - cogs.cogs_amount) / rl.acctd_amount * 100, 1)
  end as MARGIN_PCT
, rl.so_line_id
from revenue_lines rl
inner join ar.ra_customer_trx_all trxh on trxh.org_id = 83 and rl.customer_trx_id = trxh.customer_trx_id
inner join ar.ra_cust_trx_types_all trxtype on trxh.cust_trx_type_id = trxtype.cust_trx_type_id
inner join ar.hz_cust_accounts hca on nvl(trxh.sold_to_customer_id,trxh.bill_to_customer_id) = hca.cust_account_id
left join ar.ar_cons_inv_trx_all ci on ci.trx_number = nvl(trxh.trx_number,'xxxxx') and ci.transaction_type = 'INVOICE' and ci.customer_trx_id = nvl(trxh.customer_trx_id,0)
left join ont.oe_order_lines_all ool on ool.org_id = 83 and nvl(rl.so_line_id,'xx') = to_char(ool.line_id)
left join ont.oe_order_headers_all ooh on nvl(ool.header_id,0) = ooh.header_id
left join cogs on nvl(rl.so_line_id,'xx') = cogs.revenue_om_line_id and rl.qty_invoiced > 0
left join ar.hz_cust_site_uses_all billto on nvl(trxh.bill_to_site_use_id,0) = billto.site_use_id left join ar.hz_cust_acct_sites_all billto_a on billto.cust_acct_site_id = billto_a.cust_acct_site_id left join ar.hz_party_sites billto_p on billto_p.party_site_id = billto_a.party_site_id left join ar.hz_locations billto_l on billto_p.location_id = billto_l.location_id
left join ar.hz_cust_site_uses_all shipto on nvl(trxh.ship_to_site_use_id,0) = shipto.site_use_id left join ar.hz_cust_acct_sites_all shipto_a on shipto.cust_acct_site_id = shipto_a.cust_acct_site_id left join ar.hz_party_sites shipto_p on shipto_p.party_site_id = shipto_a.party_site_id left join ar.hz_locations shipto_l on shipto_p.location_id = shipto_l.location_id
left join apps.org_organization_definitions wh on nvl(rl.warehouse_id,0) = wh.organization_id
order by rl.revenue_type, rl.gl_date, ooh.order_number, rl.sku, rl.acctd_amount desc

Tony Gallo wrote the following SQL. The output is a customer account list with many associated pieces of information used by multiple departments.

select
hca.account_number
, hca.account_name
, hca.sales_channel_code
, hca.attribute4 as Inside_Rep
, hca.attribute3 as Traffic_Person
, hca.attribute12 as Label_Format
, hca.attribute1 as Business_Segment
, hca.attribute5 as Customer_Group
, hca.attribute8 as Finance_Channel
, hp.jgzz_fiscal_code as Tax_Payer_ID
, hp.tax_reference
, hp.party_number
, hp.party_name
, hp.duns_number_c as "D-U-N-S Number"
, hca.attribute15 as Order_Tracking_Report
, hca.attribute16 as Print_Invoices
, hca.attribute11 as No_Backorders
, ood.organization_code as WH
, plh.name as price_list
, ft.meaning as freight_term
, fob.meaning as fob_point
, wcs.ship_method_meaning as ship_method
, arc.name as Collector_Person
, pmt.name as Payment_Terms
, hcp.credit_checking
, hcp.credit_hold
, hcpa.overall_credit_limit
, hcpa.currency_code
, hcar.related_accounts_list
, BillToSite.bill_to_flag
, BillToSite.ece_tp_location_code as ece_tp_location_code_BillTo
, BillToParty.party_site_number as BillTo_site_number
, BillToLoc.ADDRESS1 as ADDRESS1_BillTo
, BillToLoc.ADDRESS2 as ADDRESS2_BillTo
, BillToLoc.ADDRESS3 as ADDRESS3_BillTo
, BillToLoc.ADDRESS4 as ADDRESS4_BillTo
, BillToLoc.CITY as CITY_BillTo
, BillToLoc.STATE as STATE_BillTo
, BillToLoc.POSTAL_CODE as POSTAL_CODE_BillTo
, BillToLoc.COUNTRY as COUNTRY_BillTo
, BillToLoc.PROVINCE as PROVINCE_BillTo
, BillUse.Location as BillTo_purpose_location
, BillToTerr.name as Territory
, BillToSaleper.name as Salesperson
, BillToTyp.name as BillTo_Order_Type
, BillToPriceList.name as BillTo_price_list
, BillToFreightTerms.meaning as BillTo_freight_term
, BillToFob.meaning as BillTo_fob_point
, BillToWcs.ship_method_meaning as BillTo_ship_method
, BillUse.demand_class_code
, ShipToSite.ship_to_flag
, ShipToSite.ece_tp_location_code as ece_tp_location_code_ShipTo
, ShipToParty.party_site_number as ShipTo_site_number
, ShipToLoc.ADDRESS1 as ADDRESS1_ShipTo
, ShipToLoc.ADDRESS2 as ADDRESS2_ShipTo
, ShipToLoc.ADDRESS3 as ADDRESS3_ShipTo
, ShipToLoc.ADDRESS4 as ADDRESS4_ShipTo
, ShipToLoc.CITY as CITY_ShipTo
, ShipToLoc.STATE as STATE_ShipTo
, ShipToLoc.POSTAL_CODE as POSTAL_CODE_ShipTo
, ShipToLoc.COUNTRY as COUNTRY_ShipTo
, ShipToLoc.PROVINCE as PROVINCE_ShipTo
, ShipUse.Location as ShipTo_purpose_location
, SoldToParty.party_site_number as SoldTo_site_number
, SoldToLoc.ADDRESS1 as ADDRESS1_SoldTo
, SoldToLoc.ADDRESS2 as ADDRESS2_SoldTo
, SoldToLoc.ADDRESS3 as ADDRESS3_SoldTo
, SoldToLoc.ADDRESS4 as ADDRESS4_SoldTo
, SoldToLoc.CITY as CITY_SoldTo
, SoldToLoc.STATE as STATE_SoldTo
, SoldToLoc.POSTAL_CODE as POSTAL_CODE_SoldTo
, SoldToLoc.COUNTRY as COUNTRY_SoldTo
, SoldToLoc.PROVINCE as PROVINCE_SoldTo
, acctContacts.job_title_1
, acctContacts.contact_name_1
, acctContacts.contact_phone_1
, acctContacts.email_address_1
, acctContacts.job_title_2
, acctContacts.contact_name_2
, acctContacts.contact_phone_2
, acctContacts.email_address_2
from AR.hz_cust_accounts hca

-- following left joins for cust account data in Account page and Order Management tab
left outer join AR.hz_parties hp on hca.party_id = hp.party_id and hp.party_type = 'ORGANIZATION' and hp.status = 'A'
left outer join QP.qp_list_headers_tl plh on nvl(hca.price_list_id,0) = plh.list_header_id and plh.language = 'US'
left outer join APPS.oe_lookups ft on nvl(UPPER(hca.freight_term),'naxxxx') = UPPER(ft.lookup_code) and ft.lookup_type = 'FREIGHT_TERMS' and ft.enabled_flag = 'Y' and nvl(ft.start_date_active,sysdate) <= sysdate and nvl(ft.end_date_active,sysdate)+1 > sysdate
left outer join APPS.ar_lookups fob on nvl(UPPER(hca.fob_point),'naxxxx') = UPPER(fob.lookup_code) and fob.lookup_type = 'FOB' and fob.enabled_flag = 'Y' and nvl(fob.start_date_active,sysdate) <= sysdate and nvl(fob.end_date_active,sysdate)+1 > sysdate
left outer join WSH.wsh_carrier_services wcs on nvl(hca.ship_via,'naxxx') = wcs.ship_method_code and wcs.enabled_flag = 'Y'

-- following left joins and nested queries are for data in Account Profile and Profile Amounts tabs
left outer join AR.hz_customer_profiles hcp on hca.cust_account_id = hcp.cust_account_id
left outer join AR.ar_collectors arc on nvl(hcp.collector_id,0) = arc.collector_id
left outer join AR.ra_terms_tl pmt on nvl(hcp.standard_terms,0) = pmt.term_id and pmt.language = 'US'
left outer join
      (select cust_account_profile_id, overall_credit_limit, currency_code, cust_account_id, site_use_id
       from
           (select cust_account_profile_id, overall_credit_limit, currency_code, row_number() over(partition by cust_account_id order by currency_code) rn, cust_account_id, site_use_id
            from AR.hz_cust_profile_amts
           )
       where rn = 1  -- only use the first currency found
      ) hcpa on nvl(hcp.cust_account_profile_id,0) = hcpa.cust_account_profile_id and hca.cust_account_id = hcpa.cust_account_id
left outer join apps.org_organization_definitions ood on nvl(hca.warehouse_id,0) = ood.organization_id

-- following left joins for BillTo data
left outer join AR.hz_cust_acct_sites_all BillToSite on hca.cust_account_id = BillToSite.cust_account_id and BillToSite.org_id = 83 and BillToSite.status = 'A' and BillToSite.bill_to_flag = 'P'
left outer join AR.hz_party_sites BillToParty on nvl(BillToSite.party_site_id,0) = BillToParty.party_site_id
left outer join AR.hz_locations BillToLoc on nvl(BillToParty.location_id,0) = BillToLoc.location_id
left outer join AR.hz_cust_site_uses_all BillUse on nvl(BillToSite.cust_acct_site_id,0) = BillUse.cust_acct_site_id and BillUse.site_use_code = 'BILL_TO' and BillUse.org_id = 83 and BillUse.status = 'A' and BillUse.primary_flag = 'Y'
left outer join AR.ra_territories BillToTerr on nvl(BillUse.territory_id,0) = BillToTerr.territory_id
left outer join APPS.ra_salesreps_all BillToSaleper on nvl(BillUse.primary_salesrep_id,0) = BillToSaleper.salesrep_id
left outer join ONT.oe_transaction_types_tl BillToTyp on nvl(BillUse.order_type_id,0) = BillToTyp.transaction_type_id and BillToTyp.language = 'US'
left outer join QP.qp_list_headers_tl BillToPriceList on nvl(BillUse.price_list_id,0) = BillToPriceList.list_header_id and BillToPriceList.language = 'US'
left outer join APPS.oe_lookups BillToFreightTerms on nvl(UPPER(BillUse.freight_term),'naxxxx') = UPPER(BillToFreightTerms.lookup_code) and BillToFreightTerms.lookup_type = 'FREIGHT_TERMS' and BillToFreightTerms.enabled_flag = 'Y' and nvl(BillToFreightTerms.start_date_active,sysdate) <= sysdate and nvl(BillToFreightTerms.end_date_active,sysdate)+1 > sysdate
left outer join APPS.ar_lookups BillToFob on nvl(UPPER(BillUse.fob_point),'naxxxx') = UPPER(BillToFob.lookup_code) and BillToFob.lookup_type = 'FOB' and BillToFob.enabled_flag = 'Y' and nvl(BillToFob.start_date_active,sysdate) <= sysdate and nvl(BillToFob.end_date_active,sysdate)+1 > sysdate
left outer join WSH.wsh_carrier_services BillToWcs on nvl(BillUse.ship_via,'naxxx') = BillToWcs.ship_method_code and BillToWcs.enabled_flag = 'Y'

-- following left joins and nested queries are for cust account data in Relationships tab and with Bill To checkmark flag 
left outer join
      (select cust_account_id
       , listagg(related_account, '; ') within group (order by related_account) as related_accounts_list
       from 
           (select r.cust_account_id, a.account_name || ' [' || a.account_number || ']' as related_account
            from AR.hz_cust_acct_relate_all r
            inner join AR.hz_cust_accounts a on r.related_cust_account_id = a.cust_account_id
            where r.status = 'A' and r.org_id = 83 and r.bill_to_flag = 'Y' and substr(a.account_name,1,4) <> 'INF:'
           )
       group by cust_account_id
      ) hcar on nvl(hca.cust_account_id,0) = hcar.cust_account_id

-- following left joins for ShipTo data
left outer join AR.hz_cust_acct_sites_all ShipToSite on hca.cust_account_id = ShipToSite.cust_account_id and ShipToSite.org_id = 83 and ShipToSite.status = 'A' and ShipToSite.ship_to_flag = 'P'
left outer join AR.hz_party_sites ShipToParty on nvl(ShipToSite.party_site_id,0) = ShipToParty.party_site_id
left outer join AR.hz_locations ShipToLoc on nvl(ShipToParty.location_id,0) = ShipToLoc.location_id
left outer join AR.hz_cust_site_uses_all ShipUse on nvl(ShipToSite.cust_acct_site_id,0) = ShipUse.cust_acct_site_id and ShipUse.site_use_code = 'SHIP_TO' and ShipUse.org_id = 83 and ShipUse.status = 'A' and ShipUse.primary_flag = 'Y'

-- following left joins and nested queries are for SoldTo data
left outer join
      (select cust_account_id, party_site_id
       from
           (select SoldToSite.cust_account_id, SoldToSite.party_site_id, row_number() over(partition by SoldToSite.cust_account_id order by SoldToSite.cust_acct_site_id desc) rn
            from AR.hz_cust_acct_sites_all SoldToSite
            inner join AR.hz_cust_site_uses_all SoldToUse on SoldToSite.cust_acct_site_id = SoldToUse.cust_acct_site_id and SoldToUse.site_use_code = 'SOLD_TO' and SoldToUse.org_id = 83 and SoldToUse.status = 'A'
            where SoldToSite.org_id = 83 and SoldToSite.status = 'A'
           ) 
       where rn = 1
      ) OnlySoldToSite on hca.cust_account_id = nvl(OnlySoldToSite.cust_account_id,0)
left outer join AR.hz_party_sites SoldToParty on nvl(OnlySoldToSite.party_site_id,0) = SoldToParty.party_site_id
left outer join AR.hz_locations SoldToLoc on nvl(SoldToParty.location_id,0) = SoldToLoc.location_id

-- following left joins and nested queries are for cust account contacts in Communication tab
left outer join
      (select cust_account_id
        , max(case when rn = 1 then ac.job_title end) as job_title_1
        , max(case when rn = 1 then ac.contact_name end) as contact_name_1
        , max(case when rn = 1 then ac.contact_phone end) as contact_phone_1
        , max(case when rn = 1 then ac.email_address end) as email_address_1
        , max(case when rn = 2 then ac.job_title end) as job_title_2
        , max(case when rn = 2 then ac.contact_name end) as contact_name_2
        , max(case when rn = 2 then ac.contact_phone end) as contact_phone_2
        , max(case when rn = 2 then ac.email_address end) as email_address_2
        from   (select
            hca.cust_account_id
          , hoc.job_title
          , hpPerson.party_name as Contact_Name
          , case
              when hpContact.primary_phone_number is null then ''
              when hpContact.primary_phone_extension is null then hpContact.primary_phone_country_code || '(' || hpContact.primary_phone_area_code || ')' || hpContact.primary_phone_number
              else hpContact.primary_phone_country_code || '(' || hpContact.primary_phone_area_code || ')' || hpContact.primary_phone_number || ' x' || hpContact.primary_phone_extension
            end as Contact_Phone
          , hpContact.email_address
          , row_number() over (partition by hca.cust_account_id order by hrel.creation_date desc nulls last) rn
          from AR.hz_cust_accounts hca
          inner join AR.hz_relationships hrel on hca.party_id = hrel.subject_id and hrel.relationship_type = 'CONTACT' and hrel.status = 'A' and (sysdate between hrel.start_date and hrel.end_date) and hrel.subject_type = 'ORGANIZATION' and hrel.subject_table_name = 'HZ_PARTIES' and hrel.object_type = 'PERSON' and hrel.object_table_name = 'HZ_PARTIES'
          inner join AR.hz_parties hpPerson on hrel.object_id = hpPerson.party_id
          inner join AR.hz_parties hpContact on hrel.party_id = hpContact.party_id
          inner join AR.hz_org_contacts hoc on hrel.relationship_id = hoc.party_relationship_id
          inner join AR.hz_cust_account_roles hcar on hca.cust_account_id = hcar.cust_account_id and hrel.party_id = hcar.party_id and hcar.cust_acct_site_id is null and hcar.current_role_state = 'A' --and hpPerson.party_id = hcar.party_id -- multiple cust accounts share the same party org, but this additional table filters accounts where the person parties were not entered
          where hoc.party_site_id is null
          ) ac  -- contact info is normalized through multiple Oracle tables, so denormalize first and take last two records created
        where ac.rn <= 2  -- only the first two contacts found
        group by ac.cust_account_id
      ) acctContacts on hca.cust_account_id = nvl(acctContacts.cust_account_id,0)
where hca.status = 'A'
and substr(hca.account_name,1,4) <> 'INF:'
and length(trim(hca.account_number)) >= 7
order by hca.account_name, hca.account_number, arc.name, pmt.name