To Get Installbase Details

t oeh.order_number "Order Number"




,oeh.ordered_date "Order Date"




--- ,hzp.party_number "Party"




--- ,hzp.party_name "Party Name"




,oel.line_number "Order Line"




,msi.segment1 "Item Sku"




,msi.description "Item Description"




,msi.inventory_item_status_code "Item Type"




--,msi.item_type "Item Type"




,oel.ordered_quantity "Ordered Qty"




,oel.fulfilled_quantity "Fulfilled Qty"




--,oel.cancelled_quantity "Cancelled Qty"




--,oeh.shipping_method_code "Header Shipping Method"




--,oel.shipping_method_code "Line Shipping Method"




,oel.fulfillment_date "Fulfillment Date"




,hao.name "Store"




,oel.unit_selling_price "Unit Selling Price"




--- ,jre.source_name "Sales Associate"




--,oel.subinventory "Sub-Inventory"




from oe_order_lines_all oel




,oe_order_headers_all oeh




,hr_all_organization_units hao




,mtl_system_items_b msi




---,hz_cust_site_uses_all hzsu




---,hz_cust_acct_sites_all hzas




---,hz_party_sites hzps




---,hz_parties hzp




---,jtf_rs_resource_extns jre




where 1=1




and oeh.header_id = oel.header_id




and oel.item_type_code = 'STANDARD'




and oel.line_category_code = 'ORDER'




and oel.flow_status_code = 'CLOSED'




and oeh.order_type_id=1008




and oel.ship_from_org_id = hao.organization_id




---and oeh.shipping_method_code = '000001_Customer_P_CARRY'




and msi.inventory_item_id = oel.inventory_item_id




and msi.organization_id = 22




and msi.COMMS_NL_TRACKABLE_FLAG = 'Y'




---and hzsu.site_use_id = oel.ship_to_org_id




---and hzas.cust_acct_site_id = hzsu.cust_acct_site_id




---and hzps.party_site_id = hzas.party_site_id




---and hzp.party_id = hzps.party_id




---and jre.resource_id(+) = to_number(oeh.attribute6)




--and jre.resource_id(+) = oel.salesrep_id




and not exists (select 1




from csi.csi_item_instances cii




where ---cii.accounting_class_code = 'CUST_PROD'




---and cii.instance_status_id not in (1,5)




cii.last_oe_order_line_id = oel.line_id)




/*and not exists (select 1




from oe_order_lines_all oel2




where oel.header_id = oel2.header_id




and oel.line_id = oel2.reference_line_id




and oel2.item_type_code = 'STANDARD'




and oel2.line_category_code = 'RETURN')*/




and oeh.ordered_date > '01-JAN-2008'




--and trunc(oeh.ordered_date) between '01-DEC-2006' and '31-DEC-2006'




--and oeh.order_number in ('832705','835744','876984')










--and msi.inventory_item_id = cii.inventory_item_id




--and msi.organization_id = 22




--and hp.party_id = cii.owner_party_id