DBA Data[Home] [Help]

APPS.INV_OBJECT_GENEALOGY SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 9

      SELECT 1
        INTO l_retval
        FROM mtl_lot_numbers
       WHERE gen_object_id = p_object_id;
Line: 16

          SELECT 2
            INTO l_retval
            FROM mtl_serial_numbers
           WHERE gen_object_id = p_object_id;
Line: 24

              SELECT 5
                INTO l_retval
                FROM wip_entities
               WHERE gen_object_id = p_object_id;
Line: 73

        SELECT concatenated_segments
             , msivl.description
             , mln.expiration_date
             , msivl.primary_uom_code
             , mln.inventory_item_id
             , mln.lot_number
             , mln.status_id
          INTO p_object_name
             , p_object_description
             , p_expiration_date
             , p_primary_uom
             , p_inventory_item_id
             , p_object_number
             , l_status_id
          FROM mtl_system_items_vl msivl, mtl_lot_numbers mln
         WHERE mln.gen_object_id = p_object_id
           AND mln.inventory_item_id = msivl.inventory_item_id
           AND mln.organization_id = msivl.organization_id;
Line: 127

          SELECT status_code
            INTO p_material_status
            FROM mtl_material_statuses_vl
           WHERE status_id = l_status_id;
Line: 145

      SELECT concatenated_segments
           , msivl.description
           , msn.end_item_unit_number
           , msn.serial_number
           , msn.inventory_item_id
           , msn.status_id
        INTO p_object_name
           , p_object_description
           , p_unit_number
           , p_object_number
           , p_inventory_item_id
           , l_status_id
        FROM mtl_system_items_vl msivl, mtl_serial_numbers msn
       WHERE msn.gen_object_id = p_object_id
         AND msn.inventory_item_id = msivl.inventory_item_id
         AND msn.current_organization_id = msivl.organization_id;
Line: 174

          SELECT status_code
            INTO p_material_status
            FROM mtl_material_statuses_vl
           WHERE status_id = l_status_id;
Line: 192

      SELECT meaning
        INTO p_object_type_name
        FROM mfg_lookups
       WHERE lookup_code = p_object_type
         AND lookup_type = 'INV_GENEALOGY_OBJECT_TYPE';
Line: 246

        SELECT concatenated_segments
             , msivl.description
             , mln.expiration_date
             , msivl.primary_uom_code
             , mln.inventory_item_id
             , mln.lot_number
             , mln.status_id
          INTO p_object_name
             , p_object_description
             , p_expiration_date
             , p_primary_uom
             , p_inventory_item_id
             , p_object_number
             , l_status_id
          FROM mtl_system_items_vl msivl, mtl_lot_numbers mln
         WHERE mln.gen_object_id = p_object_id
           AND mln.inventory_item_id = msivl.inventory_item_id
           AND mln.organization_id = msivl.organization_id;
Line: 300

          SELECT status_code
            INTO p_material_status
            FROM mtl_material_statuses_vl
           WHERE status_id = l_status_id;
Line: 318

      SELECT concatenated_segments
           , msivl.description
           , msn.end_item_unit_number
           , msn.serial_number
           , msn.inventory_item_id
           , msn.status_id
           , msn.wip_entity_id
           , msn.operation_seq_num
           , msn.intraoperation_step_type
           , msn.lot_number
        --Serial Tracking in WIP project. Retrieve wip_entity_id, operation_seq_num and
        -- intraoperation_step_type also.
      INTO   p_object_name
           , p_object_description
           , p_unit_number
           , p_object_number
           , p_inventory_item_id
           , l_status_id
           , x_wip_entity_id
           , x_operation_seq_num
           , x_intraoperation_step_type
           , x_current_lot_number
        FROM mtl_system_items_vl msivl, mtl_serial_numbers msn
       WHERE msn.gen_object_id = p_object_id
         AND msn.inventory_item_id = msivl.inventory_item_id
         AND msn.current_organization_id = msivl.organization_id;
Line: 367

          SELECT status_code
            INTO p_material_status
            FROM mtl_material_statuses_vl
           WHERE status_id = l_status_id;
Line: 385

      SELECT meaning
        INTO p_object_type_name
        FROM mfg_lookups
       WHERE lookup_code = p_object_type
         AND lookup_type = 'INV_GENEALOGY_OBJECT_TYPE';
Line: 414

       SELECT 1                                      --when queried by asembly serial
         FROM DUAL
        WHERE EXISTS (
                 SELECT 1
                   FROM mtl_object_genealogy
                  WHERE     genealogy_origin = 1
                        AND object_type = 2
                        AND parent_object_type = 5
                        AND (end_date_active IS NULL OR end_date_active > SYSDATE)
                        AND (object_id = NVL (p_object_id, p_object_id2))
                     OR                            --when quereid by component serial
                        EXISTS (
                           SELECT 1
                             FROM DUAL
                            WHERE EXISTS (
                                     SELECT mog.parent_object_id
                                       FROM mtl_object_genealogy mog
                                      WHERE object_type = 2
                                        AND parent_object_type = 2
                                        AND object_id =
                                                      NVL (p_object_id, p_object_id2)
                                        AND EXISTS (
                                               SELECT 1
         --if queried by comp serial then check if it's parent aser has job as parent
                                                 FROM mtl_object_genealogy
                                                WHERE genealogy_origin = 1
                                                  AND object_type = 2
                                                  AND parent_object_type = 5
                                                  AND (   end_date_active IS NULL
                                                       OR end_date_active > SYSDATE
                                                      )
                                                  AND object_id =
                                                                 mog.parent_object_id))))
           OR
          -- if queried by component lot, then check if it's parent has aser as child
              EXISTS (
                 SELECT 1
                   FROM DUAL
                  WHERE EXISTS (
                           SELECT mog.parent_object_id
                             FROM mtl_object_genealogy mog
                            WHERE object_type = 1
                              AND parent_object_type = 5
                              AND object_id = NVL (p_object_id, p_object_id2)
                              AND EXISTS (
                                     SELECT 1
                                       FROM mtl_object_genealogy
                                      WHERE genealogy_origin = 1
                                        AND object_type = 2
                                        AND parent_object_type = 5
                                        AND (   end_date_active IS NULL
                                             OR end_date_active > SYSDATE
                                            )
                                        AND parent_object_id = mog.parent_object_id)))
           OR
       --if queried by assembly lot of lot serial, then check if it's parent has aser as child
              EXISTS (
                 SELECT 1
                   FROM DUAL
                  WHERE EXISTS (
                           SELECT mog.object_id
                             FROM mtl_object_genealogy mog
                            WHERE parent_object_type = 1
                              AND object_type = 5
                              AND parent_object_id = NVL (p_object_id, p_object_id2)
                              AND EXISTS (
                                     SELECT 1
                                       FROM mtl_object_genealogy
                                      WHERE genealogy_origin = 1
                                        AND object_type = 2
                                        AND parent_object_type = 5
                                        AND (   end_date_active IS NULL
                                             OR end_date_active > SYSDATE
                                            )
                                        AND parent_object_id = mog.object_id)))
           OR
       --if queried by job and if it has aser as child
              EXISTS (
                 SELECT 1
                   FROM DUAL
                  WHERE EXISTS (
                           SELECT 1
                             FROM mtl_object_genealogy mog
                            WHERE parent_object_type = 5
                              AND object_type = 2
                              AND genealogy_origin=1
                              AND parent_object_id=p_object_id))    ;
Line: 537

        SELECT lot_number || l_genealogy_delimitter || concatenated_segments
          INTO l_object_number
          FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
         WHERE mln.gen_object_id = p_object_id
           AND mln.inventory_item_id = msikfv.inventory_item_id
           AND mln.organization_id = msikfv.organization_id;
Line: 544

        SELECT concatenated_segments || l_genealogy_delimitter || lot_number
          INTO l_object_number
          FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
         WHERE mln.gen_object_id = p_object_id
           AND mln.inventory_item_id = msikfv.inventory_item_id
           AND mln.organization_id = msikfv.organization_id;
Line: 551

        SELECT lot_number
          INTO l_object_number
          FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
         WHERE mln.gen_object_id = p_object_id
           AND mln.inventory_item_id = msikfv.inventory_item_id
           AND mln.organization_id = msikfv.organization_id;
Line: 562

          SELECT lot_number
            INTO l_lot_number
            FROM mtl_lot_numbers mln, mtl_system_items_kfv msikfv
           WHERE mln.gen_object_id = p_object_id2
             AND mln.inventory_item_id = msikfv.inventory_item_id
             AND mln.organization_id = msikfv.organization_id;
Line: 576

          SELECT    l_lot_number
                 || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter)
                 || serial_number
                 || l_genealogy_delimitter
                 || concatenated_segments
            INTO l_object_number
            FROM mtl_serial_numbers msn, mtl_system_items_kfv msikfv
           WHERE msn.gen_object_id = p_object_id
             AND msn.inventory_item_id = msikfv.inventory_item_id
             AND msn.current_organization_id = msikfv.organization_id;
Line: 587

          SELECT    concatenated_segments
                 || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter)
                 || l_lot_number
                 || l_genealogy_delimitter
                 || serial_number
            INTO l_object_number
            FROM mtl_serial_numbers msn, mtl_system_items_kfv msikfv
           WHERE msn.gen_object_id = p_object_id
             AND msn.inventory_item_id = msikfv.inventory_item_id
             AND msn.current_organization_id = msikfv.organization_id;
Line: 598

          SELECT l_lot_number || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter) || serial_number
            INTO l_object_number
            FROM mtl_serial_numbers msn, mtl_system_items_kfv msikfv
           WHERE msn.gen_object_id = p_object_id
             AND msn.inventory_item_id = msikfv.inventory_item_id
             AND msn.current_organization_id = msikfv.organization_id;
Line: 606

        SELECT l_lot_number || DECODE(l_lot_number, NULL, '', l_genealogy_delimitter) || serial_number
          INTO l_object_number
          FROM mtl_serial_numbers
         WHERE gen_object_id = p_object_id;
Line: 612

      SELECT we.primary_item_id
           , we.organization_id
           , we.wip_entity_name
        INTO l_item_id
           , l_orgn_id
           , l_job_name
        FROM wip_entities we
       WHERE we.gen_object_id = p_object_id;
Line: 622

        SELECT concatenated_segments
          INTO l_con_seg
          FROM mtl_system_items_kfv msikfv
         WHERE msikfv.inventory_item_id = l_item_id
           AND msikfv.organization_id = l_orgn_id;
Line: 651

      SELECT segment1
        INTO l_trx_src
        FROM po_headers_all
       WHERE po_header_id = p_trx_src_id;
Line: 656

      SELECT SUBSTR(concatenated_segments, 1, 30)
        INTO l_trx_src
        FROM mtl_sales_orders_kfv
       WHERE sales_order_id = p_trx_src_id;
Line: 664

      SELECT request_number
        INTO l_trx_src
        FROM mtl_txn_request_headers
       WHERE header_id = p_trx_src_id;
Line: 669

      SELECT wip_entity_name
        INTO l_trx_src
        FROM wip_entities
       WHERE wip_entity_id = p_trx_src_id
         AND organization_id = p_org_id;
Line: 675

      SELECT SUBSTR(concatenated_segments, 1, 30)
        INTO l_trx_src
        FROM mtl_generic_dispositions_kfv
       WHERE disposition_id = p_trx_src_id
         AND organization_id = p_org_id;
Line: 681

      SELECT segment1
        INTO l_trx_src
        FROM po_requisition_headers_all
       WHERE requisition_header_id = p_trx_src_id;
Line: 686

      SELECT cycle_count_header_name
        INTO l_trx_src
        FROM mtl_cycle_count_headers
       WHERE cycle_count_header_id = p_trx_src_id
         AND organization_id = p_org_id;
Line: 692

      SELECT physical_inventory_name
        INTO l_trx_src
        FROM mtl_physical_inventories
       WHERE physical_inventory_id = p_trx_src_id
         AND organization_id = p_org_id;
Line: 697

    ELSIF p_trx_src_type = 11 THEN   -- Standard Cost Update
      SELECT description
        INTO l_trx_src
        FROM cst_cost_updates
       WHERE cost_update_id = p_trx_src_id
         AND organization_id = p_org_id;
Line: 705

      SELECT COUNT(*)
        INTO row_count
        FROM mtl_txn_request_lines mol
       WHERE txn_source_id = p_trx_src_id
         AND organization_id = p_org_id
         AND EXISTS(SELECT NULL
                      FROM mtl_txn_request_headers
                     WHERE header_id = mol.header_id
                       AND move_order_type = 5
                       AND mol.transaction_source_type_id = 13);
Line: 717

        SELECT wip_entity_name
          INTO l_trx_src
          FROM wip_entities
         WHERE wip_entity_id = p_trx_src_id
           AND organization_id = p_org_id;
Line: 745

      SELECT vendor_name
        INTO l_trading_partner
        FROM po_vendors pov, po_headers_all poh
       WHERE poh.po_header_id = p_trx_src_id
         AND poh.vendor_id = pov.vendor_id;
Line: 751

      SELECT party_name
        INTO l_trading_partner
        FROM hz_parties hp, hz_cust_accounts hca ,
             -- R12 TCA Mandate  to replace RA_CUSTOMERS with the above 2
             oe_order_headers_all sha, oe_order_lines_all sla
       WHERE sla.line_id = p_trx_src_line_id
         AND sha.header_id = sla.header_id
         --AND sha.sold_to_org_id = rac.customer_id; As part of R12 TCA changes
Line: 762

      SELECT organization_code
        INTO l_trading_partner
        FROM mtl_parameters mp
       WHERE mp.organization_id = p_transfer_org_id;
Line: 781

    DELETE FROM mtl_gen_temp;
Line: 799

      SELECT   *
          FROM mtl_gen_temp
         WHERE treeno < g_treeno
           AND DEPTH < g_depth
      ORDER BY ind DESC;
Line: 809

         SELECT object_id
              , parent_object_id
              , object_type
              , NULL   object_id2
              , NULL   object_type2
              , NULL   parent_object_id2
         FROM   mtl_object_genealogy
         WHERE  parent_object_id  = p_object_id
           AND object_type<>2
       AND    (end_date_active is null or end_date_active > SYSDATE);
Line: 824

      SELECT object_id
           , parent_object_id
           , object_type
           , object_id2
           , object_type2
           , parent_object_id2
        FROM mtl_object_genealogy
       WHERE parent_object_id = p_object_id
	 AND (p_object_id2 IS NULL OR parent_object_id2 = p_object_id2)
         AND (end_date_active IS NULL OR end_date_active > SYSDATE);
Line: 853

        SELECT 1
          INTO l_count
          FROM mtl_gen_temp
         WHERE label = p_object_id
           AND related_label = 0
	        AND (p_object_id2 is null or label2=p_object_id2)
           AND ROWNUM < 2;
Line: 866

            inv_trx_util_pub.TRACE('insert1, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
Line: 869

          INSERT INTO mtl_gen_temp
                      (
                       ind
                     , treeno
                     , DEPTH
                     , label
                     , related_label
                     , child_object_type
                     , label2
                     , child_object_type2
                      )
               VALUES (
                       g_ind
                     , g_treeno
                     , g_depth
                     , p_object_id
                     , p_related_object_id
                     , p_object_type
                     , p_object_id2
                     , p_object_type2
                      );
Line: 905

        SELECT 1
          INTO l_count
          FROM mtl_gen_temp
         WHERE treeno = g_treeno
           AND label = p_object_id
	   AND (p_object_id2 is null or label2=p_object_id2)
           AND ROWNUM < 2;
Line: 919

             inv_trx_util_pub.TRACE('insert2 g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
Line: 922

          INSERT INTO mtl_gen_temp
                      (
                       ind
                     , treeno
                     , DEPTH
                     , label
                     , related_label
                     , child_object_type
                     , label2
                     , child_object_type2
                      )
               VALUES (
                       g_ind
                     , g_treeno
                     , g_depth
                     , p_object_id
                     , p_related_object_id
                     , p_object_type
                     , p_object_id2
                     , p_object_type2
                      );
Line: 967

             inv_trx_util_pub.TRACE('insert3 g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
Line: 970

          INSERT INTO mtl_gen_temp
                      (
                       ind
                     , treeno
                     , DEPTH
                     , label
                     , related_label
                     , child_object_type
                     , label2
                     , child_object_type2
                      )
               VALUES (
                       g_ind
                     , g_treeno
                     , g_depth
                     , p_object_id
                     , p_related_object_id
                     , p_object_type
                     , p_object_id2
                     , p_object_type2
                      );
Line: 1002

         inv_trx_util_pub.TRACE('insert4 g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
Line: 1005

      INSERT INTO mtl_gen_temp
                  (
                   ind
                 , treeno
                 , DEPTH
                 , label
                 , related_label
                 , child_object_type
                 , label2
                 , child_object_type2
                  )
           VALUES (
                   g_ind
                 , g_treeno
                 , g_depth
                 , p_object_id
                 , p_related_object_id
                 , p_object_type
                 , p_object_id2
                 , p_object_type2
                  );
Line: 1087

      SELECT level
            , mog.object_id
            , mog.parent_object_id
            , mog.object_type
            , NULL object_id2
            , NULL object_type2
            , NULL parent_object_id2
      FROM   mtl_object_genealogy mog
     -- WHERE object_type<>2--((object_type = 2 AND parent_object_type = object_type) OR (object_type <> 2))
     -- AND    (end_date_active is null or end_date_active > SYSDATE)
      START WITH (parent_object_id=p_object_id and (end_date_active is null or end_date_active > SYSDATE))
      CONNECT BY prior object_id = parent_object_id
      AND object_type<>2 --((object_type = 2 AND parent_object_type = object_type) OR (object_type <> 2))
      AND  (end_date_active is null or end_date_active > SYSDATE);
Line: 1105

      SELECT     LEVEL
               , object_id
               , parent_object_id
               , object_type
               , object_id2
               , object_type2
               , parent_object_id2
            FROM mtl_object_genealogy
        --  WHERE end_date_active IS NULL
        --  OR end_date_active > SYSDATE
      START WITH (parent_object_id=p_object_id and (end_date_active is null or end_date_active > SYSDATE))
      CONNECT BY PRIOR object_id = parent_object_id
      AND (end_date_active IS NULL OR end_date_active > SYSDATE);
Line: 1137

    INSERT INTO mtl_gen_temp
                (
                 ind
               , treeno
               , DEPTH
               , label
               , related_label
               , child_object_type
               , label2
               , child_object_type2
                )
         VALUES (
                 g_ind
               , g_treeno
               , g_depth
               , p_object_id
               , p_related_object_id
               , p_object_type
               , p_object_id2
               , p_object_type2
                );
Line: 1177

        INSERT INTO mtl_gen_temp
                    (
                     ind
                   , treeno
                   , DEPTH
                   , label
                   , related_label
                   , child_object_type
                   , label2
                   , child_object_type2
                    )
             VALUES
                     ( g_ind,
                       g_treeno,
                       child_rec.level+1,
                       child_rec.object_id,
                       child_rec.parent_object_id,
                       child_rec.object_type,
                       child_rec.object_id2,
                       child_rec.object_type2);
Line: 1219

        INSERT INTO mtl_gen_temp
                    (
                     ind
                   , treeno
                   , DEPTH
                   , label
                   , related_label
                   , child_object_type
                   , label2
                   , child_object_type2
                    )
             VALUES
                     ( g_ind,
                       g_treeno,
                       child_rec.level+1,
                       child_rec.object_id,
                       child_rec.parent_object_id,
                       child_rec.object_type,
                       child_rec.object_id2,
                       child_rec.object_type2);
Line: 1270

      SELECT parent_object_id
           , object_id
           , parent_object_type
           , parent_object_id2
           , parent_object_type2, object_id2,object_type2,object_type
       FROM mtl_object_genealogy
       WHERE object_id = p_object_id
	 AND (p_object_id2 IS NULL OR object_id2 = p_object_id2)
         AND(end_date_active IS NULL
             OR end_date_active > SYSDATE);
Line: 1282

      SELECT   *
          FROM mtl_gen_temp
         WHERE treeno < g_treeno
           AND DEPTH < g_depth
      ORDER BY ind DESC;
Line: 1304

        SELECT 1
          INTO l_count
          FROM mtl_gen_temp
         WHERE label = p_object_id
           AND related_label = 0
	   AND (p_object_id2 is null or label2=p_object_id2)
           AND ROWNUM < 2;
Line: 1317

            inv_trx_util_pub.TRACE('insert1, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
Line: 1321

          INSERT INTO mtl_gen_temp
                      (
                       ind
                     , treeno
                     , DEPTH
                     , label
                     , related_label
                     , child_object_type
                     , label2
                     , child_object_type2
                      )
               VALUES (
                       g_ind
                     , g_treeno
                     , g_depth
                     , p_object_id
                     , p_related_object_id
                     , p_object_type
                     , p_object_id2
                     , p_object_type2
                      );
Line: 1357

        SELECT 1
          INTO l_count
          FROM mtl_gen_temp
         WHERE treeno = g_treeno
           AND label = p_object_id
 	   AND (p_object_id2 is null or label2=p_object_id2)
           AND ROWNUM < 2;
Line: 1372

            inv_trx_util_pub.TRACE('insert2, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
Line: 1376

          INSERT INTO mtl_gen_temp
                      (
                       ind
                     , treeno
                     , DEPTH
                     , label
                     , related_label
                     , child_object_type
                     , label2
                     , child_object_type2
                      )
               VALUES (
                       g_ind
                     , g_treeno
                     , g_depth
                     , p_object_id
                     , p_related_object_id
                     , p_object_type
                     , p_object_id2
                     , p_object_type2
                      );
Line: 1420

            inv_trx_util_pub.TRACE('insert3, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
Line: 1424

          INSERT INTO mtl_gen_temp
                      (
                       ind
                     , treeno
                     , DEPTH
                     , label
                     , related_label
                     , child_object_type
                     , label2
                     , child_object_type2
                      )
               VALUES (
                       g_ind
                     , g_treeno
                     , g_depth
                     , p_object_id
                     , p_related_object_id
                     , p_object_type
                     , p_object_id2
                     , p_object_type2
                      );
Line: 1457

        inv_trx_util_pub.TRACE('insert4, g_ind '||g_ind, 'INV_OBJECT_GENEALOGY', 9);
Line: 1461

      INSERT INTO mtl_gen_temp
                  (
                   ind
                 , treeno
                 , DEPTH
                 , label
                 , related_label
                 , child_object_type
                 , label2
                 , child_object_type2
                  )
           VALUES (
                   g_ind
                 , g_treeno
                 , g_depth
                 , p_object_id
                 , p_related_object_id
                 , p_object_type
                 , p_object_id2
                 , p_object_type2
                  );
Line: 1526

      SELECT     LEVEL
              , mog.parent_object_id
              , mog.object_id
              , mog.parent_object_type
              , mog.parent_object_id2
              , mog.parent_object_type2
           FROM mtl_object_genealogy mog
         -- WHERE end_date_active IS NULL
           --  OR end_date_active > SYSDATE
     START WITH (object_id = p_object_id and (end_date_active is null or end_date_active > SYSDATE))
     CONNECT BY PRIOR parent_object_id = object_id
     AND (end_date_active IS NULL OR end_date_active > SYSDATE);
Line: 1558

    INSERT INTO mtl_gen_temp
                (
                 ind
               , treeno
               , DEPTH
               , label
               , related_label
               , child_object_type
               , label2
               , child_object_type2
                )
         VALUES (
                 g_ind
               , g_treeno
               , g_depth
               , p_object_id
               , p_related_object_id
               , p_object_type
               , p_object_id2
               , p_object_type2
                );
Line: 1596

         INSERT INTO mtl_gen_temp
                     (
                      ind
                    , treeno
                    , DEPTH
                    , label
                    , related_label
                    , child_object_type
                    , label2
                    , child_object_type2
                     )
              VALUES (
                       g_ind
                      ,g_treeno
                      ,parent_rec.level+1
                      ,parent_rec.parent_object_id
                      ,parent_rec.object_id
                      ,parent_rec.parent_object_type
                      ,parent_rec.parent_object_id2
                     ,parent_rec.parent_object_type2);