========QUERY over DB Link with driving site hint


SELECT /*+ DRIVING_SITE(dummy) */ XMLELEMENT
          ("REPORT",
           (XMLELEMENT ("REPORT_HEADER",
                        XMLELEMENT ("DOCUMENT_TYPE", 'Purchase Order'),
                        XMLELEMENT ("REPORT_SUMMARY",
                                    XMLELEMENT ("REPORT_TOTAL", 0),
                                    XMLELEMENT ("PO_CNT", 0),
                                    XMLELEMENT ("RUN_DATE", '20050705'),
                                    XMLELEMENT ("START_DATE", '20050630'),
                                    XMLELEMENT ("END_DATE", '20050705')
                                   )
                       )
           ),
           (SELECT SYS_XMLAGG
                      (XMLELEMENT
                          ("PO",
                           XMLELEMENT ("POSUMMARY",
                                       XMLELEMENT ("PO_ITEM_COUNT",
                                                   po_item_count
                                                  ),
                                       XMLELEMENT ("PO_AMT", po_amt)
                                      ),
                           XMLELEMENT
                              ("USER_NAME",
                               def_common_extraction_pks.format_data
                                                                    (user_name)
                              ),
                           XMLELEMENT
                              ("PO_NUMBER",
                               def_common_extraction_pks.format_data
                                                                    (po_number)
                              ),
                           XMLELEMENT
                              ("SUPPLIER",
                               XMLELEMENT
                                  ("SUPPLIER_NAME",
                                   def_common_extraction_pks.format_data
                                                                (supplier_name)
                                  ),
                               XMLELEMENT ("SUPPLIER_ID", supplier_id),
                               XMLELEMENT
                                  ("BUSINESS_UNIT",
                                   def_common_extraction_pks.format_data
                                                                (business_unit)
                                  )
                              ),
                           XMLELEMENT
                              ("BUYER",
                               XMLELEMENT
                                  ("BUYER_NAME",
                                   def_common_extraction_pks.format_data
                                                                   (buyer_name)
                                  ),
                               XMLELEMENT ("BUYER_CORPID", buyer_corpid)
                              ),
                           (SELECT SYS_XMLAGG
                                      (XMLELEMENT
                                          ("ITEM",
                                           XMLELEMENT ("PO_LINE_ITEM",
                                                       po_line_item
                                                      ),
                                           XMLELEMENT
                                              ("SUPPLIER_NAME",
                                               def_common_extraction_pks.format_data
                                                                (supplier_name)
                                              ),
                                           XMLELEMENT ("SUPPLIER_ID",
                                                       supplier_id
                                                      ),
                                           XMLELEMENT
                                              ("PART_NUMBER",
                                               def_common_extraction_pks.format_data
                                                                  (part_number)
                                              ),
                                           XMLELEMENT
                                              ("PNAME",
                                               def_common_extraction_pks.format_data
                                                                        (pname)
                                              ),
                                           (SELECT SYS_XMLAGG
                                                      (XMLELEMENT
                                                          ("DISTRIBUTION",
                                                           XMLELEMENT
                                                                  ("ICRVALUE",
                                                                   icrvalue
                                                                  ),
                                                           XMLELEMENT
                                                              ("LEVELTEXT",
                                                               def_common_extraction_pks.format_data
                                                                    (leveltext)
                                                              ),
                                                           XMLELEMENT
                                                                  ("SPLITAMT",
                                                                   splitamt
                                                                  )
                                                          ),
                                                       xmlformat
                                                           ('ACCOUNTING_SPLIT')
                                                      )
                                              FROM ecorders.def_split_charges@dataextraction splitcharges
                                             WHERE splitcharges.orefitem =
                                                               poline.orefitem),
                                           XMLELEMENT
                                              ("SPLIT_ACCOUNTING_TYPE",
                                               def_common_extraction_pks.format_data
                                                        (split_accounting_type)
                                              ),
                                           XMLELEMENT ("SPECIAL_REQUEST_ITEM",
                                                       special_request_item
                                                      ),
                                           XMLELEMENT
                                                  ("ADDITIONAL_SUPPLIER_ITEM",
                                                   additional_supplier_item
                                                  ),
                                           XMLELEMENT ("ITEM_QUANTITY",
                                                       item_quantity
                                                      ),
                                           XMLELEMENT ("UNIT_OF_MEASURE",
                                                       unit_of_measure
                                                      ),
                                           XMLELEMENT ("UNIT_PRICE",
                                                       unit_price
                                                      ),
                                           XMLELEMENT ("EXTENDED_PRICE",
                                                       extended_price
                                                      ),
                                           XMLELEMENT
                                              ("MANUF_PART_NUMBER",
                                               def_common_extraction_pks.format_data
                                                            (manuf_part_number)
                                              ),
                                           XMLELEMENT
                                              ("CURRENCY_CODE",
                                               def_common_extraction_pks.format_data
                                                                (currency_code)
                                              ),
                                           XMLELEMENT
                                              ("SHIPPING_METHOD",
                                               def_common_extraction_pks.format_data
                                                              (shipping_method)
                                              ),
                                           XMLELEMENT
                                              ("SHIPPING_ACCOUNT",
                                               def_common_extraction_pks.format_data
                                                             (shipping_account)
                                              ),
                                           XMLELEMENT
                                              ("LINE_ITEM_COMMENTS",
                                               def_common_extraction_pks.format_data
                                                           (line_item_comments)
                                              ),
                                           XMLELEMENT
                                                    ("EXTERNAL_LINE_COMMENTS",
                                                     external_line_comments
                                                    ),
                                           XMLELEMENT
                                              ("VENDOR_CODE",
                                               def_common_extraction_pks.format_data
                                                                  (vendor_code)
                                              ),
                                           XMLELEMENT
                                              ("LATEST_VENDOR_CODE",
                                               def_common_extraction_pks.format_data
                                                           (latest_vendor_code)
                                              ),
                                           XMLELEMENT
                                              ("CATEGORY",
                                               def_common_extraction_pks.format_data
                                                                     (CATEGORY)
                                              ),
                                           XMLELEMENT
                                              ("PRODUCT_GROUP",
                                               def_common_extraction_pks.format_data
                                                                (product_group)
                                              ),
                                           XMLELEMENT
                                              ("LEAD_TIME",
                                               def_common_extraction_pks.format_data
                                                                    (lead_time)
                                              ),
                                           XMLELEMENT
                                              ("MANUFACTURER",
                                               def_common_extraction_pks.format_data
                                                                 (manufacturer)
                                              ),
                                           XMLELEMENT ("KETERA_LINE_ID",
                                                       ketera_line_id
                                                      ),
                                           XMLELEMENT ("RUSHORDER", rushorder),
                                           XMLELEMENT
                                                  ("REQUESTED_BY_DATE",
                                                   TO_CHAR (requested_by_date,
                                                            'YYYYMMDD'
                                                           )
                                                  ),
                                           XMLELEMENT
                                                    ("PURCHASE_REVIEW_NEEDED",
                                                     purchase_review_needed
                                                    ),
                                           XMLELEMENT
                                                     ("LINE_ATTACHMENT_COUNT",
                                                      line_attachment_count
                                                     ),
                                           XMLELEMENT ("TAXABLE", taxable),
                                           XMLELEMENT
                                              ("TAXABLE_COMMENTS",
                                               def_common_extraction_pks.format_data
                                                             (taxable_comments)
                                              ),
                                           XMLELEMENT
                                              ("ORDER_TYPE",
                                               def_common_extraction_pks.format_data
                                                                   (order_type)
                                              ),
                                           XMLELEMENT
                                              ("SHIPPING_INSTRUCTION",
                                               def_common_extraction_pks.format_data
                                                         (shipping_instruction)
                                              ),
                                           XMLELEMENT
                                              ("ONCONTRACT",
                                               def_common_extraction_pks.format_data
                                                                   (oncontract)
                                              ),
                                           XMLELEMENT
                                              ("BPO_NUMBER",
                                               def_common_extraction_pks.format_data
                                                                   (bpo_number)
                                              ),
                                           XMLELEMENT ("BPO_SPEND_LIMIT",
                                                       bpo_spend_limit
                                                      ),
                                           XMLELEMENT ("BPO_IS_PAYMENT_ONLY",
                                                       bpo_is_payment_only
                                                      ),
                                           (SELECT SYS_XMLAGG
                                                      (XMLELEMENT
                                                          ("FORMNAME",
                                                           def_common_extraction_pks.format_data
                                                                     (formname)
                                                          ),
                                                       xmlformat ('FORMS')
                                                      )
                                              FROM ecorders.lineitemeforms@dataextraction forms
                                             WHERE forms.orefitem =
                                                               poline.orefitem)
                                          ),
                                       xmlformat ('ITEMLIST')
                                      )
                              FROM ecorders.def_po_line@dataextraction poline
                             WHERE poline.porefitem = poheader.porefitem),
                           XMLELEMENT
                              ("ORDER_COMMENTS",
                               def_common_extraction_pks.format_data
                                                               (order_comments)
                              ),
                           XMLELEMENT
                              ("ORDER_TITLE",
                               def_common_extraction_pks.format_data
                                                                  (order_title)
                              ),
                           XMLELEMENT
                              ("BILLING_DETAILS",
                               XMLELEMENT
                                  ("BILL_STREET1",
                                   def_common_extraction_pks.format_data
                                                                 (bill_street1)
                                  ),
                               XMLELEMENT
                                  ("BILL_STREET2",
                                   def_common_extraction_pks.format_data
                                                                 (bill_street2)
                                  ),
                               XMLELEMENT
                                  ("BILL_CITY",
                                   def_common_extraction_pks.format_data
                                                                    (bill_city)
                                  ),
                               XMLELEMENT
                                  ("BILL_STATE",
                                   def_common_extraction_pks.format_data
                                                                   (bill_state)
                                  ),
                               XMLELEMENT
                                  ("BILL_ZIP",
                                   def_common_extraction_pks.format_data
                                                                     (bill_zip)
                                  ),
                               XMLELEMENT
                                  ("BILL_COUNTRY",
                                   def_common_extraction_pks.format_data
                                                                 (bill_country)
                                  ),
                               XMLELEMENT
                                  ("BILL_ATTN",
                                   def_common_extraction_pks.format_data
                                                                    (bill_attn)
                                  ),
                               XMLELEMENT
                                  ("BILL_PHONE",
                                   def_common_extraction_pks.format_data
                                                                   (bill_phone)
                                  ),
                               XMLELEMENT
                                  ("BILL_FAX",
                                   def_common_extraction_pks.format_data
                                                                     (bill_fax)
                                  ),
                               XMLELEMENT
                                  ("BILL_EMAIL",
                                   def_common_extraction_pks.format_data
                                                                   (bill_email)
                                  ),
                               XMLELEMENT
                                  ("BILL_MAIL_STATION",
                                   def_common_extraction_pks.format_data
                                                            (bill_mail_station)
                                  ),
                               XMLELEMENT
                                  ("BILL_LOCATION_NAME",
                                   def_common_extraction_pks.format_data
                                                           (bill_location_name)
                                  )
                              ),
                           XMLELEMENT
                              ("SHIPPING_DETAILS",
                               XMLELEMENT
                                  ("SHIP_STREET1",
                                   def_common_extraction_pks.format_data
                                                                 (ship_street1)
                                  ),
                               XMLELEMENT
                                  ("SHIP_STREET2",
                                   def_common_extraction_pks.format_data
                                                                 (ship_street2)
                                  ),
                               XMLELEMENT
                                  ("SHIP_STREET3",
                                   def_common_extraction_pks.format_data
                                                                 (ship_street3)
                                  ),
                               XMLELEMENT
                                  ("SHIP_CITY",
                                   def_common_extraction_pks.format_data
                                                                    (ship_city)
                                  ),
                               XMLELEMENT
                                  ("SHIP_STATE",
                                   def_common_extraction_pks.format_data
                                                                   (ship_state)
                                  ),
                               XMLELEMENT
                                  ("SHIP_ZIP",
                                   def_common_extraction_pks.format_data
                                                                    (ship_zip)
                                  ),
                               XMLELEMENT
                                  ("SHIP_COUNTRY",
                                   def_common_extraction_pks.format_data
                                                                 (ship_country)
                                  ),
                               XMLELEMENT
                                  ("SHIP_ATTN",
                                   def_common_extraction_pks.format_data
                                                                    (ship_attn)
                                  ),
                               XMLELEMENT
                                  ("SHIP_PHONE",
                                   def_common_extraction_pks.format_data
                                                                   (ship_phone)
                                  ),
                               XMLELEMENT
                                  ("SHIP_FAX",
                                   def_common_extraction_pks.format_data
                                                                     (ship_fax)
                                  ),
                               XMLELEMENT
                                  ("SHIP_EMAIL",
                                   def_common_extraction_pks.format_data
                                                                   (ship_email)
                                  ),
                               XMLELEMENT
                                  ("SHIP_MAIL_STATION",
                                   def_common_extraction_pks.format_data
                                                            (ship_mail_station)
                                  ),
                               XMLELEMENT
                                  ("SHIP_DEPARTMENT",
                                   def_common_extraction_pks.format_data
                                                              (ship_department)
                                  ),
                               XMLELEMENT
                                  ("SHIP_ADDRESS_ID",
                                   def_common_extraction_pks.format_data
                                                              (ship_address_id)
                                  ),
                               XMLELEMENT
                                  ("SHIP_LOCATION_NAME",
                                   def_common_extraction_pks.format_data
                                                          (ship_location_name)
                                  ),
                               XMLELEMENT
                                  ("CUSTOMER_NAME",
                                   def_common_extraction_pks.format_data
                                                                (customer_name)
                                  )
                              ),
                           XMLELEMENT
                              ("PAYMENT_INFO",
                               def_common_extraction_pks.format_data
                                                                 (payment_info)
                              ),
                           XMLELEMENT
                                ("USERID",
                                 def_common_extraction_pks.format_data (userid)
                                ),
                           XMLELEMENT ("HEADER_ATTACHMENT_COUNT",
                                       NVL (header_attachment_count, 0)
                                      ),
                           XMLELEMENT ("PO_DATE",
                                       TO_CHAR (po_date, 'YYYYMMDD')
                                      ),
                          XMLELEMENT ("CREATE_DATE",
                                       TO_CHAR (create_date, 'YYYYMMDD')
                                      ),
                           XMLELEMENT
                              ("SHIPPING_INSTRUCTION",
                               def_common_extraction_pks.format_data
                                                         (shipping_instruction)
                              ),
                           XMLELEMENT
                              ("ENTIREORDER_COMMENTS",
                               def_common_extraction_pks.format_data
                                                         (entireorder_comments)
                              ),
                           XMLELEMENT
                              ("PREPARER_NAME",
                               def_common_extraction_pks.format_data
                                                                (preparer_name)
                              ),
                           XMLELEMENT ("ORDER_ON_BEHALF", order_on_behalf),
                           XMLELEMENT ("PO_TYPE", po_type),
                           XMLELEMENT
                              ("REFERENCE_PO",
                               def_common_extraction_pks.format_data
                                                                 (reference_po)
                              )
                          ),
                       xmlformat ('POLIST')
                      )
	FROM ecorders.def_po_header@dataextraction poheader,
                   ecorders.def_rolling_report_queue@dataextraction rolling
             WHERE poheader.porefitem = rolling.document_id
               AND rolling.status = 0
               AND poheader.buyerid IN (
                                       SELECT urefitem
                                         FROM ecusers.ownership@dataextraction
                                        WHERE uownerref=3930)
               AND UPPER (payment_info) LIKE UPPER ('%%')),
           XMLELEMENT ("REPORT_FORMAT",
                       XMLELEMENT ("BASE_DELIMETER", 'PSV'),
                       XMLELEMENT ("SPECIAL_DELIMITER", '')
                      )
          )
  FROM DUAL@dataextraction dummy ;



===============Explain Plan


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=8168)
   1    0   REMOTE* (Cost=11 Card=8168)                                DATAEXTR
                                                                       ACTION



   1 SERIAL_FROM_REMOTE            SELECT /*+ */ 0 FROM "DUAL" "DUMMY"