How to link HZ_PARTY_SITE_USES and HZ_CUST_SITE_USES_ALL

Following SQL statement gives the link between HZ_PARTY_SITE_USES and HZ_CUST_SITE_USES_ALL table:-

SELECT hp.party_id,
       hp.party_name,
       hp.party_number,
       hps.party_site_id,
       hcas.org_id,
       hpsu.site_use_type,
       hps.party_site_number,
       hp.status hz_parties,
       hps.status hz_party_sites,
       hpsu.status hz_party_site_uses,
       hcsu.site_use_code,
       hca.status hz_cust_accounts,
       hcas.status hz_cust_acct_sites_all,
       hcsu.status hz_cust_site_uses_all
FROM hz_parties hp,
     hz_party_sites hps,
     hz_party_site_uses hpsu,
     hz_cust_accounts hca,
     hz_cust_acct_sites_all hcas,
     hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND   hp.party_id = hca.party_id
AND   hps.party_site_id = hcas.party_site_id
AND   hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND   hps.party_site_id = hpsu.party_site_id
AND   hcsu.site_use_code = hpsu.site_use_type
AND   hp.party_id = &P_PARTY_ID;

Oracle DBMS_AQ.dequeue sample code

--------------------------------------------------------------------------------------------------------------------
-- Following is a sample code on how to dequeue and insert into a custom table from AQ table:-
--------------------------------------------------------------------------------------------------------------------
DECLARE

   r_dequeue_options       DBMS_AQ.dequeue_options_t;
   r_message_properties   DBMS_AQ.message_properties_t;
   v_message_handle        RAW (16);
   o_payload                     xx_custom_payload_obj;
   lqname                          VARCHAR2 (30 BYTE);
   l_userdata                      XMLTYPE;
   l_usertype                     ANYDATA;
   l_msgid                         RAW (16);
   l_clob                            CLOB;
   l_corrid                         VARCHAR2 (128 BYTE);
   l_recid                           NUMBER := &p_recid;
  
BEGIN
   SELECT q_name,user_prop, msgid, corrid
     INTO lqname,l_usertype, l_msgid, l_corrid
     FROM xx_custom_tbl
    WHERE msgid = &p_msgid;

   r_dequeue_options.dequeue_mode := DBMS_AQ.remove;
   r_dequeue_options.msgid := l_msgid;
   r_dequeue_options.navigation := DBMS_AQ.next_message;
   r_dequeue_options.deq_condition := l_corrid;

   DBMS_AQ.dequeue (queue_name              => lqname,
                                       dequeue_options       => r_dequeue_options,
                                       message_properties   => r_message_properties,
                                        payload                     => l_userdata,
                                        msgid                        => v_message_handle
                                      );

   SELECT XMLTYPE.getclobval (l_userdata)
        INTO l_clob
      FROM DUAL;

   INSERT INTO xx_custom_xml
               (rec_id, file_name, file_struct, xml_data, msgid
               )
        VALUES (l_recid, 'DQTESTFILE.XML', l_clob, l_userdata, l_msgid
               );

   COMMIT;
  DBMS_OUTPUT.PUT_LINE('*** Browsed message is [' || o_payload.message || '] ***');

EXCEPTION
   WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
      ROLLBACK;
END;
--------------------------------------------------------------------------------------------------------------------