[Home] [Help]
PACKAGE BODY: APPS.WSH_INVOICE_UTILITIES
Source
1 PACKAGE BODY WSH_INVOICE_UTILITIES as
2 /* $Header: WSHARINB.pls 115.3 99/07/16 08:17:42 porting ship $ */
3
4 inv_num Varchar2(40);
5
6 --
7 -- PUBLIC FUNCTIONS
8 --
9 /*===========================================================================+
10 | Name: update_numbers |
11 | Purpose: It looks in ra_interface_lines table for the lines inserted for |
12 | this run of Receivables Interface and updates them with an |
13 | Invoice Number based on the delivery name |
14 +===========================================================================*/
15
16 FUNCTION update_invoice_numbers(x_del_id NUMBER, x_del_name VARCHAR2,
17 err_msg IN OUT VARCHAR2) Return NUMBER IS
18
19 inv_num_index Number;
20 inv_num_base Varchar2(40);
21
22 BEGIN
23 --dbms_output.put_line('Update Invoice Numbers: del_id ' || to_char(x_del_id) || ' del name: ' || x_del_name );
24 inv_num_base := x_del_name;
25
26 Select nvl((max(index_number)+1), 0)
27 Into inv_num_index
28 From wsh_invoice_numbers
29 Where delivery_id = x_del_id;
30
31 If ( SQL%NOTFOUND ) Then
32 inv_num_index := 1;
33 End if;
34
35 if ( inv_num_index = 0 ) Then
36 inv_num := inv_num_base;
37 Else
38 inv_num := inv_num_base || '-' || to_char(inv_num_index);
39 End If;
40
41 --dbms_output.put_line('global user ' || fnd_global.user_id);
42 --dbms_output.put_line('inv num index ' || to_char(inv_num_index));
43 Insert Into Wsh_Invoice_Numbers
44 (INVOICE_NUMBER_ID, DELIVERY_ID, INDEX_NUMBER, LAST_UPDATE_DATE,
45 LAST_UPDATED_BY, CREATION_DATE, CREATED_BY)
46 VALUES
47 (wsh_invoice_numbers_s.nextval, x_del_id, inv_num_index, SYSDATE,
48 fnd_global.user_id, SYSDATE, fnd_global.user_id);
49
50 return 0;
51
52 EXCEPTION
53 WHEN others THEN
54 err_msg := 'Error in wsh_invoice_utilities.update_invoice_numbers:\n '||
55 SQLERRM;
56 --dbms_output.put_line(err_msg);
57 return -1;
58
59 END;
60
61 PROCEDURE update_numbers(x_org_id NUMBER ,
62 x_request_id NUMBER,
63 err_msg IN OUT VARCHAR2 ) IS
64
65 group_col_clause varchar2(10000) := '';
66 select_col varchar2(10000) := '';
67 col_name varchar2(100) ;
68 grp_stmt varchar2(20000);
69 col_length NUMBER;
70 group_cursor INTEGER;
71 rows_processed INTEGER;
72
73 last_concat_cols varchar2(5000) := '';
74 this_concat_cols varchar2(5000) := '';
75
76 last_del_name varchar2(15) := '';
77 this_del_name varchar2(15) := '';
78 this_del_id Number;
79 this_rowid Varchar2(20);
80
81 Cursor cur_get_cols IS
82 Select upper(c.from_column_name), c.from_column_length
83 From ra_group_by_columns c
84 Where c.column_type = 'M';
85
86 Cursor cur_get_del_id (x_del_name IN VARCHAR2) IS
87 Select delivery_id
88 From wsh_deliveries
89 Where name = x_del_name;
90
91 BEGIN
92
93 -- Get all the group by columns from ra_group_by_columns table
94 -- and build up the select and group bu column clauses.
95 --dbms_output.put_line('Position 1');
96 Open cur_get_cols;
97 Loop
98 Fetch cur_get_cols Into col_name, col_length;
99 Exit when cur_get_cols%NOTFOUND;
100 If ( group_col_clause is NULL ) Then
101 group_col_clause := col_name;
102 select_col := col_name;
103 Else
104 group_col_clause := group_col_clause || ', ' || col_name;
105 select_col := select_col || '||' || '''~'''|| '||'|| col_name;
106 End If;
107 End Loop;
108 Close cur_get_cols;
109 --dbms_output.put_line('Position 2');
110 --dbms_output.put_line('Grouping Clause: ' || group_col_clause);
111 --dbms_output.put_line('Select Clause: ' || select_col);
112
113 -- Build the full select statement and using dbms_sql execute
114 -- the statement
115 --dbms_output.put_line('Position 21');
116 grp_stmt := 'Select ' || select_col || ' group_cols,' ||
117 ' l.interface_line_attribute3, ROWID ' ||
118 ' From RA_INTERFACE_LINES_ALL L' ||
119 ' Where trx_number is NULL' ||
120 ' And request_id = ' || to_char(x_request_id)||
121 ' Order by ' || group_col_clause ||
122 ' , l.interface_line_attribute3, l.org_id' ;
123
124 --dbms_output.put_line(grp_stmt);
125 --dbms_output.put_line('Position 3');
126 group_cursor := dbms_sql.open_cursor;
127 dbms_sql.parse( group_cursor, grp_stmt, dbms_sql.v7);
128 --dbms_output.put_line('Position 31');
129 dbms_sql.define_column( group_cursor, 1, this_concat_cols, 5000);
130 dbms_sql.define_column( group_cursor, 2, this_del_name, 15);
131 dbms_sql.define_column( group_cursor, 3, this_rowid, 20);
132 rows_processed := dbms_sql.execute (group_cursor);
133
134 --dbms_output.put_line('Position 4');
135 Loop
136 if ( dbms_sql.fetch_rows (group_cursor) > 0 ) Then
137 dbms_sql.column_value (group_cursor, 1, this_concat_cols);
138 dbms_sql.column_value (group_cursor, 2, this_del_name);
139 dbms_sql.column_value (group_cursor, 3, this_rowid);
140 Else
141 exit;
142 End if;
143
144 --dbms_output.put_line('Delivery Name: ' || this_del_name);
145 --dbms_output.put_line('Concat Cols: ' || this_concat_cols);
146 if ( last_del_name is NULL OR
147 last_del_name <> this_del_name ) Then
148
149 --dbms_output.put_line('Position 5');
150 Open cur_get_del_id(this_del_name);
151 Fetch cur_get_del_id Into this_del_id;
152
153 if (cur_get_del_id%NOTFOUND ) Then
154 fnd_message.set_token('DELIVERY_NAME', this_del_name);
155 fnd_message.set_name('OE', 'WSH_AR_INVALID_DEL_NAME');
156 err_msg := fnd_message.get;
157 --dbms_output.put_line(err_msg);
158 Close cur_get_del_id;
159 --return;
160 End if;
161 if ( cur_get_del_id%ISOPEN ) Then
162 Close cur_get_del_id;
163 end if;
164 --dbms_output.put_line('Delivery Id: ' || to_char(this_del_id));
165
166 End if;
167
168 if ( last_concat_cols is NULL OR
169 last_concat_cols <> this_concat_cols ) Then
170
171 --dbms_output.put_line('Concat cols changed, calling update_invoice');
172 if ( update_invoice_numbers ( this_del_id, this_del_name,
173 err_msg ) < 0 ) Then
174 return;
175 end if;
176
177 last_del_name := this_del_name;
178 last_concat_cols := this_concat_cols;
179
180 Else
181
182 if ( last_del_name <> this_del_name ) Then
183
184 --dbms_output.put_line('del name changed, calling update_invoice');
185 if ( update_invoice_numbers ( this_del_id, this_del_name,
186 err_msg ) < 0 ) Then
187 return;
188 end if;
189
190 last_del_name := this_del_name;
191
192 End if;
193
194 End if;
195
196 Update RA_INTERFACE_LINES_ALL
197 set trx_number = inv_num
198 where rowid = chartorowid(this_rowid);
199
200 End Loop;
201
202 dbms_sql.close_cursor (group_cursor);
203
204 Return;
205
206 EXCEPTION
207 WHEN others THEN
208 --dbms_output.put_line('Oracle Error: ' || SQLERRM);
209 err_msg := 'Error in wsh_invoice_utilities.update_numbers:\n '|| SQLERRM;
210 --dbms_output.put_line(err_msg);
211 if (cur_get_cols%ISOPEN) Then
212 Close cur_get_cols;
213 End if;
214 if (cur_get_del_id%ISOPEN) Then
215 Close cur_get_del_id;
216 End if;
217 Return;
218
219 END;
220
221 END WSH_INVOICE_UTILITIES;