[Home] [Help]
PACKAGE BODY: APPS.INV_OPM_REASON_CODE_MIGRATION
Source
1 PACKAGE BODY INV_OPM_REASON_CODE_MIGRATION AS
2 /* $Header: INVRCDSB.pls 120.0.12020000.3 2013/01/09 08:55:14 maychen ship $ */
3
4 /*====================================================================
5 -- PROCEDURE:
6 -- MIGRATE_REASON_CODE
7 --
8 -- DESCRIPTION:
9 -- This PL/SQL procedure is used to migrate OPM Reason Codes
10 --
11 -- PARAMETERS:
12 -- p_migration_run_id This is used for message logging.
13 -- p_commit Commit flag.
14 -- x_failure_count count of the failed lines.An out parameter.
15 --
16 -- SYNOPSIS:
17 --
18 -- MIGRATE_REASON_CODE ( p_migration_run_id IN NUMBER
19 -- , p_commit IN VARCHAR2
20 -- , x_failure_count OUT NUMBER)
21 --
22 -- HISTORY
23 -- 5/23/2005 - nchekuri
24 --====================================================================*/
25 PROCEDURE MIGRATE_REASON_CODE ( p_migration_run_id IN NUMBER
26 , p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
27 , x_failure_count OUT NOCOPY NUMBER) IS
28
29 l_failure_count NUMBER := 0;
30 l_success_count NUMBER := 0;
31 l_reason_id NUMBER;
32
33 --bug 13243968
34 --add order by statement
35 CURSOR opm_reas_cds_cur IS
36 SELECT b.reason_code,
37 b.delete_mark,
38 tl.reason_desc1,
39 b.creation_date,
40 b.created_by,
41 b.last_updated_by,
42 b.last_update_login
43 FROM sy_reas_cds_b b, sy_reas_cds_tl tl,
44 fnd_languages fl
45 WHERE tl.language = fl.language_code
46 AND fl.installed_flag = 'B'
47 AND b.reason_code = tl.reason_code
48 AND b.reason_id IS NULL
49 order by b.reason_code;
50
51 BEGIN
52
53 /* Begin by logging a message that reason_code migration has started */
54 gma_common_logging.gma_migration_central_log (
55 p_run_id => p_migration_run_id
56 , p_log_level => FND_LOG.LEVEL_PROCEDURE
57 , p_app_short_name => 'GMA'
58 , p_message_token => 'GMA_MIGRATION_TABLE_STARTED'
59 , p_table_name => 'SY_REAS_CDS'
60 , p_context => 'REASON_CODES');
61
62 -- bug16067107
63 update sy_reas_cds_b a set a.reason_id =
64 (select reason_id from mtl_transaction_reasons b where b.reason_name = a.reason_code);
65
66 FOR l_rec IN opm_reas_cds_cur LOOP
67
68 BEGIN
69
70 SELECT mtl_transaction_reasons_s.nextval
71 INTO l_reason_id FROM DUAL;
72
73 INSERT INTO mtl_transaction_reasons
74 ( reason_id
75 , reason_name
76 , description
77 , disable_date
78 , creation_date
79 , created_by
80 , last_update_date
81 , last_updated_by
82 , last_update_login) VALUES
83 (l_reason_id
84 , l_rec.reason_code
85 , l_rec.reason_desc1
86 , DECODE(l_rec.delete_mark,1,SYSDATE,NULL)
87 , SYSDATE
88 , l_rec.created_by
89 , SYSDATE
90 , l_rec.last_updated_by
91 , l_rec.last_update_login);
92
93 /* set the reason_id column in sy_reas_cds with this new reason_id */
94
95 UPDATE sy_reas_cds_b
96 SET reason_id = l_reason_id
97 WHERE reason_code = l_rec.reason_code;
98
99 EXCEPTION
100 WHEN OTHERS THEN
101 /* Failure count goes up by 1 */
102 l_failure_count := l_failure_count+1;
103 gma_common_logging.gma_migration_central_log (
104 p_run_id => p_migration_run_id
105 , p_log_level => FND_LOG.LEVEL_UNEXPECTED
106 , p_app_short_name =>'GMA'
107 , p_message_token => 'GMA_MIGRATION_DB_ERROR'
108 , p_db_error => sqlerrm
109 , p_table_name => 'SY_REAS_CDS'
110 , p_context => 'REASON_CODES');
111
112 END;
113
114 /* If we are here, we take it that a row has been successfully processed */
115 l_success_count := l_success_count +1;
116 END LOOP;
117
118 /* We now have the total number of rows that failed */
119 x_failure_count := l_failure_count;
120
121 /* commit if the flag is set */
122 IF ( p_commit = FND_API.G_TRUE)
123 THEN
124 COMMIT;
125 END IF;
126
127 /* End by logging a message that reason_code migration has been succesful */
128 gma_common_logging.gma_migration_central_log (
129 p_run_id => p_migration_run_id
130 , p_log_level => FND_LOG.LEVEL_PROCEDURE
131 , p_app_short_name => 'GMA'
132 , p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS'
133 , p_table_name => 'SY_REAS_CDS'
134 , p_context => 'REASON_CODES'
135 , p_param1 => l_success_count
136 , p_param2 => l_failure_count );
137 EXCEPTION
138
139 WHEN OTHERS THEN
140
141 gma_common_logging.gma_migration_central_log (
142 p_run_id => p_migration_run_id
143 , p_log_level => FND_LOG.LEVEL_UNEXPECTED
144 , p_app_short_name =>'GMA'
145 , p_message_token => 'GMA_MIGRATION_DB_ERROR'
146 , p_db_error => sqlerrm
147 , p_table_name => 'SY_REAS_CDS'
148 , p_context => 'REASON_CODES');
149
150 END MIGRATE_REASON_CODE;
151
152 END;