Report and SQL Queries TCA

-----Report queries----

SELECT  rs.salesrep_id repid,hca.account_number customer_number,
hp.party_name customer_name,rctl.sales_order, rctl.sales_order_date, rct.trx_number,
        rct.trx_date,sum(nvl(rctl.unit_standard_price,0)) Std ,
sum(nvl(rctl.unit_selling_price,0)) sell_p,
sum(nvl(rctl.quantity_invoiced,quantity_credited)) qty,
sum( nvl(rctl.extended_amount,0))  Entered_amt
FROM   hz_parties hp, hz_cust_accounts_all hca, hz_cust_acct_sites_all hcas
        ,hz_cust_site_uses_all hcsu,hz_customer_profiles hcp,hz_cust_profile_classes hpc,
         ra_salesreps_all rs, ra_customer_trx_all rct,ra_customer_trx_lines_all rctl
WHERE   hp.party_id = hca.party_id
AND     hca.cust_account_id = hcas.cust_account_id
AND     hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND     hcas.cust_account_id = hcp.cust_account_id
AND     hcp.profile_class_id = hpc.profile_class_id
AND     rs.salesrep_id = hcsu.primary_salesrep_id
AND     rct.bill_to_customer_id = hca.cust_account_id
AND     rct.customer_trx_id = rctl.customer_trx_id
AND     rct.bill_to_site_use_id =hcsu.site_use_id
AND     hcp.party_id = hp.party_id
AND     hcp.site_use_id IS NULL
AND     rctl.line_type='LINE'
AND     rct.cust_trx_type_id not in (1046,1047,1080,1082)
AND     hca.account_number not like 'D%'
AND     hca.account_number not like '%CO'
AND    (rctl.description like '7%'  OR rctl.description like '7%' )
AND     nvl(rct.term_id,100) != 1005
AND     (rct.trx_date between (:OP_START_DATE)  and (:P_END_DATE) )
AND     upper(hpc.name)  NOT LIKE 'CONSIGNMENT%'
GROUP BY rs.salesrep_id,hca.account_number,hp.party_name,rctl.sales_order, rctl.sales_order_date, rct.trx_number,
        rct.trx_date
ORDER BY hp.party_name;


-----------Bill to addresses--------

SELECT
        hc.account_number Customer#
        ,hp_bill.party_name "CUSTOMER NAME"
        ,rep.name SALESREP
        , hcs_bill.location "BILL TO LOCATION"
        , hl_bill.address1     "BILL TO ADDRESS1"
        , hl_bill.address2     "BILL TO ADDRESS2"
        , hl_bill.city         "BILL TO CITY"
        , hl_bill.state          "BILL TO STATE"
        , hl_bill.postal_code    "BILL TO ZIP"
        , hl_bill.country        "BILL TO COUNTRY"
        , hcs_ship.location "SHIP TO LOCATION"
        , hl_ship.address1     "SHIP TO ADDRESS1"
        , hl_ship.address2      "SHIP TO ADDRESS2"
        , hl_ship.city         "SHIP TO CITY"
        , hl_ship.state        "SHIP TO STATE"
        , hl_ship.postal_code   "SHIP TO ZIP"
        , hl_ship.country       "SHIP TO COUNTRY"

FROM 
          hz_cust_accounts hc
        , hz_cust_site_uses_all hcs_ship
        , hz_cust_acct_sites_all hca_ship
        , hz_party_sites hps_ship
        , hz_parties hp_ship
        , hz_locations hl_ship
        , hz_cust_site_uses_all hcs_bill
        , hz_cust_acct_sites_all hca_bill
        , hz_party_sites hps_bill
        , hz_parties hp_bill
        , hz_locations hl_bill
        ,ra_salesreps_all rep
WHERE   hc.party_id=hp_ship.party_id
AND     hc.party_id=hp_bill.party_id
AND     hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
AND     hca_ship.party_site_id = hps_ship.party_site_id
AND     hps_ship.party_id = hp_ship.party_id
AND     hps_ship.location_id = hl_ship.location_id
AND     hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
AND     hca_bill.party_site_id = hps_bill.party_site_id
AND     hps_bill.party_id = hp_bill.party_id
AND     hps_bill.location_id = hl_bill.location_id
AND     hcs_bill.primary_salesrep_id= rep.salesrep_id
AND     hcs_ship.primary_salesrep_id= rep.salesrep_id
AND     hp_ship.party_name like 'PROMOTIONS' ;



TCA Architecture