4 /*=======================================================================+
1 PACKAGE BODY AR_CMGT_PARTY_SEARCH AS
2 /*$Header: ARDQMSRB.pls 120.5.12010000.2 2010/03/02 15:27:17 mraymond ship $ */
3
5 | Package Global Constants
6 +=======================================================================*/
7
8 /*========================================================================
9 | Prototype Declarations Procedures
10 *=======================================================================*/
11
12
13 /*========================================================================
14 | Prototype Declarations Functions
15 *=======================================================================*/
16
17 /*========================================================================
18 | PUBLIC PROCEDURE DQM_SEARCH
19 |
20 | DESCRIPTION
21 | This procedure provides the cover routine for the call to the
22 | DQM search engine.
23 | This is used from the credit management search pages.
24 |
25 | PARAMETERS
26 | p_keyword IN The keyword on which search is to
27 | be performed.
28 | p_search_context_id OUT NOCOPY The unique id returned by DQM
29 |
30 | KNOWN ISSUES
31 | Enter business functionality which was de-scoped as part of the
32 | implementation. Ideally this should never be used.
33 |
34 | NOTES
35 | Any interesting aspect of the code in the package body which needs
36 | to be stated.
37 |
38 | MODIFICATION HISTORY
39 | Date Author Description of Changes
40 | 06-AUG-2002 APANDIT Created
41 | 17-MAR-2006 KJOSHI Made changes for modified DQM search
42 *=======================================================================*/
43 Procedure DQM_SEARCH (p_keyword IN varchar2,
44 p_dqm_param IN VARCHAR2,
45 p_search_context_id OUT NOCOPY NUMBER,
46 p_return_status OUT NOCOPY VARCHAR2,
47 p_msg_count OUT NOCOPY NUMBER,
48 p_msg_data OUT NOCOPY VARCHAR2)
49 is
50 -- Pass Party search criteria in this variable
51 party_cond HZ_PARTY_SEARCH.PARTY_SEARCH_REC_TYPE;
52 l_count NUMBER;
53
54 -- Pass Party Site search criteria in this variable
55 party_site_cond HZ_PARTY_SEARCH.PARTY_SITE_LIST;
56
57 -- Pass Contact search criteria in this variable
58 contact_cond HZ_PARTY_SEARCH.CONTACT_LIST;
59
60 -- Pass Contact Point search criteria in this variable
64 -- this should be set to the ID of the match rule you
61 contact_point_cond HZ_PARTY_SEARCH.CONTACT_POINT_LIST;
62
63 -- The Match Rule to use for the search
65 -- created for your application
66 l_rule_id NUMBER;
67
68 -- The Search Context ID returned by the API.
69 -- This is used to query the results table for
70 -- the matched records.
71 l_search_context_id NUMBER;
72
73 -- Other OUT NOCOPY parameters returned by the API.
74 l_return_status VARCHAR2(1);
75 l_msg_count NUMBER;
76 l_msg_data VARCHAR2(2000);
77
78 -- API also returns the number of matches.
79 l_num_matches NUMBER;
80
81 --The partySearchResults cursor
82 CURSOR partySearchResults (p_context_id IN NUMBER) is
83 SELECT hzp.party_name, hzp.party_number,ca.account_number,
84 ARH_ADDR_PKG.FORMAT_ADDRESS(LOC.ADDRESS_STYLE,LOC.ADDRESS1,
85 LOC.ADDRESS2,LOC.ADDRESS3,LOC.ADDRESS4,LOC.CITY,LOC.COUNTY,
86 LOC.STATE,LOC.PROVINCE,LOC.POSTAL_CODE,
87 TERR.TERRITORY_SHORT_NAME ) CONCATENATED_ADDRESS
88 , score
89 FROM HZ_MATCHED_PARTIES_GT GT,
90 HZ_PARTIES HZP,
91 HZ_CUST_ACCOUNTS CA,
92 HZ_CUST_ACCT_SITES CAS,
93 HZ_LOCATIONS LOC,
94 HZ_PARTY_SITES PARTY_SITE,
95 FND_TERRITORIES_VL TERR
96 WHERE SEARCH_CONTEXT_ID = p_context_id
97 AND GT.PARTY_ID = HZP.PARTY_ID
98 AND GT.PARTY_ID = CA.PARTY_ID(+)
99 AND CAS.CUST_ACCOUNT_ID(+) = CA.CUST_ACCOUNT_ID
100 AND CAS.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
101 AND LOC.LOCATION_ID(+) = PARTY_SITE.LOCATION_ID
102 AND LOC.COUNTRY = TERR.TERRITORY_CODE(+);
103
104 CURSOR get_matching_rule IS
105 SELECT match_rule_id
106 FROM hz_match_rules_vl
107 where RULE_NAME ='HZ_ORG_SIMPLE_SEARCH_RULE';
108 --from ar_cmgt_setup_options;
109
110 BEGIN
111 -- 1. Setup the Search criteria
112 if p_dqm_param = 'PARTY_NUM'
113 THEN
114 party_cond.party_number := substr(p_keyword,0,30);
115 ELSIF p_dqm_param = 'PARTY_NAME'
116 THEN
117 party_cond.PARTY_ALL_NAMES := p_keyword;
118 END IF;
119
120 -- Note that Phone and Email address criteria need to
121 -- be passed seperately.
122
123 contact_point_cond(1).CONTACT_POINT_TYPE := 'PHONE';
124 --contact_point_cond(1).FLEX_FORMAT_PHONE_NUMBER := p_keyword;
125
126 contact_point_cond(2).CONTACT_POINT_TYPE := 'EMAIL';
127 --contact_point_cond(2).EMAIL_ADDRESS := p_keyword;
128
129
130 OPEN get_matching_rule;
131 FETCH get_matching_rule INTO l_rule_id;
132 CLOSE get_matching_rule;
133
134
135 IF l_rule_id IS NULL THEN
136 --raise error;
137 FND_MESSAGE.SET_NAME( 'AR', 'AR_CMGT_NO_MATCH_RULE' );
138 app_exception.raise_exception;
139
140 END IF;
141
142 HZ_PARTY_SEARCH.find_parties (p_init_msg_list =>'T',
143 x_rule_id => l_rule_id,
144 p_party_search_rec => party_cond,
145 p_party_site_list => party_site_cond,
146 p_contact_list => contact_cond ,
147 p_contact_point_list => contact_point_cond,
148 p_restrict_sql => null,
149 p_search_merged => null,
150 x_search_ctx_id => l_search_context_id,
151 x_num_matches => l_num_matches,
152 x_return_status => l_return_status,
153 x_msg_count => l_msg_count,
154 x_msg_data => l_msg_data);
155
156 arp_standard.debug('l_search_context_id: '||l_search_context_id);
157 arp_standard.debug('l_return_status: '||l_return_status);
158 arp_standard.debug('l_msg_count: '||l_msg_count);
159 arp_standard.debug('l_msg_data: '||l_msg_data);
160 -- 3. Setup the SEARCH_RESULTS block to show the
161 -- results. This block is based on the Global Temporary Table
162 -- HZ_MATCHED_PARTIES_GT
163
164 p_search_context_id := l_search_context_id;
165 p_return_status := l_return_status;
166 p_msg_count := l_msg_count;
167 p_msg_data := l_msg_data;
168
169 END DQM_SEARCH;
170
171
172 END AR_CMGT_PARTY_SEARCH;