Solved

Company Error in Custom PL/SQL Block on FA_OBJECT

  • 24 January 2023
  • 3 replies
  • 170 views

Badge +2

Hello community experts,

 

I have a custom block on frmObject_Ext that works when I am running manually on PL/SQL developer, however is not working in the IFS Apps. I am receiving below error. &COMPANY is not fetched from user window. 

 

 

This is my custom block

DECLARE
company_ VARCHAR2(32000) := &COMPANY;
object_ VARCHAR2(32000) := &OBJECT_ID;
code_a_ VARCHAR2(32000) := &ACCOUNT;
invoice_no_ VARCHAR2(32000) := NULL;
amount_ NUMBER := NULL;
old_info_ VARCHAR2(32000) := NULL;
old_objid_ VARCHAR2(32000) := NULL;
old_objversion_ VARCHAR2(32000) := NULL;
new_info_ VARCHAR2(32000) := NULL;
new_objid_ VARCHAR2(32000) := NULL;
new_objversion_ VARCHAR2(32000) := NULL;
attr_ VARCHAR2(32000) := NULL;
action_ VARCHAR2(32000) := 'DO';

CURSOR c1 IS
select p.invoice_no, sum(p.dom_amount)
from ifsapp.MAN_SUPP_INVOICE_POSTINGS p
where company = company_
and code_i = object_
and code_a = code_a_
and ifsapp.invoice_api.get_objstate(p.company, p.INVOICE_ID) != 'Cancelled'
group by p.invoice_no;

CURSOR c2 IS
select fa.objid, fa.objversion
from ifsapp.FA_OBJECT_PROPERTY fa
where fa.company = company_
and fa.object_id = object_
and fa.property_code = 'FA_INVOICE';

BEGIN

OPEN c2;
LOOP
FETCH c2
INTO old_objid_, old_objversion_;
EXIT WHEN c2%NOTFOUND OR c2%NOTFOUND IS NULL;

ifsapp.FA_OBJECT_PROPERTY_API.REMOVE__(old_info_,
old_objid_,
old_objversion_,
action_);
end loop;
CLOSE c2;

OPEN c1;
LOOP
FETCH c1
INTO invoice_no_, amount_;
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;

client_sys.Clear_Attr(attr_);
client_sys.Add_To_Attr('COMPANY', company_, attr_);
client_sys.Add_To_Attr('OBJECT_ID', object_, attr_);
client_sys.Add_To_Attr('PROPERTY_CODE', 'FA_INVOICE', attr_);
client_sys.Add_To_Attr('VALUE_STRING', 'Invoice No: ' || invoice_no_, attr_);
client_sys.Add_To_Attr('REFERENCE_ID', amount_, attr_);

ifsapp.FA_OBJECT_PROPERTY_API.NEW__(new_info_,
new_objid_,
new_objversion_,
attr_,
action_);

END LOOP;
CLOSE c1;
END;

I debugged the error and I can see that company is not there. Any idea what could be reason?

I am on IFS10 Update 14

Thank you

 

-- Context: frmObject_Ext

DECLARE
-- p0 -> __g_Bind.s[0]
p0_ VARCHAR2(32000) := 'FA26000005';

-- p1 -> __g_Bind.s[1]
p1_ VARCHAR2(32000) := '17400000';

BEGIN
IFSAPP.Log_SYS.Init_Debug_Session_('en');

DECLARE
company_ VARCHAR2(32000) := &COMPANY;

object_ VARCHAR2(32000) := p0_ ;

code_a_ VARCHAR2(32000) := p1_ ;

invoice_no_ VARCHAR2(32000) := NULL;

amount_ NUMBER := NULL;

old_info_ VARCHAR2(32000) := NULL;

old_objid_ VARCHAR2(32000) := NULL;

old_objversion_ VARCHAR2(32000) := NULL;

new_info_ VARCHAR2(32000) := NULL;

new_objid_ VARCHAR2(32000) := NULL;

new_objversion_ VARCHAR2(32000) := NULL;

attr_ VARCHAR2(32000) := NULL;

action_ VARCHAR2(32000) := 'DO';


CURSOR c1 IS
select p.invoice_no, sum(p.dom_amount)
from ifsapp.MAN_SUPP_INVOICE_POSTINGS p
where company = company_
and code_i = object_
and code_a = code_a_
and ifsapp.invoice_api.get_objstate(p.company, p.INVOICE_ID) != 'Cancelled'
group by p.invoice_no;


CURSOR c2 IS
select fa.objid, fa.objversion
from ifsapp.FA_OBJECT_PROPERTY fa
where fa.company = company_
and fa.object_id = object_
and fa.property_code = 'FA_INVOICE';


BEGIN


OPEN c2;

LOOP
FETCH c2
INTO old_objid_, old_objversion_;

EXIT WHEN c2%NOTFOUND OR c2%NOTFOUND IS NULL;


ifsapp.FA_OBJECT_PROPERTY_API.REMOVE__(old_info_,
old_objid_,
old_objversion_,
action_);

end loop;

CLOSE c2;


OPEN c1;

LOOP
FETCH c1
INTO invoice_no_, amount_;

EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;


client_sys.Clear_Attr(attr_);

client_sys.Add_To_Attr('COMPANY', company_, attr_);

client_sys.Add_To_Attr('OBJECT_ID', object_, attr_);

client_sys.Add_To_Attr('PROPERTY_CODE', 'FA_INVOICE', attr_);

client_sys.Add_To_Attr('VALUE_STRING', 'Invoice No: ' || invoice_no_, attr_);

client_sys.Add_To_Attr('REFERENCE_ID', amount_, attr_);


ifsapp.FA_OBJECT_PROPERTY_API.NEW__(new_info_,
new_objid_,
new_objversion_,
attr_,
action_);


END LOOP;

CLOSE c1;

END;

commit;


WHEN Error_SYS.Err_Security_Checkpoint THEN
raise;



raise;

----------------------------------
---Dbms_Output Section---
----------------------------------
Dbms_Output.Put_Line('p0_ -> __g_Bind.s[0]');
Dbms_Output.Put_Line(p0_);
Dbms_Output.New_Line;

Dbms_Output.Put_Line('p1_ -> __g_Bind.s[1]');
Dbms_Output.Put_Line(p1_);
----------------------------------

END;

 

icon

Best answer by Tomas Ruderfelt 26 January 2023, 08:10

View original

3 replies

Userlevel 3
Badge +10

That would seem to indicate that the window you are on does not have a column for COMPANY in the main view. 

Where are you entering this block?

If you want the system default Company value I believe you need to use #COMPANY. 

Userlevel 7
Badge +18

Yes it seems like it can’t find the column for &COMPANY.

I replaced &COMPANY with :i_hWndFrame.frmObject.i_sCompany and then I get the company value.

I don’t have an Ext layer in that window so you might try with :i_hWndFrame.frmObject_Ext.i_sCompany

or :i_hWndFrame.frmObject.i_sCompany

Badge +2

Hi @mwilson  and @Tomas Ruderfelt 

 

Actually company is available on screen and on view in DB. But looks like it does not fetch properly. 

I tried #COMPANY but it did not work for me. Maybe I entered it wrongly but I need company from the fixed asset not the user default company. Thank you for proposal.

 

This ‘:i_hWndFrame.frmObject.i_sCompany’ worked for me and it fetches the values now.

 

Thank you again

 

 

Reply