1 PACKAGE BODY IGI_SIA AS
2 -- $Header: igisiaab.pls 120.7.12000000.1 2007/09/12 11:47:09 mbremkum ship $
3 p_return_message VARCHAR2(240);
4
5 PROCEDURE insert_sec_hold
6 ( p_InvoiceId NUMBER
7 , p_LastUpdatedBy NUMBER
8 ) IS
9 v_org_id number(15,0);
10 BEGIN
11 p_return_message := IGI_GEN.GET_LOOKUP_MEANING('IGI_SIA_AWAITING');
12
13 /* bug # 5905278 start R12 uptake of SIA - query to fetch the org_id */
14 begin
15 select org_id
16 into v_org_id
17 from ap_invoices
18 where invoice_id = p_InvoiceId;
19 exception
20 when others then
21 null;
22 end;
23 /* bug # 5905278 end */
24
25 INSERT INTO AP_HOLDS
26 ( INVOICE_ID
27 , HOLD_LOOKUP_CODE
28 , LAST_UPDATE_DATE
29 , LAST_UPDATED_BY
30 , HELD_BY
31 , HOLD_DATE
32 , HOLD_REASON
33 , CREATION_DATE
34 , CREATED_BY
35 , HOLD_ID /* added for bug # 5905278 R12 Uptake of SIA */
36 , ORG_ID /* added for bug # 5905278 R12 Uptake of SIA */
37 )
38 SELECT
39 p_InvoiceId
40 , 'AWAIT_SEC_APP'
41 , sysdate
42 , p_LastUpdatedBy
43 , p_LastUpdatedBy
44 , sysdate
45 , p_return_message
46 , sysdate
47 , p_LastUpdatedBy
48 , AP_HOLDS_S.nextval /* added for bug # 5905278 R12 Uptake of SIA */
49 , v_org_id /* added for bug # 5905278 R12 Uptake of SIA */
50 FROM sys.dual
51 WHERE NOT EXISTS ( SELECT 1
52 FROM ap_holds_all
53 WHERE invoice_id = p_InvoiceId
54 AND hold_lookup_code in ( 'AWAIT_SEC_APP',
55 'AWAIT_PAY_APP')
56 AND release_lookup_code is NULL
57 );
58
59 EXCEPTION
60 WHEN OTHERS THEN null;
61 END;
62 --
63 PROCEDURE release_holds
64 ( p_InvoiceId NUMBER
65 , p_LastUpdatedBy NUMBER
66 ) IS
67 --
68 BEGIN
69
70 UPDATE AP_HOLDS
71 SET RELEASE_LOOKUP_CODE = 'MOD_RELEASE'
72 , RELEASE_REASON = 'Invoice Modified'
73 , LAST_UPDATE_DATE = sysdate
74 , LAST_UPDATED_BY = p_LastUpdatedBy
75 WHERE invoice_id = p_InvoiceId
76 AND hold_lookup_code IN
77 ('AWAIT_SEC_APP', 'AWAIT_PAY_APP')
78 AND release_lookup_code is null;
79 END;
80 --
81
82 PROCEDURE SET_INVOICE_ID
83 ( p_inv_id NUMBER
84 , p_upd_by NUMBER
85 , p_status NUMBER
86 ) IS
87 --
88 BEGIN
89 l_TableRow := l_TableRow + 1;
90 l_InvoiceIdTable(l_TableRow) := p_inv_id;
91 l_UpdatedByTable(l_TableRow) := p_upd_by;
92 l_StatusTable(l_TableRow) := p_status;
93 END;
94 --
95 PROCEDURE PROCESS_INVOICE_HOLDS (p_inv_id NUMBER,
96 p_upd_by NUMBER) IS
97 --
98 -- This process checks following cases
99 -- 1. Invoice is approved by core functionality no holds placed. i.e. a new invoice
100 -- 2. Invoice was amended after secondary hold was placed and is approved by core functionality
101 -- 3. Invoice was amended after secondary hold was release and was awaiting payment hold release
102 -- and is approved by core functionality.
103 -- NOTE : Approved by core functionality means approval package has run successfully and has not
104 -- placed any non-SIA holds.
105 --
106 -- Added cancellation functionality, check sum(amount) = 0 for all dist. lines
107
108 CURSOR c IS
109 Select 1
110 From AP_INVOICE_DISTRIBUTIONS_ALL
111 Where Invoice_id = p_inv_id
112 AND NVL(match_status_flag,'N') <> 'A';
113
114 CURSOR c1(p_hold VARCHAR2) IS
115 Select hold_lookup_code
116 From AP_HOLDS
117 Where invoice_id = p_inv_id
118 And hold_lookup_code = p_hold
119 And release_lookup_code is null;
120
121 CURSOR c2 IS
122 Select sum(nvl(amount,0))
123 From AP_INVOICE_DISTRIBUTIONS_ALL
124 Where Invoice_id = p_inv_id;
125
126 CURSOR c3 IS
127 Select hold_lookup_code
128 From AP_HOLDS
129 Where invoice_id = p_inv_id
130 And hold_lookup_code = 'AWAIT_PAY_APP';
131
132 CURSOR c4 IS
133 Select COUNT(1)
134 From AP_INVOICE_DISTRIBUTIONS_ALL
135 Where Invoice_id = p_inv_id
136 and reversal_flag = 'Y';
137
138 l_hold_lookup_code AP_HOLDS.hold_lookup_code%TYPE;
139 l_count NUMBER;
140 l_sum NUMBER;
141 l_reverse_flag_cnt NUMBER;
142
143 CURSOR cur_get_core_hold_count IS
144 Select COUNT(1)
145 From AP_HOLDS
146 Where invoice_id = p_inv_id
147 And hold_lookup_code <> 'AWAIT_SEC_APP'
148 And Release_Lookup_code is Null;
149
150 l_cnt NUMBER;
151
152 BEGIN
153 --Initialized inside BEGIN because of GSCC Standard - File.Sql.35
154
155 l_hold_lookup_code := NULL;
156 l_reverse_flag_cnt := 0;
157 l_count := 0;
158 l_sum := 0;
159
160 l_cnt := 0;
161 --
162 -- Check if any distribution line not approved by core functionality i.e approval package
163 --
164 OPEN c;
165 FETCH c INTO l_count;
166 IF c%NOTFOUND THEN
167 l_count := 0;
168 END IF;
169 CLOSE c;
170
171 -- Bug 3409394 Start (1) --
172
173 OPEN cur_get_core_hold_count;
174 FETCH cur_get_core_hold_count INTO l_cnt;
175 close cur_get_core_hold_count;
176
177 -- Bug 3409394 End (1) --
178
179
180 IF l_count = 0 THEN
181 --
182 -- Check if the invoice is cancelled. i.e. Total Distribution amount = 0;
183 -- Bug 3409394
184 -- To truely check that the invoice is cancelled, you will need to check that
185 -- that invoice dist. add to zero, but also need check that the reversal_flag
186 -- is 'Y', otherwise it may be a pre-payment which has reversed the charges
187 -- in the distribution lines. There are many methods for checking invoice is
188 -- truely cancelled, but the easiest method is checking the reversal_flag, as
189 -- it does get set in the 'ap_cancel_pkg', for a cancelled invoice.
190 --
191 OPEN c2;
192 FETCH c2 INTO l_sum;
193 CLOSE c2;
194
195 -- Bug 3671954 Start (1) --
196 OPEN c4;
197 FETCH c4 INTO l_reverse_flag_cnt;
198 CLOSE c4;
199 -- Bug 3671954 End (1) --
200 --
201 -- If Invoice is not cancelled
202 --
203 IF l_sum <> 0 OR l_reverse_flag_cnt = 0 THEN -- Bug 3671954
204
205 --
206 -- If no such distribution line i.e. all are approved by approval package
207 -- check if there is any existing secondary hold , ignore any other holds
208 -- placed by core functionality . In case of amendment a Secondary hold may
209 -- already exist.
210 --
211
212
213 IF l_cnt = 0 then -- Bug 3409394 Start (2) Only IF Condition
214
215 OPEN c1('AWAIT_SEC_APP');
216 FETCH c1 INTO l_hold_lookup_code;
217 IF c1%NOTFOUND THEN
218 l_hold_lookup_code := null;
219 END IF;
220 CLOSE c1;
221 --
222 -- If no hold exists then place secondary hold : case 1
223 --
224 IF l_hold_lookup_code is NULL THEN
225 insert_sec_hold(p_inv_id,p_upd_by);
226 ELSIF l_hold_lookup_code = 'AWAIT_PAY_APP' then
227 -- if payment hold exists then Release Payment hold and place sec hold case 3
228 release_holds(p_inv_id,p_upd_by);
229 insert_sec_hold(p_inv_id,p_upd_by);
230 END IF;
231 END IF; -- If invoice is cancelled i.e sum = 0 then handle in trigger on
232 -- AP_INVOICES_ALL calling REVERSE_HOLDS.
233
234 END IF; -- Bug 3409394 Start (2) Only END IF Condition
235
236 -- if only secondary exists or any other hold exists don't do anything
237 ELSE
238 -- If there is an unapproved distribution line then check if a payment hold exists i.e. if invoice
239 -- was awaiting payment approval. release it and place secondary approval hold.
240 --
241
242
243 OPEN c1('AWAIT_PAY_APP');
244 FETCH c1 INTO l_hold_lookup_code;
245 IF c1%NOTFOUND THEN
246 l_hold_lookup_code := null;
247 END IF;
248 CLOSE c1;
249 IF l_hold_lookup_code = 'AWAIT_PAY_APP' THEN
250 release_holds(p_inv_id,p_upd_by);
251 insert_sec_hold(p_inv_id,p_upd_by);
252 END IF;
253
254
255 -- else donot release any other hold including secondary hold.
256 END IF;
257
258 END;
259
260 PROCEDURE REVERSE_HOLDS (p_inv_id NUMBER,
261 p_upd_by NUMBER) IS
262 --
263 -- This procedure releases PAYMENT hold but places SECONDARY hold, this is
264 -- required if an invoice is cancelled
265 -- Release SECONDARY HOLD if invoice is cancelled. Bug - 1346321
266 -- added afterbug 1346321- all holds are release on cancellation
267 -- as cancellation adds more distribution lines i.e. same functionaliy as
268 -- amendment.
269 CURSOR c1 IS
270 Select hold_lookup_code
271 From AP_HOLDS
272 Where invoice_id = p_inv_id
273 And hold_lookup_code = 'AWAIT_PAY_APP';
274
275 CURSOR c2 IS
276 Select hold_lookup_code
277 From AP_HOLDS
278 Where invoice_id = p_inv_id
279 And hold_lookup_code = 'AWAIT_SEC_APP'
280 AND release_lookup_code = 'SEC_APP';
281
282 l_hold_lookup_code AP_HOLDS.hold_lookup_code%TYPE;
283
284 BEGIN
285 --Intialized variable inside BEGIN because of GSCC Standard - File.Sql.35
286 l_hold_lookup_code := NULL;
287
288 IF FND_PROFILE.VALUE('IGI_SIA_PAYMENT_APP') = 'Y' THEN
289 OPEN c1;
290 FETCH c1 INTO l_hold_lookup_code;
291 IF c1%FOUND THEN
292 insert_sec_hold(p_inv_id,p_upd_by);
293 END IF;
294 CLOSE c1;
295 ELSE
296 OPEN c2;
297 FETCH c2 INTO l_hold_lookup_code;
298 IF c2%FOUND THEN
299 insert_sec_hold(p_inv_id,p_upd_by);
300 END IF;
301 CLOSE c2;
302 END IF;
303 END;
304
305 PROCEDURE PROCESS_HOLDS
306 IS
307 l_Var NUMBER(15);
308 p_invoice_Id NUMBER;
309 BEGIN
310 --Intialized variable inside BEGIN because of GSCC Standard - File.Sql.35
311 l_var := 0;
312 p_invoice_id := 0;
313
314 FOR i in 1..l_TableRow
315 LOOP
316 l_Var := l_Var + 1;
317 IF l_StatusTable(l_var) = 0 THEN
318 IF p_invoice_id <> l_InvoiceIdTable(l_var) THEN
319 IGI_SIA.PROCESS_INVOICE_HOLDS
320 ( l_InvoiceIdTable(l_Var)
321 , l_UpdatedByTable(l_Var)
322 );
323 p_invoice_id := l_InvoiceIdTable(l_Var);
324 END IF;
325 l_StatusTable(l_var):= 1;
326 END IF;
327 END LOOP;
328 l_Var := 0;
329 END;
330
331 PROCEDURE RELEASE_HOLDS
332 IS
333 l_Var NUMBER(15);
334 p_invoice_Id NUMBER;
335 BEGIN
336 --Intialized variable inside BEGIN because of GSCC Standard - File.Sql.35
337 l_var := 0;
338 p_invoice_id := 0;
339
340 FOR i in 1..l_TableRow
341 LOOP
342 l_Var := l_Var + 1;
343 IF l_StatusTable(l_var) = 0 THEN
344 IF p_invoice_id <> l_InvoiceIdTable(l_var) THEN
345 IGI_SIA.release_holds
346 ( l_InvoiceIdTable(l_Var)
347 , l_UpdatedByTable(l_Var)
348 );
349 p_invoice_id := l_InvoiceIdTable(l_Var);
350 END IF;
351 l_StatusTable(l_var):= 1;
352 END IF;
353 END LOOP;
354 l_Var := 0;
355 END;
356 END; -- package body
[Home] [Help]