Master IBM BPM/BAW in 2026: Workflow Automation Made Simple

Stored Procedure Code Below:

CREATE OR REPLACE PROCEDURE InsertOrUpdateLoanApplicationFromXML(   IN caseid VARCHAR(100),   IN initiatedDate TIMESTAMP,   IN currentStage VARCHAR(100),   IN currentUser VARCHAR(100),   IN in_xmlInput CLOB,   IN currentStatus VARCHAR(100))LANGUAGE SQLNOT DETERMINISTICMODIFIES SQL DATABEGIN   DECLARE xmlData XML;   DECLARE v_fullName VARCHAR(100);   DECLARE v_dateOfBirth TIMESTAMP;   DECLARE v_gender VARCHAR(100);   DECLARE v_contactNumber VARCHAR(15);   DECLARE v_email VARCHAR(100);   DECLARE v_addressLine1 VARCHAR(100);   DECLARE v_addressLine2 VARCHAR(100);   DECLARE v_city VARCHAR(100);   DECLARE v_state_name VARCHAR(100);   DECLARE v_zipcode VARCHAR(100);   DECLARE v_loanAmount INTEGER;   DECLARE v_purpose VARCHAR(100);   DECLARE v_loanTerm VARCHAR(100);   DECLARE v_isReducing BOOLEAN DEFAULT FALSE;   DECLARE v_employerType VARCHAR(100);   DECLARE v_EmployerName VARCHAR(100);   DECLARE v_annualIncome DECIMAL(15, 2);   DECLARE v_creditScore INTEGER;   DECLARE v_ExistingLoans VARCHAR(100);   DECLARE v_currentStage VARCHAR(100);   DECLARE v_initiatedDate TIMESTAMP;   DECLARE v_current_status VARCHAR(100);

   SET v_currentStage = currentStage;   SET v_initiatedDate = initiatedDate;   SET v_current_status = COALESCE(currentStatus, 'NEW');

   SET xmlData = XMLPARSE(DOCUMENT in_xmlInput);

   SELECT        T.fullName,       CASE            WHEN T.dateOfBirth IS NOT NULL            THEN TIMESTAMP_FORMAT(REPLACE(SUBSTRING(T.dateOfBirth, 1, 19), '/', '-'), 'YYYY-MM-DD HH24:MI:SS')           ELSE NULL        END AS dateOfBirth,       T.gender,       T.contactNumber,       T.email   INTO        v_fullName,       v_dateOfBirth,       v_gender,       v_contactNumber,       v_email   FROM XMLTABLE('$doc/variable/personalDetails'        PASSING xmlData AS "doc"        COLUMNS            fullName VARCHAR(100) PATH 'fullName/text()',            dateOfBirth VARCHAR(30) PATH 'dateOfBirth/text()',            gender VARCHAR(100) PATH 'gender/text()',            contactNumber VARCHAR(15) PATH 'contactNumber/text()',            email VARCHAR(100) PATH 'email/text()'        ) AS T;

   SELECT        T.line1,       T.line2,       T.city,       T.state,       T.zip   INTO        v_addressLine1,       v_addressLine2,       v_city,       v_state_name,       v_zipcode   FROM XMLTABLE('$doc/variable/addressDetails'        PASSING xmlData AS "doc"        COLUMNS            line1 VARCHAR(100) PATH 'line1/text()',            line2 VARCHAR(100) PATH 'line2/text()',            city VARCHAR(100) PATH 'city/text()',            state VARCHAR(100) PATH 'state/text()',            zip VARCHAR(100) PATH 'zip/text()'        ) AS T;

   SELECT        T.amount,       T.purpose,       T.term,       CASE WHEN UPPER(T.pReducing) = 'TRUE' THEN TRUE ELSE FALSE END   INTO        v_loanAmount,       v_purpose,       v_loanTerm,       v_isReducing   FROM XMLTABLE('$doc/variable/loanDetails'        PASSING xmlData AS "doc"        COLUMNS            amount INTEGER PATH 'amount/text()',            purpose VARCHAR(100) PATH 'purpose/text()',            term VARCHAR(100) PATH 'term/text()',            pReducing VARCHAR(10) PATH 'pReducing/text()'        ) AS T;

   SELECT        T.employmentType,       T.employerName,       T.annualIncome,       T.creditScore,       T.existingLoan   INTO        v_employerType,       v_EmployerName,       v_annualIncome,       v_creditScore,       v_ExistingLoans   FROM XMLTABLE('$doc/variable/financialInformation'        PASSING xmlData AS "doc"        COLUMNS            employmentType VARCHAR(100) PATH 'employmentType/text()',            employerName VARCHAR(100) PATH 'employerName/text()',            annualIncome DECIMAL(15,2) PATH 'annualIncome/text()',            creditScore INTEGER PATH 'creditScore/text()',            existingLoan VARCHAR(100) PATH 'existingLoan/text()'        ) AS T;

   IF EXISTS (SELECT 1 FROM MYSCHEMA.LoanApplication WHERE caseno = caseid) THEN      UPDATE MYSCHEMA.LoanApplication       SET          initiatedDate = v_initiatedDate,         currentStage = v_currentStage,         currentUser = currentUser,         fullName = v_fullName,         dateOfBirth = v_dateOfBirth,         gender = v_gender,         contactNumber = v_contactNumber,         email = v_email,         addressLine1 = v_addressLine1,         addressLine2 = v_addressLine2,         city = v_city,         state_name = v_state_name,         zipcode = v_zipcode,         loanAmount = v_loanAmount,         purpose = v_purpose,         loanTerm = v_loanTerm,         isReducing = v_isReducing,         employerType = v_employerType,         EmployerName = v_EmployerName,         annualIncome = v_annualIncome,         creditScore = v_creditScore,         ExistingLoans = v_ExistingLoans,         current_status = v_current_status      WHERE caseno = caseid;   ELSE      INSERT INTO MYSCHEMA.LoanApplication (         caseno,         initiatedDate,         currentStage,         currentUser,         fullName,         dateOfBirth,         gender,         contactNumber,         email,         addressLine1,         addressLine2,         city,         state_name,         zipcode,         loanAmount,         purpose,         loanTerm,         isReducing,         employerType,         EmployerName,         annualIncome,         creditScore,         ExistingLoans,         current_status      ) VALUES (         caseid,         v_initiatedDate,         v_currentStage,         currentUser,         v_fullName,         v_dateOfBirth,         v_gender,         v_contactNumber,         v_email,         v_addressLine1,         v_addressLine2,         v_city,         v_state_name,         v_zipcode,         v_loanAmount,         v_purpose,         v_loanTerm,         v_isReducing,         v_employerType,         v_EmployerName,         v_annualIncome,         v_creditScore,         v_ExistingLoans,         v_current_status      );   END IF;END;