Order Management Complete Script and Customer Information in Oracle apps

Customer -:HZ_PARTIES, column name (Party _name)

Customer Number-: HZ_CUST_ACCOUNTS, Column Name (Account_number)

Join Condition:  HZ_CUST_ACCOUNTS..Party_Id = HZ_PARTIES.Party_Id

Reference Table:

Column in RA_CUSTOMERS	Corresponding Table	Column
customer_name	hz_parties	substrb(party_name,1,50)
customer_id	hz_cust_accounts	cust_account_id
customer_number	hz_cust_accounts	account_number
status	hz_cust_accounts	status

Customer PO-:OE_ORDER_HEADERS_ALL—Column Name (CUST_PO_NUMBER)

Customer Contact: 

Select SUBSTRB(HP_CUST.PERSON_FIRST_NAME,1,40)||' '||SUBSTRB(HP_CUST.PERSON_LAST_NAME,1,50) Customer_Contact ,substrb(HP.party_name,1,50) Customer_Name ,hca.account_number Customer_Number ,ooh.cust_po_number Customer PO From HZ_PARTIES HP_CUST, HZ_RELATIONSHIPS HZR, HZ_CUST_ACCOUNT_ROLES HCAR, OE_ORDER_HEADERS_ALL  OOH, HZ_CONTACT_POINTS HCP, HZ_CUST_ACCOUNTS HCA, HZ_PARTIES HP, WSH_NEW_DELIVERIES WND, WSH_DELIVERY_ASSIGNMENTS WDS , WSH_DELIVERY_DETAILS WDD Where 1=1 AND HP_CUST.PARTY_ID(+)=HZR.SUBJECT_ID AND HCAR.PARTY_ID=HZR.PARTY_ID(+) AND  HZR.SUBJECT_TYPE(+)='PERSON' AND HCP.OWNER_TABLE_ID(+)=HZR.PARTY_ID AND OOH.sold_to_contact_id=HCAR.CUST_ACCOUNT_ROLE_ID(+) AND HCAR.ROLE_TYPE(+) = 'CONTACT' AND HCP.CONTACT_POINT_TYPE(+)='PHONE' AND HCP.OWNER_TABLE_NAME(+)='HZ_PARTIES' AND OOH.SOLD_TO_ORG_ID=HCA.CUST_ACCOUNT_ID AND HCA.PARTY_ID=HP.PARTY_ID AND OOH.ORDER_NUMBER  = '66521' /*and wnd.delivery_id=:c_q2_delivery_id and WDS.DELIVERY_ID=WND.DELIVERY_ID and WDD.DELIVERY_DETAIL_ID=WDS.DELIVERY_DETAIL_ID and WDD.SOURCE_HEADER_ID=OOH.HEADER_ID*/-----------formula column reference and rownum = 1

Order type-:

select ott.name from OE_TRANSACTION_TYPES_TL ott,oe_order_headers_all ooh where ott.transaction_type_id=ooh.order_type_id                   (OR) select sot.name from SO_ORDER_TYPES_ALL sot,oe_order_headers_all ooh where sot.order_type_id=ooh.order_type_id

Sales Person (or) Ordered By-: ra_salesreps_all –Column Name (Name)

Join Condition: OE_ORDER_HEADERS_ALL .salesrep_id = ra_salesreps_all.salesrep_id (+)

And  OE_ORDER_HEADERS_ALL .org_id= ra_salesreps_all.org_id

Created by (Means USER(MVARADARAJU)):

function CF_Created_byFormula return Char is l_Created_By varchar2(20); begin   select USER_NAME   into l_Created_By   from fnd_user fu, oe_order_headers_all ooh where ooh.created_by=fu.user_id and ooh.header_id=:header_id and rownum =1; return(l_Created_By); end;

Ordered date:

function CF_Order_DateFormula return Char is l_order_date varchar2(32); begin    Select TO_CHAR(ooh.ORDERED_DATE, 'DD/MM/YYYY HH24:MI:SS') ORDERED_DATE  into l_order_date from oe_order_headers_all ooh where  1=1 and ooh.header_id=:header_id; return(l_order_date); end;