/*select count(*) from XXINV.XXINV_MTL_MTRL_TRX_RECEIPTS
select * from xxcb_debug_messages -- where message like '%XXINV.XXINV_MTL_MTRL_TRX_RECEIPTS%'
ORDER BY USER_ID DESC--ATTRIBUTE1 DESC */


SELECT  /*+ star_transformation*/  '' oper_unit,  
           wrs.country sold_from_country,
           wrf.country ship_from_country,
           rsh.organization_id ship_from_org_id,
           mps.organization_code ship_from_warehouse,
           '' ship_from_VAT,
           wrt.country ship_to_country,
           mtl.organization_id ship_to_org_id,
           mpt.organization_code ship_to_warehouse,
           '' ship_to_VAT,
           msi.segment1 Item_code,
           msi.description item_description,
           msi.attribute4 country_of_origin,
           substr(msi.attribute1, 1, 8) HSC_code_8_digits,
           substr(msi.attribute1, 1, 10) HSC_code_10_digits,
           substr(msi.attribute1, 1, 12) HSC_code_12_digits,
           wdd.shipped_quantity transfer_quantity,
           mtl.subinventory_code subinventory,
           msi.attribute6 net_weight,
           msi.weight_uom_code weight_UOM,
           nvl(msi.unit_volume, 0) item_volume,
           msi.volume_uom_code volume_uom,
           cst.item_cost cost_price,
           cs2.item_cost unit_price,
           '' total_price,
           rts.currency_code currency_code,
           trunc(mtl.transaction_date) physical_trx_date,
           to_char(mtl.transaction_date, 'MM-YYYY') physical_trx_month,
           trx.trx_number invoice_number,
           trx.trx_date invoice_date,
           oha.order_number order_number,
           wdd.source_line_number line_number,
           '' mode_of_transport,
           '' transaction_type,
           '' l_id,
           '' l_code,
           '' bill_to_site_use_id,
           '' ship_to_site_use_id,
           'Truck' freight_terms_code,
           '' intermed_ship_to,
           mtl.transaction_id mtl_transaction_id,
           '' interface_line_attr5,
           '' interface_line_attr7,
           '' operating_org_id,
           '' sales_org_id
      from inv.mtl_system_items_b        msi,
           inv.mtl_parameters            mps,
           inv.mtl_parameters            mpt,
           po.rcv_shipment_headers       rsh,
           po.rcv_shipment_lines         rsl,
           apps.hr_organization_units_v  wrf, --  Ship from country
          apps.hr_organization_units_v  wrt, -- Ship to country
          apps.hr_organization_units_v  wrs, -- Sold from country
          hr.hr_all_organization_units  aou,
           po.po_requisition_headers_all rha,
           ont.oe_order_headers_all      oha,
           ar.ra_customer_trx_all        trx,
           ar.ra_customer_trx_lines_all  trl,
           ar.ra_cust_trx_types_all      ctt,
           cst_item_costs                cst,
           cst_item_costs                cs2,
           ar.hz_locations               lcs,
           wsh.wsh_delivery_details      wdd,
           ont.oe_order_lines_all        ola,
           po.po_requisition_lines_all   rla,
           po.rcv_transactions           rts,
           inv.mtl_material_transactions mtl
     where mtl.transaction_type_id = 61 -- Int Req Intr Rcpt
       and mtl.transaction_date between '01-DEC-2009' and '31-DEC-2009'
          -- trunc(p_enddate) -- By Jagan 19-01-10
           --  and    mtl.transaction_date       between to_date(to_char(p_startdate,'dd-mon-yyyy')||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
                      --                                      and to_date(to_char(p_enddate,'dd-mon-yyyy')  ||' 23:59:59','dd-mon-yyyy hh24:mi:ss')  -- BY Jagan 19-01-10
      -- and mtl.organization_id = nvl(p_wrhs_org_id, mtl.organization_id)
       and mpt.organization_id = mtl.organization_id
       and rts.transaction_id = mtl.rcv_transaction_id
       and rla.requisition_line_id = rts.requisition_line_id
     --  and ola.source_document_line_id = rla.requisition_line_id
       and wdd.source_header_id = ola.header_id
       and oha.header_id=oha.header_id+0
       and wdd.source_line_id = ola.line_id
      -- and rha.requisition_header_id = rla.requisition_header_id
       and rha.org_id = 624--p_unit_org_id
       and mtl.inventory_item_id = msi.inventory_item_id
       and mtl.organization_id = msi.organization_id
       and mtl.organization_id = wrt.organization_id
       and rsh.organization_id = cst.organization_id
       and mtl.inventory_item_id = cst.inventory_item_id
       and mtl.organization_id = cs2.organization_id
       and mtl.inventory_item_id = cs2.inventory_item_id
       and rts.shipment_header_id = rsh.shipment_header_id
       and rts.shipment_line_id = rsl.shipment_line_id
       --and rha.requisition_header_id = oha.source_document_id
       and oha.header_id = ola.header_id
       and ola.sold_from_org_id = wrs.organization_id
      -- and to_char(ola.line_id) = trl.interface_line_attribute6(+)
      and trl.interface_line_attribute6=ola.line_id   --by Jagan 22-01-10
       and trl.sales_order_line(+) is not null
       and trl.sales_order=oha.order_number
       --and trl.customer_trx_id = trx.customer_trx_id(+)
       and trx.customer_trx_id=trl.customer_trx_id -- By jagan 22-01-10
       --and ctt.cust_trx_type_id(+) = trx.cust_trx_type_id
       and trx.cust_trx_type_id=ctt.cust_trx_type_id -- By jagan 22-01-10
       --and ctt.org_id(+) = trx.org_id 
       and trx.org_id=oha.org_id -- By jagan 22-01-10
       and rsh.organization_id = wrf.organization_id
       and rsh.organization_id = mps.organization_id
       and aou.organization_id = oha.org_id
       --and lcs.location_id(+) = aou.location_id   
       and wrf.country <> wrt.country
        and wdd.delivery_detail_id = --- 1.5
           (SELECT MAX(wdd1.delivery_detail_id) --- 1.5
              FROM wsh.wsh_delivery_details wdd1 --- 1.5
             WHERE wdd1.source_header_id = ola.header_id --- 1.5
               AND wdd1.source_line_id = ola.line_id) --- 1.5
               and oha.order_source_id = 10
       and  ctt.cust_trx_type_id=51341
       and cst.cost_type_id = 1
       and cs2.cost_type_id = 1
       order by item_code, Physical_trx_date, Order_number, Line_number;