xmllint : a UNIX utility to check for badly-formed XML data

It’s now commonplace to send or receive data files in XML format. And some eBusiness Suite functionality relies on XML configurations files. For example, BI Publisher data definitions are XML-formatted, and if you try to upload a badly-formed data template file to a BI Publisher data definition, Oracle responds with:

Error: The uploaded file XXAP073_BW_USER_RIGHTS_INT_XDO_DATA_TEMPL.xml is invalid. The file should be in XML-DATA-TEMPLATE format.

bi_publisher_data_template_format_error

Not very helpful.

However, there’s a useful UNIX utility that will parse an XML file, and highlight any badly-formed XML syntax. It’s called xmllint:

xmllint --noout ${XML_FILE} 

It will return 0 (zero) if the XML file contains well-formed XML data; if the XML is badly formed, xmllint will list the specific errors. For example…

bash:dave$ XML_FILE='XML_DATAFILE.xml';
bash:dave$ echo ${XML_FILE}
XML_DATAFILE.xml
bash:dave$
bash:dave$ xmllint  --noout ${XML_FILE}
XML_DATAFILE.xml:320: parser error : attributes construct error
      <element name='FIRST_NAME' value='FIRST_NAME'/>;
                                                     ^
XML_DATAFILE.xml:320: parser error : Couldn't find end of Start Tag element line 320
      <element name='FIRST_NAME' value='FIRST_NAME'/>;
                                                     ^
bash:dave$

I’ve used this in file transfer interfaces, to check that the XML file being passed contains well-formed XML data, for example:

xmllint  --noout ${XML_FILE}
VALIDATE_XML=$?

echo 'XML format validation result : ${VALIDATE_XML}'
  
if [ ${VALIDATE_XML} != 0 ]; then
  echo ' ';
  echo 'XML data is badly formed';
fi

Delete a concurrent program definition

During the build/development phase in a development environment, sometimes it’s just easier to delete a concurrent program and start again.

However, this isn’t possible via the Concurrent Programs screen.

If you have DML access to the database, you can simply delete the concurrent program using a standard API in an anonymous PL/SQL block:

BEGIN
  fnd_program.delete_program
  (program_short_name  => 'XXAP004_INACTIVATE_SUPPLIERS'
  ,application         => 'XXAP'
  );
END;

Oracle eBusiness R12 Supplier Query

SELECT aps.vendor_name                  SUPPLIER_NAME
,      aps.segment1                     SUPPLIER_NUM
,      aps.national_insurance_number    NI_NUMBER
,      aps.company_registration_number  COMPANY_REG_NUM
,      aps.allow_awt_flag               SUPP_ALLOW_WHT
,      aps.unique_tax_reference_num     SUB_CONT_CERT_NUM
,      aps.vendor_type_lookup_code      TYPE
,      haou.name                        ORG_NAME
,      apss.vendor_site_code            SITE_CODE
,      apss.allow_awt_flag              SUBCONTRACTOR
,      apss.email_address               EMAIL
,      iep.remit_advice_email           REMIT_EMAIL
,      apss.attribute5                  PAYEE_NAME
,      apss.pay_site_flag               PAY_SITE
,      ieppm.payment_method_code        PAY_METHOD
,      apss.hold_all_payments_flag      HOLD_PAYMT
,      apss.attribute6                  INVESTIGATE_FLAG
,      hp.party_name                    BANK_NAME
,      hop.organization_name            BANK_BRANCH_NAME
,      hop.bank_or_branch_number        BRANCH_SORT_CODE
,      ieba.masked_bank_account_num     BANK_ACCT
,      apss.payment_currency_code       PAYMENT_CURR
,      apss.purchasing_site_flag        PURCH_SITE
,      apss.invoice_currency_code       INVOICE_CURR
,      apss.allow_awt_flag              SITE_ALLOW_WHT
,      apss.pay_group_lookup_code       PAY_GROUP
,      apt.name                         PAYMENT_TERM
,      apss.pay_date_basis_lookup_code  PAY_DATE_BASIS
,      hl_bill.location_code            BILL_TO_ADDRESS
,      hl_ship.location_code            SHIP_TO_ADDRESS
,      apss.supplier_notif_method       NOTIF_METHOD
,      zr.registration_status_code      TAX_REG_STATUS
,      zr.tax                           TAX
,      zr.tax_regime_code               TAX_REGIME_CODE
,      zr.registration_number           TAX_REG_NUMBER
,      gcc.concatenated_segments        PAY_ACCT
,      gccp.concatenated_segments       PREPAY_ACCT
FROM   ap_suppliers                 aps
,      ap_supplier_sites_all        apss
,      hr_all_organization_units    haou
,      ap_terms                     apt
,      hr_locations                 hl_bill
,      hr_locations                 hl_ship
,      gl_code_combinations_kfv     gcc
,      gl_code_combinations_kfv     gccp
,      zx_party_tax_profile         zptp
,      zx_registrations             zr
,      iby_ext_bank_accounts        ieba
,      iby_external_payees_all      iep
,      iby_pmt_instr_uses_all       ipiu
,      iby_ext_party_pmt_mthds      ieppm
,      hz_parties                   hp
,      hz_organization_profiles     hop
WHERE  1 = 1
AND    aps.enabled_flag = 'Y'
AND    aps.vendor_type_lookup_code != 'EMPLOYEE'
AND    aps.vendor_id = apss.vendor_id
AND    apss.org_id = haou.organization_id
AND    haou.date_from &lt; SYSDATE
AND    NVL(apss.inactive_date,SYSDATE + 1) &gt; SYSDATE
AND    apt.term_id(+) = apss.terms_id
AND    apt.start_date_active(+) &lt; SYSDATE
AND    apss.bill_to_location_id = hl_bill.location_id(+)
AND    apss.ship_to_location_id = hl_ship.location_id(+)
AND    NVL(hl_bill.inactive_date(+),SYSDATE + 1) &gt; SYSDATE
AND    NVL(hl_ship.inactive_date(+),SYSDATE + 1) &gt; SYSDATE
AND    gcc.code_combination_id = apss.accts_pay_code_combination_id
AND    gccp.code_combination_id = apss.prepay_code_combination_id
AND    NVL(gcc.start_date_active,SYSDATE - 1) &lt; SYSDATE
AND    NVL(gccp.start_date_active,SYSDATE - 1) &lt; SYSDATE
AND    zptp.party_id(+) = apss.party_site_id
AND    zptp.party_tax_profile_id = zr.party_tax_profile_id(+)
AND    NVL(zr.effective_from(+),SYSDATE - 1) &lt; SYSDATE
AND    ipiu.instrument_id=ieba.ext_bank_account_id(+)
AND    NVL(ieba.start_date(+),SYSDATE - 1) &lt; SYSDATE
AND    NVL(iep.inactive_date(+),SYSDATE + 1) &gt; SYSDATE
AND    ipiu.ext_pmt_party_id(+)=iep.ext_payee_id
AND    ipiu.instrument_type(+)='BANKACCOUNT'
AND    ipiu.payment_flow(+)='DISBURSEMENTS'
AND    NVL(ipiu.start_date(+),SYSDATE - 1) &lt; SYSDATE
AND    ieppm.ext_pmt_party_id(+) = ipiu.ext_pmt_party_id
AND    NVL(ieppm.inactive_date(+),SYSDATE + 1) &gt; SYSDATE
AND    apss.vendor_site_id = iep.supplier_site_id(+)
AND    hp.party_type(+) = 'ORGANIZATION'
AND    hop.party_id(+) = ieba.branch_id
AND    hp.party_id(+) = ieba.bank_id
AND    NVL(hop.effective_start_date(+),SYSDATE - 1) &lt; SYSDATE
AND    zptp.party_type_code(+) = 'THIRD_PARTY_SITE'
ORDER BY 1,8,9

How to save a pipe-delimited “csv” file from MS Excel on Windows 7

Sometimes when preparing a text file to upload data from an ASCII file to a database table (for example, using Oracle SQL*Loader, or an external table), a comma-separated-value (csv) file format can be a problem – maybe your data fields themselves contain commas, which would obviously confuse the way in which fields are parsed.

So, you may find it useful to save a version of your ASCII data file in, say, a pipe-delimited format – the pipe (“|”) character rarely appears in the source data itself. In fact, you can specify any delimiter character.

But how to save a custom-delimited text file from MS Excel? On Windows 7, this can be achieved as follows:

  1. Start Menu > Control Panel > Region and Language > Additional Settings
  2. Set List Separator to | (i.e. a “pipe” symbol) – or any symbol of your choice
  3. In Excel, choose File > Save As
  4. Save as type = CSV (comma delimited) (*.csv)

Note that other PC applications may be affected by the “List Separator” setting, so it may be necessary to set this back to a comma (“,”) when your work is done.

A Workaround to View Other Users’ Concurrent Request Output

Responsibility: System Administrator

Navigation: Requests > View

Query back the concurrent request of interest. If you are not the owner of the request, the View Output button will be greyed out. However, if you know the user_id (in the FND_USER table) for the owner of the request, and you have access to Diagnostics-Examine functionality,  you can trick the system into showing you the request output, as follows:

Menu: Help > Diagnostics > Examine

Block = WORLD

Field = USER_ID

Enter the request owner’s user_id in the Value field, and click OK.

Re-query the concurrent request of interest, the View Output button will no longer be greyed out, and the request output will be displayed as if you were the owner of the request.

SQL Query to identify Users and Responsibilities

The following query will list active FND users and their active responsibility assignments:

SELECT fu.user_name
,      fr.responsibility_name
FROM   fnd_user fu
,      fnd_user_resp_groups_direct furgd
,      fnd_responsibility_vl fr
WHERE  TRUNC(SYSDATE) BETWEEN TRUNC(fu.start_date) AND NVL(TRUNC(fu.end_date),SYSDATE + 1)
AND    fu.user_id = furgd.user_id
AND    TRUNC(SYSDATE) BETWEEN TRUNC(furgd.start_date) AND NVL(TRUNC(furgd.end_date),SYSDATE + 1)
AND    fr.responsibility_id = furgd.responsibility_id
AND    TRUNC(SYSDATE) BETWEEN TRUNC(fr.start_date) AND NVL(TRUNC(fr.end_date),SYSDATE + 1)
ORDER BY 1,2
;

Using SQL PIVOT

The SQL PIVOT command can be used to show the summarised amounts/values of an aggregated query (GROUP BY…) as columns, rather than grouped rows. Here’s an example, in HR, to show total amounts in specific pay elements, per employee/assignment (assignment_id):

SELECT assignment_id
,      NVL(gen,0)         gen
,      NVL(car,0)         car
,      NVL(study,0)       study
,      NVL(study_subs,0)  study_subs
,      NVL(acting,0)      acting
,      NVL(parking,0)     parking
,      NVL(retire,0)      retire
,      NVL(medical,0)     medical
FROM
(
SELECT ee.assignment_id
,      et.element_name
,      SUM(TO_NUMBER(ev.screen_entry_value)) val
FROM   pay_element_types_f_tl      et
,      pay_element_links_f         el
,      pay_element_entries_f       ee
,      pay_element_entry_values_f  ev
,      pay_input_values_f          iv
WHERE  et.element_type_id = el.element_type_id
AND    el.element_link_id = ee.element_link_id
AND    ee.element_entry_id = ev.element_entry_id
AND    ev.input_value_id = iv.input_value_id
AND    TRUNC(SYSDATE) BETWEEN el.effective_start_date AND el.effective_end_date
AND    TRUNC(SYSDATE) BETWEEN ee.effective_start_date AND ee.effective_end_date
AND    TRUNC(SYSDATE) BETWEEN ev.effective_start_date AND ev.effective_end_date
AND    TRUNC(SYSDATE) BETWEEN iv.effective_start_date AND iv.effective_end_date
AND    et.element_name IN ('Gen Allowance'
                          ,'Car Allow'
                          ,'Professional Study Allowance'
                          ,'Professional Study Subscription'
                          ,'Acting Up'
                          ,'Car Parking Space Paid'
                          ,'Retirement Allowance'
                          ,'Medical Insurance'
                          )
GROUP BY ee.assignment_id
,      et.element_name
)
PIVOT
(
SUM(val)
FOR element_name IN ('Gen Allowance'                   gen
                    ,'Car Allow'                       car
                    ,'Professional Study Allowance'    study
                    ,'Professional Study Subscription' study_subs
                    ,'Acting Up'                       acting
                    ,'Car Parking Space Paid'          parking
                    ,'Retirement Allowance'            retire
                    ,'Medical Insurance'               medical
                    )
)
ORDER BY 1,2
;

BI or XML Publisher Bursting : how to switch on debugging

  1. Connect to the Applications server as ‘applmgr’.
  2. Create an $XDO_TOP/temp directory.
  3. Go to the $XDO_TOP/resource folder.
  4. Create an xdodebug.cfg file with the following 2 lines (specifying the actual file path, not the UNIX environment variable):
LogLevel=STATEMENT
LogDir=<path to $XDO_TOP>/temp
  1. Restart the Concurrent Managers (see here for details).
  2. Run a concurrent request with XML Publisher bursting, and debug files will be created under the $XDO_TOP/temp.

Note: for standalone instances of BI Publisher, the xdodebug.cfg file should be created in the $AF_JRE_TOP/lib folder.

SQL Validation used for a Profile Option’s List of Values

Application developers can define their own Profile Options, and it’s often useful to define a list of values (LOV) to validate the value assigned to the profile option.

Conventional SQL is used to define these LOV’s, but the exact syntax is a little Applications-specific.

Here is an example, which is entered (Responsibility: Application Developer; Navigation Path:  Profile) in the “SQL Validation used for the Profile Option’s List of Values” field:

SQL="
SELECT SUBSTR(qrslt.display_name||' ('||qrslt.email_address||')',1,80) "Full Name"
,      SUBSTR(qrslt.name,1,30)  "Name"
INTO   :visible_option_value
,      :profile_option_value
FROM
(
SELECT display_name
,      name
,      email_address
FROM   wf_users
WHERE  orig_system = 'FND_USR'
AND    email_address IS NOT NULL
AND    status = 'ACTIVE'
AND    TRUNC(SYSDATE) >= TRUNC(start_date)
AND    SYSDATE < NVL(expiration_date, SYSDATE + 1)
AND    name = 'APHELPDESK'
UNION
SELECT display_name
,      name
,      email_address
FROM   wf_users
WHERE  orig_system = 'PER'
AND    email_address IS NOT NULL
AND    status = 'ACTIVE'
AND    TRUNC(SYSDATE) >= TRUNC(start_date)
AND    SYSDATE < NVL(expiration_date, SYSDATE + 1)
)      qrslt
ORDER BY 1"
COLUMN=""Full Name"(30),"Name"(30)"