Round 2: aggregate & concatenate values from related tables Oracle 12c SQL

Listing this anew in hopes that if I ask a better question, I may get a helpful response from the resident experts.  

I need to use listagg and concatenation to create a field that begins with the track_item_id, adds any and all p_code, add any and all serial_number, and then places the status for each track_item_id,  p_code / serial_number combination.  

There are thousands of records, the example attempts to bring the logic definition to the barest possible groupings.

 To get there, take a cmp_id from the track_item1 table, add a product id from the mipp2 table.  See if those two fields define a record in a third prodlic3 table…  If so, list the p_code and serial_number, then pick up the status_id from there and use it to determine the status coming from pm_product_permit_status table..

Insert the status value into the current concatenation....placing the status after the serial number .. If there is no serial number, add a comma in front of the status

Product status
The prod_permit4 table carries a perrmit_status_id and the tip_status5 table carries the coded status (done--notdone--ignored--etc.)

There will be one only status for each combination. I have tried adding to the concatenation, but find it time I ask for help in putting together the SQL to get it right.

Any and all pointers and suggestion will be greatly appreciated……..
-----------------------------
        •   For each product, take cmp_id from track_item1 table and one of the PRD_ID from mipp2 table.  
             •   See if those two fields define a record in prod3.  If they do, then return the value from tip_status5 corresponding to the permit_status_id in the prod_permit table.
             •   If the cmp_id and PRD_ID do NOT define a record in the PROD3 table, return the value “NotHere”.
-----------------------------

CREATE TABLE MIPP2
(
  TI_ID          VARCHAR2(12 BYTE),
  PR_ID          VARCHAR2(12 BYTE),
  SERIAL_NUMBER  NUMBER(8)
);

CREATE TABLE PRODLIC3
(
  P_ID    VARCHAR2(12 BYTE),
  P_CODE  VARCHAR2(12 BYTE),
  CMP_ID  NUMBER(8)
);

CREATE TABLE PROD_PERMIT4
(
  PRD_ID            NUMBER(8),
  CMP_ID            NUMBER(8),
  PERMIT_STATUS_ID  NUMBER(8)
);

CREATE TABLE TIP_STATUS5
(
  STATUS_ID  NUMBER(8),
  STATUS     VARCHAR2(12 BYTE)
);

CREATE TABLE TRACK_ITEM1
(
  TRACK_ITEM_ID  NUMBER(8),
  CMP_ID         NUMBER(8)
);

Insert into MIPP2
   (TI_ID, PR_ID, SERIAL_NUMBER)
 Values
   ('11', '8', 452);
Insert into MIPP2
   (TI_ID, PR_ID, SERIAL_NUMBER)
 Values
   ('12', '96', NULL);
Insert into MIPP2
   (TI_ID, PR_ID, SERIAL_NUMBER)
 Values
   ('13', '9', NULL);

Insert into PRODLIC3
   (P_ID, P_CODE, CMP_ID)
 Values
   ('8', '66', 5);
Insert into PRODLIC3
   (P_ID, P_CODE, CMP_ID)
 Values
   ('96', '55', 6);
Insert into PRODLIC3
   (P_ID, P_CODE, CMP_ID)
 Values
   ('90', '45', NULL);

Insert into PROD_PERMIT4
   (PRD_ID, CMP_ID, PERMIT_STATUS_ID)
 Values
   (8, 5, 1);
Insert into PROD_PERMIT4
   (PRD_ID, CMP_ID, PERMIT_STATUS_ID)
 Values
   (96, 6, 2);

Insert into TIP_STATUS5
   (STATUS_ID, STATUS)
 Values
   (3, 'ignored');
Insert into TIP_STATUS5
   (STATUS_ID, STATUS)
 Values
   (1, 'done');
Insert into TIP_STATUS5
   (STATUS_ID, STATUS)
 Values
   (2, 'notdone');

Insert into TRACK_ITEM1
   (TRACK_ITEM_ID, CMP_ID)
 Values
   (12, 6);
Insert into TRACK_ITEM1
   (TRACK_ITEM_ID, CMP_ID)
 Values
   (13, 7);
Insert into TRACK_ITEM1
   (TRACK_ITEM_ID, CMP_ID)
 Values
   (11, 5);

Select allOpen in new window

Desired Result:

Track_item_id   concatenation
     11                      66 -  452, done
     12                      55 -    , notdone
     13                   NotHere