[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_CATALOG_ROLLOVER
Source
1 PACKAGE BODY Igs_Ps_Catalog_Rollover AS
2 /* $Header: IGSPS75B.pls 115.9 2003/11/06 10:56:18 jdeekoll ship $ */
3
4 PROCEDURE catalog_rollover (
5 errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY NUMBER,
7 p_old_catalog_version IN IGS_PS_CATLG_VERS_ALL.CATALOG_VERSION%TYPE,
8 p_new_catalog_version IN IGS_PS_CATLG_VERS_ALL.CATALOG_VERSION%TYPE,
9 p_override_flag IN VARCHAR2,
10 p_debug_flag IN VARCHAR2,
11 p_org_id IN NUMBER ) AS
12 ------------------------------------------------------------------
13 --Created by :
14 --Date created:
15 --
16 --Purpose:
17 --
18 --
19 --Known limitations/enhancements and/or remarks:
20 --
21 --Change History:
22 --Who When What
23 --smvk 06-Jan-2003 Bug # 2647185. Removed the hard coded strings
24 -- ('New Catalog Version','New Catalog Notes','Updated Catalog Notes')
25 -------------------------------------------------------------------
26 CURSOR cur_sel_catalog_vers(p_catalog_version IGS_PS_CATLG_VERS.CATALOG_VERSION%TYPE) IS
27 SELECT catalog_version_id,
28 catalog_version,
29 description,
30 closed_ind,
31 catalog_schedule,
32 created_by,
33 creation_date,
34 last_update_date,
35 last_update_login,
36 last_updated_by
37 FROM IGS_PS_CATLG_VERS
38 WHERE catalog_version = p_catalog_version
39 AND catalog_schedule = 'CATALOG';
40
41 CURSOR cur_sel_old_catalog_notes(p_catalog_version_id IGS_PS_CATLG_NOTES.CATALOG_VERSION_ID%TYPE) IS
42 SELECT catalog_note_id,
43 catalog_version_id,
44 note_type_id,
45 create_date,
46 end_date,
47 SEQUENCE,
48 note_text,
49 created_by,
50 creation_date,
51 last_update_date,
52 last_update_login,
53 last_updated_by
54 FROM IGS_PS_CATLG_NOTES
55 WHERE catalog_version_id = p_catalog_version_id;
56
57 CURSOR cur_sel_new_catalog_notes(p_catalog_version_id IGS_PS_CATLG_NOTES.CATALOG_VERSION_ID%TYPE,
58 p_note_type_id IGS_PS_CATLG_NOTES.NOTE_TYPE_ID%TYPE,
59 p_sequence IGS_PS_CATLG_NOTES.SEQUENCE%TYPE ) IS
60 SELECT ROWID, -- included
61 catalog_note_id,
62 catalog_version_id,
63 note_type_id,
64 create_date,
65 end_date,
66 SEQUENCE,
67 note_text,
68 created_by,
69 creation_date,
70 last_update_date,
71 last_update_login,
72 last_updated_by
73 FROM IGS_PS_CATLG_NOTES
74 WHERE catalog_version_id = p_catalog_version_id
75 AND note_type_id = p_note_type_id
76 AND SEQUENCE = p_sequence
77 FOR UPDATE NOWAIT;
78
79 cur_sel_ctlg_vers cur_sel_catalog_vers%ROWTYPE;
80 cur_sel_old_ctlg_notes cur_sel_old_catalog_notes%ROWTYPE;
81 cur_sel_new_ctlg_notes cur_sel_new_catalog_notes%ROWTYPE;
82
83
84 lv_new_catalog_version_id IGS_PS_CATLG_VERS.CATALOG_VERSION_ID%TYPE;
85 lv_old_catalog_version_id IGS_PS_CATLG_VERS.CATALOG_VERSION_ID%TYPE;
86 lv_catalog_note_id IGS_PS_CATLG_NOTES.CATALOG_NOTE_ID%TYPE;
87
88 BEGIN
89 igs_ge_gen_003.set_org_id(p_org_id);
90 retcode:=0;
91
92 OPEN cur_sel_catalog_vers(p_old_catalog_version);
93 FETCH cur_sel_catalog_vers INTO cur_sel_ctlg_vers;
94 lv_old_catalog_version_id := cur_sel_ctlg_vers.catalog_version_id;
95 CLOSE cur_sel_catalog_vers;
96
97 OPEN cur_sel_catalog_vers(p_new_catalog_version);
98 FETCH cur_sel_catalog_vers INTO cur_sel_ctlg_vers;
99 lv_new_catalog_version_id := cur_sel_ctlg_vers.catalog_version_id;
100 IF cur_sel_catalog_vers%NOTFOUND THEN
101
102 CLOSE cur_sel_catalog_vers;
103 OPEN cur_sel_catalog_vers(p_old_catalog_version);
104 FETCH cur_sel_catalog_vers INTO cur_sel_ctlg_vers;
105 IF cur_sel_catalog_vers%NOTFOUND THEN
106
107 fnd_message.set_name('IGS','IGS_PS_NO_CAT_VER');
108 fnd_file.put_line(fnd_file.LOG,fnd_message.get);
109 app_exception.raise_exception;
110 ELSE
111
112 DECLARE
113 lv_rowid VARCHAR2(25);
114 BEGIN
115 igs_ps_catlg_vers_pkg.insert_row(
116 x_rowid => lv_rowid,
117 x_catalog_version_id => lv_new_catalog_version_id,
118 x_catalog_version => p_new_catalog_version,
119 x_description => cur_sel_ctlg_vers.description,
120 x_closed_ind => cur_sel_ctlg_vers.closed_ind,
121 x_catalog_schedule => cur_sel_ctlg_vers.catalog_schedule,
122 x_mode => 'R',
123 x_org_id => p_org_id );
124 END;
125
126 END IF;
127
128 IF p_debug_flag = 'Y' THEN
129 fnd_message.set_name('IGS','IGS_PS_NEW_CTLG_VERSION');
130 fnd_file.put_line(fnd_file.LOG, fnd_message.get ||': '||lv_new_catalog_version_id||' '||
131 p_new_catalog_version ||' '||cur_sel_ctlg_vers.description||' '||
132 cur_sel_ctlg_vers.closed_ind||' '||cur_sel_ctlg_vers.catalog_schedule||' '||
133 cur_sel_old_ctlg_notes.note_text);
134 END IF;
135 CLOSE cur_sel_catalog_vers;
136 END IF;
137
138 --Second part
139 OPEN cur_sel_old_catalog_notes(lv_old_catalog_version_id);
140 LOOP
141 FETCH cur_sel_old_catalog_notes INTO cur_sel_old_ctlg_notes;
142
143 IF cur_sel_old_catalog_notes%NOTFOUND THEN
144
145 EXIT;
146 END IF;
147
148
149 OPEN cur_sel_new_catalog_notes( lv_new_catalog_version_id, --cur_sel_old_ctlg_notes.catalog_version_id,
150 cur_sel_old_ctlg_notes.note_type_id ,
151 cur_sel_old_ctlg_notes.SEQUENCE);
152 FETCH cur_sel_new_catalog_notes INTO cur_sel_new_ctlg_notes;
153
154
155 IF cur_sel_new_catalog_notes%NOTFOUND THEN
156
157 DECLARE
158 lv_rowid VARCHAR2(25);
159 BEGIN
160 igs_ps_catlg_notes_pkg.insert_row(
161 x_rowid => lv_rowid,
162 x_catalog_note_id => lv_catalog_note_id,
163 x_catalog_version_id => lv_new_catalog_version_id,
164 x_note_type_id => cur_sel_old_ctlg_notes.note_type_id,
165 x_create_date => cur_sel_old_ctlg_notes.create_date,
166 x_end_date => cur_sel_old_ctlg_notes.end_date,
167 x_sequence => cur_sel_old_ctlg_notes.SEQUENCE,
168 x_note_text => cur_sel_old_ctlg_notes.note_text,
169 x_mode => 'R',
170 x_org_id => p_org_id );
171 END;
172
173 IF p_debug_flag = 'Y' THEN
174 fnd_message.set_name('IGS','IGS_PS_NEW_CTLG_NOTES');
175 fnd_file.put_line(fnd_file.LOG,fnd_message.get || ': '||lv_catalog_note_id||' '||lv_new_catalog_version_id
176 ||' '||cur_sel_old_ctlg_notes.note_type_id||' '||cur_sel_old_ctlg_notes.create_date||' '||
177 cur_sel_old_ctlg_notes.end_date||' '||cur_sel_old_ctlg_notes.SEQUENCE||' '||
178 cur_sel_old_ctlg_notes.note_text);
179 END IF;
180 ELSE
181
182 IF p_override_flag = 'Y' THEN -- was 'YES'
183
184 igs_ps_catlg_notes_pkg.update_row(
185 x_rowid => cur_sel_new_ctlg_notes.ROWID,
186 x_catalog_note_id => cur_sel_new_ctlg_notes.catalog_note_id,
187 x_catalog_version_id => cur_sel_new_ctlg_notes.catalog_version_id,
188 x_note_type_id => cur_sel_new_ctlg_notes.note_type_id,
189 x_create_date => cur_sel_new_ctlg_notes.create_date,
190 x_end_date => cur_sel_new_ctlg_notes.end_date,
191 x_sequence => cur_sel_new_ctlg_notes.SEQUENCE,
192 x_note_text => cur_sel_old_ctlg_notes.note_text,
193 x_mode => 'R');
194
195 IF p_debug_flag = 'Y' THEN
196 fnd_message.set_name('IGS','IGS_PS_UPD_CTLG_NOTES');
197 fnd_file.put_line(fnd_file.LOG, fnd_message.get || ': '||cur_sel_new_ctlg_notes.catalog_note_id||' '||
198 cur_sel_new_ctlg_notes.catalog_version_id||' '||cur_sel_new_ctlg_notes.note_type_id||' '||
199 cur_sel_new_ctlg_notes.create_date||' '|| cur_sel_new_ctlg_notes.end_date||' '||
200 cur_sel_new_ctlg_notes.SEQUENCE||' '|| cur_sel_new_ctlg_notes.note_text);
201 END IF;
202 END IF;
203 END IF;
204 CLOSE cur_sel_new_catalog_notes ;
205 END LOOP;
206 CLOSE cur_sel_old_catalog_notes ;
207 EXCEPTION
208 WHEN OTHERS THEN
209 RETCODE:=2;
210 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
211 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
212 END catalog_rollover;
213 END Igs_Ps_Catalog_Rollover;