1 PACKAGE BODY AR_CMGT_PARTY_SEARCH AS
2 /*$Header: ARDQMSRB.pls 120.5 2006/03/21 10:21:19 kjoshi noship $ */
3
4 /*=======================================================================+
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
61 contact_point_cond HZ_PARTY_SEARCH.CONTACT_POINT_LIST;
62
63 -- The Match Rule to use for the search
64 -- this should be set to the ID of the match rule you
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
115 --party_cond.party_name := p_keyword;
116 --party_cond.organization_name := p_keyword;
117 party_cond.party_number := p_keyword;
118 --party_cond.all_account_numbers := p_keyword;
119 ELSIF p_dqm_param = 'PARTY_NAME'
120 THEN
121 party_cond.PARTY_ALL_NAMES := p_keyword;
122
123 --party_cond.organization_name :=p_keyword;
124 END IF;
125 -- Note that the party_site, contact and contact point
126 -- search records are tables. So you can pass any
127 -- number of addresses to search on.
128 --Party_site_cond(1).address := p_keyword;
129 --party_site_cond(2).city := p_keyword;
130 --party_site_cond(3).state := p_keyword;
131 --party_site_cond(4).country := p_keyword;
132
133 -- Note that Phone and Email address criteria need to
134 -- be passed seperately.
135
136 contact_point_cond(1).CONTACT_POINT_TYPE := 'PHONE';
137 --contact_point_cond(1).FLEX_FORMAT_PHONE_NUMBER := p_keyword;
138
139 contact_point_cond(2).CONTACT_POINT_TYPE := 'EMAIL';
140 --contact_point_cond(2).EMAIL_ADDRESS := p_keyword;
141
142
143 OPEN get_matching_rule;
144 FETCH get_matching_rule INTO l_rule_id;
145 CLOSE get_matching_rule;
146
147
148 IF l_rule_id IS NULL THEN
149 --raise error;
150 FND_MESSAGE.SET_NAME( 'AR', 'AR_CMGT_NO_MATCH_RULE' );
151 app_exception.raise_exception;
152
153 END IF;
154
155 HZ_PARTY_SEARCH.find_parties (p_init_msg_list =>'T',
156 x_rule_id => l_rule_id,
157 p_party_search_rec => party_cond,
158 p_party_site_list => party_site_cond,
159 p_contact_list => contact_cond ,
160 p_contact_point_list => contact_point_cond,
161 p_restrict_sql => null,
162 p_search_merged => null,
163 x_search_ctx_id => l_search_context_id,
164 x_num_matches => l_num_matches,
165 x_return_status => l_return_status,
166 x_msg_count => l_msg_count,
167 x_msg_data => l_msg_data);
168
169 -- 3. Setup the SEARCH_RESULTS block to show the
170 -- results. This block is based on the Global Temporary Table
171 -- HZ_MATCHED_PARTIES_GT
172
173 p_search_context_id := l_search_context_id;
174 p_return_status := l_return_status;
175 p_msg_count := l_msg_count;
176 p_msg_data := l_msg_data;
177
178 END DQM_SEARCH;
179
180
181 END AR_CMGT_PARTY_SEARCH;
[Home] [Help]