1 PACKAGE BODY pos_supp_generate_rpt_pkg AS
2 /* $Header: POSSPRPTB.pls 120.1 2010/11/25 12:16:15 puppulur noship $ */
3 -- Start of comments
4 -- API name : Generate Report API
5 -- Author : BHUVANA VAMSI
6 -- Purpose : Generate report for selected supplier based on XML Payload
7 -- Version : Initial version 1.0
8 -- End of comments
9
10 ----------------------------------------------------
11 FUNCTION rem_first_comma(in_string IN VARCHAR2) RETURN VARCHAR2 IS
12 BEGIN
13 IF (TRIM(in_string) IS NOT NULL) THEN
14 RETURN substr(in_string, 2, length(in_string));
15 ELSE
16 RETURN in_string;
17 END IF;
18 EXCEPTION
19 WHEN OTHERS THEN
20 RETURN '';
21 END;
22 -------------------------------------------------------------
23
24 PROCEDURE list_to_csv_varchar(x_array IN pos_tbl_number,
25 x_result1 OUT NOCOPY VARCHAR2,
26 x_result2 OUT NOCOPY VARCHAR2,
27 x_result3 OUT NOCOPY VARCHAR2) IS
28 -- For longcomments enhancement, Bug 2234299
29 -- changed 'value' type from qa_results.character1%TYPE to varchar2(2000)
30 -- rponnusa Thu Mar 14 21:27:04 PST 2002
31 separator CONSTANT VARCHAR2(1) := ',';
32 l_list_count INTEGER;
33 BEGIN
34 -- Loop until a single ',' is found or x_result is exhausted.
35 l_list_count := x_array.count;
36 IF l_list_count > 2900 THEN
37 FOR i IN 1 .. 2900 LOOP
38 x_result1 := x_result1 || separator || x_array(i);
39 END LOOP;
40 x_result1 := rem_first_comma(x_result1);
41 IF l_list_count < 5800 THEN
42 FOR i IN 2901 .. l_list_count LOOP
43 x_result2 := x_result2 || separator || x_array(i);
44 END LOOP;
45 x_result2 := rem_first_comma(x_result2);
46 ELSE
47 FOR i IN 2901 .. 5800 LOOP
48 x_result2 := x_result2 || separator || x_array(i);
49 END LOOP;
50 FOR i IN 5801 .. l_list_count LOOP
51 x_result3 := x_result3 || separator || x_array(i);
52 END LOOP;
53 x_result2 := rem_first_comma(x_result2);
54 x_result3 := rem_first_comma(x_result3);
55 END IF;
56 ELSE
57 FOR i IN x_array.first .. x_array.last LOOP
58 x_result1 := x_result1 || separator || x_array(i);
59 END LOOP;
60 x_result1 := rem_first_comma(x_result1);
61 END IF;
62 END list_to_csv_varchar;
63 ---------------------------------------------
64
65 PROCEDURE parse_list(x_result IN VARCHAR2,
66 x_array IN OUT NOCOPY pos_tbl_number) IS
67 -- For longcomments enhancement, Bug 2234299
68 -- changed 'value' type from qa_results.character1%TYPE to varchar2(2000)
69 -- rponnusa Thu Mar 14 21:27:04 PST 2002
70 l_value VARCHAR2(2000);
71 c VARCHAR2(10);
72 separator CONSTANT VARCHAR2(1) := ',';
73 arr_index INTEGER;
74 p INTEGER;
75 n INTEGER;
76 l_array_count INTEGER := 0;
77 BEGIN
78 -- Loop until a single ',' is found or x_result is exhausted.
79 BEGIN
80 l_array_count := x_array.count;
81 EXCEPTION
82 WHEN OTHERS THEN
83 NULL;
84 END;
85 IF l_array_count > 1 THEN
86 arr_index := l_array_count;
87 ELSE
88 arr_index := 1;
89 END IF;
90 p := 1;
91 n := length(x_result);
92 WHILE p <= n LOOP
93 c := substr(x_result, p, 1);
94 p := p + 1;
95 IF (c = separator) THEN
96 x_array(arr_index) := l_value;
97 arr_index := arr_index + 1;
98 l_value := '';
99 x_array.extend(1);
100 ELSE
101 l_value := l_value || c;
102 END IF;
103
104 END LOOP;
105 x_array(arr_index) := l_value;
106
107 END parse_list;
108 ----------------------------------------
109 -- Main Procedure which is being called from the Generate Report AM Method
110 PROCEDURE generate_report_event( p_api_version IN INTEGER,
111 p_init_msg_list IN VARCHAR2,
112 p_party_id IN pos_tbl_number,
113 x_report_id OUT NOCOPY NUMBER,
114 x_actions_request_id OUT NOCOPY NUMBER,
115 x_return_status OUT NOCOPY VARCHAR2,
116 x_msg_count OUT NOCOPY NUMBER,
117 x_msg_data OUT NOCOPY VARCHAR2) IS
118
119 l_party_id_cs_1 VARCHAR(32767) := '';
120 l_party_id_cs_2 VARCHAR(32767) := '';
121 l_party_id_cs_3 VARCHAR(32767) := '';
122 actions_request_id NUMBER := 0;
123
124 BEGIN
125 x_report_id := get_curr_supp_xml_rpt_id;
126 list_to_csv_varchar(p_party_id,
127 l_party_id_cs_1,
128 l_party_id_cs_2,
129 l_party_id_cs_3);
130
131 populate_bo_and_save_concur(l_party_id_cs_1,
132 l_party_id_cs_2,
133 l_party_id_cs_3,
134 x_report_id);
135
136 EXCEPTION
137 WHEN OTHERS THEN
138 RAISE;
139 END generate_report_event;
140 ---------------------------------------------
141
142 PROCEDURE populate_bo_and_save_concur(p_party_id_cs_1 IN VARCHAR2 DEFAULT '',
143 p_party_id_cs_2 IN VARCHAR2 DEFAULT '',
144 p_party_id_cs_3 IN VARCHAR2 DEFAULT '',
145 p_report_id_in IN VARCHAR2 DEFAULT '') IS
146
147 x_return_status VARCHAR2(100);
148 x_msg_data VARCHAR2(100);
149 x_msg_count NUMBER;
150 l_report_id NUMBER := NULL;
151 p_party_id pos_tbl_number := pos_tbl_number();
152
153 BEGIN
154
155 p_party_id.extend(1);
156
157 IF p_report_id_in IS NULL THEN
158 l_report_id := get_curr_supp_xml_rpt_id;
159 ELSE
160 l_report_id := p_report_id_in;
161 END IF;
162
163 IF TRIM(p_party_id_cs_1) IS NULL THEN
164 RETURN;
165 END IF;
166 parse_list(p_party_id_cs_1, p_party_id);
167
168 IF TRIM(p_party_id_cs_2) IS NOT NULL THEN
169 parse_list(p_party_id_cs_2, p_party_id);
170 END IF;
171 IF TRIM(p_party_id_cs_3) IS NOT NULL THEN
172 parse_list(p_party_id_cs_3, p_party_id);
173 END IF;
174
175 -- Procedure to Generate the BO and insert in the table
176 get_bo_and_insert(p_party_id,
177 l_report_id
178 );
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 RAISE;
183 END;
184 ---------------------------------------------------
185 -- Function to generate the report ID
186 FUNCTION get_curr_supp_xml_rpt_id RETURN NUMBER IS
187 BEGIN
188 SELECT pos_supp_gen_xml_rpt_s.nextval
189 INTO g_curr_supp_xml_rpt_id
190 FROM dual;
191 RETURN g_curr_supp_xml_rpt_id;
192 EXCEPTION
193 WHEN OTHERS THEN
194 RETURN - 1;
195 END;
196 ------------------------------------------------
197 -- Procedure to get the XML content for each party Id and insert in the table
198 PROCEDURE get_bo_and_insert(p_party_id IN pos_tbl_number,
199 p_report_id IN NUMBER ) IS
200 l_user_id NUMBER := fnd_global.user_id;
201 l_last_update_login NUMBER := fnd_global.login_id;
202 l_pos_supplier_bo pos_supplier_bo;
203 x_return_status VARCHAR2(1000);
204 x_msg_count NUMBER:=0;
205 x_msg_data VARCHAR2(1000);
206
207 BEGIN
208
209 FOR i IN p_party_id.first .. p_party_id.last LOOP
210
211 -- Procedure to get the XML content based on Party ID
212 pos_supplier_bo_pkg.pos_get_supplier_bo(NULL,
213 NULL,
214 p_party_id(i),
215 NULL,
216 NULL,
217 l_pos_supplier_bo,
218 x_return_status,
219 x_msg_count,
220 x_msg_data);
221
222 INSERT INTO POS_SUPP_GENERATE_XML_RPT
223 (report_id,
224 party_id,
225 xmlcontent,
226 created_by,
227 creation_date,
228 last_updated_by,
229 last_update_date,
230 last_update_login)
231 VALUES
232 (p_report_id,
233 p_party_id(i),
234 xmltype(l_pos_supplier_bo),
235 l_user_id,
236 SYSDATE,
237 l_user_id,
238 SYSDATE,
239 l_last_update_login);
240 COMMIT;
241 END LOOP;
242 EXCEPTION
243 WHEN OTHERS THEN
244 RAISE;
245 END get_bo_and_insert;
246 ------------------------------------------
247 Function BEFORE_REPORT_TRIGGER (P_REPORT_ID in number,P_PUBLICATION_ID in varchar2) return Boolean is
248 begin
249 if (p_report_id <> 0) then
250 FromClause:='POS_SUPP_GENERATE_XML_RPT';
251 WhereClause:='x.report_id='||P_REPORT_ID;
252 else
253 if (p_publication_id<> 'NA') then
254 FromClause:='pos_supp_pub_history';
255 WhereClause:='x.publication_event_id in ('||P_PUBLICATION_ID||')';
256 end if;
257 end if;
258 return true;
259 end;
260
261 END pos_supp_generate_rpt_pkg;
[Home] [Help]