1 PACKAGE BODY igr_gen_002 AS
2 /* $Header: IGSRT07B.pls 120.0 2005/06/01 23:22:07 appldev noship $ */
3
4 /****************************************************************************
5 Created By : nsinha
6 Date Created On : August 27, 2003
7 Purpose : 2664699
8
9 Change History
10 Who When What
11 hreddych 26-may-2003 Capture Event Campaign
12 Added the x_source_promotion_id in the call to
13 igs_rc_inquiry_pkg
14 jchin 14-Feb-05 Modified package for IGR pseudo product
15
16 (reverse chronological order - newest change first)
17 *****************************************************************************/
18 PROCEDURE Get_latest_batch_id (
19 p_batch_id OUT NOCOPY NUMBER )
20 IS
21 /*************************************************************
22 Created By : Navin Sinha
23 Date Created By : Tuesday, August 12, 2003
24 Purpose : Admin_Quick_Entry, Enh#: 2885789
25 Know limitations, enhancements or remarks
26 Change History
27 Who When What
28 (reverse chronological order - newest change first)
29 ***************************************************************/
30
31 CURSOR cur_max_batch_id IS
32 SELECT MAX(batch_id)
33 FROM igs_ad_imp_batch_det
34 WHERE created_by = FND_GLOBAL.USER_ID;
35 l_max_batch_id NUMBER;
36
37 CURSOR cur_chk_batch (cp_batch_id igs_ad_interface_ctl.batch_id%TYPE) IS
38 SELECT batch_id
39 FROM igs_ad_interface_ctl
40 WHERE batch_id = cp_batch_id;
41 rec_chk_batch cur_chk_batch%ROWTYPE;
42 BEGIN
43
44 -- If any records are available for that user in the interface table then get the max batch_id.
45 OPEN cur_max_batch_id;
46 FETCH cur_max_batch_id INTO l_max_batch_id ;
47 IF ( NVL(l_max_batch_id,0) > 0 ) THEN
48 -- For this batch_id check in igs_ad_interface_ctl if there is a record
49 OPEN cur_chk_batch(l_max_batch_id) ;
50 FETCH cur_chk_batch INTO rec_chk_batch ;
51 IF (cur_chk_batch%FOUND) THEN
52 p_batch_id := 0; -- created new Batch ID.
53 ELSE -- No record found in igs_ad_interface_ctl for this batch_id
54 p_batch_id := l_max_batch_id; -- Return the existing MAX Batch ID.
55 END IF;
56 CLOSE cur_chk_batch ;
57 ELSE -- No record is available for that user in the interface table
58 p_batch_id := 0; -- created new Batch ID.
59 END IF;
60 CLOSE cur_max_batch_id ;
61 END Get_latest_batch_id;
62
63 PROCEDURE Get_batch_id (
64 p_batch_id OUT NOCOPY NUMBER )
65 IS
66 /*************************************************************
67 Created By : Navin Sinha
68 Date Created By : Tuesday, August 12, 2003
69 Purpose : Admin_Quick_Entry, Enh#: 2885789
70 Know limitations, enhancements or remarks
71 Change History
72 Who When What
73 (reverse chronological order - newest change first)
74 ***************************************************************/
75 -- Get the Batch ID for admission application import process
76 CURSOR c_bat_id IS
77 SELECT igs_ad_interface_batch_id_s.NEXTVAL
78 FROM dual;
79 l_imp_batch_id NUMBER;
80
81 l_batch_desc VARCHAR2(2000);
82 l_user_id NUMBER := FND_GLOBAL.USER_ID;
83
84 -- Get the user name
85 CURSOR cur_user_name IS
86 SELECT SUBSTR(user_name,1,20)
87 FROM fnd_user
88 WHERE USER_ID = FND_GLOBAL.USER_ID;
89 l_user_name VARCHAR2(50);
90
91 l_create_new_batch BOOLEAN := FALSE;
92 BEGIN
93 -- If any records are available for that user in the interface table then get the max batch_id.
94 igr_gen_002.Get_latest_batch_id (p_batch_id);
95 IF p_batch_id = 0 THEN -- create a new batch
96 l_imp_batch_id := NULL ;
97
98 -- Get the Batch ID for admission application import process
99 OPEN c_bat_id;
100 FETCH c_bat_id INTO l_imp_batch_id;
101 CLOSE c_bat_id;
102
103 -- Get the user name
104 OPEN cur_user_name;
105 FETCH cur_user_name INTO l_user_name;
106 CLOSE cur_user_name;
107
108 l_batch_desc := 'Quick Entry Batch Created For '||Substr(l_user_name,1,20) ||' on ' || sysdate;
109
110 INSERT INTO igs_ad_imp_batch_det (
111 batch_id,
112 batch_desc,
113 created_by,
114 creation_date,
115 last_updated_by,
116 last_update_date,
117 last_update_login,
118 request_id,
119 program_application_id,
120 program_update_date,
121 program_id)
122 VALUES ( l_imp_batch_id,
123 l_batch_desc,
124 fnd_global.user_id,
125 SYSDATE,
126 fnd_global.user_id,
127 SYSDATE,
128 NULL,
129 NULL,
130 NULL,
131 NULL,
132 NULL
133 );
134 --hreddych Added this Since this record needs to be commited to be visible from other sessions
135 COMMIT;
136 p_batch_id := l_imp_batch_id; -- Return the newly created Batch ID.
137 END IF;
138 END Get_batch_id;
139
140 PROCEDURE Delete_Inquiry_Dtls (
141 p_interface_id IN NUMBER )
142 IS
143 /*************************************************************
144 Created By : Navin Sinha
145 Date Created By : Tuesday, August 12, 2003
146 Purpose : Admin_Quick_Entry, Enh#: 2885789
147 If user clicks on Delete, the same record will be deleted from interface tables.
148 Know limitations, enhancements or remarks
149 Change History
150 Who When What
151 (reverse chronological order - newest change first)
152 ***************************************************************/
153
154 BEGIN
155 -- All The records in the below interface tables
156 -- which correspond to that Inquiry should be deleted .
157 DELETE igs_ad_interface_all WHERE interface_id = p_interface_id;
158 DELETE igs_ad_addr_int_all WHERE interface_id = p_interface_id;
159
160 DELETE igr_i_lines_int WHERE interface_inq_appl_id IN
161 (SELECT interface_inq_appl_id FROM igr_i_appl_int WHERE interface_id = p_interface_id);
162 DELETE igr_i_appl_int WHERE interface_id = p_interface_id;
163
164 DELETE igs_ad_acadhis_int_all WHERE interface_id = p_interface_id;
165 DELETE igs_ad_contacts_int_all WHERE interface_id = p_interface_id;
166 DELETE igs_pe_race_int WHERE interface_id = p_interface_id;
167 DELETE igs_ad_stat_int WHERE interface_id = p_interface_id;
168 COMMIT;
169 EXCEPTION
170 WHEN OTHERS THEN
171 Fnd_Message.Set_Name ('FND', 'IGS_GE_UNHANDLED_EXCEPTION');
172 igs_ge_msg_stack.add;
173 App_Exception.Raise_Exception;
174 END Delete_Inquiry_Dtls;
175
176 END igr_gen_002;
[Home] [Help]