[Home] [Help]
PACKAGE BODY: APPS.ARP_ARXCOBL_MLS_PACKAGE
Source
1 PACKAGE body ARP_ARXCOBL_MLS_PACKAGE AS
2 /* $Header: ARCBLMLB.pls 120.4 2006/06/20 22:35:02 alawang ship $ */
3
4 function ARP_ARXCOBL_MLS_FUNCTION return varchar2 is
5
6 -- variables used by build_where_clause
7 p_customer_name_from hz_parties.party_name%TYPE;
8 p_customer_name_to hz_parties.party_name%TYPE;
9 l_customer_id number := NULL;
10 p_where1 varchar2(8096);
11 p_where2 varchar2(8096);
12 p_table1 varchar2(8096);
13
14 -- variables used by ARP_ARXCOBL_MLS_FUNCTION
15 p_userenv_lang varchar2(4);
16 retval number;
17 parm_number number;
18 parm_name varchar2(80);
19 cr CONSTANT char(1) := '';
20 select_sql1_c number;
21 select_sql1 varchar2(10000);
22 select_sql2_c number;
23 select_sql2 varchar2(10000);
24 lang_str varchar2(240);
25 --Cursor to return the customer_id for the given range of customers
26 /* modified for tca uptake */
27 /* bug1946875: This cursor is not needed.
28 Cursor cusinfo(p_customer_name_from varchar2,
29 p_customer_name_to varchar2) is
30 SELECT cust_acct.cust_account_id customer_id
31 from hz_cust_accounts cust_acct,
32 hz_parties party
33 where cust_acct.party_id = party.party_id
34 and upper(party.party_name) between
35 nvl(upper(p_customer_name_from),'A')
36 and nvl(upper(p_customer_name_to),'Z');
37 */
38 TYPE select_rec_type IS RECORD
39 (language VARCHAR2(4));
40
41 select_rec1 select_rec_type;
42 null_rec CONSTANT select_rec_type := select_rec1;
43 l_ignore INTEGER;
44
45 BEGIN
46
47 select substr(userenv('LANG'),1,4)
48 into p_userenv_lang
49 from dual;
50
51 arp_standard.debug('ARP_ARXCOBL_MLS_FUNCTION');
52
53 arp_standard.debug('P_USERENV_LANG = ' || p_userenv_lang);
54
55 /* Read in Parameter Values supplied by user */
56
57 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Customer Name From',parm_number);
58 if retval = -1 then
59 P_CUSTOMER_NAME_FROM := NULL;
60 else
61 P_CUSTOMER_NAME_FROM := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
62
63 end if;
64 arp_standard.debug('P_CUSTOMER_NAME_FROM ='||P_CUSTOMER_NAME_FROM);
65
66 retval := FND_REQUEST_INFO.GET_PARAM_NUMBER('Customer Name To',parm_number);
67 if retval = -1 then
68 P_CUSTOMER_NAME_TO := NULL;
69 else
70 P_CUSTOMER_NAME_TO := FND_REQUEST_INFO.GET_PARAMETER(parm_number);
71 end if;
72 arp_standard.debug('P_CUSTOMER_NAME_TO ='|| P_CUSTOMER_NAME_TO);
73
74 arp_standard.debug('Will call BUILD_WHERE_CLAUSE');
75 p_table1:='hz_cust_accounts cust_acct,';
76 p_table1:=p_table1||'hz_cust_acct_sites acct_site,';
77 p_table1:=p_table1||'hz_cust_site_uses site_uses,';
78 p_table1:=p_table1||'hz_locations loc,';
79 p_table1:=p_table1||'hz_party_sites party_site';
80 p_where1:='cust_acct.cust_account_id=acct_site.cust_account_id';
81 p_where1:=p_where1||' and acct_site.cust_acct_site_id=site_uses.cust_acct_site_id';
82 p_where1:=p_where1||' and site_uses.site_use_code=''BILL_TO''';
83 p_where1:=p_where1||' and acct_site.party_site_id = party_site.party_site_id';
84 p_where1:=p_where1||' and loc.location_id = party_site.location_id';
85 /* bug1946875: cursor is not used. so this is not used , neither. */
86 -- p_where2:=p_where1||' and acct_site.cust_account_id=:l_customer_id';
87 p_where2:=p_where1||' and nvl(site_uses.status,''A'')=''A''';
88
89 /* bug1946875: add given customer parameters condition to where clause */
90 if (p_customer_name_to is not null
91 or p_customer_name_from is not null )then
92 p_table1:=p_table1||',hz_parties party';
93 p_where2:=p_where2||' and cust_acct.party_id = party.party_id' ;
94
95 if p_customer_name_from is not null then
96 /* bug1994326: add equal sign */
97 /*Bug2541377 Added replace function to check if their is any apostrophe and replace it with two single quotes*/
98 p_where2:=p_where2||' and party.party_name >= :p_customer_name_from ' ;
99 end if;
100
101 if p_customer_name_to is not null then
102 /* bug1994326: add equal sign */
103 /*Bug2541377 Added replace function to check if their is any apostrophe and replace it with two single quotes*/
104 p_where2:=p_where2||' and party.party_name <= :p_customer_name_to ' ;
105 end if;
106
107 end if;
108
109 arp_standard.debug('done with BUILD_WHERE_CLAUSE');
110
111 /* bug1946875: This cursor is not used. customer condition has already been included in select_sql1. */
112 ---Customer Loop . For every customer the loop finds out the languages for corresponding Bill To addresses.
113
114
115 -- For Customer IN cusinfo(P_CUSTOMER_NAME_FROM,P_CUSTOMER_NAME_TO)
116 -- LOOP
117 select_sql1 :=
118 'select distinct(nvl(rtrim(substr(loc.language,1,4)), ''' || p_userenv_lang || ''')) language ' || cr ||
119 'from ' || p_table1 || cr ||
120 'where ' || cr || p_where2;
121 --dbms_output.put_line('select is'||select_sql1);
122
123 arp_standard.debug('raxinv.select_sql1 = ' || cr ||
124 select_sql1 || cr );
125
126
127 ------------------------------------------------
128 -- Parse sql stmts
129 ------------------------------------------------
130
131 BEGIN
132 arp_standard.debug( ' Parsing select_sql1 stmt');
133 select_sql1_c := dbms_sql.open_cursor;
134 dbms_sql.parse( select_sql1_c, select_sql1, dbms_sql.v7 );
135 -- DBMS_SQL.BIND_VARIABLE(select_sql1_c,':l_customer_id',Customer.customer_id);
136 EXCEPTION
137 WHEN OTHERS THEN
138 arp_standard.debug( 'EXCEPTION: Error parsing select_sql1 stmt' );
139 RAISE;
140 END;
141
142
143 arp_standard.debug( 'Completed parsing select stmts' );
144
145 arp_standard.debug( 'define_columns for select_sql1_c');
146 dbms_sql.define_column( select_sql1_c, 1, select_rec1.language, 4);
147 -- Bug 5173488: use bind variable instead of hard code literal in where clause.
148 if p_customer_name_from is not null then
149 dbms_sql.bind_variable(select_sql1_c, ':p_customer_name_from', p_customer_name_from);
150 end if;
151 if p_customer_name_to is not null then
152 dbms_sql.bind_variable(select_sql1_c, ':p_customer_name_to', p_customer_name_to);
153 end if;
154
155
156 arp_standard.debug( ' Executing select_sql1' );
157 BEGIN
158 l_ignore := dbms_sql.execute( select_sql1_c );
159
160 EXCEPTION
161 WHEN OTHERS THEN
162 arp_standard.debug( 'EXCEPTION: Error executing select_sql1' );
163 RAISE;
164 END;
165
166
167 --------------------------------------------------------------
168 -- Fetch rows
169 --------------------------------------------------------------
170 arp_standard.debug( ' Fetching select_sql1 stmt');
171
172 begin
173 loop
174 if (dbms_sql.fetch_rows( select_sql1_c ) > 0)
175 then
176
177 arp_standard.debug(' fetched a row' );
178 select_rec1 := null_rec;
179 ------------------------------------------------------
180 -- Get column values
181 ------------------------------------------------------
182 dbms_sql.column_value( select_sql1_c, 1, select_rec1.language );
183 arp_standard.debug( 'Language code: ' || select_rec1.language );
184
185 if (lang_str is null) then
186 lang_str := select_rec1.language;
187 else
188 lang_str := lang_str || ',' || select_rec1.language;
189 end if;
190
191 else
192 arp_standard.debug('Done fetching select_sql1');
193 EXIT;
194 end if;
195 end loop;
196 end;
197
198
199 return lang_str;
200 -- return('US');
201 -- end loop;
202
203 end ARP_ARXCOBL_MLS_FUNCTION;
204
205 end ARP_ARXCOBL_MLS_PACKAGE;