hi Friends,
I have created the custom WebADI to update the data in global_attribute1 ,global_attribute12 fields in ra_customer_trx_all table.
The WebADI is working properly till last month on our test instance.
But suddenly it stopped working.
Data is getting inserted into the custom staging table but PL/SQL API in importer is not getting fired.
If any one can shade some light on this sudden behavior then it will be helpful.
Please look into the images of my WebADI.
PACKAGE BODY:-
create or replace
package body XXST_WEBADI_PKG as
procedure XXST_WEBADI_UPDATE_PRC (p_group_id IN NUMBER)
IS
CURSOR c_validated_record
IS
SELECT *
FROM XXST_SOFTEX_DATA_STG
WHERE GROUP_ID = p_group_id
FOR UPDATE OF ERROR_FLAG, error_msg;
rec c_validated_record%rowtype;
i number := 0;
errCount number := 1;
error_code varchar2(5);
v_count number;
v_code varchar2(2000);
V_ERR_COUNT number;
v_trx_number varchar2(2000);
begin
delete from XXST_SOFTEX_DATA_STG where GROUP_ID != p_group_id;
commit;
for rec1 in c_validated_record loop
select count(INV_NO)
into v_count
from XXST_SOFTEX_DATA_STG
where INV_NO = rec1.INV_NO
and GROUP_ID = p_group_id;
dbms_output.put_line('count:-'||v_count);
if v_count > 1 then
update XXST_SOFTEX_DATA_STG
set ERROR_FLAG = 'Y' , ERROR_MSG = 'Error Due to Duplicate Inv Number :-' ||rec1.INV_NO
where INV_NO = rec1.INV_NO;
-- commit;
elsif v_count = 1 then
update XXST_SOFTEX_DATA_STG
set ERROR_FLAG = 'N', ERROR_MSG = 'Success for ' ||rec1.INV_NO
where INV_NO = rec1.INV_NO;
-- commit;
elsif v_count = 0 then
update XXST_SOFTEX_DATA_STG
set ERROR_FLAG = 'N', ERROR_MSG = 'Success for ' ||rec1.INV_NO
where INV_NO = rec1.INV_NO;
end if;
begin
select TRX_NUMBER
into v_trx_number
from ra_customer_trx_all
where TRX_NUMBER = rec1.INV_NO
and org_id = (select organization_id from hr_organization_units where name = rec1.OPERATING_UNIT);
exception
when others then
update XXST_SOFTEX_DATA_STG
set ERROR_FLAG = 'Y' , ERROR_MSG = 'Error Due to Invalid Inv Number :-' ||rec1.INV_NO
where INV_NO = rec1.INV_NO;
end;
end loop;
commit;
XXST_WEBADI_AR_UPDATE_PRC (p_group_id);
end XXST_WEBADI_UPDATE_PRC;
procedure XXST_WEBADI_AR_UPDATE_PRC (p_group_id IN NUMBER)
IS
CURSOR c_ar_insert_record
IS
SELECT *
FROM XXST_SOFTEX_DATA_STG
WHERE GROUP_ID = p_group_id;
v_error_count number;
begin
for i in c_ar_insert_record loop
select count(INV_NO)
into v_error_count
from XXST_SOFTEX_DATA_STG
where ERROR_FLAG = 'Y'
AND GROUP_ID = p_group_id;
if v_error_count = 0 then
UPDATE ra_customer_trx_all
SET global\_attribute1 = nvl(i.SOFTEX\_NO, (select global\_attribute1 from ra\_customer\_trx\_all WHERE org\_id = (select organization\_id from hr\_organization\_units where name = i.OPERATING\_UNIT)
AND trx\_number = i.INV\_NO)),
global_attribute2 = nvl(i.FIRC_NO, (select global_attribute2 from ra_customer_trx_all WHERE org_id = (select organization_id from hr_organization_units where name = i.OPERATING_UNIT)
AND trx\_number = i.INV\_NO)),
global_attribute3 = nvl(i.FIRC_DATE, (select global_attribute3 from ra_customer_trx_all WHERE org_id = (select organization_id from hr_organization_units where name = i.OPERATING_UNIT)
AND trx\_number = i.INV\_NO)),
global_attribute4 = nvl(i.EDPMS_NUM, (select global_attribute4 from ra_customer_trx_all WHERE org_id = (select organization_id from hr_organization_units where name = i.OPERATING_UNIT)
AND trx\_number = i.INV\_NO)),
global_attribute5 = nvl(i.EDPMS_DATE, (select global_attribute5 from ra_customer_trx_all WHERE org_id = (select organization_id from hr_organization_units where name = i.OPERATING_UNIT)
AND trx\_number = i.INV\_NO))
WHERE org\_id = (select organization\_id from hr\_organization\_units where name = i.OPERATING\_UNIT)
AND trx\_number = i.INV\_NO ;
/*
UPDATE ra_customer_trx_all
SET global\_attribute1 = i.SOFTEX\_NO,
global_attribute2 = i.FIRC_NO,
global\_attribute3 = i.FIRC\_DATE,
global_attribute4 = i.EDPMS_NUM,
global_attribute5 = i.EDPMS_DATE
WHERE org\_id = (select organization\_id from hr\_organization\_units where name = i.OPERATING\_UNIT)
AND trx\_number = i.INV\_NO ;
*/
end if;
end loop;
commit;
end XXST_WEBADI_AR_UPDATE_PRC;
end XXST_WEBADI_PKG;
Regards,
Doifode