1 PACKAGE OE_Create_account_info AUTHID CURRENT_USER AS
2 /* $Header: OEXCACTS.pls 120.2 2006/07/21 13:47:21 mbhoumik noship $ */
3
4
5 -- API Operation control flags.
6 TYPE Control_Rec_Type IS RECORD
7 (
8 p_allow_account_creation varchar2(20):='CHECK'--ALL,SITE_AND_CONTACT,NONE,
9 --CHECK and NULL
10 ,p_init_msg_list boolean := FALSE
11 ,p_commit BOOLEAN := TRUE
12 ,p_multiple_account_is_error BOOLEAN :=TRUE
13 ,p_multiple_contact_is_error BOOLEAN :=TRUE
14 --Created_by_Module for creating tca entities
15 --IT should be ONT_<STARTUP_MODE>_AUTOMATIC_ACCOUNT
16 ,p_created_by_module varchar2(150)
17 -- This is applicable to contact. If the search contact
18 -- fails then we need to still continue and find the site
19 ,p_continue_processing_on_error boolean := FALSE
20 -- control parameters.When we call this api again due to lov or ..
21 -- we do not want to check the previous entities again
22 ,p_process_customer boolean := TRUE
23 ,p_process_contact boolean := TRUE
24 -- This is to return without processing if no accounts found and there
25 -- is no site information for the party. In this scenario for telesales
26 -- we will take the user to the add customer form
27 ,p_return_if_only_party boolean :=TRUE
28
29 -- this determines if we need to give higher preceden to primary party sites
30 -- compared to the address actually passed. This is done for Contact Center
31 -- integration
32 ,p_fetch_primary_party_sites boolean :=FALSE
33
34 -- This flag is to ignore Value To Id process in AAC. The default value is TRUE
35 -- so that the process order will always do it. We should not do this for
36 -- Telesales and Teleservice integration
37 ,p_do_value_to_id boolean :=TRUE
38
39 -- This flag is done for the Contact Center Integration.
40 -- The processing returns from the AAC if the Customer is found and when cust
41 -- is NOT Created. This is to facilitate the scenario where we need to call
42 -- defaulting after an account is identified if we need to give higher
43 -- precedence to defaulting over the address passed
44 ,p_return_if_customer_found boolean :=FALSE
45
46 );
47
48 -- Variable representing missing control record.
49 G_MISS_CONTROL_REC Control_Rec_Type;
50
51 TYPE party_customer_rec IS RECORD
52 (
53 p_party_id number := null -- can be customer or contacts party_id
54 ,p_party_number varchar2(30) := null
55 ,p_cust_account_id number := null
56 ,p_cust_account_number varchar2(30) := null
57
58 -- only contact attributes
59 ,p_org_contact_id number := null
60 ,p_cust_account_role_id number := null
61
62 -- value columns for value-to-id conversion
63 ,p_party_name varchar(360)
64 ,p_contact_name varchar(383)
65
66
67 -- this determines if the account is created in AAC or found an existing account
68 ,p_account_created_or_found varchar2(50) := 'FOUND'
69
70 );
71
72 -- Variable Representing missing party_customer_Rec
73 G_MISS_PARTY_CUSTOMER_REC party_customer_rec;
74
75
76 TYPE site_rec IS RECORD
77 (
78 -- site_use_code is mandatory even if party_site_use_id is passed
79 -- as STMTS party_site_use can still become an account site of bill,ship,deliver
80 p_site_use_code varchar2(30) := null
81 ,p_party_site_id number := null
82 ,p_party_site_use_id number := null
83 ,p_site_use_id number
84 ,p_cust_acct_site_id number
85 ,p_create_primary_acct_site_use boolean
86 ,p_process_site boolean := TRUE
87
88 --Site Customer Information
89 ,p_party_id number := null -- customer party_id
90 ,p_party_number varchar2(30) := null
91 ,p_cust_account_id number := null
92 ,p_cust_account_number varchar2(30) := null
93
94 -- contact attributes
95 ,p_org_contact_id number := null
96 ,p_cust_account_role_id number := null
97 ,p_create_responsibility boolean := FALSE
98 ,p_assign_contact_to_site boolean:=FALSE
99
100 -- value columns for value-to-id conversion
101 ,p_party_name varchar2(360)
102 ,p_contact_name varchar2(383)
103
104 -- value columns for site_use
105 ,p_site_address1 VARCHAR2(240)
106 ,p_site_address2 VARCHAR2(240)
107 ,p_site_address3 VARCHAR2(240)
108 ,p_site_address4 VARCHAR2(240)
109 ,p_site_org VARCHAR2(240)
110 ,p_site_city VARCHAR2(240)
111 ,p_site_state VARCHAR2(240)
112 ,p_site_postal_code VARCHAR2(240)
113 ,p_site_country VARCHAR2(240)
114 ,p_site_customer_id number
115 );
116
117
118 TYPE customer_rec IS RECORD
119 (
120 p_cust_account_id number
121 ,p_customer_name varchar2(360)
122 ,p_email_address varchar2(2000)
123 ,p_gsa_indicator varchar2(1)
124 ,p_account_number varchar2(30)
125 );
126
127
128 -- Table structure to store site attribues
129 TYPE customer_tbl IS TABLE OF Customer_rec
130 INDEX BY BINARY_INTEGER;
131
132
133 -- Table structure to store site attribues
134 TYPE Site_tbl_type IS TABLE OF Site_rec
135 INDEX BY BINARY_INTEGER;
136
137 -- Missing Site Table type
138 G_MISS_SITE_TBL Site_tbl_type;
139
140 -- Table structure to store site attribues
141 TYPE Account_tbl IS TABLE OF NUMBER
142 INDEX BY BINARY_INTEGER;
143
144
145 -- Table structure to store site attribues
146 TYPE Contact_Tbl IS TABLE OF NUMBER
147 INDEX BY BINARY_INTEGER;
148
149 -- Start of Comments
150 -- API name Crate_Account_Layer
151 -- Type Private
152 -- Function Automatic creation of Account Layer from Party Layer
153 --
154 -- Pre-reqs
155 --
156 -- Parameters
157 --
158 -- Notes
159 --
160 -- End of Comments
161
162 PROCEDURE Create_Account_Layer(
163 p_control_rec IN Control_Rec_Type := G_MISS_CONTROL_REC
164 ,x_return_status OUT NOCOPY VARCHAR2
165 ,x_msg_count OUT NOCOPY NUMBER
166 ,x_msg_data OUT NOCOPY VARCHAR2
167 ,p_party_customer_rec IN OUT NOCOPY /* file.sql.39 change */ Party_customer_rec
168 ,p_site_tbl IN OUT NOCOPY /* file.sql.39 change */ site_tbl_type
169 ,p_account_tbl OUT NOCOPY account_tbl
170 ,p_contact_tbl OUT NOCOPY contact_tbl
171 );
172
173
174
175 PROCEDURE Check_and_Create_Account(
176 p_party_id in number
177 ,p_party_number in varchar2
178 ,p_allow_account_creation in boolean
179 ,p_multiple_account_is_error in boolean
180 ,p_account_tbl out NOCOPY account_tbl
181 ,p_out_org_contact_id out NOCOPY number
182 ,p_out_cust_account_role_id out NOCOPY number
183 ,x_return_status OUT NOCOPY VARCHAR2
184 ,x_msg_count OUT NOCOPY NUMBER
185 ,x_msg_data OUT NOCOPY VARCHAR2
186 ,p_site_tbl_count IN number
187 ,p_return_if_only_party in boolean
188 );
189
190
191 PROCEDURE Check_and_Create_Contact(
192 p_party_id in number
193 ,p_cust_account_id in number
194 ,p_org_contact_id in number
195 ,P_site_use_code in varchar2
196 ,p_allow_contact_creation in boolean
197 ,p_create_responsibility in boolean
198 ,p_cust_account_role_id in out NOCOPY /* file.sql.39 change */ number
199 ,p_cust_account_site_id in number
200 ,p_assign_contact_to_site in boolean
201 ,p_multiple_account_is_error in boolean
202 ,p_multiple_contact_is_error in boolean
203 ,p_contact_tbl out NOCOPY contact_tbl
204 ,p_multiple_account out NOCOPY boolean
205 ,x_return_status OUT NOCOPY VARCHAR2
206 ,x_msg_count OUT NOCOPY NUMBER
207 ,x_msg_data OUT NOCOPY VARCHAR2
208 );
209
210
211 PROCEDURE Check_and_Create_Sites (
212 p_party_id in number
213 ,p_cust_account_id in number
214 ,p_site_tbl in out NOCOPY /* file.sql.39 change */ site_tbl_Type
215 ,p_allow_site_creation in boolean
216 ,p_continue_on_error in boolean
217 -- this expects either party_site_use_id or site_use_code and party_site_id
218 ,x_return_status out NOCOPY varchar2
219 ,x_msg_data out NOCOPY varchar2
220 ,x_msg_count out NOCOPY varchar2
221 );
222
223 PROCEDURE set_debug_on ;
224
225 PROCEDURE if_multiple_accounts(
226 p_party_id in number
227 ,p_party_number varchar2
228 ,p_account_Tbl out NOCOPY account_tbl
229 ,x_return_status out NOCOPY varchar2
230 ,x_msg_data out NOCOPY varchar2
231 ,x_msg_count out NOCOPY number
232 );
233
234
235 PROCEDURE Value_to_id(
236 p_party_customer_rec IN OUT NOCOPY Party_customer_rec
237 ,p_site_tbl IN OUT NOCOPY site_tbl_type
238 ,p_permission IN varchar2
239 ,x_return_status OUT NOCOPY VARCHAR2
240 ,x_msg_count OUT NOCOPY NUMBER
241 ,x_msg_data OUT NOCOPY VARCHAR2
242 );
243
244 PROCEDURE find_sold_to_id(
245 p_party_id IN OUT NOCOPY number
246 ,p_cust_account_id in OUT NOCOPY number
247 ,p_party_name IN varchar2
248 ,p_cust_account_number IN varchar2
249 ,p_party_number IN varchar2
250 ,p_permission IN varchar2
251 ,p_site_use_id IN OUT NOCOPY number
252 ,p_party_site_id IN OUT NOCOPY number
253 ,p_party_site_use_id IN OUT NOCOPY number
254 ,p_party_site_use_code IN varchar2 DEFAULT NULL
255 ,p_process_site IN OUT NOCOPY boolean
256 ,x_return_status OUT NOCOPY varchar2
257 );
258
259 procedure find_contact_id(
260 p_contact_id IN OUT NOCOPY number
261 ,p_cust_contact_id in out NOCOPY number
262 ,p_contact_name IN varchar2
263 ,p_permission in varchar2
264 ,p_sold_to_org_id in number
265 ,p_site_use_id in number
266 ,p_party_id in number
267 ,p_site_use_code in varchar2 default null
268 ,x_return_status OUT NOCOPY VARCHAR2
269 );
270
271 procedure find_site_id(
272 p_site_use_id IN OUT NOCOPY number
273 ,p_site_id IN OUT NOCOPY number
274 ,p_account_site_use_id in out NOCOPY number
275 ,p_site_use_code in varchar2
276 ,p_site_address1 in VARCHAR2
277 ,p_site_address2 in VARCHAR2
278 ,p_site_address3 in VARCHAR2
279 ,p_site_address4 in VARCHAR2
280 ,p_site_org in VARCHAR2
281 ,p_site_city in VARCHAR2
282 ,p_site_state in VARCHAR2
283 ,p_site_postal_code in VARCHAR2
284 ,p_site_country in VARCHAR2
285 ,p_site_customer_id in number
286 ,p_sold_to_org_id in number
287 ,p_sold_to_party_id IN number
288 ,p_party_id IN out nocopy number
289 ,p_permission in varchar2
290 ,x_return_status OUT NOCOPY VARCHAR2
291 ,x_msg_data out NOCOPY varchar2
292 ,x_msg_count out NOCOPY varchar2
293 );
294
295 FUNCTION get_party_id(
296 p_party_name in varchar2
297 ,p_party_number in varchar2
298 ,p_party_site_use_code in varchar2
299 ) return number;
300
301 FUNCTION get_party_contact_id(
302 p_contact_name in varchar2
303 ,p_party_id in number
304 ,p_sold_to_org_id in number
305 ) return number;
306
307 FUNCTION get_party_site_id(
308 p_site_address1 IN VARCHAR2
309 ,p_site_address2 IN VARCHAR2
310 ,p_site_address3 IN VARCHAR2
311 ,p_site_address4 IN VARCHAR2
312 ,p_site_location IN VARCHAR2
313 ,p_site_org IN VARCHAR2
314 ,p_sold_to_party_id IN number
315 ,p_site_city IN VARCHAR2
316 ,p_site_state IN VARCHAR2
317 ,p_site_postal_code IN VARCHAR2
318 ,p_site_country IN VARCHAR2
319 ,p_site_customer_id IN VARCHAR2
320 ,p_site_use_code IN VARCHAR2
321 ,p_party_id IN number
322 ) return number;
323
324 FUNCTION Sold_To_Org(
325 p_sold_to_org IN VARCHAR2
326 , p_customer_number IN VARCHAR2
327 , p_site_use_code IN VARCHAR2
328 ) RETURN NUMBER;
329
330 FUNCTION CUST_EXISTS(cust_id number) return Boolean;
331
332 END oe_create_account_info;
333
[Home] [Help]