1 PACKAGE BODY ari_config AS
2 /* $Header: ARICNFGB.pls 120.21.12010000.5 2008/11/10 11:51:51 avepati ship $ */
3
4 -- generates raw html code used by the homepage for the configurable section : the second column : when customer_id, user_id, or site_use_id are not available, the procedure should be passed -1.
5 -- If p_site_use_id = -1, then user has Customer level access. In all your custom queries, you need to set site ID to NULL
6 -- Please note the customerId and customerSiteUseId has been enforced to be encrypted in all URLs
7 -- If there are any customized links in this customized area in which p_customer_id or p_site_use_id is there in URL
8 -- THE LINK WILL STOP WORKING
9 -- For all links to any iReceivables pages in the URL use p_encrypted_customer_id and p_encrypted_site_use_id
10 -- The p_customer_id and p_site_use_id contains the same id in plain text to be used for select query or any other purpose.
11
12 PROCEDURE get_homepage_customization(
13 p_user_id IN NUMBER,
14 p_customer_id IN NUMBER,
15 p_site_use_id IN NUMBER,
16 p_encrypted_customer_id IN VARCHAR2,
17 p_encrypted_site_use_id IN VARCHAR2,
18 p_language IN VARCHAR2,
19 p_output_string OUT NOCOPY VARCHAR2) IS
20 BEGIN
21 p_output_string := ' <table border="0" cellpadding="0" cellspacing="0" width="100%">
22 <tr>
23 <td>
24 <table border="0" cellpadding="0" cellspacing="0" width="100%">
25 <tr>
26 <td class="OraHeader">';
27
28 p_output_string := p_output_string || fnd_message.get_string('AR', 'ARI_HOMEPAGE_CUST_NEWS');
29
30 p_output_string := p_output_string || '</td>
31 </tr>
32 <tr>
33 <td class="OraBGAccentDark"></td>
34 </tr>
35 </table>
36 </td>
37 </tr>
38 <tr>
39 <td height="10"></td>
40 </tr>
41 <tr>
42 <td>
43 <table border="0" cellpadding="0" cellspacing="0" width="100%">
44 <tr>
45 <td><ul>';
46
47 p_output_string := p_output_string || fnd_message.get_string('AR', 'ARI_HOMEPAGE_CUST_NEWS_BODY');
48
49 p_output_string := p_output_string || '</ul></td>
50 </tr>
51 </table>
52 </td>
53 </tr>
54 </table><br>
55 <table border="0" cellpadding="0" cellspacing="0" width="100%">
56 <tr>
57 <td>
58 <table border="0" cellpadding="0" cellspacing="0" width="100%">
59 <tr>
60 <td class="OraHeader">';
61
62
63 p_output_string := p_output_string || fnd_message.get_string('AR', 'ARI_HOMEPAGE_CUST_FAQS');
64
65 p_output_string := p_output_string || '</td>
66 </tr>
67 <tr>
68 <td class="OraBGAccentDark"></td>
69 </tr>
70 </table>
71 </td>
72 </tr>
73 <tr>
74 <td height="10"></td>
75 </tr>
76 <tr>
77 <td>
78 <table border="0" cellpadding="0" cellspacing="0" width="100%">
79 <tr>
80 <td><ul>';
81
82 p_output_string := p_output_string || fnd_message.get_string('AR', 'ARI_HOMEPAGE_CUST_FAQS_BODY');
83
84 p_output_string := p_output_string || '</td>
85 </tr>
86 </table>
87 </td>
88 </tr>
89 </table><br>
90 <table border="0" cellpadding="0" cellspacing="0" width="100%">
91 <tr>
92 <td>
93 <table border="0" cellpadding="0" cellspacing="0" width="100%">
94 <tr>
95 <td class="OraHeader">';
96
97 p_output_string := p_output_string || fnd_message.get_string('AR', 'ARI_HOMEPAGE_CUST_POLICY');
98
99 p_output_string := p_output_string || '</td>
100 </tr>
101 <tr>
102 <td class="OraBGAccentDark"></td>
103 </tr>
104 </table>
105 </td>
106 </tr>
107 <tr>
108 <td height="10"></td>
109 </tr>
110 <tr>
111 <td>
112 <table border="0" cellpadding="0" cellspacing="0" width="100%">
113 <tr>
114 <td><ul>';
115
116 p_output_string := p_output_string || fnd_message.get_string('AR', 'ARI_HOMEPAGE_CUST_POLICY_BODY');
117
118 p_output_string := p_output_string || '</td>
119 </tr>
120 </table>
121 </td>
122 </tr>
123 </table><br>
124 <table border="0" cellpadding="0" cellspacing="0" width="100%">
125 <tr>
126 <td>
127 <table border="0" cellpadding="0" cellspacing="0" width="100%">
128 <tr>
129 <td class="OraHeader">';
130
131 p_output_string := p_output_string || fnd_message.get_string('AR', 'ARI_HOMEPAGE_CUST_RESRC');
132
133 p_output_string := p_output_string || '</td>
134 </tr>
135 <tr>
136 <td class="OraBGAccentDark"></td>
137 </tr>
138 </table>
139 </td>
140 </tr>
141 <tr>
142 <td height="10"></td>
143 </tr>
144 <tr>
145 <td>
146 <table border="0" cellpadding="0" cellspacing="0" width="100%">
147 <tr>
148 <td><ul>';
149
150 p_output_string := p_output_string || fnd_message.get_string('AR', 'ARI_HOMEPAGE_CUST_RESRC_BODY');
151
152 p_output_string := p_output_string || '</ul></td>
153 </tr>
154 </table>
155 </td>
156 </tr>
157 </table>
158 ';
159
160 END;
161
162
163
164 -- this procedure outputs the number of rows that the default
165 -- account details page view should show in the results region.
166 PROCEDURE Restrict_By_Rows (
167 x_output_number OUT NOCOPY NUMBER,
168 x_customer_id IN VARCHAR2,
169 x_customer_site_use_id IN VARCHAR2,
170 x_language_string IN VARCHAR2
171 )
172 IS
173 BEGIN
174 x_output_number := 10;
175 END Restrict_By_Rows;
176
177
178 PROCEDURE get_discount_customization(
179 p_customer_id IN NUMBER,
180 p_site_use_id IN NUMBER,
181 p_language IN VARCHAR2,
182 p_render OUT NOCOPY VARCHAR2,
183 p_output_string OUT NOCOPY VARCHAR2) IS
184 BEGIN
185
186 p_output_string := 'Put your customized discount information here.';
187 p_render := 'Y';
188
189 END get_discount_customization;
190
191
192 PROCEDURE get_dispute_customization(
193 p_customer_id IN NUMBER,
194 p_site_use_id IN NUMBER,
195 p_language IN VARCHAR2,
196 p_render OUT NOCOPY VARCHAR2,
197 p_output_string OUT NOCOPY VARCHAR2) IS
198 BEGIN
199
200 p_output_string := 'Put your customized dispute information here.';
201 p_render := 'Y';
202
203 END get_dispute_customization;
204
205
206
207
208 -- CUSTOM TRANSACTION SEARCH
209 -- This procedure need to be modified by deploying company to write
210 -- the query for the customized search
211 -- The customer MUST select all columns in AR_IREC_CUSTOM_SRCH_GT
212 -- Table , except columns Attribute1 to Attribute5 which are optional
213 --
214 -- The input parameter to the procedure are :-
215 -- p_customer_id Customer ID
216 -- p_customer_site_id Customer Site Use ID
217 -- p_person_id Person ID
218 -- p_transaction_status Transaction Status value in pop list
219 -- p_transaction_type Transaction Type value in pop list
220 -- p_currency Active Currency type
221 -- p_keyword Search Keyword , NULL if user not entered
222 -- p_amount_from Amount From in Advance search , NULL if not entered
223 -- p_amount_to Amount To in Advance search , NULL if user not entered
224 -- p_trans_date_from Transaction Date From in Advance search (DD-MON-YYYY )
225 -- NULL if user not entered
226 -- p_trans_date_to Transaction Date To in Advance Search (DD-MON-YYYY )
227 -- NULL if user not entered
228 -- p_due_date_from Due Date From in Advance search , NULL if user not entered
229 -- p_due_date_to Due Date To in Advance search , NULL if user not entered
230
231 -- The users need to return the column heading for all columns which has
232 -- to be displayed in the custom search
233 -- and to return NULL for the columns which needs not to be displayed
234 --
235 -- PARAMETER Corresponding Field in Table Default Column Heading
236 -- ( AR_IREC_CUSTOM_SRCH_GT)
237 -- p_transaction_col TRX_NUMBER "Transactions"
238 -- p_type_col CLASS "Type"
239 -- p_status_col STATUS "Status"
240 -- p_date_col TRX_DATE "Date"
241 -- p_due_date_col DUE_DATE "Due Date"
242 -- p_purchase_order_col - - - "Purchase Order"
243 -- p_sales_order_col - - - "Sales Order / Project"
244 -- p_original_amt_col AMOUNT_DUE_ORIGINAL "Original Amount"
245 -- p_remaining_amt_col AMOUNT_DUE_REMAINING "Remaining Amount"
246 -- p_attribute1_col ATTRIBUTE1 NULL ( not displayed )
247 -- p_attribute2_col ATTRIBUTE2 NULL ( not displayed )
248 -- p_attribute3_col ATTRIBUTE3 NULL ( not displayed )
249 -- p_attribute4_col ATTRIBUTE4 NULL ( not displayed )
250 -- p_attribute5_col ATTRIBUTE5 NULL ( not displayed )
251 --
252 -- The users need to do all validation checks , depending on the custom
253 -- search attribute , in case of any error
254 --
255 -- set the value of p_search_result to 'ERROR'
256 --
257 -- set the value of p_message_id to the Error message Id which is to be thrown in
258 -- case of the error
259 --
260 -- set the value of p_msg_app_id to the application id of error message. If no
261 -- application id is specified default is taken as 'AR'
262 --
263 -- For more Reference please refer to "iReceivables Custom Transaction Search"
264 -- White paper available on MetaLink
265 --
266 PROCEDURE search_custom_trx (
267 p_session_id IN VARCHAR2,
268 p_customer_id IN VARCHAR2,
269 p_customer_site_id IN VARCHAR2,
270 p_org_id IN VARCHAR2,
271 p_person_id IN VARCHAR2,
272 p_transaction_status IN VARCHAR2,
273 p_transaction_type IN VARCHAR2,
274 p_currency IN VARCHAR2,
275 p_keyword IN VARCHAR2,
276 p_amount_from IN VARCHAR2,
277 p_amount_to IN VARCHAR2,
278 p_trans_date_from IN VARCHAR2,
279 p_trans_date_to IN VARCHAR2,
280 p_due_date_from IN VARCHAR2,
281 p_due_date_to IN VARCHAR2,
282 p_org_name OUT NOCOPY VARCHAR2,
283 p_transaction_col OUT NOCOPY VARCHAR2,
284 p_type_col OUT NOCOPY VARCHAR2,
285 p_status_col OUT NOCOPY VARCHAR2,
286 p_date_col OUT NOCOPY VARCHAR2,
287 p_due_date_col OUT NOCOPY VARCHAR2,
288 p_purchase_order_col OUT NOCOPY VARCHAR2,
289 p_sales_order_col OUT NOCOPY VARCHAR2,
290 p_original_amt_col OUT NOCOPY VARCHAR2,
291 p_remaining_amt_col OUT NOCOPY VARCHAR2,
292 p_attribute1_col OUT NOCOPY VARCHAR2,
293 p_attribute2_col OUT NOCOPY VARCHAR2,
294 p_attribute3_col OUT NOCOPY VARCHAR2,
295 p_attribute4_col OUT NOCOPY VARCHAR2,
296 p_attribute5_col OUT NOCOPY VARCHAR2,
297 p_search_result OUT NOCOPY VARCHAR2,
298 p_message_id OUT NOCOPY VARCHAR2,
299 p_msg_app_id OUT NOCOPY VARCHAR2
300 ) IS
301
302 BEGIN
303
304 p_transaction_col := 'Transaction' ;
305 p_type_col := 'Type' ;
306 p_status_col := 'Status' ;
307 p_date_col := 'Date' ;
308 p_due_date_col := 'Due Date' ;
309 p_purchase_order_col := 'Purchase Order';
310 p_sales_order_col := 'Sales Order /Project' ;
311 p_original_amt_col := 'Original Amount ' ;
312 p_remaining_amt_col := 'Remaining Amount ' ;
313 p_attribute1_col := NULL ;
314 p_attribute2_col := NULL ;
315 p_attribute3_col := NULL ;
316 p_attribute4_col := NULL ;
317 p_attribute5_col := NULL ;
318 p_msg_app_id := 'AR' ;
319
320 END search_custom_trx ;
321
322
323
324
325 -- CUSTOM CUSTOMER SEARCH
326 -- This procedure need to be modified by deploying company to write
327 -- the query for the customized customer search
328
329 -- The following columns MUST be inserted in table AR_IREC_CUSTOM_CUST_GT for all rows
330 --
331 -- CUSTOMER_ID
332 -- ADDRESS_ID
333
334 -- The following columns are not mandatory but are advised to be inserted in table
335 -- AR_IREC_CUSTOM_CUST_GT for all rows
336 --
337 -- CUSTOMER_NUMBER
338 -- CUSTOMER_NAME
339 -- CONCATENATED_ADDRESS
340 -- LOCATION
341 --
342 -- The link for customer account level ( all locations ) could be created by putting
343 -- the address_id as -1 ( minus one )
344 -- The address ( concatenated_address ) displayed for account level link is 'All Locations'.
345 -- Any value for column CONCATENATED_ADDRESS if entered is ignored for the row
346 -- with ADDRESS_ID as -1 and 'All Locations' text is shown for address column.
347 -- For All Locations, set LOCATION to null
348
349 -- The following columns MUST be inserted in table AR_IREC_CUSTOM_CUST_GT if
350 -- the TRANSACTION NUMBER column is displayed and transaction is of type
351 -- Invoice , Debit Memo , Charge Back , Deposit or Guarantee ( Class as INV , DM , CB ,
352 -- DEP , or GAUR respectively )
353 --
354 -- TRX_NUMBER
355 -- CUSTOMER_TRX_ID
356 -- CASH_RECEIPT_ID
357 -- TERMS_SEQUENCE_NUMBER
358 -- CLASS ( e.g. INV , DM , CB , DEP or GAUR )
359
360
361 -- The following columns MUST be inserted in table AR_IREC_CUSTOM_CUST_GT if
362 -- the TRANSACTION NUMBER column is displayed and transaction is of type
363 -- Payment ( Class as PMT )
364 --
365 -- TRX_NUMBER
366 -- CASH_RECEIPT_ID
370 -- the TRANSACTION NUMBER column is displayed and transaction is of type
367 -- CLASS ( as PMT )
368
369 -- The following columns MUST be inserted in table AR_IREC_CUSTOM_CUST_GT if
371 -- Credit Memo ( Class as CM )
372 --
373 -- TRX_NUMBER
374 -- CUSTOMER_TRX_ID
375 -- TERMS_SEQUENCE_NUMBER
376 -- CLASS ( as CM )
377 --
378 -- The following columns MUST be inserted in table AR_IREC_CUSTOM_CUST_GT if
379 -- the TRANSACTION NUMBER column is displayed and transaction is of type
380 -- Credit Request ( CLASS as REQ)
381 --
382 -- TRX_NUMBER
383 -- CUSTOMER_TRX_ID
384 -- INVOICE_CURRENCY_CODE
385 -- REQUEST_ID
386 -- REQUEST_URL ( column URL in view ra_cm_requests )
387 -- CLASS ( as REQ )
388 --
389 --
390 -- columns Attribute1 to Attribute5 which are optional for all search types
391 --
392 --
393 -- The input parameter to the procedure are :-
394 -- p_user_id User Name
395 -- p_is_external_user Responsibility type , in case of External user value is 'Y'
396 -- and in case of internal user the value is 'N'
397 -- p_search_attribute lookup code for custom search attribute
398 -- p_search_keyword Search keyword , null in case user has not entered anything
399 -- p_org_id OrgId of the user
400 --
401 -- The users need to return the column heading for all columns which has
402 -- to be displayed in the custom search
403 -- and to return NULL for the columns which needs not to be displayed
404 --
405 -- PARAMETER Corresponding Field in Table Suggested Column Heading
406 -- ( AR_IREC_CUSTOM_CUST_GT)
407 -- p_org_name Organization "Organization"
408 -- p_trx_number_col TRX_NUMBER "Transaction Number"
409 -- p_customer_name_col CUSTOMER_NAME "Customer Name"
410 -- p_customer_number_col CUSTOMER_NUMBER "Customer Number"
411 -- p_address_col CONCATENATED_ADDRESS "Address"
412 -- p_address_type_col Transient (based on Customer_id "Address Type"
413 -- and Addrress_id )
414 -- p_contact_name_col - do - "Contact Name"
415 -- p_contact_phone_col - do - "Contact Phone"
416 -- p_account_summary_col - do - "Account Summary"
417 -- p_attribute1_col ATTRIBUTE1 NULL ( not displayed )
418 -- p_attribute2_col ATTRIBUTE2 NULL ( not displayed )
419 -- p_attribute3_col ATTRIBUTE3 NULL ( not displayed )
420 -- p_attribute4_col ATTRIBUTE4 NULL ( not displayed )
421 -- p_attribute5_col ATTRIBUTE5 NULL ( not displayed )
422 -- p_customer_location_col LOCATION "Customer Location"
423 --
424 -- ERROR DISPLAY
425 --
426 -- The users need to do all validation checks , depending on the context of custom
427 -- search attribute.
428 -- In case of any error set the value of p_search_result to FND_API.G_RET_STS_ERROR
429 -- In case of success search set the value of p_search_result to FND_API.G_RET_STS_SUCCESS
430 --
431 -- set the value of p_message_id to the Error message Id which is to be thrown in
432 -- case of the error
433 --
434 -- set the value of p_msg_app_id to the application id of error message. If no
435 -- application id is specified default is taken as 'AR'
436 --
437 -- For more Reference please refer to "iReceivables Custom Customer Search"
438 -- White paper available on MetaLink
439 --
440 PROCEDURE search_custom_customer(
441 p_user_name IN VARCHAR2,
442 p_is_external_user IN VARCHAR2,
443 p_search_attribute IN VARCHAR2,
444 p_search_keyword IN VARCHAR2,
445 p_org_id IN NUMBER,
446 p_org_name OUT NOCOPY VARCHAR2,
447 p_trx_number_col OUT NOCOPY VARCHAR2,
448 p_customer_name_col OUT NOCOPY VARCHAR2,
449 p_customer_number_col OUT NOCOPY VARCHAR2,
450 p_address_col OUT NOCOPY VARCHAR2,
451 p_address_type_col OUT NOCOPY VARCHAR2,
452 p_contact_name_col OUT NOCOPY VARCHAR2,
453 p_contact_phone_col OUT NOCOPY VARCHAR2,
454 p_account_summary_col OUT NOCOPY VARCHAR2,
455 p_attribute1_col OUT NOCOPY VARCHAR2,
456 p_attribute2_col OUT NOCOPY VARCHAR2,
457 p_attribute3_col OUT NOCOPY VARCHAR2,
458 p_attribute4_col OUT NOCOPY VARCHAR2,
459 p_attribute5_col OUT NOCOPY VARCHAR2,
460 p_search_result OUT NOCOPY VARCHAR2,
461 p_message_id OUT NOCOPY VARCHAR2,
462 p_msg_app_id OUT NOCOPY VARCHAR2,
463 p_customer_location_col OUT NOCOPY VARCHAR2
464 ) IS
465
466 BEGIN
467
468 p_org_name := 'Organization';
469 p_trx_number_col := NULL ;
470 p_customer_name_col := 'Customer Name' ;
471 p_customer_number_col := 'Customer Number' ;
472 p_customer_location_col := 'Customer Location';
473 p_address_col := 'Address' ;
474 p_address_type_col := 'Address Type' ;
475 p_contact_name_col := 'Contact Name' ;
476 p_contact_phone_col := 'Contact Phone' ;
477 p_account_summary_col := 'Account Summary' ;
478 p_attribute1_col := NULL ;
479 p_attribute2_col := NULL ;
480 p_attribute3_col := NULL ;
484 p_message_id := NULL ;
481 p_attribute4_col := NULL ;
482 p_attribute5_col := NULL ;
483 p_search_result := NULL ;
485 p_msg_app_id := NULL ;
486
487
488 END search_custom_customer ;
489
490
491 END ari_config;