1 PACKAGE BODY GR_DISCLOSURE_PKG AS
2 /*$Header: GRHIDISB.pls 115.7 2002/10/25 18:11:12 mgrosser ship $*/
3
4
5 PROCEDURE Check_Primary_Key
6 /* p_document_code is the document code to check.
7 ** p_called_by_form is 'T' if called by a form or 'F' if not.
8 ** x_rowid is the row id of the record if found.
9 ** x_key_exists is 'T' is the record is found, 'F' if not.
10 */
11 (p_disclosure_code IN VARCHAR2,
12 p_called_by_form IN VARCHAR2,
13 x_rowid OUT NOCOPY VARCHAR2,
14 x_key_exists OUT NOCOPY VARCHAR2)
15 IS
16 /* Alphanumeric variables */
17
18 L_MSG_DATA VARCHAR2(80);
19
20 /* Declare any variables and the cursor */
21
22
23 CURSOR c_get_disclosure_rowid
24 IS
25 SELECT dc.rowid
26 FROM gr_disclosures dc
27 WHERE dc.disclosure_code = p_disclosure_code;
28 DisclosureRecord c_get_disclosure_rowid%ROWTYPE;
29
30 BEGIN
31
32 x_key_exists := 'F';
33 l_msg_data := p_disclosure_code;
34 OPEN c_get_disclosure_rowid;
35 FETCH c_get_disclosure_rowid INTO DisclosureRecord;
36 IF c_get_disclosure_rowid%FOUND THEN
37 x_key_exists := 'T';
38 x_rowid := DisclosureRecord.rowid;
39 ELSE
40 x_key_exists := 'F';
41 END IF;
42 CLOSE c_get_disclosure_rowid;
43
44 EXCEPTION
45
46 WHEN Others THEN
47 l_msg_data := SUBSTR(SQLERRM, 1, 200);
48 FND_MESSAGE.SET_NAME('GR',
49 'GR_UNEXPECTED_ERROR');
50 FND_MESSAGE.SET_TOKEN('TEXT',
51 l_msg_data,
52 FALSE);
53 IF FND_API.To_Boolean(p_called_by_form) THEN
54 APP_EXCEPTION.Raise_Exception;
55 END IF;
56
57 END Check_Primary_Key;
58
59
60 PROCEDURE Check_References
61 (delete_disclosure gr_disclosures.disclosure_code%TYPE)
62
63 IS
64
65 /* Alpha Variables */
66 L_CODE_BLOCK VARCHAR2(2000);
67 L_TABLE_NAME VARCHAR2(80);
68
69
70 /* Numeric Variables */
71 L_ORACLE_ERROR NUMBER;
72 L_COUNT NUMBER;
73
74 /* Exception */
75 ROW_IN_USE_ERROR EXCEPTION;
76
77
78 /* Cursor Declarations */
79 /* Cursor to call disclosure code out of GR_COUNTRY */
80 CURSOR country_disclosure_cursor
81 IS
82 SELECT COUNT(1)
83 FROM gr_country_profiles cp
84 WHERE cp.disclosure_code = delete_disclosure;
85
86 /* Cursor to call disclosure code out of GR_ITEM_DISCLOSURE */
87 CURSOR itemdisc_disclosure_cursor
88 IS
89 SELECT COUNT(1)
90 FROM gr_item_disclosures id
91 WHERE id.disclosure_code = delete_disclosure;
92
93 /* Cursor to call disclosure code out of GR_DOCUMENT_PRINT (The Recipient Disclosure) */
94 CURSOR docprint_rec_disclosure_cursor
95 IS
96 SELECT COUNT(1)
97 FROM gr_document_print dp
98 WHERE dp.disclosure_code_recipient = delete_disclosure;
99
100 /* Cursor to call disclosure code out of GR_DOCUMENT_PRINT (The Country Disclosure) */
101 CURSOR docprint_con_disclosure_cursor
102 IS
103 SELECT COUNT(1)
104 FROM gr_document_print dp
105 WHERE dp.disclosure_code_country = delete_disclosure;
106
107 /* Cursor to call disclosure code out of GR_RECIPIENT_INFO */
108 CURSOR recip_disclosure_cursor
109 IS
110 SELECT COUNT(1)
111 FROM gr_recipient_info ri
112 WHERE ri.disclosure_code = delete_disclosure;
113
114 BEGIN
115
116 l_table_name := 'GR_COUNTRY_PROFILES';
117
118 /*
119 ** Open cursor which counts total records that match the item code to be deleted
120 */
121 OPEN country_disclosure_cursor;
122 FETCH country_disclosure_cursor INTO l_count;
123 CLOSE country_disclosure_cursor;
124 /*
125 ** If the code is not in use in this table then l_count will = 0,
126 ** otherwise an exception will be raised
127 */
128 IF (l_count >= 1) THEN
129 RAISE Row_In_Use_Error;
130 END IF;
131
132 /* Check the GR_ITEM_DISCLOSURE table!!! */
133
134 l_table_name := 'GR_ITEM_DISCLOSURES';
135
136 OPEN itemdisc_disclosure_cursor;
137 FETCH itemdisc_disclosure_cursor INTO l_count;
138 CLOSE itemdisc_disclosure_cursor;
139
140 IF (l_count >= 1) THEN
141 RAISE Row_In_Use_Error;
142 END IF;
143
144 /* Check the Document Print table for both the Recipient and the Country Disclosure Code */
145
146 l_table_name := 'GR_DOCUMENT_PRINT';
147
148 OPEN docprint_rec_disclosure_cursor;
149 FETCH docprint_rec_disclosure_cursor INTO l_count;
150 CLOSE docprint_rec_disclosure_cursor;
151
152 IF (l_count >= 1) THEN
153 RAISE Row_In_Use_Error;
154 END IF;
155
156 OPEN docprint_con_disclosure_cursor;
157 FETCH docprint_con_disclosure_cursor INTO l_count;
158 CLOSE docprint_con_disclosure_cursor;
159
160 IF (l_count >= 1) THEN
161 RAISE Row_In_Use_Error;
162 END IF;
163
164 /* Check the Recipient Info table for the Disclosure Code */
165
166 l_table_name := 'GR_RECIPIENT_INFO';
167
168 OPEN recip_disclosure_cursor;
169 FETCH recip_disclosure_cursor INTO l_count;
170 CLOSE recip_disclosure_cursor;
171
172 IF (l_count >= 1) THEN
173 RAISE Row_In_Use_Error;
174 END IF;
175
176 EXCEPTION
177
178 WHEN Row_In_Use_Error THEN
179 FND_MESSAGE.SET_NAME('GR',
180 'GR_INTEGRITY_HDR');
181 FND_MESSAGE.SET_TOKEN('CODE', delete_disclosure,
182 FALSE);
183 FND_MESSAGE.SET_TOKEN('TEXT',
184 l_table_name,
185 FALSE);
186 APP_EXCEPTION.Raise_Exception;
187
188 WHEN OTHERS THEN
189 l_oracle_error := SQLCODE;
190 --l_code_block := SUBSTR(SQLERRM, 1, 200);
191 l_code_block := l_code_block || ' ' || TO_CHAR(l_oracle_error);
192 FND_MESSAGE.SET_NAME('GR',
193 'GR_UNEXPECTED_ERROR');
194 FND_MESSAGE.SET_TOKEN('TEXT',
195 l_code_block||sqlerrm,
196 FALSE);
197 APP_EXCEPTION.Raise_Exception;
198
199 END Check_References;
200
201 END GR_DISCLOSURE_PKG;
[Home] [Help]