1 PACKAGE ARW_SEARCH_CUSTOMERS AUTHID CURRENT_USER AS
2 /*$Header: ARWCUSRS.pls 120.9.12020000.4 2013/04/29 12:41:00 shvimal ship $*/
3 --
4
5 TYPE cust_rec IS RECORD (
6 cus_seq_num BINARY_INTEGER := 0,
7 addr_cnt BINARY_INTEGER := 0
8 );
9
10 TYPE rev_cust_rec IS RECORD (
11 customer_id hz_cust_accounts.cust_account_id%TYPE,
12 addr_cnt BINARY_INTEGER := 0
13 );
14
15 TYPE addr_rec IS RECORD (
16 customer_id hz_cust_acct_sites.cust_account_id%TYPE,
17 addr_seq_num BINARY_INTEGER := 0,
18 total_score NUMBER(15) := 0
19 );
20
21 TYPE customer_rectype IS RECORD (
22 customer_id ari_customer_search_v.customer_id%TYPE,
23 DETAILS_LEVEL ari_customer_search_v.DETAILS_LEVEL%TYPE,
24 CUSTOMER_NUMBER ari_customer_search_v.CUSTOMER_NUMBER%TYPE,
25 CUSTOMER_NAME ari_customer_search_v.CUSTOMER_NAME%TYPE,
26 ADDRESS_ID hz_cust_acct_sites.cust_acct_site_id%TYPE,
27 CONCATENATED_ADDRESS ari_customer_search_v.CONCATENATED_ADDRESS%TYPE,
28 CONTACT_NAME ar_cust_search_gt.CONTACT_NAME%TYPE,
29 CONTACT_PHONE ar_cust_search_gt.CONTACT_PHONE%TYPE,
30 BILL_TO_SITE_USE_ID hz_cust_site_uses.SITE_USE_ID%type,
31 SITE_USES ar_cust_search_gt.SITE_USES%TYPE,
32 ORG_ID ari_customer_search_v.ORG_ID%TYPE,
33 SELECTED VARCHAR2(2),
34 LOCATION VARCHAR2(4000)
35 );
36
37 TYPE cust_tab IS TABLE OF cust_rec INDEX BY BINARY_INTEGER;
38
39 TYPE rev_cust_tab IS TABLE OF rev_cust_rec INDEX BY BINARY_INTEGER;
40
41 -- Bug# 14475430
42 -- To fix numeric overflow error
43 TYPE addr_tab IS TABLE OF addr_rec INDEX BY LONG;
44
45 TYPE customer_tabletype IS TABLE OF customer_rectype INDEX BY BINARY_INTEGER;
46
47 --
48
49 FUNCTION search_customers(
50 i_keyword IN varchar2 DEFAULT null,
51 i_show_all_sites IN varchar2 DEFAULT 'Y', -- Default added for Bug 16737932
52 i_first_row IN binary_integer DEFAULT 1,
53 i_last_row IN binary_integer DEFAULT null
54 ) RETURN customer_tabletype;
55 --
56 PROCEDURE ari_search ( i_keyword IN varchar2,
57 i_name_num IN VARCHAR2,
58 i_exclude_contact IN VARCHAR2,
59 i_show_all_sites IN VARCHAR2,
60 x_status OUT NOCOPY VARCHAR2,
61 x_msg_count OUT NOCOPY NUMBER,
62 x_msg_data OUT NOCOPY VARCHAR2 );
63 --
64
65 TYPE CustSite_rec_type IS RECORD (
66 CustomerId NUMBER,
67 SiteUseId NUMBER
68 );
69
70 TYPE CustSite_tbl IS TABLE of CustSite_rec_type INDEX BY BINARY_INTEGER;
71
72 -- Bug# 5858769
73 -- Provision to select sites
74 PROCEDURE initialize_account_sites ( p_custsite_rec_tbl in CustSite_tbl,
75 p_party_id in number,
76 p_session_id in number,
77 p_user_id in number ,
78 p_org_id in number ,
79 p_is_internal_user in varchar2
80 );
81
82
83 PROCEDURE init_acct_sites_anon_login ( p_customer_id in number,
84 p_site_use_id in number,
85 p_party_id in number,
86 p_session_id in number,
87 p_user_id in number ,
88 p_org_id in number ,
89 p_is_internal_user in varchar2
90 );
91 PROCEDURE update_account_sites ( p_customer_id in number,
92 p_session_id in number,
93 p_user_id in number ,
94 p_org_id in number ,
95 p_is_internal_user in varchar2
96 );
97
98 -- Bug 14486763 - To insert multiple bill to site use ids of a CUST_ACCT_SITE_ID
99 PROCEDURE insert_acct_site_uses ( p_session_id in number,
100 p_user_id in number ,
101 p_org_id in number ,
102 p_customer_id in number,
103 p_cust_acct_site_id in number default null,
104 p_site_use_id in number default null
105 );
106
107 END arw_search_customers;