CUSTOMER DETAILS

SELECT HS.PARTY_NUMBER,

HS.PARTY_NAME, HS.PARTY_TYPE,

HCA.ACCOUNT_NUMBER,

HCA.STATUS,

HCAA.PARTY_SITE_ID,

HCSU.SITE_USE_CODE,

HCSU.LOCATION

FROM HZ_PARTIES HS,

HZ_CUST_ACCOUNTS HCA,

HZ_CUST_ACCT_SITES_ALL HCAA,

HZ_CUST_SITE_USES_ALL HCSU

WHERE HS.PARTY_ID = HCA.PARTY_ID

AND HCA.CUST_ACCOUNT_ID = HCAA.CUST_ACCOUNT_ID

AND HCAA.CUST_ACCT_SITE_ID = HCSU.CUST_ACCT_SITE_ID

CUSTOMER – ORDER DETAILS – ITEM DETAILS

SELECT DISTINCT

RC.CUSTOMER_NAME,

RC.CUSTOMER_NUMBER, RC.CUSTOMER_ID,

OEH.ORDER_NUMBER, OEH.ORG_ID,

OEH.ORDER_SOURCE_ID,

OEH.ORIG_SYS_DOCUMENT_REF,

OEH.ORDERED_DATE,

OEH.PRICE_LIST_ID,

OEH.SOLD_FROM_ORG_ID,

OEH.SOLD_TO_ORG_ID,

OEH.INVOICE_TO_ORG_ID,

OEH.FLOW_STATUS_CODE,

OEH.BOOKED_DATE,

OEL.ORDERED_QUANTITY,

OEL.SHIPPED_QUANTITY,

OEL.SHIPPED_QUANTITY,

OEL.INVENTORY_ITEM_ID,

OEL.PRICE_LIST_ID, OEL.UNIT_SELLING_PRICE,

HS.PARTY_NAME, HS.PARTY_TYPE, MSI.SEGMENT1

--OTT.TRANSACTION_TYPE_CODE,OTT.NAME TRNAME

FROM OE_ORDER_HEADERS_ALL OEH,

OE_ORDER_LINES_ALL OEL, RA_CUSTOMERS RC,

HZ_PARTIES HS, MTL_SYSTEM_ITEMS_B MSI,

OE_TRANSACTION_TYPES OTT

WHERE OEH.HEADER_ID = OEL.HEADER_ID

AND OEH.SOLD_TO_ORG_ID = RC.CUSTOMER_ID

AND RC.PARTY_ID = HS.PARTY_ID

AND MSI.INVENTORY_ITEM_ID = OEL.INVENTORY_ITEM_ID

--AND OEH.ORDER_TYPE_ID = OTT.TRANSACTION_TYPE_ID

AND OEH.ORDER_NUMBER = '10396'

SELECT * FROM OE_PRICE_LISTS

SELECT * FROM QP_LIST_HEADERS_ALL

SELECT * FROM QP_LIST_LINES

SELECT * FROM OE_ORDER_LINES_ALL where price_list_id ='1000'

SELECT * FROM OE_PRICE_LISTS WHERE NAME = 'Corporate'

CUSTOMER – ORDERS – ITEMS - TERMS - PRICELISTS

SELECT DISTINCT RC.CUSTOMER_NAME,

RC.CUSTOMER_NUMBER, RC.CUSTOMER_ID,

OEH.ORDER_NUMBER, OEH.ORG_ID,

OEH.ORDER_SOURCE_ID,

OEH.ORIG_SYS_DOCUMENT_REF,

OEH.ORDERED_DATE,

OEH.PRICE_LIST_ID,

OEH.SOLD_FROM_ORG_ID,

OEH.SOLD_TO_ORG_ID,

OEH.INVOICE_TO_ORG_ID, OEH.FLOW_STATUS_CODE,

OEH.BOOKED_DATE, OEL.ORDERED_QUANTITY,

OEL.SHIPPED_QUANTITY,

OEL.SHIPPED_QUANTITY,

OEL.INVENTORY_ITEM_ID,

OEL.PRICE_LIST_ID,

OEL.UNIT_SELLING_PRICE,

HS.PARTY_NAME, HS.PARTY_TYPE,

MSI.SEGMENT1, OEPL.NAME LISTNAME,

RT.NAME TERMNAME, RT.DESCRIPTION

FROM OE_ORDER_HEADERS_ALL OEH,

OE_ORDER_LINES_ALL OEL, RA_CUSTOMERS

RC, HZ_PARTIES HS, MTL_SYSTEM_ITEMS_B MSI,

OE_PRICE_LISTS OEPL, RA_TERMS RT

WHERE OEH.HEADER_ID = OEL.HEADER_ID

AND OEH.SOLD_TO_ORG_ID = RC.CUSTOMER_ID

AND RC.PARTY_ID = HS.PARTY_ID

AND MSI.INVENTORY_ITEM_ID = OEL.INVENTORY_ITEM_ID

AND OEH.ORDER_NUMBER = '10396'

AND OEL.PRICE_LIST_ID = OEPL.PRICE_LIST_ID

AND OEH.PAYMENT_TERM_ID =RT.TERM_ID