Question

NCR report. Inserting data error.

  • 20 May 2021
  • 3 replies
  • 138 views
D
Rank
Userlevel 3
Badge +5

Hi all,

Another one thats driving me up the wall.

Apps10

Requirement is to insert sysdate + 14 into target completion date when a user creates an NCR.

First stop was writing a custom event to populate the field.

I’ve made so many code alterations i cant even remember if this is the correct one now so use it as an example.

DECLARE
pragma autonomous_transaction;

  INFO_ VARCHAR2(200) := NULL;
  OBJID_ VARCHAR2(200) := NULL;
  OBJVERSION_ VARCHAR2(200) := NULL;
  ATTR_ VARCHAR2(200) := NULL;
  ACTION_ VARCHAR2(200) := 'DO';
  DATE_ VARCHAR2(200) ;


CURSOR GET_DATE_ IS   
select to_char((sysdate + 14),'DD/MM/YYYY')
from dual;


BEGIN
    OPEN GET_DATE_;
    FETCH GET_DATE_ INTO  DATE_;
    CLOSE GET_DATE_;

       Client_SYS.Clear_Attr(attr_);
    Client_SYS.Add_To_Attr('TARGET_COMPLETION_DATE',DATE_, attr_);

     NON_CONFORMANCE_REPORT_API.modify__ ( INFO_,OBJID_,OBJVERSION_,ATTR_,'DO');

END;

If i dont add the pragma then i get the mutating trigger etc.

If i add the pragma i get.

 

 

So figured to get around this i would write a package and run it as a background job but low and behold the error is returned once again and it fails execution.

 

 

I havent used NCR report before so im assuming im missing something important.

 

Any help would be appreciated!

 

Thanks

This topic has been closed for comments

3 replies

Shardha Weeratunga
Rank
Userlevel 5
Badge +6

Hi @DBowden,

 

Please try adjusting your code with the suggestion below.

1. The Date_ variable should on type DATE, not VARCHAR2(200);

2. When you call the Modify__() method of the NCR, the values for objid_, objversion_ should also be passed with correct values, in order to identify the NCR to be modified. You have set values of objid_ and objversion_ to NULL in the  begining of your PLSQL Block so, the Modify__() method will not be able to identify the NCR to be modified.

Please have a look at the below code, of the Modify method..

 

ELSIF (action_ = 'DO') THEN

         oldrec_ := Lock_By_Id___(objid_, objversion_);

         newrec_ := oldrec_;

         Unpack___(newrec_, indrec_, attr_);

         Check_Update___(oldrec_, newrec_, indrec_, attr_);

         Update___(objid_, oldrec_, newrec_, attr_, objversion_);

      END IF;

 

The value for objid_ and objversion_ are needed to lock and fetch the database record before the update. As the values passed in for objid_ and objversion_ are NULL, the database will not be able to find a record to be updated.

Please have a look at the Non_Conformance_Report_API.Get_Id_Version_By_Keys___() and get an idea on how to obtain the values for objid_ and objversion_ using ncr_no.

 

 

ShaR
D
Rank
Userlevel 3
Badge +5

Hi Shardha,

 

Thanks for the reply.

Could you please elaborate on

Please have a look at the Non_Conformance_Report_API.Get_Id_Version_By_Keys___() and get an idea on how to obtain the values for objid_ and objversion_ using ncr_no.

 

I have looked through the package and i cannot see this to get objid_ and objversion_

 

 

 

Thanks

 

 

Shardha Weeratunga
Rank
Userlevel 5
Badge +6

Hi @DBowden ,

Sorry for the late reply. I’m not sure why you are not able to see the method, Non_Conformance_Report_API.Get_Id_Version_By_Keys___() in the navigator. You can find the method content below.

 

Method: Non_Conformance_Report_API.Get_Id_Version_By_Keys___()

 

ShaR
Terms & ConditionsCookie settings