Getting primary key from db2 merge in php script

[thenorman138]

I have a successful select query, where I'm looping and building a row for getting parameters which works correctly

    while ($row = $orderDetailCheck->fetch(PDO::FETCH_ASSOC)) {

                $params = [
                ":INVOICE_NUMBER" => $row["INVOICE_NUMBER"],
                ":CUSTNM" => $row["CUSTNM"],
                ":SELLINGN" => $row["SELLINGN"],
                ":GROUP" => $row["GROUP"],
                ":DTL12" => $row["DTL12"],
                ":DTL13" => $row["DTL13"],
                ":QUANTITY" => $row["QUANTITY"],
                ":COUNT_PLMN_1" => $row["COUNT_PLMN_1"],
                ":LAST_DATE" => $row["LAST_DATE"],
            ];

    }

My issue now is that, for each row, I need to perform two merges because the data from that select is going to be split into two tables in db2. Some values are truly split between the tables but some values are shared between the two. I'm not sure the best way to perform these two merges because if the first one (products table) inserts, then it creates an ID that I need as a foreign key basically, to insert into the orders table. So on insert I need to grab that newly created ID and use it for ```product_id``` in the second merge. If the first merge performs the update when matched, then I need to grab the existing ID for that record so that I can update the proper record in the orders table.

My two merge statements:

    /*products table*/
    MERGE INTO products AS P
        USING(VALUES(

            :GROUP,
            :DTL12,
            :DTL13,
            :CUSTNM,
            :SELLINGN,
            :COUNT_PLMN_1,
            :LAST_DATE

            )
        )

    AS S(GROUP,DTL12,DTL13,CUSTNM,SELLINGN,COUNT_PLMN_1,LAST_DATE)
    ON s.GROUP = p.GROUP and s.DTL12 = p.DTL12 and s.DTL13 = p.DTL13 and s.CUSTNM = p.CUSTNM 

    WHEN MATCHED
        THEN UPDATE SET LAST_DATE = s.LAST_DATE

    WHEN NOT MATCHED
        THEN INSERT VALUES (s.GROUP,s.DTL12,s.DTL13,s.CUSTNM,s.SELLINGN,s.COUNT_PLMN_1,s.LAST_DATE)

    /*ORDERS Table*/
    MERGE INTO ORDERS AS PO
        USING(VALUES(

            /*need foreign key, which is id from products table*/
            :QUANTITY,
            :LAST_DATE,
            :INVOICE_NUMBER
        ))

    AS S(PRODUCT_ID,quantity_ordered,LAST_DATE,invoice_number)
    ON s.PRODUCT_ID = po.id

    WHEN MATCHED
        THEN UPDATE SET LAST_DATE = s.LAST_DATE, quantity_ordered = s.quantity_ordered, invoice_number = s.invoice_number

    WHEN NOT MATCHED
        THEN INSERT VALUES (s.PRODUCT_ID, s.quantity_ordered, s.LAST_DATE, s.invoice_number)

Examples:

If my ROW Returns

    INVOICE  |  CUSTNM  |  SELLINGNUM  |  GROUP  |  DTL12  |  DTL13  |  QUANTITY  |  COUNT_PLMN_1  |  LAST_DATE
    ==================================================================================================================
    1122        123         321         995        1452       12        13              5               '2018-12-14'

Then my insert into products would be 

    products

        ID      |  GROUP  |  DTL12  |  DTL13  |  CUSTNM  |  SELLINGNUM  |  COUNT_PLMN_1  |  LAST_DATE
    ==========================================================================================================
    1                995        1452       12       123         321             5               '2018-12-14'

and my insert to orders:

    ORDERS

    PRODUCT_ID  |  QUANTITY_ORDERED  |  LAST_DATE   |  INVOICE
    ============================================================
    1                       13           '2018-12-14'    1122    

But if my next record matched on my unique qualities with a new invoice, quantity and date

ROW Returns

    INVOICE  |  CUSTNM  |  SELLINGNUM  |  GROUP  |  DTL12  |  DTL13  |  QUANTITY  |  COUNT_PLMN_1  |  LAST_DATE
    ==================================================================================================================
    1133        123         321           995        1452       12        4              5               '2018-12-18'

Then I would update products like so:

    products

        ID      |  GROUP  |  DTL12  |  DTL13  |  CUSTNM  |  SELLINGNUM  |  COUNT_PLMN_1  |  LAST_DATE
    ==========================================================================================================
    1                995        1452       12       123         321             5               '2018-12-18'

and update orders like so:

    ORDERS

    PRODUCT_ID  |  QUANTITY_ORDERED  |  LAST_DATE   |  INVOICE
    ============================================================
    1                       4            '2018-12-18'    1133    

I guess the main question is: How can I get the ID of a record from the products table (whether it's an existing match OR newly created in the merge) and once I get it, how can I use it for the 2nd merge?