4 Replies Latest reply: Mar 28, 2013 12:10 PM by user_anumoses RSS

    Oracle forms help- Post query problem

    user_anumoses
      Forms [32 Bit] Version 10.1.2.0.2 (Production)
      Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
      With the Partitioning, OLAP and Oracle Data Mining options
      JServer Release 9.2.0.8.0 - Production
      Oracle Toolkit Version 10.1.2.0.2 (Production)
      PL/SQL Version 10.1.0.4.2 (Production)
      Oracle Procedure Builder V10.1.2.0.2 - Production
      PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
      Oracle Query Builder 10.1.2.0.2 - Production
      Oracle Virtual Graphics System Version 10.1.2.0.2 (Production)
      Oracle Tools GUI Utilities Version 10.1.2.0.2 (Production)
      Oracle Multimedia Version 10.1.2.0.2 (Production)
      Oracle Tools Integration Version 10.1.2.0.2 (Production)
      Oracle Tools Common Area Version 10.1.2.0.2
      Oracle CORE     10.1.0.4.0     Production

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

      I have a form. When the user will choose an equip code, they have to do some procedures. Its a multi record block.
      When the user clicks on the procedure button, it takes to another canvas. The block here is qc_procedures block. They will perform the procedure. My sample table tab1 is the qc_procedures table. The results are stores in qc_procedure_results table and here it is tab2.

      drop table tab1

      create table tab1(PROCEDURE_ID NUMBER(10),
      PROCEDURE_DESC VARCHAR2(80 BYTE),
      FREQUENCY NUMBER,
      EQUIP_TYPE_ID NUMBER(10)
      )     


      insert into tab1(procedure_id,procedure_desc,frequency,equip_type_id) values(1206,'Weekly Procedures',7,5005)
      insert into tab1(procedure_id,procedure_desc,frequency,equip_type_id) values(1207,'Monthly Procedures',30,5005)
      insert into tab1(procedure_id,procedure_desc,frequency,equip_type_id) values(1073,'Daily Lot Release',1,5005)

      commit

      drop table tab2

      create table tab2(PROCEDURE_ID NUMBER(10),
      EQUIP_CODE VARCHAR2(10 BYTE),
      EQUIP_TYPE_ID NUMBER(10),
      CREATE_TIME DATE
      )
                
      insert into tab2(procedure_id,equip_code,equip_type_id,create_time) values(1206,'CELSTRI010',5005,trunc(sysdate-1))
      insert into tab2(procedure_id,equip_code,equip_type_id,create_time) values(1073,'CELSTRI010',5005,trunc(sysdate-1))

      commit

      ------------------------week 1st day is Sunday and last day is Saturday.
      frequency 1 is daily, 7 is weekly. If the weekly is done in the week, it should not populate until the week is over. The way I code is

      declare
      v_first_day date;
      v_last_day date;     
      v_system_date date;
      v_procedure_result_id number;
      v_procedure_id number;

      Cursor CA is     
      select TRUNC(SYSDATE, 'Day'),
      TRUNC(SYSDATE, 'Day')+6 from dual;


      Cursor CB is
      select distinct trunc(create_time)
      from tab2
      where EQUIP_TYPE_ID = 5005
      and procedure_id = :qc_procedures2.procedure_id;

      Begin

           Open CA;
           Fetch CA into v_first_day,v_last_day;
           Close CA;
           

           
           Open CB;
           Fetch CB into v_system_date;
           Close CB;

      If v_system_date is null then

           GO_BLOCK('QC_PROCEDURES2');     
      EXECUTE_QUERY;
      Else
      go_block('qc_procedures2');

      first_RECORD;
      LOOP

      select count(procedure_id) into v_procedure_id
      from tab1
      where EQUIP_TYPE_ID = 5005;

      ( Result is 3)

      If v_procedure_id > 0 then
      If v_system_date between v_first_day and v_last_day then

                select count(procedure_id) into v_procedure_result_id
      from tab2
      where EQUIP_TYPE_ID = 5005
      and equip_code = 'CELSTRI010'
      and procedure_id = :qc_procedures2.procedure_id;

      If v_procedure_result_id > 0 then
           If :qc_procedures2.frequency = 7 then
           set_item_property('qc_procedures2.task_button1',enabled,property_false);
      End if;
      End if;
      End if;
      End if;
      EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
      NEXT_RECORD;
      END LOOP;
      End if;
      End;

      There will be actually 3 lines on the canvas


      1206 Weekly Procedures 7 5005
      1207 Monthly Procedures 30 5005
      1073 Daily Lot Release 1 5005


      But my 1207 Monthly Procedures 30 5005 has to display . Either I want that line not to be queried at all or disabled. I am not able to get that. It goes to the block and only displays frequency 1 but not 30. Help appreciated.