eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.EGO_ITEM_ORG_BULKLOAD_PVT

Source


1 PACKAGE BODY EGO_ITEM_ORG_BULKLOAD_PVT AS
2 /* $Header: EGOIOBKB.pls 120.2 2006/09/20 23:21:17 dphilip noship $ */
3 
4 -- =================================================================
5 -- Global constants that need to be used.
6 -- =================================================================
7 
8   --
9   -- Package Name
10   --
11   G_PACKAGE_NAME               CONSTANT VARCHAR2(30) := 'EGO_ITEM_ORG_BULKLOAD_PVT';
12   --
13   --  Return values for RETCODE parameter (standard for concurrent programs)
14   --
15   RETCODE_SUCCESS              NUMBER    := 0;
16   RETCODE_WARNING              NUMBER    := 1;
17   RETCODE_ERROR                NUMBER    := 2;
18 
19   -- The user language (to display the error messages in appropriate language)
20   -- This is Database Session Language.
21   G_SESSION_LANG           CONSTANT VARCHAR2(99) := USERENV('LANG');
22 
23   --This is the UI language.
24   G_LANGUAGE_CODE          VARCHAR2(3);
25 
26   G_ERROR_TABLE_NAME      VARCHAR2(99) := 'EGO_BULKLOAD_INTF';
27   G_ERROR_ENTITY_CODE     VARCHAR2(99) := 'EGO_ITEM';
28   G_ERROR_FILE_NAME       VARCHAR2(99);
29   G_BO_IDENTIFIER         VARCHAR2(99) := 'EGO_ITEM';
30 
31    ----------------------------------------------------------------------------
32    -- The Date Format is chosen to be as close as possible to Timestamp format,
33    -- except that we support dates before zero A.D. (the "S" in the year part).
34    ----------------------------------------------------------------------------
35   G_DATE_FORMAT                            CONSTANT VARCHAR2(30) := 'SYYYY-MM-DD HH24:MI:SS';
36 
37 
38   G_CONCREQ_VALID_FLAG     BOOLEAN;
39 
40   G_STATUS_SUCCESS    CONSTANT VARCHAR2(1)    := 'S';
41   G_STATUS_ERROR      CONSTANT VARCHAR2(1)    := 'E';
42 
43   G_INTF_STATUS_TOBE_PROCESS   CONSTANT NUMBER := 1;
44   G_INTF_STATUS_SUCCESS        CONSTANT NUMBER := 7;
45   G_INTF_STATUS_ERROR          CONSTANT NUMBER := 3;
46 
47   G_CREATE             CONSTANT VARCHAR2(10) := 'CREATE';
48   G_UPDATE             CONSTANT VARCHAR2(10) := 'UPDATE';
49   G_SYNC               CONSTANT VARCHAR2(10) := 'SYNC';
50 
51 
52   --Define the Base Attribute Names that require Value-to-ID Conversion.
53   G_ITEM_NUMBER        VARCHAR2(50) := 'ITEM_NUMBER';
54   G_ORG_CODE               VARCHAR2(50) := 'ORGANIZATION_CODE';
55 
56   --Chosing Err Status for MSII that dont conflict with other status.
57   G_PRIMARY_UOM_ERR_STS            NUMBER := 1000002;
58 
59 -- =================================================================
60 -- Global variables used in Concurrent Program.
61 -- =================================================================
62 
63   G_USER_ID         NUMBER  :=  -1;
64   G_LOGIN_ID        NUMBER  :=  -1;
65   G_PROG_APPID      NUMBER  :=  -1;
66   G_PROG_ID         NUMBER  :=  -1;
67   G_REQUEST_ID      NUMBER  :=  -1;
68 
69   --Define Exceptions
70   G_SEGMENT_SEQ_INVALID    EXCEPTION;
71   G_DATA_TYPE_INVALID      EXCEPTION;
72 
73   --error_number is a negative integer in the range -20000 .. -20999
74   PRAGMA EXCEPTION_INIT(G_SEGMENT_SEQ_INVALID, -20000);
75   PRAGMA EXCEPTION_INIT(G_DATA_TYPE_INVALID, -20001);
76 
77   -- Used for Developer debugging
78   G_MSG_LINE_NUM            NUMBER := 1;
79 
80   --Debug Profile option used to write Error_Handler.Write_Debug
81   --Profile option name = INV_DEBUG_TRACE ; User Profile Option Name = INV: Debug Trace
82   --Value: 1 (True) ; 0 (False)
83   G_DEBUG CONSTANT VARCHAR2(10) := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
84 
85 PROCEDURE Write_Debug (p_msg  IN  VARCHAR2) IS
86 BEGIN
87 
88   --If Profile set to TRUE
89   IF (G_DEBUG = 1) THEN
90      Error_Handler.Write_Debug(p_msg);
91   END IF;
92 
93 END;
94 
95 PROCEDURE Write_Conc_Log_Debug (p_msg  IN  VARCHAR2) IS
96  l_err_msg VARCHAR2(240);
97 BEGIN
98 
99   --If Profile set to TRUE
100   IF (G_DEBUG = 1) THEN
101     FND_FILE.put_line(FND_FILE.LOG, p_msg);
102   END IF;
103 
104 END;
105 
106 PROCEDURE open_debug_session IS
107 
108   CURSOR c_get_utl_file_dir IS
109      SELECT VALUE
110       FROM V$PARAMETER
111       WHERE NAME = 'utl_file_dir';
112 
113   --local variables
114   l_log_output_dir       VARCHAR2(512);
115   l_log_return_status    VARCHAR2(99);
116   l_errbuff              VARCHAR2(999);
117 BEGIN
118 
119    -------------------------------------------------------------------------
120    -- Obtaining the Debug file output Directory                           --
121    -------------------------------------------------------------------------
122     OPEN c_get_utl_file_dir;
123     FETCH c_get_utl_file_dir INTO l_log_output_dir;
124     --Write_Conc_Log_Debug('UTL_FILE_DIR : '||l_log_output_dir);
125     IF c_get_utl_file_dir%FOUND THEN
126 
127     ------------------------------------------------------
128     -- Trim to get only the first directory in the list --
129     ------------------------------------------------------
130     IF INSTR(l_log_output_dir,',') <> 0 THEN
131       l_log_output_dir := SUBSTR(l_log_output_dir, 1, INSTR(l_log_output_dir, ',') - 1);
132       --Write_Conc_Log_Debug('Log Output Dir : '||l_log_output_dir);
133     END IF;
134 
135    -------------------------------------------------------------------------
136    -- Prepare the Debug file name based on the current timestamp          --
137    -------------------------------------------------------------------------
138     G_ERROR_FILE_NAME := G_ERROR_TABLE_NAME||'_'||to_char(sysdate, 'DDMONYYYY_HH24MISS')||'.err';
139     --Write_Conc_Log_Debug('Trying to open the Error File => '||G_ERROR_FILE_NAME);
140 
141    -------------------------------------------------------------------------
142    -- Opening the Debug file, to write log information                    --
143    -------------------------------------------------------------------------
144     Error_Handler.Open_Debug_Session(
145       p_debug_filename   => G_ERROR_FILE_NAME
146      ,p_output_dir       => l_log_output_dir
147      ,x_return_status    => l_log_return_status
148      ,x_error_mesg       => l_errbuff
149      );
150 
151     Write_Conc_Log_Debug(' Log file location --> '||l_log_output_dir||'/'||G_ERROR_FILE_NAME ||' created with status '|| l_log_return_status);
152 
153     IF (l_log_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
154        Write_Conc_Log_Debug('Unable to open error log file. Error => '||l_errbuff);
155     END IF;
156 
157   END IF;--IF c_get_utl_file_dir%FOUND THEN
158 
159 END open_debug_session;
160 
161 
162 --Setup Item Interface Lines
163 PROCEDURE Preprocess_Item_Orgs
164                (
165                  p_set_process_id        IN         NUMBER
166                 ) IS
167 
168     -- Start OF comments
169     -- API name  : Setup MSII Item Interface Lines for processing
170     -- TYPE      : Private
171     -- Pre-reqs  : None
172     -- FUNCTION  : Populate and Prepare Item interfance lines.
173     --             Eliminates any redundancy / errors in MSII
174 
175 
176   --Long Dynamic SQL String
177   l_dyn_sql                VARCHAR2(20000);
178 
179   --Error messages.
180   l_item_catalog_err_msg      VARCHAR2(1000);
181   l_uom_err_msg               VARCHAR2(1000);
182   l_lifecycle_err_msg         VARCHAR2(1000);
183   l_lifecycle_ph_err_msg      VARCHAR2(1000);
184   l_useritemtype_err_msg      VARCHAR2(1000);
185   l_bomitemtype_err_msg       VARCHAR2(1000);
186   l_engitemflag_err_msg       VARCHAR2(1000);
187 
188 BEGIN
189 
190    Write_Debug('Getting the Error messages.');
191 
192    --Preparation for Inserting error messages for all pre-processing
193    --Validation errors.
194    FND_MESSAGE.SET_NAME('EGO','EGO_PRIMARYUOM_INVALID');
195    l_uom_err_msg := FND_MESSAGE.GET;
196 
197    --Insert the Pre-processed error messages.
198    l_dyn_sql := '';
199    l_dyn_sql := l_dyn_sql || 'INSERT INTO MTL_INTERFACE_ERRORS ';
200    l_dyn_sql := l_dyn_sql || '( ';
201    l_dyn_sql := l_dyn_sql || ' ORGANIZATION_ID	 ';
202    l_dyn_sql := l_dyn_sql || ', UNIQUE_ID		 ';
203    l_dyn_sql := l_dyn_sql || ', LAST_UPDATE_DATE	 ';
204    l_dyn_sql := l_dyn_sql || ', LAST_UPDATED_BY	 ';
205    l_dyn_sql := l_dyn_sql || ', CREATION_DATE		 ';
206    l_dyn_sql := l_dyn_sql || ', CREATED_BY		 ';
207    l_dyn_sql := l_dyn_sql || ', LAST_UPDATE_LOGIN	 ';
208    l_dyn_sql := l_dyn_sql || ', TABLE_NAME		 ';
209    l_dyn_sql := l_dyn_sql || ', MESSAGE_NAME		 ';
210    l_dyn_sql := l_dyn_sql || ', COLUMN_NAME		 ';
211    l_dyn_sql := l_dyn_sql || ', REQUEST_ID		 ';
212    l_dyn_sql := l_dyn_sql || ', PROGRAM_APPLICATION_ID  ';
213    l_dyn_sql := l_dyn_sql || ', PROGRAM_ID		 ';
214    l_dyn_sql := l_dyn_sql || ', PROGRAM_UPDATE_DATE	 ';
215    l_dyn_sql := l_dyn_sql || ', ERROR_MESSAGE		 ';
216    l_dyn_sql := l_dyn_sql || ', TRANSACTION_ID 	 ';
217    l_dyn_sql := l_dyn_sql || ', ENTITY_IDENTIFIER	 ';
218    l_dyn_sql := l_dyn_sql || ', BO_IDENTIFIER		 ';
219    l_dyn_sql := l_dyn_sql || ') ';
220    l_dyn_sql := l_dyn_sql || 'SELECT ';
221    l_dyn_sql := l_dyn_sql || ' -1 ';
222    l_dyn_sql := l_dyn_sql || ', MSII.TRANSACTION_ID '; --Change to -1 if prob
223    l_dyn_sql := l_dyn_sql || ', SYSDATE ';
224    l_dyn_sql := l_dyn_sql || ', '||G_USER_ID ;
225    l_dyn_sql := l_dyn_sql || ', SYSDATE ';
226    l_dyn_sql := l_dyn_sql || ', '||G_USER_ID;
227    l_dyn_sql := l_dyn_sql || ', '||G_LOGIN_ID;
228    l_dyn_sql := l_dyn_sql || ', ''MTL_SYSTEM_ITEMS_INTERFACE'' ';
229    l_dyn_sql := l_dyn_sql || ', DECODE(MSII.PROCESS_FLAG, ';
230    l_dyn_sql := l_dyn_sql || 	  G_PRIMARY_UOM_ERR_STS||', ''EGO_PRIMARYUOM_INVALID'' ';
231    l_dyn_sql := l_dyn_sql || '	  ) ';
232    l_dyn_sql := l_dyn_sql || ', NULL ';
233    l_dyn_sql := l_dyn_sql || ', '||G_REQUEST_ID ;
234    l_dyn_sql := l_dyn_sql || ', '||G_PROG_APPID ;
235    l_dyn_sql := l_dyn_sql || ', '||G_PROG_ID ;
236    l_dyn_sql := l_dyn_sql || ', SYSDATE ';
237    l_dyn_sql := l_dyn_sql || ', DECODE(MSII.PROCESS_FLAG,  ';
238    l_dyn_sql := l_dyn_sql ||      G_PRIMARY_UOM_ERR_STS||', MSII.PRIMARY_UNIT_OF_MEASURE '||' || '' : '||l_uom_err_msg||'''';
239    l_dyn_sql := l_dyn_sql || '         )     ';
240    l_dyn_sql := l_dyn_sql || ', MSII.TRANSACTION_ID ';
241    l_dyn_sql := l_dyn_sql || ', NULL ';
242    l_dyn_sql := l_dyn_sql || ', NULL ';
243    l_dyn_sql := l_dyn_sql || 'FROM  MTL_SYSTEM_ITEMS_INTERFACE MSII ';
244    l_dyn_sql := l_dyn_sql || ' AND  MSII.PROCESS_FLAG IN  ';
245    l_dyn_sql := l_dyn_sql ||  ' ( ';
246    l_dyn_sql := l_dyn_sql || 	  G_PRIMARY_UOM_ERR_STS;
247    l_dyn_sql := l_dyn_sql ||  ' ) ';
248    l_dyn_sql := l_dyn_sql || ' AND  MSII.PROCESS_FLAG = 1 ';
249    l_dyn_sql := l_dyn_sql || ' AND  MSII.SET_PROCESS_ID = :SET_PROCESS_ID ';
250 
251    Write_Debug('l_dyn_sql');
252    --There is a limit of 1024 characters through Write_Debug (it uses
253    --UTL_FILE)
254    Write_Debug(SUBSTR(l_dyn_sql, 1, 1000));
255    Write_Debug(SUBSTR(l_dyn_sql, 1001, 2000));
256    EXECUTE IMMEDIATE l_dyn_sql USING p_set_process_id;
257    Write_Debug('MIERR: Inserted Pre-processed error messages in MTL_INTERFACE_ERRORS');
258 
259    --Now that the error messages are inserted, update MSII lines to
260    --Process status ERROR.
261    UPDATE MTL_SYSTEM_ITEMS_INTERFACE
262     SET   PROCESS_FLAG = G_INTF_STATUS_ERROR
263    WHERE  PROCESS_FLAG IN
264 	  (
265 	     G_PRIMARY_UOM_ERR_STS
266 	   )
267      AND  SET_PROCESS_ID = p_set_process_id;
268 
269    Write_Debug('MSII: Updated all the line statuses to Error for Pre-processing validation errors');
270 
271    Write_Debug('Preprocess_Item_Interface : Done.');
272 END Preprocess_Item_Orgs;
273 
274 
275 PROCEDURE Process_item_org_assignments  (
276   ERRBUF                OUT     NOCOPY VARCHAR2
277  ,RETCODE               OUT     NOCOPY NUMBER
278  ,p_Set_Process_ID      IN             NUMBER
279  ,p_commit              IN             VARCHAR2 DEFAULT FND_API.G_TRUE
280 					 ) IS
281 -- Start OF comments
282 -- API name  : Process Item Org Assignments
283 -- TYPE      : Concurrent Program
284 -- Pre-reqs  : None
285 -- FUNCTION  : Process and Load Item Org Assignments
286 --
287 -- Parameters:
288 --     IN    :
289 --             p_resultfmt_usage_id        IN      NUMBER
290 --               Similar to job number. Maps one-to-one with Data_Set_Id,
291 --               i.e. job number.
292 --
293 
294   --API return parameters
295   l_retcode               VARCHAR2(10);
296   l_errbuff               VARCHAR2(2000);
297   l_msii_set_process_id    NUMBER;
298   l_item_ioi_commit        NUMBER;
299   l_return_code            VARCHAR2(10);
300   l_err_text               VARCHAR2(2000);
301 
302 BEGIN
303 
304     Write_Conc_Log_Debug('Begin: Process_item_org_assignments');
305 
306     IF (NVL(fnd_profile.value('CONC_REQUEST_ID'), 0) <> 0) THEN
307       g_concReq_valid_flag  := TRUE;
308     END IF;
309 
310    -------------------------------------------------------------------------
311    -- Set Global variables for future usage                               --
312    -- The values are chosen from the FND_GLOBALS                          --
313    -------------------------------------------------------------------------
314    G_USER_ID    := FND_GLOBAL.user_id         ;
315    G_LOGIN_ID   := FND_GLOBAL.login_id        ;
316    G_PROG_APPID := FND_GLOBAL.prog_appl_id    ;
317    G_PROG_ID    := FND_GLOBAL.conc_program_id ;
318    G_REQUEST_ID := FND_GLOBAL.conc_request_id ;
319    --G_LANGUAGE_CODE := G_SESSION_LANG;
320 
321    --Write to Concurrent Log
322    Write_Conc_Log_Debug('G_USER_ID : '||To_char(G_USER_ID));
323    Write_Conc_Log_Debug('G_PROG_ID : '||To_char(G_PROG_ID));
324    Write_Conc_Log_Debug('G_REQUEST_ID : '||To_char(G_REQUEST_ID));
325 
326    -------------------------------------------------------------------------
327    -- Opening the Debug file, to write log information                    --
328    -------------------------------------------------------------------------
329    Write_Conc_Log_Debug('Before Error_Handler.initialize');
330    Error_Handler.initialize();
331    Error_Handler.set_bo_identifier(G_BO_IDENTIFIER);
332    --Commented on 12/17/2003 (PPEDDAMA). Open_Debug_Session should set the value
333    --appropriately, so that when the Debug Session is successfully opened :
334    --will return Error_Handler.Get_Debug = 'Y', else Error_Handler.Get_Debug = 'N'
335    --Error_Handler.Set_Debug('Y');
336 
337    --Opens Error_Handler debug session
338    Open_Debug_Session;
339    Write_Conc_Log_Debug('After Open_Debug_Session');
340 
341    --After Open_Debug_Session, can log using Write_Debug()
342    Write_Debug('G_USER_ID : '||To_char(G_USER_ID));
343    Write_Debug('G_PROG_ID : '||To_char(G_PROG_ID));
344    Write_Debug('G_REQUEST_ID : '||To_char(G_REQUEST_ID));
345 
346    -------------------------------------------------------------------------
347    -- Set the commit flag required to call to the IOI API                 --
348    -------------------------------------------------------------------------
349    IF (p_commit = FND_API.G_TRUE) THEN
350       l_item_ioi_commit := 1;
351     ELSE
352       l_item_ioi_commit := 2;
353    END IF;
354 
355    -------------------------------------------------------------------------
356    -- Preoprocessing to filter out error rows                             --
357    -------------------------------------------------------------------------
358    --Preprocess_Item_Orgs(p_Set_Process_ID);
359 
360    -------------------------------------------------------------------------
361    -- Calling IOI API to perform Item Org Assignments                     --
362    -------------------------------------------------------------------------
363 
364    FND_FILE.put_line(FND_FILE.LOG, '*Importing Item Org Assignments*. SET_PROCESS_ID : '||p_Set_Process_ID);
365 
366    --5259908
367    INV_EGO_REVISION_VALIDATE.set_Process_Control('EGO_ITEM_BULKLOAD');
368 
369    -- Using Wrapper API for INV IOI to support data security.
370    EGO_ITEM_OPEN_INTERFACE_PVT.item_open_interface_process
371    (
372       ERRBUF           =>  l_err_text
373    ,  RETCODE          =>  l_return_code
374    ,  p_org_id         =>  204 --Dummy value, all_org below carries precedence
375    ,  p_all_org        =>  1   --All Orgs
376    ,  p_val_item_flag  =>  1   -- validate item
377    ,  p_pro_item_flag  =>  1   -- process validated items
378    ,  p_del_rec_flag   =>  2   -- do not delete processed records
379    ,  p_prog_appid     =>  G_PROG_APPID
380    ,  p_prog_id        =>  G_PROG_ID
381    ,  p_request_id     =>  G_REQUEST_ID
382    ,  p_user_id        =>  G_USER_ID
383    ,  p_login_id       =>  G_LOGIN_ID
384    ,  p_xset_id        =>  p_Set_Process_ID  -- run for all the IOI  records
385    ,  p_commit_flag    =>  l_item_ioi_commit -- NOTE: 1 = Commit 2 = Donot Commit
386    ,  p_run_mode       =>  3 --NOTE: 1 = CREATE; 2 = UPDATE; 3 = SYNC
387    );
388 
389     Write_Debug('IOI_Process : SYNC : done INVPOPIF.inopinp_open_interface_process: l_return_code = ' || l_return_code);
390     Write_Debug('IOI_Process : SYNC : l_err_text = ' || l_err_text);
391 
392     FND_FILE.put_line(FND_FILE.LOG, '*Import Item Org Assignments Completed* ');
393 
394     IF (p_commit = FND_API.G_TRUE) THEN
395       COMMIT;
396     END IF;
397 
398    -------------------------------------------------------------------------
399    -- Calling Error Handler API to report errors encountered              --
400    -------------------------------------------------------------------------
401 
402     --Log Errors to MTL_INTERFACE_ERRORS table and Debug file.
403     Error_Handler.Log_Error(
404 			    p_write_err_to_inttable  => 'Y',
405 			    p_write_err_to_debugfile => 'Y'
406 			    );
407 
408     Error_Handler.Close_Debug_Session;
409 
410     --5259908
411     INV_EGO_REVISION_VALIDATE.set_Process_Control(NULL);
412 
413    -------------------------------------------------------------------------
414    -- Catch exception and report to the Debug file                        --
415    -------------------------------------------------------------------------
416    EXCEPTION
417     WHEN OTHERS THEN
418       --5259908
419       INV_EGO_REVISION_VALIDATE.set_Process_Control(NULL);
420       Write_Debug('WHEN OTHERS Exception.');
421       Write_Debug('error code : '|| to_char(SQLCODE));
422       Write_Debug('error text : '|| SQLERRM);
423       ERRBUF := 'Error : '||to_char(SQLCODE)||'---'||SQLERRM;
424       RETCODE := Error_Handler.G_STATUS_ERROR;
425       Error_Handler.Close_Debug_Session;
426       ----------------Exception block ends.
427 
428 END process_item_org_assignments;
429 
430 
431 END EGO_ITEM_ORG_BULKLOAD_PVT;