Looping in PL SQL

 

These are the examples to update table data using loop in PL SQL.

BEGIN FOR I IN (

SELECT A.EMPLID, A.ACAD_PROG

  FROM PS_ACAD_PROG A , ps_UOD_STU_ADV_UPL B

  WHERE ( A.EFFDT =

        (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED

        WHERE A.EMPLID = A_ED.EMPLID

          AND A.ACAD_CAREER = A_ED.ACAD_CAREER

          AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR

          AND A_ED.EFFDT <= SYSDATE)

    AND A.EFFSEQ =

        (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES

        WHERE A.EMPLID = A_ES.EMPLID

          AND A.ACAD_CAREER = A_ES.ACAD_CAREER

          AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR

          AND A.EFFDT = A_ES.EFFDT))

          AND A.EMPLID = B.EMPLID

          AND B.OPRID = 'dar.tech2' ) LOOP

UPDATE  ps_UOD_STU_ADV_UPL D

SET D.acad_prog1 = I.acad_prog

WHERE D.EMPLID = I.EMPLID;

END LOOP;

END;

------------------------------------------------------------------------------------------

BEGIN

    FOR I IN (

        SELECT COUNT(A.EMPLID) AS TOT, A.CLASS_NBR, A.STRM  

        FROM ps_stdnt_enrl A 

        WHERE A.strm = '2241' 

          AND A.STDNT_ENRL_STATUS = 'E' 

        GROUP BY A.CLASS_NBR, A.STRM 

    ) LOOP

        UPDATE PS_CLASS_TBL K  

        SET K.ENRL_TOT = I.TOT 

        WHERE K.strm = I.STRM 

          AND K.class_nbr = I.CLASS_NBR;

    END LOOP;

    COMMIT; -- Commit changes after the loop

EXCEPTION

    WHEN OTHERS THEN

        RAISE; -- Handle exceptions if needed

END;

CI Peoplecode to update Student Name

 /* ===>

This is a dynamically generated PeopleCode template to be used only as a helper

to the application developer.

You need to replace all references to '[*]' OR default values with  references to

PeopleCode variables and/or a Rec.Fields. */

Local File &fileLog;

Local ApiObject &oSession, &oCiPersonalData;

Local ApiObject &oCollNameTypeVwCollection, &oCollNameTypeVw;

Local ApiObject &oCollNamesCollection, &oCollNames;

Function errorHandler()

   Local ApiObject &oPSMessageCollection, &oPSMessage;

   Local number &i;

   Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;

   

   &oPSMessageCollection = &oSession.PSMessages;

   For &i = 1 To &oPSMessageCollection.Count

      &oPSMessage = &oPSMessageCollection.Item(&i);

      &sErrMsgSetNum = &oPSMessage.MessageSetNumber;

      &sErrMsgNum = &oPSMessage.MessageNumber;

      &sErrMsgText = &oPSMessage.Text;

      Error MsgGet(&oPSMessage.MessageSetNumber, &oPSMessage.MessageNumber, &sErrMsgText);

      rem &fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);

   End-For;

   rem ***** Delete the Messages from the collection *****;

   &oPSMessageCollection.DeleteAll();

End-Function;

Function UpdatePersonalData(&EmplId As string, &NameType As string, &EffDate As date, &CountryFormat As string, &LastName As string, &FirstName As string, &MiddleName As string, &SecondLastName As string) Returns boolean

   Local number &WorkAround;

   Local integer &CollCount1, &NameCount;

   

   

   try

      /* Initialize session */

      &oSession = %Session;

      &oSession.PSMessagesMode = 1;

      

      /* Get the Component Interface */

      &oCiPersonalData = &oSession.GetCompIntfc(CompIntfc.CI_PERSONAL_DATA);

      If &oCiPersonalData = Null Then

         errorHandler();

         throw CreateException(0, 0, "GetCompIntfc failed");

      End-If;

      

      /* Set Component Interface Mode */

      &oCiPersonalData.InteractiveMode = True;

      &oCiPersonalData.GetHistoryItems = True;

      &oCiPersonalData.EditHistoryItems = True;

      

      /* Set Component Interface Get/Create Keys */

      &oCiPersonalData.KEYPROP_EMPLID = &EmplId;

      

      /* Execute Get */

      If Not &oCiPersonalData.Get() Then

         errorHandler();

         throw CreateException(0, 0, "Get failed");

      End-If;

      

      /* Set/Get COLL_NAME_TYPE_VW Collection Field Properties */

      &oCollNameTypeVwCollection = &oCiPersonalData.COLL_NAME_TYPE_VW;

      &oCollNameTypeVw = &oCollNameTypeVwCollection.insertitem(&oCollNameTypeVwCollection.count);

      &WorkAround = &oCollNameTypeVw.itemnum;

      &CollCount1 = &oCollNameTypeVwCollection.count;

      &oCollNameTypeVw = &oCollNameTypeVwCollection.Item(&CollCount1);

      &oCollNameTypeVw.KEYPROP_NAME_TYPE = &NameType;

      

      /* Set Names */

      &oCollNamesCollection = &oCollNameTypeVw.COLL_NAMES;

      &NameCount = 0;

      Local integer &i122;

      For &i122 = 1 To 1

         &NameCount = &NameCount + 1;

         If &NameCount > 1 Then

            &oCollNames = &oCollNamesCollection.insertitem(&oCollNamesCollection.count);

            &WorkAround = &oCollNames.itemnum;

         End-If;

         

         &CollCount1 = &oCollNamesCollection.count;

         &oCollNames = &oCollNamesCollection.Item(&CollCount1);

         &oCollNames.KEYPROP_NAME_TYPE = &NameType;

         &oCollNames.KEYPROP_EFFDT = &EffDate;

         &oCollNames.PROP_COUNTRY_NM_FORMAT = &CountryFormat;

         &oCollNames.PROP_NAME_PREFIX = "";

         &oCollNames.PROP_NAME_SUFFIX = "";

         &oCollNames.PROP_LAST_NAME = &LastName;

         &oCollNames.PROP_FIRST_NAME = &FirstName;

         &oCollNames.PROP_MIDDLE_NAME = &MiddleName;

         &oCollNames.PROP_SECOND_LAST_NAME = &SecondLastName;

      End-For;

      

      /* Execute Save */

      If Not &oCiPersonalData.Save() Then

         errorHandler();

         throw CreateException(0, 0, "Save failed");

      End-If;

      

      /* Execute Cancel */

      If Not &oCiPersonalData.Cancel() Then

         errorHandler();

         throw CreateException(0, 0, "Cancel failed");

      End-If;

      

      /* Return True if successful */

      Return True;

      

   catch Exception &ex1

      Error MsgGet(0, 0, "Caught exception: " | &ex1.ToString());

      /* Return False if an error occurs */

      Return False;

   end-try;

   

End-Function;

Local boolean &isSuccessful;

&SQL1 = CreateSQL("select distinct emplid ,initcap(last_name) , initcap(first_name) ,initcap(middle_name) ,initcap(second_last_name) from ps_uod_name_load WHERE descr = 'rr' and ROWNUM <= 400");

While &SQL1.Fetch(&emplid, &LastName, &FirstName, &MiddleName, &SecondLastName)

   

   &isSuccessful = UpdatePersonalData(&emplid, "PRI", %Date, "001", &LastName, &FirstName, &MiddleName, &SecondLastName);

   

   If &isSuccessful Then

      

      SQLExec("update ps_uod_name_load set descr='true' where emplid =:1", &emplid);

      rem   MessageBox(0, "", 0, 0, "Update was successful.");

      

   Else

      rem MessageBox(0, "", 0, 0, "Update failed.");

      SQLExec("update ps_uod_name_load set descr='false' where emplid =:1 ", &emplid);

   End-If;

   

End-While;

Code to delete term activation using peoplecode - component interface

 

/* ===>

This is a dynamically generated PeopleCode template to be used only as a helper

to the application developer.

You need to replace all references to '[*]' OR default values with references to

PeopleCode variables and/or a Rec.Fields. */

Local File &fileLog;

Local ApiObject &oSession, &oTermActivationCi;

Local ApiObject &oStdntCareerCollection, &oStdntCareer;

Local ApiObject &oStdntCarTermCollection, &oStdntCarTerm;

Local boolean &result;

Function errorHandler()

   Local ApiObject &oPSMessageCollection, &oPSMessage;

   Local number &i;

   Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;

   

   &oPSMessageCollection = &oSession.PSMessages;

   For &i = 1 To &oPSMessageCollection.Count

      &oPSMessage = &oPSMessageCollection.Item(&i);

      &sErrMsgSetNum = &oPSMessage.MessageSetNumber;

      &sErrMsgNum = &oPSMessage.MessageNumber;

      &sErrMsgText = &oPSMessage.Text;

      rem &fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);

   End-For;

   rem ***** Delete the Messages from the collection *****;

   &oPSMessageCollection.DeleteAll();

End-Function;

Function ProcessTermActivation(&emplid As string, &strm As string) Returns boolean

   

   &fileLog = GetFile("C:\Users\mmlatif\AppData\Local\Temp\TERM_ACTIVATION_CI.log", "w", "a", %FilePath_Absolute);

   &fileLog.WriteLine("Begin");

   

   

   &result = False; /* Initialize the result as False */

   

   try

      rem ***** Get current PeopleSoft Session *****;

      &oSession = %Session;

      

      rem ***** Set the PeopleSoft Session Error Message Mode *****;

      &oSession.PSMessagesMode = 1;

      

      rem ***** Get the Component Interface *****;

      &oTermActivationCi = &oSession.GetCompIntfc(CompIntfc.TERM_ACTIVATION_CI);

      If &oTermActivationCi = Null Then

         errorHandler();

         throw CreateException(0, 0, "GetCompIntfc failed");

      End-If;

      

      rem ***** Set the Component Interface Mode *****;

      &oTermActivationCi.InteractiveMode = False;

      &oTermActivationCi.GetHistoryItems = True;

      &oTermActivationCi.EditHistoryItems = False;

      

      rem ***** Set Component Interface Get/Create Keys *****;

      &oTermActivationCi.EMPLID = &emplid;

      

      rem ***** Execute Get *****;

      If Not &oTermActivationCi.Get() Then

         rem ***** No rows exist for the specified keys. *****;

         errorHandler();

         throw CreateException(0, 0, "Get failed");

      End-If;

      

      rem ***** Begin: Get/Set Component Interface Properties *****;

      &oStdntCareerCollection = &oTermActivationCi.STDNT_CAREER;

      Local integer &i132;

      For &i132 = &oStdntCareerCollection.Count To 1 Step - 1

         &oStdntCareer = &oStdntCareerCollection.Item(&i132);

         

         rem ***** Set STDNT_CAR_TERM Collection Field Properties -- Parent: STDNT_CAREER Collection *****;

         &oStdntCarTermCollection = &oStdntCareer.STDNT_CAR_TERM;

         Local integer &i235;

         For &i235 = &oStdntCarTermCollection.Count To 1 Step - 1

            &oStdntCarTerm = &oStdntCarTermCollection.Item(&i235);

            If &oStdntCarTerm.STRM = &strm Then

               rem ***** Delete the row with the specified STRM *****;

               &oStdntCarTermCollection.DeleteItem(&i235);

            End-If;

         End-For;

      End-For;

      

      rem ***** Save the changes *****;

      If Not &oTermActivationCi.Save() Then

         errorHandler();

         throw CreateException(0, 0, "Save failed");

      End-If;

      

      rem ***** Cancel the CI (cleanup) *****;

      If Not &oTermActivationCi.Cancel() Then

         errorHandler();

         throw CreateException(0, 0, "Cancel failed");

      End-If;

      

      &result = True; /* If all steps succeed, set the result to True */

      

   catch Exception &ex

      

      &fileLog.WriteLine(&ex.ToString());

      

      

      &result = False; /* Ensure the result remains False if any exception is thrown */

   end-try;

   

   Return &result;

End-Function;

&SQL1 = CreateSQL("select distinct emplid  from PS_UOD_STDDEL_TERM ");

While &SQL1.Fetch(&emplid)

   

   Local boolean &isSuccess;

   &isSuccess = ProcessTermActivation(&emplid, "2241");

   

   If &isSuccess Then

      

      

      &fileLog.WriteLine("Term Activation processed successfully.");

      REM MessageBox(0, "", 0, 0, "Term Activation processed successfully.");

   Else

      &fileLog.WriteLine("Failed to process Term Activation.");

      

      REM MessageBox(0, "", 0, 0, "Failed to process Term Activation.");

   End-If;

   

End-While;

PeopleSoft - Blackboard Integration

End Point 1:    https://absc-staging.blackboard.com/learn/api/public/v1/oauth2/token

End Point 2:     https://absc-staging.blackboard.com/learn/api/public/v3/courses 

Function get_BB_token() Returns array of string

   Local boolean &ret;

   Local string &authStr, &clientId, &clientSecret, &responseStr;

   Local array of string &result = CreateArrayRept("", 20);

      /* Application Key */

   &clientId = "9c18bcc1-8be4-4f61-a2e8-815baaac9e2e1";

      /*Secret*/

   &clientSecret = "KlT4VdO0sclGwtQuGvcLkPlALo7fF3Kk1";

    &request = CreateMessage(Operation.X_RCVD_BB_TOKEN_POST);

   &ret = &request.IBInfo.LoadRESTHeaders();

    &request.URIResourceIndex = 1;

   &request.SegmentContentType = "application/x-www-form-urlencoded;charset=UTF-8";

   Local object &plainStr = CreateJavaObject("java.lang.String", &clientId | ":" | &clientSecret);

   Local object &encoder = GetJavaClass("com.peoplesoft.tools.util.Base64");

   &authStr = &encoder.encode(&plainStr.getBytes());

    Local string &postData = "grant_type=client_credentials";

   &ret = &request.IBInfo.IBConnectorInfo.AddConnectorProperties("Authorization", "Basic " | &authStr, %HttpHeader);

   &ret = &request.SetContentString(&postData);

   &response = %IntBroker.SyncRequest(&request);

    If &response.ResponseStatus = %IB_Status_Success Then

     

      Local string &jsonRespStr = &response.GetContentString();

      Local JsonParser &parser = CreateJsonParser();

      &ret = &parser.Parse(&jsonRespStr);

      Local JsonObject &jsonResp = &parser.GetRootObject();

   

      &result [1] = &jsonResp.GetProperty("access_token");

      &result [2] = &jsonResp.GetProperty("token_type");

      &result [3] = &jsonResp.GetProperty("expires_in");

      &result [4] = &jsonResp.GetProperty("scope");

            

      rem   MessageBox(0, "", 0, 0, "Response: " | &result [1]);

      Return &result;

   Else

      rem MessageBox(0, "", 0, 0, "Error: " | &response.ErrorText);

      Return &response.ErrorText;

   End-If;

End-Function;

/*----------------------------------------------------------------------*/

Local array of string &result1 = get_BB_token();

/* Use the result */

&access_token = &result1 [1];

&token_type = &result1 [2];

If &access_token = "" Then

   /* Handle error case */

   Error MessageBox(0, "", 0, 0, "Error: Token is not valid");

Else

Local boolean &ret1, &organization, &allowGuests, &allowObservers, &closedComplete, &force;

   Local object &rootJSON, &availabilityJSON, &durationJSON, &enrollmentJSON, &localeJSON;

   

   &Request_MSG = CreateMessage(Operation.X_RCVD_BB_TOKEN_POST);

   &ret1 = &request.IBInfo.LoadRESTHeaders();

   

   &ret1 = &Request_MSG.IBInfo.IBConnectorInfo.AddConnectorProperties("Method", "POST", %Property);

   &ret1 = &Request_MSG.IBInfo.IBConnectorInfo.AddConnectorProperties("Authorization", "Bearer " | &access_token, %HttpHeader);

   &ret1 = &Request_MSG.IBInfo.IBConnectorInfo.AddConnectorProperties("Content-Type", "application/json", %HttpHeader);

   

   rem Error MessageBox(0, "", 0, 0, "1" | &_accessToken);

   &rootJSON = CreateJsonObject();

   Local string &url = "learn/api/public/v3/courses";

   

   &Request_MSG.IBInfo.ConnectorOverride = True;

   &Request_MSG.OverrideURIResource(" ");

   If Len(&url) > 0 Then

      &Request_MSG.OverrideURIResource(&url);

   End-If;

  

   

   Local SQL &sqlHeader = CreateSQL(SQL.X_GET_COURSE_DTL, "2401");

   While &sqlHeader.Fetch(&CRSE_ID, &CRSE_OFFER_NBR, &STRM, &SESSION_CODE, &CLASS_SECTION, &CLASS_NBR, &SUBJECT, &CATALOG_NBR, &DESCR);

      &externalId = &CRSE_ID | "-" | NumberToString("%02", &CRSE_OFFER_NBR) | "-" | &STRM | "-" | &SESSION_CODE | "-" | &CLASS_SECTION | "-" | NumberToString("%05", &CLASS_NBR);

      &courseId = &SUBJECT | &CATALOG_NBR | "-" | NumberToString("%02", &CRSE_OFFER_NBR) | "-" | &STRM | "-" | &SESSION_CODE | "-" | &CLASS_SECTION | "-" | NumberToString("%05", &CLASS_NBR);

      &name = &DESCR | " " | &CLASS_SECTION | "-" | NumberToString("%05", &CLASS_NBR);

      &description = &SUBJECT | " " | &CATALOG_NBR;

      &organization = False;

      &ultraStatus = "Ultra";

      &allowGuests = False;

      &allowObservers = False;

      &closedComplete = False;

      &termId = "_241_1";

      &available = "Yes";

      &type = "Continuous";

      &type1 = "InstructorLed";

      &force = True;

      

      /*-------------------------------------------------*/

      

      

      &durationJSON = CreateJsonObject();

      &durationJSON.AddProperty("type", "Continuous");

      &durationJSON.AddProperty("start", "2024-06-24T12:46:26.588Z");

      &durationJSON.AddProperty("end", "2024-06-24T12:46:26.588Z");

      &durationJSON.AddProperty("daysOfUse", 0);

      

      

      &availabilityJSON = CreateJsonObject();

      &availabilityJSON.AddProperty("available", "Yes");

      &availabilityJSON.AddProperty("duration", &durationJSON);

      

      

      

      &enrollmentJSON = CreateJsonObject();

      &enrollmentJSON.AddProperty("type", "InstructorLed");

      &enrollmentJSON.AddProperty("start", "2024-06-24T12:46:26.588Z");

      &enrollmentJSON.AddProperty("end", "2024-06-24T12:46:26.588Z");

      &enrollmentJSON.AddProperty("accessCode", "01");

      

      

      &localeJSON = CreateJsonObject();

      &localeJSON.AddProperty("id", "string");

      &localeJSON.AddProperty("force", True);

      

      

      &rootJSON.AddProperty("externalId", &externalId);

      &rootJSON.AddProperty("courseId", &courseId);

      &rootJSON.AddProperty("name", &name);

      &rootJSON.AddProperty("description", &description);

      &rootJSON.AddProperty("organization", &organization);

      &rootJSON.AddProperty("ultraStatus", &ultraStatus);

      &rootJSON.AddProperty("allowGuests", &allowGuests);

      &rootJSON.AddProperty("allowObservers", &allowObservers);

      &rootJSON.AddProperty("closedComplete", &closedComplete);

      

      &rootJSON.AddProperty("availability", &availabilityJSON);

      &rootJSON.AddProperty("enrollment", &enrollmentJSON);

      &rootJSON.AddProperty("locale", &localeJSON);

      

      &jsonString = &rootJSON.ToString();

          

      /* Set the request body */

      &ret1 = &Request_MSG.SetContentString(&jsonString);

      

      /* Send the request */

      &Response_MSG = %IntBroker.SyncRequest(&Request_MSG);

      

      If &Response_MSG.ResponseStatus = %IB_Status_Success Then

         Local string &jsonRespStr = &Response_MSG.GetContentString();

         

         Local JsonParser &parser = CreateJsonParser();

         &ret1 = &parser.Parse(&jsonRespStr);

         Local JsonObject &jsonResp = &parser.GetRootObject();

         MessageBox(0, "", 0, 0, "" | &jsonRespStr);

         

      End-If;

      

   End-While;

   End-If;

Read CSV file stored in database

 Local File &MYFILE;

Local array of string &ARRAY;

Local Record &REC;

Local Rowset &studentList ;

&studentList = GetLevel0()(1).GetRowset(Scroll.L_TRNS_CRSE_TBL);

&REC = CreateRecord(Record.L_TRNSCRS_FILE);

SQLExec("SELECT ATTACHSYSFILENAME FROM PS_L_TRNS_CRS_ATCH WHERE OPRID =:1 AND RUN_CNTL_ID=:2", %UserId, L_TRNS_CRS_ATCH.RUN_CNTL_ID.Value, &FileName);

rem Messagebox(0,"",0,0,""|&FileName);

&SQL = CreateSQL("%SelectAll(:2)  WHERE ATTACHSYSFILENAME =:1 ", &FileName, Record.L_TRNSCRS_FILE);

While &SQL.Fetch(&REC)  

   &FILE1 = GetFile(&FileName, "w", "a", %FilePath_Absolute);

    rem Messagebox(0,"",0,0, " | %FilePath_Absolute | "File Open:" | &FILE1.IsOpen);

   

   If &FILE1.IsOpen Then

      try

         &FILE1.WriteRaw(&REC.FILE_DATA.Value);

         &FILE1.Close();

         

      catch Exception &ex2

         throw CreateException(0, 0, "Unable to write  file: " | &ex2.ToString());

      end-try;

   End-If;

End-While;

&MYFILE = GetFile(&FileName, "r", "a", %FilePath_Absolute);

&ARRAY = CreateArrayRept("", 0);

rem MessageBox(0, "", 0, 0, "" | &MYFILE.IsOpen);

If &MYFILE.IsOpen Then

   If &MYFILE.SetFileLayout(FileLayout.L_TRNS_CRSE_MNL_FL) Then

            While &MYFILE.ReadLine(&STRING);

               &ARRAY = Split(&STRING, ",");

         For &i = 1 To &ARRAY.Len

            MessageBox(0, "", 0, 0, "" | ("Value of &ARRAY[" | &i | "]: " | &ARRAY [&i]));

         End-For;

         

         End-while;

end-if;

end-if;

how to create JSON using PeopleCode

 Below is a simple JSON example that uses the JSONObject class. 

{"invoiceHeader":   {

        "CompanyNo": 685,

        "InvoiceNo": "90000021",

        "InvoiceType": 388,

        "InvoiceDate": "2024-01-25",

        "InvoiceTime": "15:30:00",

        "UUID": "dcc3e906-42c0-4741-b871-16f63f8a2686",

        "CustomerName": "EMT Company2",

        "CustomerVATNumber": "302233808500003",

        "CustomerCRNumber": "1010627830",

        "CustomerStreetName": "Street Name",

        "CustomerBuildingNumber": "1234",

        "CustomerZipCode": "12345",

        "CustomerCityName": "City Name",

        "CustomerDistrictName": "District Name",

        "CustomerRegionName": "Region Name",

        "InvoiceSubTotal": 1100.00,

        "InvoiceDiscount": 100.00,

        "InvoiceVAT": 150.00,

        "InvoiceNetTotal": 1150.00,

        "FromInvoiceNo": "00000003",

        "InvoiceVATType": "01"

    },

    "invoiceDetails": [

        {

            "RowNo": 1,

            "ServiceName": "Villa rent for 6 months",

            "UnitPrice": 500.00,

            "Q": 2,

            "NetTotal": 1000.00

        },

        {

            "RowNo": 2,

            "ServiceName": "Another Service",

            "UnitPrice": 300.00,

            "Q": 3,

            "NetTotal": 900.00

        },

     {

            "RowNo": 3,

            "ServiceName": "Another Service",

            "UnitPrice": 300.00,

            "Q": 3,

            "NetTotal": 900.00

        }

    ]

}

------------------------------------------------------------------------------------------------------------------------

Global File &fileLog;

Global string &FileName;

&FileName = "Logging for posting of invoices from staging to zatca..." | ".TXT";

&fileLog = GetFile(&FileName, "W");

try

   

   &request = CreateMessage(Operation.T_ZATCA_INVC_OPR_POST);

   &request.IBInfo.IBConnectorInfo.ConnectorName = "HTTPTARGET";

   &request.IBInfo.IBConnectorInfo.ConnectorClassName = "HttpTargetConnector";

   

   &bOK = &request.IBInfo.IBConnectorInfo.AddConnectorProperties("Method", "POST", %Property);

   &bOK = &request.IBInfo.IBConnectorInfo.AddConnectorProperties("authentication_type", "CompanySecret", %HttpHeader);

   &bOK = &request.IBInfo.IBConnectorInfo.AddConnectorProperties("CompanySecret", "123456nh#=", %HttpHeader);

   &bOK = &request.IBInfo.IBConnectorInfo.AddConnectorProperties("Content-Type", "application/json", %HttpHeader);

   &rootJSON = CreateJsonObject();

   Local SQL &sqlHeader, &sqlDtl;

   Local Record &recHeader = CreateRecord(Record.T_ZATCA_REQ);

   Local Record &recDtl = CreateRecord(Record.T_ZATCA_REQ_DTL);

   Local Record &respRec = CreateRecord(Record.T_ZATCA_RESP);

   &sqlHeader = CreateSQL("%SelectAll(:1) WHERE T_ZATCA_PSTNG_STTS!=:2", &recHeader, "Posted");

   

   While &sqlHeader.Fetch(&recHeader)

      Local string &ZATCA_UUID = "";

      &ZATCA_UUID = UuidGenBase64();

      &rootJSON.AddProperty("CompanyNo", &recHeader.Z_COMPANY_NO.Value);

      &rootJSON.AddProperty("InvoiceNo", &recHeader.INVOICE_ID.Value);

      Local number &type = &recHeader.Z_INVOICE_TYPE.Value;

      &rootJSON.AddProperty("InvoiceType", &type);

      &rootJSON.AddProperty("InvoiceDate", &recHeader.DATE1.Value);

      &rootJSON.AddProperty("InvoiceTime", &recHeader.Z_INVOICE_TIME.Value);

      &rootJSON.AddProperty("UUID", &ZATCA_UUID);

      &rootJSON.AddProperty("CustomerName", &recHeader.Z_CUST_NAME.Value);

      &rootJSON.AddProperty("CustomerVATNumber", &recHeader.I_VAT_NO.Value);

      &rootJSON.AddProperty("CustomerCRNumber", &recHeader.Z_CR_NUM.Value);

      &rootJSON.AddProperty("CustomerStreetName", &recHeader.Z_STREET_NAME.Value);

      &rootJSON.AddProperty("CustomerBuildingNumber", &recHeader.Z_BUILDING_NO.Value);

      &rootJSON.AddProperty("CustomerZipCode", &recHeader.Z_ZIP_CODE.Value);

      &rootJSON.AddProperty("CustomerCityName", &recHeader.CITY.Value);

      &rootJSON.AddProperty("CustomerDistrictName", &recHeader.DISTRICT.Value);

      &rootJSON.AddProperty("CustomerRegionName", &recHeader.Z_REGION.Value);

      &rootJSON.AddProperty("InvoiceSubTotal", &recHeader.Z_SUBTOTAL.Value);

      &rootJSON.AddProperty("InvoiceDiscount", &recHeader.Z_DISCOUNT.Value);

      &rootJSON.AddProperty("InvoiceVAT", &recHeader.Z_VATAMOUNT.Value);

      &rootJSON.AddProperty("InvoiceNetTotal", &recHeader.Z_NETTOTAL.Value);

      &rootJSON.AddProperty("FromInvoiceNo", "00000000");

      &rootJSON.AddProperty("InvoiceVATType", &recHeader.Z_INVOICE_VATTYPE.Value);

      

      &sqlDtl = CreateSQL("%SelectAll(:1) WHERE INVOICE_ID=:2", &recDtl, &recHeader.INVOICE_ID.Value);

      

      &recDtlArray = CreateJsonArray();

      Local boolean &isDetailExist = False;

      While &sqlDtl.Fetch(&recDtl)

         &childJSON = CreateJsonObject();

         &childJSON.AddProperty("RowNo", &recDtl.Z_ROWNO.Value);

         &childJSON.AddProperty("ServiceName", &recDtl.Z_SERVICENAME.Value);

         &childJSON.AddProperty("UnitPrice", &recDtl.Z_UNITPRICE.Value);

         &childJSON.AddProperty("Q", &recDtl.Z_QUANTITY.Value);

         &childJSON.AddProperty("NetTotal", &recDtl.Z_SRVCE_NETTOTAL.Value);

         &recDtlArray.AddJsonObjectElement("", &childJSON);

         &isDetailExist = True;

      End-While;

      

      If &isDetailExist Then

         &finalJson = CreateJsonObject();

         &finalJson.AddProperty("invoiceHeader", &rootJSON);

         &finalJson.AddJsonArray("invoiceDetails", &recDtlArray);

         &rootJSONStr = &finalJson.ToString();

         

         &request.URIResourceIndex = 1;

         &bRet = &request.SetContentString(&rootJSONStr);

         

         &response = %IntBroker.SyncRequest(&request);

         &jsonRespStr = &response.GetContentString();

         &parser = CreateJsonParser();

         &ret = &parser.Parse(&jsonRespStr);

         &jsonResp = &parser.GetRootObject();

         rem &fileLog.WriteLine("Final Packet--->" | &rootJSONStr);

         

         Local boolean &isSubmitted = False;

         

         try

            &isSubmitted = &jsonResp.GetProperty("IsReportedToZatca");

         catch Exception &innerExc

            &isSubmitted = False;

         end-try;

         rem Warning "Reported to zatca-->" | &jsonRespStr;

         If All(&jsonRespStr) And

               &isSubmitted Then

            rem &respRec.BILL_DATE_TIME.Value = &jsonResp.GetProperty("SubmissionDate");

            &respRec.BILL_DATE_TIME.Value = DateTimeValue(&jsonResp.GetProperty("SubmissionDate"));

            &respRec.INVOICE_ID.Value = &recHeader.INVOICE_ID.Value;

            &respRec.Z_INVOICEHASH.Value = &jsonResp.GetProperty("InvoiceHash");

            &respRec.Z_QRCODE.Value = &jsonResp.GetProperty("QrCode");

            &respRec.Z_REPORTEDTOZATCA.Value = "Yes";

            &respRec.Z_REPORTINGRESULT.Value = &jsonResp.GetProperty("ReportingResult");

            &respRec.Z_REPORTINGSTATUS.Value = &jsonResp.GetProperty("ReportingStatus");

            &respRec.Z_SIGNEDXML.Value = &jsonResp.GetProperty("SignedXml");

            &respRec.Z_UUID.Value = &ZATCA_UUID;

            &respRec.Z_WARNINGMSG.Value = &jsonResp.GetProperty("WarningMessages");

            &respRec.T_CODE.Value = &jsonResp.GetProperty("code");

            &respRec.Insert();

            SQLExec("UPDATE PS_T_ZATCA_REQ SET T_ZATCA_PSTNG_STTS=:1,T_ZATCA_POSTED_DTT=:2,Z_UUID=:3 WHERE INVOICE_ID=:4", "Posted", %Datetime, &ZATCA_UUID, &recHeader.INVOICE_ID.Value);

            &fileLog.WriteLine("Invoivice ID[" | &recHeader.INVOICE_ID.Value | "] Posted Successfully.");

         Else

            &fileLog.WriteLine("The inovice not posted. Following are the details");

            &fileLog.WriteLine("Inovioce Number--------->" | &recHeader.INVOICE_ID.Value);

            &fileLog.WriteLine("Zatca Invoice Response---->" | &jsonRespStr);

            &fileLog.WriteLine("---------------------End------------------------------------");

         End-If;

      Else

         &json = "";

         &fileLog.WriteLine("The inovice not posted. Following are the details");

         &fileLog.WriteLine("Inovioce Number--------->" | &recHeader.INVOICE_ID.Value);

         &fileLog.WriteLine("Resaon------------------>Invoice details missing");

         &fileLog.WriteLine("---------------------End------------------------------------");

         rem Warning "There is no invoice details";

      End-If;

      

   End-While;

   rem DoSave();

   &sqlHeader.Close();

   &sqlDtl.Close();

   

   

catch Exception &exc;

   rem Warning "Error-->" | &exc.ToString();

   &fileLog.WriteLine("The inovice(s) are not posted. Following are the details");

   &fileLog.WriteLine("Error Message:-->" | &exc.ToString());

   &fileLog.WriteLine("---------------------End------------------------------------");

end-try;

How to create a TLV QR Code ?

 

Hex Representation 

SELECT '01'||TO_CHAR(12, 'FM0X')||RAWTOHEX('Bobs Records')||

 '02'||TO_CHAR(15, 'FM0X')||RAWTOHEX('310122393500003')||

  '03'||TO_CHAR(20, 'FM0X')||RAWTOHEX('2022-04-25t15:30:000Z') ||

   '04'||TO_CHAR(7, 'FM0X')||RAWTOHEX('1000.00') ||

    '05'||TO_CHAR(6, 'FM0X')||RAWTOHEX('150.00') AS hex_representation FROM dual;

Base64 Representation 

SELECT UTL_RAW.CAST_TO_VARCHAR2(

UTL_ENCODE.BASE64_enCODE(

'01'||TO_CHAR(12, 'FM0X')||RAWTOHEX('Bobs Records')||

 '02'||TO_CHAR(15, 'FM0X')||RAWTOHEX('310122393500003')||

  '03'||TO_CHAR(20, 'FM0X')||RAWTOHEX('2022-04-25t15:30:000Z') ||

   '04'||TO_CHAR(7, 'FM0X')||RAWTOHEX('1000.00') ||

    '05'||TO_CHAR(6, 'FM0X')||RAWTOHEX('150.00')

    

)) AS base64_representation FROM dual;