SELECT OH.ORG_ID "Org Id",
       CUST.CUSTOMER_NUMBER "Customer Number",
       CUST.CUSTOMER_NAME "Customer Name",
       CUST.STATUS "Customer Status",
       CUST.CUSTOMER_CLASS_CODE "Customer Class",
       CUST.CUSTOMER_CATEGORY_CODE "Customer Category",
        con.territory_code || ' - ' || con.territory_short_name "Country",
       REPS.NAME "Salesman",
       TRUNC(OH.ORDER_NUMBER) "Order Number",
       TRUNC(OH.CREATION_DATE) "Creation Date",
       OH.ORDERED_DATE "Ordered Date",
       OH.CUST_PO_NUMBER "Customer PO Number",
       OH.ATTRIBUTE1 REMARKS,
       OH.TRANSACTIONAL_CURR_CODE "Order Currency",
       HST.MEANING      "Order Status",
      (OL.LINE_NUMBER||'.'||OL.SHIPMENT_NUMBER)    "Order Line Number",
       OL.ORDER_QUANTITY_UOM       UOM, 
       --MMT.NEW_COST                          "Trans Cost",
decode(LINE_CATEGORY_CODE,'RETURN',OL.ORDERED_QUANTITY * -1,OL.ORDERED_QUANTITY ) Quantity,
decode(LINE_CATEGORY_CODE,'RETURN',OL.UNIT_SELLING_PRICE * -1,OL.UNIT_SELLING_PRICE )  Price,
decode(LINE_CATEGORY_CODE,'RETURN',(OL.UNIT_SELLING_PRICE * OL.ORDERED_QUANTITY * -1),(OL.UNIT_SELLING_PRICE * OL.ORDERED_QUANTITY )) "Value",
                   NVL(CST.ITEM_COST,0) "Item Cost",
       T.NAME "Order Type",
       T1.NAME "Order Line Type",
       ST.MEANING "Line Status"   ,
       MTL.SEGMENT1 "Item Code",
       MTL.DESCRIPTION||' '||OL.ATTRIBUTE1 "Item Name",
       MC.SEGMENT1                     "Item Type",
       MC.DESCRIPTION                  "Item Category",
       DECODE(MTL.PURCHASING_ITEM_FLAG, 'Y', 'Yes', 'N', 'No') "Purchased",
                   OL.ACTUAL_SHIPMENT_DATE      "Actual Shipment Date"     
  from 
 
  --     APPS.RA_SITE_USES            SIT,
  --    APPS.RA_ADDRESSES            A,
--      APPS.PA_COUNTRY_V            CON,
     APPS.Ar_CUSTOMERS            cust,
        apps.hz_cust_acct_sites_all hcas,
        apps.hz_party_sites hps,
        apps.hz_locations hl,
        apps.fnd_territories_vl con,
       apps.HZ_CUST_SITE_USES_all     SIT,
       APPS.MTL_SYSTEM_ITEMS_B      MTL,
       APPS.OE_ORDER_HEADERS        OH,
       APPS.OE_ORDER_LINES          OL,
       APPS.MTL_ITEM_CATEGORIES     MIC,
       APPS.JTF_RS_salesreps        reps,
       APPS.MTL_CATEGORIES          MC,
       APPS.OE_TRANSACTION_TYPES_TL T,
       APPS.OE_TRANSACTION_TYPES_TL T1,
       APPS.FND_LOOKUP_VALUES       HST,
      APPS.FND_LOOKUP_VALUES       ST,
       APPS.CSTBV_AVERAGE_ITEM_COSTS CST
 where NVL(OH.CANCELLED_FLAG,'N')  <> 'Y'      
   and MIC.CATEGORY_SET_ID = 1      
   and HST.LOOKUP_TYPE     = 'FLOW_STATUS'      
   and ST.LOOKUP_TYPE      = 'LINE_FLOW_STATUS'         
   and ST.ENABLED_FLAG     = 'Y'      
--   and A.COUNTRY           = CON.COUNTRY_CODE (+)
--   and A.ADDRESS_ID       (+) = SIT.ADDRESS_ID      
   and MTL.INVENTORY_ITEM_ID = CST.INVENTORY_ITEM_ID (+)
   and CST.ORGANIZATION_ID(+) =TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10))  
AND sit.cust_acct_site_id = hcas.cust_acct_site_id
 AND hcas.party_site_id = hps.party_site_id
 AND hps.location_id = hl.location_id
 AND hl.country = con.territory_code
   and CST.COST_GROUP_ID  (+) = decode (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)) ,41,1000,114,1028,94,1008,154,1148,93,1108,134,1048,1000)
   and mtl.ORGANIZATION_ID = TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10))  
   and OH.INVOICE_TO_ORG_ID = SIT.SITE_USE_ID  (+)
   and CUST.CUSTOMER_ID   (+) = OH.SOLD_TO_ORG_ID      
   and OL.ORG_ID           = MTL.ORGANIZATION_ID         
   and MTL.ORGANIZATION_ID = MIC.ORGANIZATION_ID      
   and OL.HEADER_ID        = OH.HEADER_ID
   and HST.LOOKUP_CODE     = OH.FLOW_STATUS_CODE         
   and ST.LOOKUP_CODE      = OL.FLOW_STATUS_CODE         
   and T1.TRANSACTION_TYPE_ID = OL.LINE_TYPE_ID        
   and T.TRANSACTION_TYPE_ID = OH.ORDER_TYPE_ID      
   and OL.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID         
   and MTL.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID      
   and MIC.CATEGORY_ID     = MC.CATEGORY_ID           
   and REPS.SALESREP_ID(+)  = OH.SALESREP_ID