oracle apex - How can I select section ID from the collection and insert into database depends on IF condition? - Stack Overflow

admin管理员组

文章数量:1122846

I am building laboratory order system using oracle apex ,

each test in laboratory belongs to specific section or department for example hematology , clinical chemistry , hormones , serology and parasitology.

I created a collection and I need to generate sample ID for each section from the sequence also I created oracle sequence for each section and I need to select the section from the collection before insert , this is the code in process I tried when click save order :

declare
L_SECTION NUMBER;
L_SAMPLE_TYPE NUMBER; 
L_SAMPLE_ID VARCHAR2(20);
cursor SECTION is select section_id from lab_sections;
cursor SAMPLES is select id from lab_sample_type;

begin 
 
if not apex_collection.collection_exists('TESTS') then 
apex_collection.create_collection('TESTS');
end if;

SELECT LAB_ORDERS_SEQ.NEXTVAL INTO :P58_ORDER_NO FROM DUAL;

insert into lab_orders(ORDER_ID , patient_no , cust_no , order_date , diagnosis , order_status , order_priority , patient_category , 
pregnancy , lactating , created_by )
values (:P58_ORDER_NO , :P58_IDNO , :P58_CUSTOMER , sysdate , :P58_DIAGNOSIS , 1 , :P58_ORDER_PRIORITY , 1, :P58_PREGNANCY , :P58_LACTATING, :P0_USERNAME);

INSERT INTO LAB_ORDER_DETAILS (ORDER_ID,TEST_NO,TEST_COST,TEST_VAT,TOTAL_AMOUNT,PROFILE_NO,SECTION_ID,QTY)
SELECT :P58_ORDER_NO,c001,c005,c006,c007,1,c003,c004 from apex_collections where collection_name='TESTS'; 

-- GENERATE SAMPLES DETAILS 

FOR S IN SECTION LOOP 
L_SECTION := S.SECTION_ID; 
IF L_SECTION = 1 THEN 
SELECT 'HE'||'-'||LAB_HEMA.NEXTVAL INTO L_SAMPLE_ID FROM DUAL; 

insert into lab_sample_header(ORDER_ID , cust_no , patient_no , sample_status , section_id , sample_id )
values (:P58_ORDER_NO , :P58_CUSTOMER , :P58_IDNO , 1 , :P58_SECTION ,L_SAMPLE_ID  );

INSERT INTO LAB_SAMPLE_DETAILS (ORDER_ID,SAMPLE_ID,TEST_NO,PROFILE_NO,SAMPLE_STATUS,SAMPLE_TYPE,SECTION_ID)
SELECT :P58_ORDER_NO,L_SAMPLE_ID,c001,1,1,c008,c003 from apex_collections where collection_name='TESTS'; 


ELSIF L_SECTION = 2 THEN 
SELECT 'BCH'||'-'||LAB_CHEM.NEXTVAL INTO L_SAMPLE_ID FROM DUAL; 

insert into lab_sample_header(ORDER_ID , cust_no , patient_no , sample_status , section_id , sample_id )
values (:P58_ORDER_NO , :P58_CUSTOMER , :P58_IDNO , 1 , :P58_SECTION ,L_SAMPLE_ID  );

INSERT INTO LAB_SAMPLE_DETAILS (ORDER_ID,SAMPLE_ID,TEST_NO,PROFILE_NO,SAMPLE_STATUS,SAMPLE_TYPE,SECTION_ID)
SELECT :P58_ORDER_NO,L_SAMPLE_ID,c001,1,1,c008,c003 from apex_collections where collection_name='TESTS'; 

ELSIF L_SECTION = 3 THEN 
SELECT 'HOR'||'-'||LAB_HORM.NEXTVAL INTO L_SAMPLE_ID FROM DUAL; 

insert into lab_sample_header(ORDER_ID , cust_no , patient_no , sample_status , section_id , sample_id )
values (:P58_ORDER_NO , :P58_CUSTOMER , :P58_IDNO , 1 , :P58_SECTION ,L_SAMPLE_ID  );

INSERT INTO LAB_SAMPLE_DETAILS (ORDER_ID,SAMPLE_ID,TEST_NO,PROFILE_NO,SAMPLE_STATUS,SAMPLE_TYPE,SECTION_ID)
SELECT :P58_ORDER_NO,L_SAMPLE_ID,c001,1,1,c008,c003 from apex_collections where collection_name='TESTS'; 

ELSIF L_SECTION = 4 THEN 
SELECT 'SER'||'-'||LAB_SERO.NEXTVAL INTO L_SAMPLE_ID FROM DUAL; 

insert into lab_sample_header(ORDER_ID , cust_no , patient_no , sample_status , section_id , sample_id )
values (:P58_ORDER_NO , :P58_CUSTOMER , :P58_IDNO , 1 , :P58_SECTION ,L_SAMPLE_ID  );

INSERT INTO LAB_SAMPLE_DETAILS (ORDER_ID,SAMPLE_ID,TEST_NO,PROFILE_NO,SAMPLE_STATUS,SAMPLE_TYPE,SECTION_ID)
SELECT :P58_ORDER_NO,L_SAMPLE_ID,c001,1,1,c008,c003 from apex_collections where collection_name='TESTS'; 

ELSIF L_SECTION = 5 THEN 
SELECT 'BB'||'-'||LAB_BB.NEXTVAL INTO L_SAMPLE_ID FROM DUAL; 

insert into lab_sample_header(ORDER_ID , cust_no , patient_no , sample_status , section_id , sample_id )
values (:P58_ORDER_NO , :P58_CUSTOMER , :P58_IDNO , 1 , :P58_SECTION ,L_SAMPLE_ID  );

INSERT INTO LAB_SAMPLE_DETAILS (ORDER_ID,SAMPLE_ID,TEST_NO,PROFILE_NO,SAMPLE_STATUS,SAMPLE_TYPE,SECTION_ID)
SELECT :P58_ORDER_NO,L_SAMPLE_ID,c001,1,1,c008,c003 from apex_collections where collection_name='TESTS'; 

ELSIF L_SECTION = 6 THEN 
SELECT 'PARA'||'-'||LAB_PARA.NEXTVAL INTO L_SAMPLE_ID FROM DUAL; 

insert into lab_sample_header(ORDER_ID , cust_no , patient_no , sample_status , section_id , sample_id )
values (:P58_ORDER_NO , :P58_CUSTOMER , :P58_IDNO , 1 , :P58_SECTION ,L_SAMPLE_ID  );

INSERT INTO LAB_SAMPLE_DETAILS (ORDER_ID,SAMPLE_ID,TEST_NO,PROFILE_NO,SAMPLE_STATUS,SAMPLE_TYPE,SECTION_ID)
SELECT :P58_ORDER_NO,L_SAMPLE_ID,c001,1,1,c008,c003 from apex_collections where collection_name='TESTS'; 

ELSIF L_SECTION = 7 THEN 
SELECT 'CULT'||'-'||LAB_CULT.NEXTVAL INTO L_SAMPLE_ID FROM DUAL; 

insert into lab_sample_header(ORDER_ID , cust_no , patient_no , sample_status , section_id , sample_id )
values (:P58_ORDER_NO , :P58_CUSTOMER , :P58_IDNO , 1 , :P58_SECTION ,L_SAMPLE_ID  );

INSERT INTO LAB_SAMPLE_DETAILS (ORDER_ID,SAMPLE_ID,TEST_NO,PROFILE_NO,SAMPLE_STATUS,SAMPLE_TYPE,SECTION_ID)
SELECT :P58_ORDER_NO,L_SAMPLE_ID,c001,1,1,c008,c003 from apex_collections where collection_name='TESTS'; 

ELSIF L_SECTION = 8 THEN 
SELECT 'HISTO'||'-'||LAB_HISTO.NEXTVAL INTO L_SAMPLE_ID FROM DUAL; 

insert into lab_sample_header(ORDER_ID , cust_no , patient_no , sample_status , section_id , sample_id )
values (:P58_ORDER_NO , :P58_CUSTOMER , :P58_IDNO , 1 , :P58_SECTION ,L_SAMPLE_ID  );

INSERT INTO LAB_SAMPLE_DETAILS (ORDER_ID,SAMPLE_ID,TEST_NO,PROFILE_NO,SAMPLE_STATUS,SAMPLE_TYPE,SECTION_ID)
SELECT :P58_ORDER_NO,L_SAMPLE_ID,c001,1,1,c008,c003 from apex_collections where collection_name='TESTS'; 

COMMIT;

apex_collection.truncate_collection(p_collection_name => 'TESTS') ;
END IF;
END LOOP;
end;

See the image please in this example I selected 4 tests 2 tests section clinical chemistry , 1 test hematology section and 1 test parasitology section. now the issue the IF statement not compare the section_id with the section in the collection and inserting each test for all sections

How to select section_id from the collection into variable L_SAMPLE_ID and use it within IF statement ?

check this part from here -- GENERATE SAMPLES DETAILS

and thank you.

本文标签：