This query is to analyse the type of items that a customer is buying in specific period through out his orders. In the below example we have taken only the drop shipment or direct import orders related data.
select
Customer_Name
,Customer_Number
,Item_Number
,Item_Description
,sum(Sum_Of_QUANTITY_ORDERED) sum_Ordered_Qty
,sum(NET_QTY) sum_Pricing_Qty
,Vendor_Number
,Vendor_Name
,Supplier_Site
,Per_Num
,Per_name
from
(
select
hp.party_name Customer_Name
,hca.account_number Customer_Number
,oola.ordered_item Item_Number
,msib.description Item_Description
,oola.ORDERED_QUANTITY Sum_Of_QUANTITY_ORDERED
,oola.PRICING_QUANTITY NET_QTY
,pv.segment1 Vendor_Number
,pv.vendor_name Vendor_Name
,pvsa.ADDRESS_LINE1 Supplier_Site
,(select gp.PERIOD_NUM from gl_periods gp
where oola.promise_date between gp.start_date and gp.end_date
) Per_Num
,(select gp.ENTERED_PERIOD_NAME from gl_periods gp
where oola.promise_date between gp.start_date and gp.end_date
) Per_Name
FROM apps.oe_order_headers_all ooha,
apps.oe_order_lines_all oola,
apps.oe_drop_ship_sources odss,
apps.po_requisition_headers_all prha,
apps.po_requisition_lines_all prla,
apps.po_headers_all pha,
apps.ap_terms apt,
apps.po_vendors pv,
apps.po_vendor_sites_all pvsa,
mtl_system_items_b msib,
hz_cust_accounts hca,
hz_cust_site_uses_all hcsua,
hz_cust_acct_sites_all hcasa,
hz_parties hp
–hz_party_sites hzp
WHERE 1=1
AND hca.account_number = :p_cust_account_num –30322 –31102
–AND ooha.order_type_id IN (1029, 1518, 1432, 1033, 2294)
–AND ooha.order_type_id IN
–(select lookup_code
— from fnd_lookup_values
— where lookup_type = ‘xxdoyen_Sample_lookup’
— and enabled_flag = ‘Y’)
AND ooha.header_id = odss.header_id
AND ooha.header_id = oola.header_id
AND oola.line_id = odss.line_id
AND odss.requisition_header_id = prha.requisition_header_id(+)
AND odss.requisition_line_id = prla.requisition_line_id(+)
AND odss.po_header_id = pha.po_header_id(+)
AND pha.terms_id = apt.term_id(+)
AND pha.vendor_id = pv.vendor_id(+)
AND pv.vendor_id = pvsa.vendor_id(+)
AND pha.vendor_site_id = pvsa.vendor_site_id(+)
–AND msib.segment1 = ‘41315D’ –‘25757D’
AND msib.inventory_item_id = oola.inventory_item_id
AND ooha.sold_to_org_id = hca.cust_account_id
AND ooha.invoice_to_org_id = hcsua.site_use_id(+)
AND hca.cust_account_id = hcasa.cust_account_id
AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
AND hca.party_id = hp.party_id
–AND ooha.flow_status_code NOT IN (‘CLOSED’, ‘CANCELLED’)
AND msib.organization_id = ooha.ship_from_org_id
–AND hp.party_id = hzp.party_id
)
group by
Customer_Name
,Customer_Number
,Item_Number
,Item_Description
,Vendor_Number
,Vendor_Name
,Supplier_Site
,Per_Num
,Per_name
–order by 3,7
;
