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 < SYSDATE
AND    NVL(apss.inactive_date,SYSDATE + 1) > SYSDATE
AND    apt.term_id(+) = apss.terms_id
AND    apt.start_date_active(+) < 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) > SYSDATE
AND    NVL(hl_ship.inactive_date(+),SYSDATE + 1) > 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) < SYSDATE
AND    NVL(gccp.start_date_active,SYSDATE - 1) < 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) < SYSDATE
AND    ipiu.instrument_id=ieba.ext_bank_account_id(+)
AND    NVL(ieba.start_date(+),SYSDATE - 1) < SYSDATE
AND    NVL(iep.inactive_date(+),SYSDATE + 1) > 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) < SYSDATE
AND    ieppm.ext_pmt_party_id(+) = ipiu.ext_pmt_party_id
AND    NVL(ieppm.inactive_date(+),SYSDATE + 1) > 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) < 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
;

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)"

Discoverer Reporting : using SQL to generate report usage statistics

Oracle Discoverer holds a set of database tables for each Discoverer EUL being used. In the following queries, the prefix eul_owner should be replaced by the database owner corresponding to the specific EUL in question.

The following reporting statistics may be of interest…

1. Discoverer Reports run in the last year (i.e. since SYSDATE-365):

SELECT qs.qs_doc_name              report_run
,      qs.qs_doc_details           report_detail
,      qs.qs_created_date          date_run
,      fnd.user_name               user_name
FROM   &lt;eul_owner&gt;.eul5_qpp_stats  qs
,      fnd_user                    fnd
WHERE  1 = 1
AND    fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','')
-- AND    user_name = 'SYSADMIN'
AND    qs.qs_created_date &gt; SYSDATE - 365
ORDER BY qs.qs_created_date DESC
;

2. Discoverer Report usage by USER:

SELECT COUNT(*)                    num_reports
,      fnd.user_name               user_name
,      ppx.full_name               employee_name
FROM   &lt;eul_owner&gt;.eul5_qpp_stats  qs
,      fnd_user                    fnd
,      per_people_x                ppx
WHERE  1 = 1
AND    fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','')
AND    ppx.person_id(+) = fnd.employee_id
-- AND    user_name = 'SYSADMIN'
AND    qs.qs_created_date &gt; SYSDATE - 365
GROUP BY fnd.user_name
,      ppx.full_name
ORDER BY COUNT(*) DESC
;

3. Discoverer Report usage by REPORT:

SELECT COUNT(*)                    num_reports
,      qs.qs_doc_name              report_run
,      qs.qs_doc_details           report_detail
FROM   &lt;eul_owner&gt;.eul5_qpp_stats  qs
,      fnd_user                    fnd
,      per_people_x                ppx
WHERE  1 = 1
AND    fnd.user_id(+) = REPLACE(qs.qs_created_by,'#','')
AND    ppx.person_id(+) = fnd.employee_id
-- AND    user_name = 'SYSADMIN'
AND    qs.qs_created_date &gt; SYSDATE - 365
GROUP BY qs.qs_doc_name
,      qs.qs_doc_details
ORDER BY COUNT(*) DESC
;

4. Discoverer Report ownership and assignment (users/responsibilities):

SELECT DISTINCT a.doc_name             disco_report
,      fu_owner.user_name              owner
,      d.responsibility_key            responsbility
,      fu.user_name
,      ppx.full_name                   employee_name
FROM   &lt;eul_owner&gt;.eul5_documents      a
,      &lt;eul_owner&gt;.eul5_access_privs   b
,      &lt;eul_owner&gt;.eul5_eul_users      c
,      fnd_responsibility              d
,      fnd_user                        fu
,      fnd_user                        fu_owner
,      per_people_x                    ppx
WHERE  a.doc_id = b.gd_doc_id(+)
AND    b.ap_type(+) = 'GD'
AND    b.ap_eu_id = c.eu_id(+)
AND    REPLACE(c.eu_username,'#','') = TO_CHAR(d.responsibility_id(+)) || TO_CHAR(d.application_id(+))
AND    TO_CHAR(fu.user_id(+)) = REPLACE(c.eu_username,'#','')
AND    TO_CHAR(fu_owner.user_id(+)) = REPLACE(a.doc_created_by,'#','')
AND    NVL(fu.end_date(+),SYSDATE + 1) &gt; SYSDATE
AND    NVL(fu_owner.end_date(+),SYSDATE + 1) &gt; SYSDATE
AND    ppx.person_id(+) = fu.employee_id
AND    (   fu_owner.user_name = &lt;user&gt;
       OR  fu.user_name = &lt;user&gt;
       )
ORDER BY a.doc_name ASC
;

See also…

How To Find Out Who The Owner of a Workbook is and To Whom it is Shared [ID 1076907.6]

 

 

SQL Query to identify Oracle Database Locks

The following query will list database locks, but needs to be run as a user with the appropriate access to the v$… and dba_locks tables:

SELECT w.session_id   waiting_session
,      h.session_id   holding_session
,      w.lock_type
--,      h.mode_held
--,      w.mode_requested
,      (
       SELECT module
       ||     ' :: '
       ||     action
       FROM   v$session
       WHERE  sid=h.session_id
       )              holding_module_action
,      (
       SELECT module
       ||     ' :: '
       ||     action
       FROM   v$session
       WHERE  sid = w.session_id
       )              waiting_module_action
FROM   (
       SELECT /*+ NO_MERGE */ *
       FROM   dba_locks
       )              w
,      (
       SELECT /*+ NO_MERGE */ *
       FROM   dba_locks
       )              h
WHERE  ((   (h.mode_held != 'None')
        AND (h.mode_held != 'Null')
        AND (  (h.mode_requested = 'None')
            OR (h.mode_requested = 'Null')
            )
        )
        AND ((  (w.mode_held = 'None')
             OR (w.mode_held = 'Null')
             )
             AND (   (w.mode_requested != 'None')
                 AND (w.mode_requested != 'Null')
                 )
             )
       )
AND    w.lock_type = h.lock_type
AND    w.lock_id1 = h.lock_id1
AND    w.lock_id2 = h.lock_id2
AND    w.session_id != h.session_id
;

..or you can try the following:

SELECT NVL(s.username, '(oracle)') AS username
,      s.sid
,      s.serial#
,      s.osuser
,      s.machine
,      s.program
,      s.module
,      o.object_name
,      sq.sql_text
,      TO_CHAR(s.logon_time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$locked_object l
,      dba_objects     o
,      v$session       s
,      v$process       p
,      v$sql sq
WHERE  l.object_id = o.object_id
AND    l.session_id = s.sid
AND    s.paddr = p.addr
AND    s.sql_address = sq.address
;

Locking sessions can be killed using the following SQL command, using the SID and SERIAL# from the query above:

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' IMMEDIATE;