igi_sia_ap_invoices_all_t1
AFTER UPDATE ON AP_INVOICES_ALL
FOR EACH ROW
Type
AFTER EACH ROW
Event
UPDATE
Column
When
Referencing
REFERENCING NEW AS NEW OLD AS OLD
Body
DECLARE
Cursor cur_get_core_hold_count(p_inv_id ap_holds.invoice_id%type)
is
select count(1)
from ap_holds
where invoice_id = p_inv_id
and hold_lookup_code <>'AWAIT_SEC_APP'
And release_lookup_code is null;
Cursor cur_get_hold(p_inv_id ap_holds.invoice_id%type)
is
Select hold_lookup_code
From AP_HOLDS
Where invoice_id = p_inv_id
And release_lookup_code is null;
Cursor cur_get_SIA_hold_count(p_inv_id ap_holds.invoice_id%type)
is
select count(1)
from ap_holds
where invoice_id = p_inv_id
and hold_lookup_code = 'AWAIT_SEC_APP'
And release_lookup_code is null;
l_cnt number;
l_SapStatusFlag VARCHAR2(1);
l_SapErrorNum NUMBER;
l_count number;
l_hold_lookup_code AP_HOLDS.hold_lookup_code%TYPE := NULL;
BEGIN
--Fixed GSCC Warnings
l_cnt := 0;
l_count := 0;
IGI_GEN.get_option_status('SIA'
,l_SapStatusFlag
,l_SapErrorNum);
IF (l_SapStatusFlag='Y' ) and (:new.cancelled_date is not null)
and (:old.cancelled_date is NULL) then
IGI_SIA.REVERSE_HOLDS(:new.invoice_id, :new.last_updated_by);
END IF;
-- Bug 3409394 Start --
IF (l_SapStatusFlag='Y') THEN
OPEN cur_get_core_hold_count(:new.invoice_id);
FETCH cur_get_core_hold_count into l_count;
if cur_get_core_hold_count%NOTFOUND then
l_count := 0;
end if;
CLOSE cur_get_core_hold_count;
if l_count <> 0 then
OPEN cur_get_hold(:new.invoice_id);
LOOP
FETCH cur_get_hold into l_hold_lookup_code;
EXIT when cur_get_hold%NOTFOUND;
if cur_get_hold%FOUND then
if l_hold_lookup_code = 'AWAIT_SEC_APP' then
DELETE from AP_HOLDS_ALL
WHERE invoice_id = :OLD.INVOICE_ID
AND Hold_Lookup_Code ='AWAIT_SEC_APP'
AND Release_Lookup_Code IS NULL;
end if;
end if;
END LOOP;
CLOSE cur_get_hold;
elsif l_count =0 then
OPEN cur_get_SIA_hold_count(:new.invoice_id);
FETCH cur_get_SIA_hold_count into l_cnt;
CLOSE cur_get_SIA_hold_count;
if l_cnt <> 0 then
IGI_SIA.SET_INVOICE_ID (:NEW.INVOICE_ID,:NEW.LAST_UPDATED_BY,0);
IGI_SIA.PROCESS_HOLDS;
end if;
end if;
END IF;
-- Bug 3409394 End --
END;