[Home] [Help]
PACKAGE BODY: APPS.INV_COPY_ORG_REPORT_PUB
Source
1 PACKAGE BODY Inv_Copy_Org_Report_Pub AS
2 -- $Header: INVVCORB.pls 115.5 2002/05/16 14:40:55 pkm ship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVVCORB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Body of Inv_Copy_Org_Report |
13 --| |
14 --| HISTORY |
15 --| 10/02/2001 Vincent Chu Created |
16 --+======================================================================*/
17
18 /*
19 ** -------------------------------------------------------------------------
20 ** Procedure: purge_interface_data
21 ** Description: Purges the records in the copy organization interface
22 ** table that correspond to a particular group code
23 ** Output:
24 ** x_retcode
25 ** return status indicating success, error, unexpected error
26 ** x_errbuf
27 ** contains the message text, if there are any
28 **
29 ** Input:
30 ** p_group_code
31 ** the group code that corresponds to the records that are to
32 ** be purged from the interface table
33 ** purge_interface
34 ** purges the interface table only if this is set to 'Y'
35 ** --------------------------------------------------------------------------
36 */
37
38 PROCEDURE purge_interface_data( x_retcode OUT VARCHAR2
39 , x_errbuf OUT VARCHAR2
40 , p_group_code IN VARCHAR2
41 , purge_interface IN VARCHAR2
42 )
43 IS
44 BEGIN
45 IF( UPPER(purge_interface) = 'Y' ) THEN
46 DELETE FROM mtl_copy_org_interface
47 WHERE group_code = p_group_code;
48 END IF;
49
50 x_retcode := 0;
51
52 EXCEPTION
53
54 WHEN OTHERS THEN
55
56 IF
57 FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
58 THEN
59 FND_MSG_PUB.Add_Exc_Msg
60 ( 'Inv_Copy_Org_Report_Pub', 'Purge_Reports_Data' );
61 END IF;
62
63 x_errbuf := FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE );
64 x_retcode := 2;
65
66 END purge_interface_data;
67
68 /*
69 ** -------------------------------------------------------------------------
70 ** Procedure: purge_previous_reports_data
71 ** Description: Purges any records in the copy organization report
72 ** table that correspond to a completed cocurrent request
73 ** --------------------------------------------------------------------------
74 */
75
76 PROCEDURE purge_previous_reports_data
77 IS
78 CURSOR concurrent_req_id_cursor IS
79 SELECT DISTINCT request_id
80 FROM mtl_copy_org_report;
81
82 phase VARCHAR2( 200 );
83 status VARCHAR2( 200 );
84 dev_phase VARCHAR2( 30 );
85 dev_status VARCHAR2( 30 );
86 message VARCHAR2( 250 );
87 returned_val BOOLEAN;
88 request_id_rec concurrent_req_id_cursor%ROWTYPE;
89
90 BEGIN
91 FOR request_id_rec IN concurrent_req_id_cursor LOOP
92 returned_val := FND_CONCURRENT.GET_REQUEST_STATUS
93 ( request_id_rec.request_id
94 , NULL
95 , NULL
96 , phase
97 , status
98 , dev_phase
99 , dev_status, message
100 );
101
102 IF UPPER( dev_phase ) = 'COMPLETE' THEN
103 DELETE FROM
104 mtl_copy_org_report
105 WHERE
106 request_id = request_id_rec.request_id;
107 END IF;
108
109 END LOOP;
110 END purge_previous_reports_data;
111
112
113 /*
114 ** -------------------------------------------------------------------------
115 ** Procedure: purge_reports_data
116 ** Description: Purges the records in the copy organization report
117 ** table that correspond to a particular group code
118 ** Output:
119 ** x_retcode
120 ** return status indicating success, error, unexpected error
121 ** x_errbuf
122 ** contains the message text, if there are any
123 **
124 ** Input:
125 ** p_group_code
126 ** the group code that corresponds to the records that are to
127 ** be purged from the report table
128 ** --------------------------------------------------------------------------
129 */
130
131 PROCEDURE purge_reports_data
132 ( x_retcode OUT VARCHAR2
133 , x_errbuf OUT VARCHAR2
134 , p_group_code IN VARCHAR2
135 )
136 IS
137 BEGIN
138
139 DELETE FROM mtl_copy_org_report
140 WHERE group_code = p_group_code;
141
142 purge_previous_reports_data;
143 COMMIT;
144
145 x_retcode := 0;
146
147 EXCEPTION
148
149 WHEN OTHERS THEN
150
151 IF
152 FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
153 THEN
154 FND_MSG_PUB.Add_Exc_Msg
155 ( 'Inv_Copy_Org_Report_Pub', 'Purge_Reports_Data' );
156 END IF;
157
158 x_errbuf := FND_MSG_PUB.Get( p_encoded => FND_API.G_FALSE );
159 x_retcode := 2;
160
161 END purge_reports_data;
162
163
164 /*
165 ** -------------------------------------------------------------------------
166 ** Function: clob_to_varchar
167 ** Description: Takes in a CLOB database object and returns the
168 ** corresponding VARCHAR2 object
169 ** Input:
170 ** lobsrc
171 ** The CLOB to be converted into a VARCHAR2 string
172 **
173 ** Returns:
174 ** The VARCHAR2 string that was converted from the passed in CLOB
175 ** --------------------------------------------------------------------------
176 */
177
178 FUNCTION clob_to_varchar( lobsrc IN CLOB ) RETURN VARCHAR2
179 IS
180 buffer VARCHAR2( 1800 );
181 amount NUMBER;
182 BEGIN
183 amount := 1800;
184
185 IF lobsrc IS NOT NULL THEN
186 DBMS_LOB.READ( lobsrc, amount, 1, buffer );
187 END IF;
188
189 RETURN buffer;
190 END clob_to_varchar;
191
192 /*
193 ** -------------------------------------------------------------------------
194 ** Function: submit_report_conc_req
195 ** Description: Submits a request to run the copy organization report
196 ** request set, which generates a report and purges the
197 ** corresponding report table and interface table data
198 ** Input:
199 ** p_group_code
200 ** the group code that corresponds to particular run of
201 ** copy organization, for which a report is to be generated
202 ** purge_interface
203 ** purges the interface table only if this is set to 'Y'
204 ** Returns:
205 ** ID of the request that runs the report request set
206 ** --------------------------------------------------------------------------
207 */
208
209 FUNCTION submit_report_conc_req ( p_group_code IN VARCHAR2
210 , purge_interface IN VARCHAR2
211 )
212 RETURN NUMBER
213 IS
214 success BOOLEAN;
215 request_id NUMBER;
216 BEGIN
217 request_id := -1;
218 success := fnd_submit.set_request_set('INV', 'INVGCORPSET');
219 IF( success ) THEN
220 success := fnd_submit.submit_program( 'INV'
221 , 'INVGCORP'
222 , 'INVGCORP10'
223 , p_group_code
224 );
225
226 success := fnd_submit.submit_program( 'INV'
227 , 'INVCORPP'
228 , 'INVGCORP20'
229 , p_group_code
230 );
231
232 success := fnd_submit.submit_program( 'INV'
233 , 'INVISCORP'
234 , 'INVGCORP20'
235 , p_group_code
236 , purge_interface
237 );
238
239 request_id := fnd_submit.submit_set( NULL, FALSE );
240 END IF;
241
242 RETURN request_id;
243
244 END submit_report_conc_req;
245
246 END Inv_Copy_Org_Report_Pub;