in Oracle, how can we dequeue data conditionally in dbms_aq.dequeue

Oracle DB is 11g. My question is related to Oracle APPS ERP.

We are using dbms_aq.dequeue to dequeue Sales Orders. The problem is, all the Sales Orders Lines are dequeued. We want to put a condition that dbms_aq.dequeue should only dequeue such Sales Order Lines where ORG_ID is 82.

This is a standard package ASO_ORDER_FEEDBACK_GET_PVT in Oracle.

DECLARATION

l_message SYSTEM.ASO_Order_Feedback_Type;

CODE

dbms_aq.dequeue(queue_name => l_queue_name,
                dequeue_options => l_dequeue_options,
                message_properties => l_message_properties,
                payload => l_message,
                msgid => l_msg_id);

Now, SYSTEM.ASO_Order_Feedback_Type is made up of many other types i.e. header_type ASO_Header_Type, line_varray ASO_Line_Var_Type etc.

Suppose, if I want to put a condition to retrieve rows conditionally from header_type, I am able to put the following condition before calling dbms_aq.dequeue

l_dequeue_options.deq_condition := 'tab.user_data.header_type.org_id=99';

But I do not know how to put a condition to retrieve rows conditionally from line_varray because

1. line_varray is of ASO_Line_Var_Type
2. type ASO_Line_Var_Type is defined AS VARRAY ( 1000000000 ) OF ASO_Line_Type
3. and ASO_Line_Type is defined AS OBJECT (accounting_rule_id NUMBER , actual_arrival_date DATE , .... , org_id NUMBER , ....)

Can I put the condition like this

l_dequeue_options.deq_condition := 'tab.user_data.line_varray.org_id=99';

Use TABLE and EXISTS operators.

l_dequeue_options.deq_condition := 'EXISTS(
      SELECT 1 FROM
      TABLE(CAST(tab.user_data.line_varray AS SYSTEM.ASO_Line_Var_Type)) e
      WHERE e.org_id = 99)';