Hi everyone, I have an issue. I am developing a canteen feedback application.
This is the report query:
SELECT
ROWNUM AS row_id,mi.MEAL_ID AS MEAL_ID,mi.ITEM_ID AS ITEM_ID,mi.ITEM_NAME AS ITEM_NAME,
mi.SLOT AS SLOT,
APEX_ITEM.RADIOGROUP(ROWNUM, '1', NULL, NULL, 'style="accent-color: #007bff; transform: scale(1.3);"') AS EXCELLENT,
APEX_ITEM.RADIOGROUP(ROWNUM, '2', NULL, NULL, 'style="accent-color: #007bff; transform: scale(1.3);"') AS GOOD,
APEX_ITEM.RADIOGROUP(ROWNUM, '3', NULL, NULL, 'style="accent-color: #007bff; transform: scale(1.3);"') AS SATISFACTORY,
APEX_ITEM.RADIOGROUP(ROWNUM, '4', NULL, NULL, 'style="accent-color: #007bff; transform: scale(1.3);"') AS POOR
FROM MEAL_ITEMS mi
JOIN MEAL_SCHEDULE ms ON mi.MEAL_ID = ms.ID
WHERE TRUNC(ms.MENU_DATE) = TRUNC(SYSDATE)
AND mi.SLOT = COALESCE(:P3_SELECTED_SLOT,(SELECT CASE
WHEN EXTRACT(HOUR FROM CAST(SYSTIMESTAMP AT TIME ZONE 'Asia/Kolkata' AS TIMESTAMP)) BETWEEN 6 AND 11 THEN 'BREAKFAST'
WHEN EXTRACT(HOUR FROM CAST(SYSTIMESTAMP AT TIME ZONE 'Asia/Kolkata' AS TIMESTAMP)) BETWEEN 12 AND 17 THEN 'LUNCH'
WHEN EXTRACT(HOUR FROM CAST(SYSTIMESTAMP AT TIME ZONE 'Asia/Kolkata' AS TIMESTAMP)) BETWEEN 18 AND 20 THEN 'SNACKS'
ELSE 'DINNER' END FROM DUAL));
---user interface look like this
Meal_id Item_Id Item Name Slot Excellent Good Satisfactory Poor Row Id
3086 1 KARA BOONDI LUNCH 1
3086 2 BISIBELEBHATH LUNCH 2
3086 3 CURD RICE LUNCH 3
3086 4 CURD CHILLY LUNCH 4
3086 5 MUSKMELON PANKAM LUNCH 5
---user has to select any one Excellent or Good or Satisfactory or Poor for each row as many rows we have
---for the report query if i remove rownum for RADIOGROUP i cant select RADIOGROUP for each row of the items
-- in the insert process i want to store in table rating column Excellent=>1 Good=>2 Satisfactory=>3 Poor=>4 but i am using sepertae RADIOGROUP how to insert
---Here is the insert process:
BEGIN
IF APEX_COLLECTION.COLLECTION_EXISTS('MEAL_FEEDBACK') THEN
APEX_COLLECTION.DELETE_COLLECTION('MEAL_FEEDBACK');
END IF;
APEX_COLLECTION.CREATE_COLLECTION('MEAL_FEEDBACK');
FOR i IN 1 .. APEX_APPLICATION.G_F03.COUNT LOOP
APEX_COLLECTION.ADD_MEMBER(
p_collection_name => 'MEAL_FEEDBACK',
p_c001 => APEX_APPLICATION.G_F02(i),
p_c002 => APEX_APPLICATION.G_F03(i),
p_c003 => APEX_APPLICATION.G_F04(i),
p_c004 => APEX_APPLICATION.G_F05(i),
p_c005 => APEX_APPLICATION.G_F06(i)
);
END LOOP;
FOR rec IN (
SELECT
c001 AS MEAL_ID,
c002 AS ITEM_ID,
c005 AS RATING
FROM APEX_COLLECTIONS
WHERE COLLECTION_NAME = 'MEAL_FEEDBACK'
) LOOP
INSERT INTO EMPLOYEES_MEAL_FEEDBACK (MEAL_ID,ITEM_ID,RATING,FEEDBACK_DATE,EMPLOYEES_ID,COMMENTS,OFFICE_LOCATIONNAME)
VALUES (rec.MEAL_ID,rec.ITEM_ID,rec.RATING,SYSDATE,:P3_EMPLOYEE_NUM,:P3_YOUR_COMMENTS,'Bangalore');
END LOOP;
END;