Understanding stored procedure logic

Question

I am not as familiar with PL/SQL as I am with standard SQL and need a little help understanding the below procedure, as I am seeing in the data cases where the data is NOT being updated based on the below, and I'd like to understand why this might be happening.

This procedure package gets called from another program and looks to be updating PARTY_ORIGINAL_SYSTEM_REF and LOCATION_ORIGINAL_SYSTEM_REF per the below, however I have found examples where the data is not updated with the below 'INT018<l_seq_id>' even though it seems it meets the criteria.

My other question is where the object rec_pmt is referring to the same query as the UPDATE statement below it?

PROCEDURE TRANSFORM_PMT_REQ_DATA (p_instance_id  IN  NUMBER, 
                                    p_metadata_id  IN  NUMBER,
                                    p_sourcefile   IN  VARCHAR2,
                                    p_status      OUT  VARCHAR2,
                                    p_err_msg     OUT  VARCHAR2
                                   )
  AS
    l_seq_id NUMBER;  
    l_intg_code VARCHAR2(240) :='INT018';
    l_counter NUMBER:=0;
  BEGIN
    FOR rec_pmt IN (SELECT DISTINCT PARTY_NAME,ADDRESS_LINE_1,ADDRESS_LINE_2,
                           CITY,STATE,POSTAL_CODE,POSTAL_PLUS_4_CODE
                      FROM TGC_INT018_PMT_REQ_FBDI
                     WHERE INSTANCE_ID = p_instance_id
                       AND METADATA_ID = p_metadata_id
                       AND SOURCE_FILE_NAME = p_sourcefile
                       AND INTF_STATUS = 'NEW'
                   ) 
    LOOP
      SELECT TGC_INT018_PMT_REQ_SEQ.NEXTVAL INTO l_seq_id FROM DUAL;

      UPDATE TGC_INT018_PMT_REQ_FBDI
         SET PARTY_ORIGINAL_SYSTEM_REF = l_intg_code||l_seq_id
            ,LOCATION_ORIGINAL_SYSTEM_REF = l_intg_code||l_seq_id
       WHERE PARTY_NAME = rec_pmt.PARTY_NAME
         AND ADDRESS_LINE_1 = rec_pmt.ADDRESS_LINE_1
         AND NVL(ADDRESS_LINE_2,'1') = NVL(rec_pmt.ADDRESS_LINE_2,'1')
         AND CITY = rec_pmt.CITY
         AND STATE = rec_pmt.STATE
         AND POSTAL_CODE = rec_pmt.POSTAL_CODE
         AND NVL(POSTAL_PLUS_4_CODE,'1') = NVL(rec_pmt.POSTAL_PLUS_4_CODE,'1')
         AND INSTANCE_ID = p_instance_id
         AND METADATA_ID = p_metadata_id
         AND SOURCE_FILE_NAME = p_sourcefile
         AND INTF_STATUS = 'NEW' ;
         COMMIT;
    END LOOP;
END TRANSFORM_PMT_REQ_DATA

Answer 1

There are two possibilities that prevent row(s) to be updated:

1. parameters passed to procedure caused cursor's select statement NOT to return any rows, so update isn't executed at all
2. if cursor returned something, then it is update's where clause that prevented any row(s) to be updated

---

You said that you

have found examples where the data is not updated with the below 'INT018<l_seq_id>' even though it seems it meets the criteria

We can't help much as we don't have your table nor data, don't see parameters being passed to the procedure ... nothing. Therefore, you should debug it and make sure that criteria is really met.