Skip to main content
Solved

Company Error in Custom PL/SQL Block on FA_OBJECT

  • January 24, 2023
  • 3 replies
  • 244 views

Forum|alt.badge.img+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;

 

Best answer by Tomas Ruderfelt

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

View original
Did this topic help you find an answer to your question?

Forum|alt.badge.img+10
  • Hero (Customer)
  • January 25, 2023

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. 


Forum|alt.badge.img+20

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


Forum|alt.badge.img+2
  • Do Gooder (Customer)
  • January 26, 2023

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


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings