ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 25383B52BF4D8564E0635863B10AF11DY SELECT operating_unit, organization_code, delivery_number, customer, country, salesrep_name, product_category, order_number, order_type, inco_term, item, SUM(nvl(quantity_invoiced, 0)) invoiced_qty, entered_currency, ledger_currency, invoice_number, invoice_date, SUM(nvl(invoiced_amount, 0)) func_currency_invoiced_amount, usd_conversion_rate, round(SUM(nvl(invoiced_amount, 0) * usd_conversion_rate), 2) sales_value_in_usd, item_description FROM ( SELECT hu.name operating_unit, ood.organization_code, hp.party_name customer, hca.account_number customer_number, gl.currency_code ledger_currency, rt.trx_number invoice_number, rt.trx_date invoice_date, rt.invoice_currency_code entered_currency, ol.freight_terms_code inco_term, ( SELECT glr.conversion_rate FROM gl_daily_rates_v glr WHERE glr.conversion_type = 'Corporate' AND trunc(glr.conversion_date) = trunc(sysdate) AND glr.to_currency = 'USD' AND glr.from_currency = gl.currency_code ) usd_conversion_rate, ( SELECT ft.territory_short_name FROM hz_cust_site_uses_all hs, hz_cust_acct_sites_all hca, hz_party_sites hps, hz_locations hl, fnd_territories_vl ft WHERE hs.site_use_id = rt.ship_to_site_use_id AND hs.cust_acct_site_id = hca.cust_acct_site_id AND hca.party_site_id = hps.party_site_id AND hps.location_id = hl.location_id AND hl.country = ft.territory_code ) country, ( SELECT name FROM jtf_rs_salesreps WHERE salesrep_id = oh.salesrep_id AND org_id = oh.org_id ) salesrep_name,-- oh.salesrep_id, ( SELECT mc.segment1 FROM mtl_item_categories mic, mtl_categories mc WHERE mic.inventory_item_id = rctl.inventory_item_id AND mic.organization_id = rctl.warehouse_id AND mic.category_id = mc.category_id AND mic.category_set_id = 1100000022 ) product_category, rt.org_id, rt.trx_number, rt.cust_trx_type_id, oh.order_number, otl.name order_type, ( SELECT segment1 FROM mtl_system_items_b WHERE inventory_item_id = rctl.inventory_item_id AND organization_id = rctl.warehouse_id ) item, ( SELECT description FROM mtl_system_items_b WHERE inventory_item_id = rctl.inventory_item_id AND organization_id = rctl.warehouse_id ) item_description, rctl.quantity_ordered, nvl(rctl.quantity_invoiced, rctl.quantity_credited) quantity_invoiced, rt.invoice_currency_code, rctl.extended_amount, round(rctl.extended_amount * nvl(rt.exchange_rate, 1), 2) invoiced_amount, decode(rctl.interface_line_context, 'INTERCOMPANY',( SELECT shipment_number FROM mtl_material_transactions WHERE transaction_id = rctl.interface_line_attribute7 ), rctl.interface_line_attribute3) delivery_number FROM ra_customer_trx_all rt, gl_ledgers gl, hr_operating_units hu, ra_customer_trx_lines_all rctl, ra_cust_trx_line_gl_dist_all rgd, oe_order_headers_all oh, oe_order_lines_all ol, oe_transaction_types_tl otl, hz_cust_accounts hca, hz_parties hp, org_organization_definitions ood WHERE 1=1 AND rt.customer_trx_id = rctl.customer_trx_id --AND rt.org_id = 11023 AND rt.org_id = hu.organization_id AND hu.set_of_books_id = gl.ledger_id AND rt.customer_trx_id = rgd.customer_trx_id AND rgd.account_class = 'REC' AND rctl.line_type = 'LINE' AND rgd.latest_rec_flag = 'Y' AND rgd.gl_date >= nvl(:p_fdate, nvl(trunc(:p_tdate ,'MM'), trunc(sysdate,'MM')) ) AND rgd.gl_date <= nvl(:p_tdate,trunc(sysdate)) AND oh.header_id = ol.header_id (+) AND oh.order_number = rctl.sales_order --AND ol.line_id (+)= rctl.interface_line_attribute6 --Shipping Charges Not Expected AND ol.line_id = rctl.interface_line_attribute6 AND oh.order_type_id = otl.transaction_type_id AND otl.language = userenv('LANG') AND rctl.interface_line_context IN ( 'ORDER ENTRY', 'INTERCOMPANY' ) AND hca.cust_account_id = rt.bill_to_customer_id AND hca.party_id = hp.party_id AND rctl.warehouse_id = ood.organization_id ) WHERE 2=2 GROUP BY operating_unit, organization_code, delivery_number, customer, country, salesrep_name, product_category, order_number, order_type, inco_term, item, entered_currency, ledger_currency, invoice_number, invoice_date, usd_conversion_rate, item_description US Terex Daily Order Items Revenue Report Report:Terex Daily Order Items Revenue Report PVCS Code : Terex_Daily_Order_Items_Revenue_Report.xml Default from Date:Starting of Current Month Default Date to :Current Date Report Set move Files to /TMSCIFS/Blitz/MP-Parts/Daily Order Items Revenue 1=1 2=2 :p_fdate :p_tdate 1 10 1=1 ood.organization_code = :PinvOrgCode LOV custom Y select ORGANIZATION_CODE value, ORGANIZATION_NAME description from org_organization_definitions order by 1 Y US Inventory Org 2 15 :p_tdate Date select trunc(sysdate) from dual US To Date 3 20 :p_fdate Date select trunc(sysdate ,'MM') from dual US From Date 4 30 2=2 Product_Category = :pcategory LOV custom Y select distinct a.SEGMENT1 value , a.SEGMENT1 description from mtl_categories a,mtl_category_sets b where b.CATEGORY_SET_ID =1100000022 and a.STRUCTURE_ID =b.STRUCTURE_ID and a.ENABLED_FLAG='Y' and nvl(END_DATE_ACTIVE,sysdate) >= sysdate US Product Category