13 Replies Latest reply: Apr 26, 2013 6:38 AM by Gabriel RSS

    APEX Collection - how to process

    933667
      Hello all.

      I am fairly new to apex and using apex 4.0. I have a SQL report based on collections, with 2 columns updateable. Following is the query.

      SELECT APEX_ITEM.TEXT(1,SEQ_ID) AS SEQ_ID,
      APEX_ITEM.TEXT(2,c001) AS R_TYPE,
      APEX_ITEM.TEXT(3,c002) AS TA_ID,
      APEX_ITEM.TEXT(4,c003) AS ANSWER_ID,
      c008 AS R_TITLE,
      CASE WHEN c001 = 'QUESTION'
      THEN APEX_ITEM.CHECKBOX(40,c009)
      ELSE APEX_ITEM.HIDDEN(40,c009) END AS ANSWER_Y_N,
      CASE WHEN c001 = 'QUESTION' AND c011 = 'Y'
      THEN APEX_ITEM.TEXTAREA(41,c010,3,40)
      ELSE APEX_ITEM.HIDDEN(41,c010) END AS ANSWER_TEXT,
      APEX_ITEM.TEXT(11,c011) AS TEXT_FLAG
      FROM APEX_COLLECTIONS
      WHERE COLLECTION_NAME = 'AUDIT_ANSWER'
      AND c002 = :P18_TA_ID
      ORDER BY c004, c005, c006, c007

      ANSWER_ID is the primary key which is not displaying. R_TITLE is the question which is display only. ANSWER_Y_N is a column having Y or Null answers. ANSWER_TEXT is enterable comment column.

      After SAVE button, it fires page process (On Submit - Before computations) having following code -

      BEGIN

      FOR i IN 1..APEX_APPLICATION.G_F04.COUNT
      LOOP

      IF APEX_APPLICATION.G_F02(i) = 'QUESTION'
      THEN
      APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE('AUDIT_ANSWER', APEX_APPLICATION.G_F04(i), '9', APEX_APPLICATION.G_F40(i));
      APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE('AUDIT_ANSWER', APEX_APPLICATION.G_F04(i), '10', APEX_APPLICATION.G_F41(i));
      END IF;
      END LOOP;

      FOR r IN ( SELECT c003, c009, c010
      +     FROM APEX_COLLECTIONS+
      +     WHERE COLLECTION_NAME = 'AUDIT_ANSWER' )+
      LOOP
      UPDATE VW_OTA_AUDIT_ANSWERS
      SET ANSWER_Y_N  = r.c009,
      ANSWER_TEXT = r.c010
      WHERE ANSWER_ID = r.c003;
      END LOOP;

      END;

      It is not updating the collection. What could be the problem here.

      Edited by: 930664 on Nov 29, 2012 3:53 AM