Document Attachments: Private Stuff

Noticed a few posts on Oracle forums about FND Attached Documents i.e. Oracle Applications/eBusiness Suite Attachments. Thought I'd post some unsupported code here just for reference, and a query for getting the attachments (text/documents) themselves.

How to attach a "Web Page" URL attachment to a Payables Invoice via FND Document Attachment Private API.

To use this code there are a few things that you'll need to change:

1. In the fnd_global call change the variables to your required user_id, resp_id, resp_appl_id, or comment the call if not needed see my post if you're unsure of this
2. The l_pk1_value which is ap_invoices_all.invoice_id
3. The l_filename and l_description variables
4. Possibly l_category_id: create an real example, check out examine and find the category_id you need to use.
5. Remember to commit!
6. Updated 6-Mar-2012: This code has been tested against 11.5.10.2, 12.0 and 12.1 (code differences noted in comments - only queries tested for R12.0/12.1), if you have a different version this may have changed. Note this is a customization via a private API so is unsupported.

declare
l_rowid rowid;
l_attached_document_id number;
l_document_id number;
l_media_id number;
l_category_id number := 291; -- Invoice Internal
l_pk1_value fnd_attached_documents.pk1_value%TYPE := '63227';
l_description fnd_documents_tl.description%TYPE   := 'Gareth Document Attach Demo';
l_filename fnd_documents_tl.file_name%TYPE := 'http://www.oracle.com/index.html';
l_seq_num number;
begin
-- Setup applications session
-- User = OPERATIONS
-- Resp = Payables, Vision Operations
-- App  = Payables
fnd_global.apps_initialize(1318,50554,200);

select FND_DOCUMENTS_S.nextval
into   l_document_id
from   dual;

select FND_ATTACHED_DOCUMENTS_S.nextval
into   l_attached_document_id
from   dual;

select nvl(max(seq_num),0) + 10
into   l_seq_num
from   fnd_attached_documents
where  pk1_value = l_pk1_value
and    entity_name = 'AP_INVOICES';

fnd_documents_pkg.insert_row
( X_ROWID                        => l_rowid
, X_DOCUMENT_ID                  => l_document_id
, X_CREATION_DATE                => sysdate
, X_CREATED_BY                   => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_DATE             => sysdate
, X_LAST_UPDATED_BY              => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_LOGIN            => fnd_profile.value('LOGIN_ID')
, X_DATATYPE_ID                  => 5 -- Web Page
, X_CATEGORY_ID                  => l_category_id
, X_SECURITY_TYPE                => 2
, X_PUBLISH_FLAG                 => 'Y'
, X_USAGE_TYPE                   => 'O'
, X_LANGUAGE                     => 'US'
, X_DESCRIPTION                  => l_description
, X_FILE_NAME                    => l_filename
, X_MEDIA_ID                     => l_media_id
);

fnd_documents_pkg.insert_tl_row
( X_DOCUMENT_ID                  => l_document_id
, X_CREATION_DATE                => sysdate
, X_CREATED_BY                   => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_DATE             => sysdate
, X_LAST_UPDATED_BY              => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_LOGIN            => fnd_profile.value('LOGIN_ID')
, X_LANGUAGE                     => 'US'
, X_DESCRIPTION                  => l_description
, X_FILE_NAME                    => l_filename
, X_MEDIA_ID                     => l_media_id
);

fnd_attached_documents_pkg.insert_row
( X_ROWID                        => l_rowid
, X_ATTACHED_DOCUMENT_ID         => l_attached_document_id
, X_DOCUMENT_ID                  => l_document_id
, X_CREATION_DATE                => sysdate
, X_CREATED_BY                   => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_DATE             => sysdate
, X_LAST_UPDATED_BY              => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_LOGIN            => fnd_profile.value('LOGIN_ID')
, X_SEQ_NUM                      => l_seq_num
, X_ENTITY_NAME                  => 'AP_INVOICES'
, X_COLUMN1                      => null
, X_PK1_VALUE                    => l_pk1_value
, X_PK2_VALUE                    => null
, X_PK3_VALUE                    => null
, X_PK4_VALUE                    => null
, X_PK5_VALUE                    => null
, X_AUTOMATICALLY_ADDED_FLAG     => 'N'
, X_DATATYPE_ID                  => 5
, X_CATEGORY_ID                  => l_category_id
, X_SECURITY_TYPE                => 2
, X_PUBLISH_FLAG                 => 'Y'
, X_LANGUAGE                     => 'US'
, X_DESCRIPTION                  => l_description
, X_FILE_NAME                    => l_filename
, X_MEDIA_ID                     => l_media_id
);
end;
/

Where to the actual attachments/documents get stored for attachments?

Here are some queries to get back the Payables Invoice attachments of category "Invoice Internal" for a given invoice. Attachments of type Web Page (URL or Filename)

select fad_f.seq_num
,      fdt_f.file_name media_data_text
from   fnd_attached_documents fad_f
,      fnd_documents fd_f
,      fnd_documents_tl fdt_f
,      fnd_document_categories_tl fdct_f
where  1 = 1
and    fad_f.document_id = fd_f.document_id
and    fad_f.document_id = fdt_f.document_id
and    fdct_f.category_id = fd_f.category_id
and    fdct_f.user_name = 'Invoice Internal'
and    fd_f.datatype_id = 5
and    fad_f.entity_name = 'AP_INVOICES'
and    fad_f.pk1_value = '63227';

Attachments of type "Long Text"

select fad_f.seq_num
,      fdlt_f.media_id   media_id
,      fdlt_f.long_text media_data_text
from   fnd_attached_documents fad_f
,      fnd_documents fd_f
,      fnd_documents_tl fdt_f
,      fnd_document_categories_tl fdct_f
,      fnd_documents_long_text fdlt_f
where  1 = 1
and    fad_f.document_id = fd_f.document_id
and    fad_f.document_id = fdt_f.document_id
and    fdct_f.category_id = fd_f.category_id
and    fdt_f.media_id = fdlt_f.media_id /* R11i */
-- and    fd_f.media_id = fdlt_f.media_id /* R12 */
and    fdct_f.user_name = 'Invoice Internal'
and    fad_f.entity_name = 'AP_INVOICES'
and    fad_f.pk1_value = '63227';

Attachments of type "Short Text"

select fad_f.seq_num
,      fdst_f.media_id   media_id
,      fdst_f.short_text media_data_text
from   fnd_attached_documents fad_f
,      fnd_documents fd_f
,      fnd_documents_tl fdt_f
,      fnd_document_categories_tl fdct_f
,      fnd_documents_short_text fdst_f
where  1 = 1
and    fad_f.document_id = fd_f.document_id
and    fad_f.document_id = fdt_f.document_id
and    fdct_f.category_id = fd_f.category_id
and    fdt_f.media_id = fdst_f.media_id /* R11i */
-- and    fd_f.media_id = fdst_f.media_id /* R12 */
and    fdct_f.user_name = 'Invoice Internal'
and    fad_f.entity_name = 'AP_INVOICES'
and    fad_f.pk1_value = '63227';

Attachments of type "File"

select fad_l.seq_num
,      fl_l.file_id      media_id
,      fl_l.file_data    media_data_blob
from   fnd_attached_documents fad_l
,      fnd_documents fd_l
,      fnd_documents_tl fdt_l
,      fnd_document_categories_tl fdct_l
,      fnd_lobs fl_l
where  1 = 1
and    fad_l.document_id = fd_l.document_id
and    fad_l.document_id = fdt_l.document_id
and    fdct_l.category_id = fd_l.category_id
and    fdt_l.media_id = fl_l.file_id /* Release 11i */
-- and fd_l.media_id = fl_l.file_id /* Release 12 */
and    fad_l.entity_name = 'AP_INVOICES'
and    fdct_l.user_name = 'Invoice Internal'
and    fad_l.pk1_value = '63227';

Update: Added extra line to file query for R12. Update: In case anyone needs to delete any attachments here's an example (thanks to Mark L):

declare
l_entity_name varchar2(20):= 'PO_LINES';  -- This defines the PO_LINES entity
l_pk1_value varchar2 (20) := '200487';    -- PO_LINE_ID items are attached to
l_delete_document_flag varchar2 (1) := 'Y';    -- Delete the document
begin
--fnd_global.apps_initialize(0,20420,1); -- SYSADMIN System Administrator
fnd_attached_documents2_pkg.delete_attachments
( X_entity_name                  => l_entity_name
, X_pk1_value                    => l_pk1_value
, X_delete_document_flag         => l_delete_document_flag
);
end;
/
commit;

Happy Attaching!

Best Practices Solutions: choose your moving parts carefully

Working with the Oracle eBusiness beast is a tad challenging given the breadth and complexity of its components. It uses a large majority of Oracle's core technology, both built and bought by Oracle. Integration throughout the technologies is fundamentally good and bound to get better with the whole Fusion soup. If there's something you need to provide a solution for, you have a myriad of options available to you without looking to that latest and greatest 3rd Party shiny thing. Or even that latest and greatest shiny thing within the tech stack.

The biggest workhorse with regards to language is PL/SQL but Java is making its presence felt. The number of Oracle Applications "Products" in use and available is increasing - both from a core technology stack perspective and an End User Application perspective.

This growth of moving parts leads to a necessity of choice:
What tool/language do I use given a custom development requirement?

Given my affinity to the intelligent or "thick" database paradigm, wherever possible I'll try to use the tool closest to the database.

What does this mean for me?
a) use SQL
b) use PL/SQL
c) use something else if you really have to

If you use SQL and PL/SQL wherever you can:

• Identification of performance issues should be easier due to the wealth of excellent tools at this level, e.g. AWR, statspack, dbms_profiler
• Integration issues should be easier to identify as you primarily have one place to look - the database
• The "disconnects" between components can be avoided (your session context is preserved)
• Code should be able to be consolidated to reduce complexity
• The number of product experts needed should be reduced
• Your licensing jigsaw puzzle should be simpler to arrange

Of course I'm over simplifying things here. There are situations where you can and must use another tool. However, the chances are that there will be something available to suit your needs in the Oracle eBusiness Suite Technology Stack. My point is even with additional tools, keep it simple and keep as much as possible in the database.

When you need to choose another tool, use the tool that:

• Is already in use performing a task similar to what you need to do
• Centralizes or consolidates the code and business rules
• Implements a simple rather than a complex solution

Lets take a few rules I use relating to other tools, with a caveat that there will be times when these rules are contradicted. I use these examples as the red flag in front of the eBusiness bull:

1. If you need to write a concurrent program, use PL/SQL
2. If you need to schedule something, use the Concurrent Manager calling your PL/SQL program
3. If you need a user interface, use the existing tech stack with business logic in the database wherever possible (I'll contradict myself upfront and say occasionally use Oracle APEX)
4. If you need to write a report*, use BI Publisher (BIP/XMLP) or Oracle Reports, with the business rules in PL/SQL
5. If you need an adhoc report*, use Discoverer
6. If you need to send an email, use PL/SQL and the Workflow Notification Mailer (Java Mailer) API

* = check existing reporting capabilities before making this decision - standard reports, FSGs, RX reports, etc.

Another point I want to make in this post is a quick reference to hardcoding. In the past I was a very strong advocate on avoiding hardcoding, but a number of recent experiences have softened my view on this. Now I'd recommend avoiding hardcoding only when necessary ... another one of those contentious issues, a read of this is recommended.

I can hear you saying "Alright, guv, where you going with this then, ay?".

Well, I'm writing this both as a point of reference and a point of reflection. Originally I wanted to include a comment on open source, but it just didn't surface.

Keep an eye out though ;-)

PS. I'm not an Oracle employee, so I get no direct benefit from the inherently Oracle bias to this post. I just like keeping my ducks in a row ;-)

Audit Trail Must Do: Bank Accounts

If you are paying suppliers and other parties out of the Oracle eBusiness Suite via Oracle Payables and haven't performed the steps documented in Part 1 of this post, do it now! Even if you don't fully understand ...

This post details the steps required to implement the auditing of Banks, Bank Accounts and Bank Account Uses (Supplier to Bank Account Assignment), using standard Oracle Applications AuditTrail functionality. Auditing in this context means create, update and delete actions performed by authenticated Oracle eBusiness Suite Users. Specific columns only are tracked, with a full history from the completion of the setup steps.

The impact of performing these steps is:

• You will be able to run AuditTrail Reports against the columns configured for auditing
• New tables will be created: ap_bank_acccounts_all_a, ap_bank_branches_a, ap_bank_account_uses_all_a
• New triggers will be created on the tables ap_bank_accounts_all, ap_bank_branches and ap_bank_account_uses_all that insert into the _all tables
• There will be a very small storage and performance impact, but in the realm of your entire Oracle eBusiness Suite this impact is negligible and from where I sit, auditing of the items mentioned is a fundamental requirement.

Okay, enough mumble, onto the steps.

Part 1. Setup the AuditTrail Data Capture and Group

Note: Replace references to "Virtuate" with your company name/abbreviation - if you like ;-)

1. System Administrator > Security > AuditTrail > Groups

Create Group Virtuate Bank Accounts
Application Name: Payables
Audit Group: Virtuate Bank Accounts
Group State: Enabled
Add tables:
AP_BANK_ACCOUNTS_ALL
AP_BANK_BRANCHES
AP_BANK_ACCOUNT_USES_ALL
Save

2. System Administrator > Security > AuditTrail > Tables

Query table name AP_BANK_ACCOUNTS_ALL
Add row for column BANK_ACCOUNT_NUM
Add row for column BANK_ACCOUNT_NAME
Add rows for any other columns that you store critical bank account information in
Save

Query table name AP_BANK_BRANCHES
Add row for column BANK_NUMBER
Add row for column BANK_NUM
Add rows for any other columns that you store critical bank account information in
Save

Query table name AP_BANK_ACCOUNT_USES%
Add row for column EXTERNAL_BANK_ACCOUNT_ID
Add row for column VENDOR_ID
Add row for column VENDOR_SITE_ID
Add row for column CUSTOMER_ID
Add row for column CUSTOMER_SITE_USE_ID
Add row for column START_DATE
Add row for column END_DATE
Add rows for any other columns that you store critical bank account information in
Save


3. System Administrator > Security > AuditTrail > Install

Query User AP
Check Audit Enabled if not already checked
Save


4. System Administrator > Requests > Run

AuditTrail Update Tables
(no parameters)
Submit
Review Log to ensure no errors were encountered


5. System Administrator > Security > AuditTrail > Audit Trail Reporting > Audit

Industry Template Create:
Template Name: VIRTUATE_BANK_ACCOUNTS
Description: Virtuate Bank Accounts
Functional Areas:
Add:
Virtuate Bank Accounts
Save

Please Note: If you subsequently make any changes to the above setup you need to rerun AuditTrail Update Tables

Part 2 (optional) Verify auditing of bank accounts is working

1. Check rows in ap_bank_accounts_all_a



sqlplus apps
select * from ap_bank_accounts_all_a;

should get no rows selected

2. Payables > Setup > Payment > Banks

Query an existing bank account, go to Bank Accounts
Change bank account "Number" field and save


3. Check rows in ap_bank_accounts_all_a



sqlplus
select * from ap_bank_accounts_all_a;

Should see old value of changed record, plus other who and when columns etc.

4. Payables > Setup > Payment > Banks

Query the bank account from Step 7, go to Bank Accounts
Revert the change made to bank account "Number" field in Step 2. and save

Part 3 Run Audit Report

1. Run Test Report

System Administrator > Security > AuditTrail > Audit Trail Reporting > Audit Report
Functional Group: Virtuate Bank Accounts
Audit Table Name: AP_BANK_ACCOUNTS_ALL
Transacted By: (your username)
Click Select Columns
Choose BANK_ACCOUNT_NUM in column 1
Click Run Report
View report to see the details of the changes made.

Other Notes

The way that Oracle stores audit information is to record only the changed columns, plus the who (username) when (timestamp) and type (create/update/delete). So getting the complete row information at a given point in time in history is a little tricky, as you need to tree walk back in time through the rows of the audit table.

The associated report is left to a future blog ... maybe a good starting point for a new BIP Report.

At least the Audit Hierarchy Navigator (System Administrator > AuditTrail > AuditTrail Reporting > Audit Hierarchy Navigator) is there to use for now albeit not that useful given the tracking based on primary key ID fields.

Now your auditors might be a bit happier, and if someone tries any trickery on the front end you'll have a history of it on the backend!

Oracle eBusiness Suite R12 Demo on a Laptop

One of my readers asked whether it would be possible to setup a Release 12 Vision Demo environment on laptop. Possible? Absolutely. Disk and memory hungry beast? Of course! Support for 1,000 concurrent users. Maybe not!

I've done this previously with R11i a couple of ways and I'm planning to do it with R12. You can install Linux on the laptop, or use a Virtual Machine - These days I'd go down the Virtual Machine route for demo systems for a number of reasons:

• You need a client tier so the host is the client
• You don't need a high performance solution, you're running on a laptop after all!
• Ease of backup/duplicate
• Ease of "suspend" of the server
• Possibility of creating the environment on a grunty machine and move to laptop
• If things go haywire you don't have to rebuild your laptop
• If Oracle was kind enough to provide an eBusiness Suite R12 Vision Demo Virtual Appliance you'd have bugger all to do to get this running!

My choice for the Oracle Applications/eBusiness Suite is always to run under Linux/Unix as opposed to Windows as the 3rd Party Software requirements are a real pain for the Windows option, both in terms of licensing and install/configuration.

Anyway I digress, I'd get this up and running as follows, assuming you have all required licensing in place.

1. Make sure you have a recent laptop with 2Gb RAM or more, running say Windows XP SP2
2. Source an external USB2.0, Firewire or eSATA 250Gb + drive, whichever is compatible with the laptop. Note R12 Vision Demo is apparently 175Gb, plus you'll need space for O/S and patches and all your other toys.
3. Source and install VMWare Server on your laptop
4. Source and install Oracle Enterprise Linux 4 32bit (or Red Hat 4.0, Suse 9 or Whitebox 4.0) under VMWare and apply updates - check Metalink Certify for supported O/S
5. Source the latest Oracle Applications R12 32bit Release - its like 41Gb (13 DVDs) so would take a while to download - better to get the box!
6. Install R12 choosing the Vision Demo environment onto your Linux Server under VMWare
7. Download and apply the latest R12 update pack - check out Steven Chan's blog for things to watch. Also you should check the R12 recommended patches on this page on Metalink
8. If you want to setup outbound email you'll need an SMTP Mail server and to configure the Workflow Java Mailer, but best to keep email within the laptop, so just setup Sendmail and Dovecot ... I feel another post is due on that.

Sweet, all sorted.

Some other pointers:

• User Manuals etc. for R12 are publicly available at http://download.oracle.com/docs/cd/B40089_05/current/html/docset.html
• Technical Reference Manuals (includes ER diagrams, table details etc) are at https://etrm.oracle.com/pls/etrmlatest/etrm_search.search but you need a Metalink account (ie pay for Support) for access to that.

PS. Another question that came out of this was:

Can I run Oracle eBusiness Suite on Linux on an AMD Athlon 64bit CPU?

Absolutely, for Release 11i and prior you can run the 32bit version of the O/S on 64bit hardware and put the eBusiness Suite on that. From Release 12 you can run 64bit, see Metalink Note 416305.1. When you source your software be sure to match the O/S and eBusiness Suite media, ie. 32bit O/S + 32bit eBiz, or 64bit O/S and 64bit eBiz.

I'd be keen to hear any pointers from anyone running R12 Vision Demo on a Laptop under VMWare!

Account Generator Custom Error Messages

One of my top 8 Oracle Applications (eBiz) developer hooks is workflow.

Most commonly workflow is used for Approvals and Account Generators.

The Account Generators are very specific little critters with a couple of subtleties, one of which is how they pass back error messages to the user. It ain't hard, just a straightforward 1-2-3 procedure.

1. Set the Error Message node attribute of the "Abort Generating Code Combination" item attribute "Error Message"
2. Ensure the workflow goes to "Abort Generating Code Combination" when an error occurs in your custom code
3. Make sure your code calls FND_MESSAGE and sets the ERROR_MESSAGE item attribute (internal identifier) to fnd_message.get_encoded

Lets do it for the Purchase Order (PO) Account Generator POWFPOAG, process Generate Default Charge Account.

1. Abort Generating Code Combination Error Message attribute.

• Open up the PO Account Generator in Workflow Builder
• Navigate to and open process "Generate Default Charge Account".
• Find and right click to get properties of "Abort Generating Code Combination" after the flow from Generate Default Expense AccountClick the Node Attributes tab
• Select Error Message from the Name dropdown
• Choose Type Item Attribute
• Choose Value "Error Message". NB: This is not in alphabetical order.
• Save

2. and 3. Code the "Failure" and set the message in your custom code.

In your custom procedure that generates the account, ensure you have code that looks something like the following code. Note we "borrow" the existing generic FND message ERROR_MESSAGE to save having to create a new message and generate message files. Also note that in this specific example we are generating the account based on some component of the Employee's default expense account, which if not setup correctly will set success = 'N'. Of course your logic will be different here, and I don't recommend multiple returns in a function, but this code is presented for simplicity.

if (success = 'N') then
 FND_MESSAGE.set_name('FND', 'ERROR_MESSAGE');
 FND_MESSAGE.set_token('MESSAGE',
   'The PO Account Generator failed to generate the default account. ' ||
   'Please ask your System Administrator to check your ' ||
   'Employee Default Expense Account is setup correctly.');
 wf_engine.SetItemAttrText
         ( itemtype=> itemtype,
           itemkey => itemkey,
           aname   => 'ERROR_MESSAGE',
           avalue  => fnd_message.get_encoded);
 result := 'COMPLETE:FAILURE';
 return;
end if;

All done, test it out! You should see something like the second screenshot when you hit an error.

PS. Persistence and Account Generators showing in Status Monitor

As a side note account generators persistence is "temporary" and also they don't appear under the Status Monitor in Workflow Administrator unless you switch a couple of debug profile options to Yes:

• PO: Set Debug Workflow ON
• Account Generator:Run in Debug Mode

PPS. Show workflows to all users with Workflow Admininstrator

For development and test environments, the following may come in handy to enable ALL users with access to Workflow Administrator Status Monitor to see ALL permanent workflows.

prompt Remember what the value was so we can set it back if needed.
select text
from   wf_resources
where  name = 'WF_ADMIN_ROLE';

TEXT
--------------------------------------------------------------------------------
SYSADMIN

update wf_resources
set    text = '*'
where  name = 'WF_ADMIN_ROLE';

1 row updated.

commit;

Happy Account Generating!

GL Account Code Combinations on the fly: Key Flexfield API

Every once in a while I've had the need to validate code combinations, and dynamically create them on the fly. With a properly structured Account Generator workflow, you can just throw in the "Validate Code Combination" function from Standard Flexfield Workflow. But what about a nice neat, keep it all in PL/SQL scenario? Well, it ain't too hard, here's some code for the General Ledger Accounting Flexfield, shouldn't be too hard to adapt for other key flexfields. Remove the dbms_output calls and adjust as you need:

create or replace function create_ccid 
( p_concat_segs in varchar2
) return varchar2
is
  -- pragma autonomous_transaction; -- if you need autonomy!
  l_keyval_status     BOOLEAN;
  l_coa_id            NUMBER;
begin
  begin
    select chart_of_accounts_id
    into   l_coa_id
    from   gl_sets_of_books
    where  set_of_books_id = fnd_profile.value('GL_SET_OF_BKS_ID');
  exception
    when no_data_found then
      dbms_output.put_line('Chart of Accounts ID not found from profile option GL_SET_OF_BKS_ID');
      dbms_output.put_line('Try setting up your environment with fnd_global.apps_initialize');
      raise;
  end;
  -- keyval_mode can be one of CREATE_COMBINATION CHECK_COMBINATION FIND_COMBINATION
  -- create will only work if dynamic inserts on and cross validation rules not broken
  l_keyval_status := fnd_flex_keyval.validate_segs(
                                           'CREATE_COMBINATION',
                                           'SQLGL',
                                           'GL#',
                                           l_coa_id,
                                           p_concat_segs,
                                           'V',
                                           sysdate,
                                           'ALL', NULL, NULL, NULL, NULL,
                                           FALSE,FALSE, NULL, NULL, NULL);
  if l_keyval_status then  
    dbms_output.put_line('Success');
    return 'S';
  else
    dbms_output.put_line('Error message: ' || fnd_flex_keyval.error_message);
    return 'F';
  end if;
end create_ccid;
/

Gotta have an example of its use, even easier:

set serverout on size 1000000
declare
  l_status varchar2(1);
begin
  l_status := create_ccid('1-9999-9999-9999-9999');
  dbms_output.put_line('Returned: ' || l_status);
  commit;
end;
/

And of course remember to COMMIT; If you need to query back what you just created, how about:

select gcc.code_combination_id
,      gcc.concatenated_segments
,      gcc.enabled_flag
,      gcc.start_date_active
,      gcc.end_date_active
,      gcc.detail_posting_allowed
,      gcc.detail_budgeting_allowed
from   gl_code_combinations_kfv gcc
where  concatenated_segments = '1-9999-9999-9999-9999';

PS. Check out my posts on the GL Account Structure and fnd_global.apps_initialize if necessary.

Short n Fat vs Tall n Slim: Apps Printer Drivers 66 lines per page A4 printing

In the old school days before the 3Ps - PASTA PDF Postscript and yonks before BIP there was character mode printing.

Actually, there still be character mode printing last time I checked. Gotta love those accountant's reports.

Being one of the ?unlucky? few operating outside the USA meant that the default printer driver initialization strings - or should that be initialisation - just didn't cut it with our tall n slim A4 paper vs short n fat US Letter paper. Hey, I'm not insinuating anything ;-)

And it was always a challenge to get character mode printing sorted for A4 paper without double spaced lines and printers that spat the dummy when you sent them US Letter. I could never understand why Oracle didn't deliver a few friendly print drivers for A4 paper.

Anyway one day many years ago I got out the handy character printing toolkit of:

• a PCL 4 syntax manual,
• the PCL escape character
• some nifty shell scripts

And decided to save a few trees and get portrait 80 x 66, landscape 132x66 and landwide 180x66 printing nicely making best use of space, fonts and not flowing 5 or so lines over to the next page.

Before this info is relegated to the age of dinosaurs, this is what I came up with:

A4 Paper Printer Driver Initialization Strings for HPLJ4 (or higher) printers

A4 Portrait 80x66 /eE/e&l0o26a7C/e(s0t0p11H/e&a5L/e&k2G
A4 Landscape 132x66 /eE/e(0N/e&l1o26a5.25c-340z150u0S/e(s0p13H/e&k2G
A4 Landwide 180x66 /eE/e(0N/e&l1o26a5.25c-340z150u0S/e(s0p16.66H/e&k6.75h2G/e(2N

Oh, while I'm here I should say that the o26 means A4 paper and the &k2G means set LF to CRLF which solves lines being printed in cascading style and disappearing off the right hand side of the page like this:

abcdefghijklmnopqrstuvwxyz
                          abcdefghijklmnopqrstuvwxyz
                                                    abcdefghijklmnopqrstuvwxyz
                                                                              abcdefghijklmnopqrstuvwxyz

Of course, you can also add a duplex mode via a bunch of additional drivers to save even more trees by adding one of:
&l1S - Duplex Long Edge
&l2S - Duplex Short Edge

Or even add some more drivers with relevant tray control with one of:
&l1H Feed from Tray 1 (upper)
&l2H Manual Feed
&l3H Manual Envelope Feed
&l4H Feed from Tray 2 (lower)
&l5H Feed from paper deck

A very quick and nasty way to get this sorted in an environment not using US Letter paper with printers setup as type HPLJ4SI is (not supported of course), oh and remember to restart the concurrent managers:

set define ~
update fnd_printer_drivers set
--user_printer_driver_name = 'LANDSCAPE A4 for Printer HPLJ4SI',
description = 'Init was ' || initialization,
initialization = '/eE/e(0N/e&l1o26a5.25c-340z150u0S/e(s0p13H/e&k2G',
last_update_date = sysdate
where printer_driver_name = 'LANDSCAPEHPLJ4SI';

update fnd_printer_drivers set
--user_printer_driver_name = 'LANDWIDE A4 for Printer HPLJ4SI',
description = 'Init was ' || initialization,
initialization = '/eE/e(0N/e&l1o26a5.25c-340z150u0S/e(s0p16.66H/e&k6.75h2G/e(2N',
last_update_date = sysdate
where printer_driver_name = 'LANDWIDEHPLJ4SI';

update fnd_printer_drivers set
--user_printer_driver_name = 'PORTRAIT A4 for Printer HPLJ4SI',
description = 'Init was ' || initialization,
initialization = '/eE/e&l0o26a7C/e(s0t0p11H/e&a5L/e&k2G',
last_update_date = sysdate
where printer_driver_name = 'PORTRAITHPLJ4SI';

commit;

Old school stuff, but can't remember how many times these statements have helped.

PS. Did I remember to say restart the concurrent managers after making printer driver changes?

PPS. Despite PASTA being a fantastic tool, I found myself modifying the following in order to get around a tad more setup to force US Letter to A4 paper

In file $FND_TOP/resource/pasta_pdf.cfg there is a line:
preprocess=pdftops {infile} {outfile}
Change this to the following (replace /usr/local/bin to wherever pdftops is installed):
preprocess=/usr/local/bin/pdftops -paper A4 {infile} {outfile}

Sweet.

Update: fixed some HTML formatting issues and missing || in the code, plus added a couple more comments.

Zero-Gee: the ultimate developers or gaming chair, err, throne

A few months back I attended an Unlimited Potential event on design and technology. A prototype of the NASA research based "Zero-Gee" chair - a 135° zero-gravity motorized ergonomic chair - made its appearance and got some interesting reactions from the audience. Well, its gone from design to prototype to reality. I had an email last week from Tom Rynaarts letting me know the first small batch is close to delivery. Talk about a great concept, bound to be a strong following in tech communities for this ultimate piece of hardware. Now, where did I put that spare Xmas money!

PS. Unfortunately they're not generally available yet, but keep an eye out ;-)

XLite outgoing calls via 2Talk get error 408 with Zone Alarm

A week or two back I signed up at New Zealand's 2talk on the free plan to try out some funky VOIP services ... you get a free New Zealand "mobile" phone and fax number and a range of services, voice mail, call forwarding, fax mailbox etc, I'm planning to use it next time I'm on holiday/business to avoid exorbitant roaming charges. Tried out the call forwarding to my mobile, worked fine.

Just tried to call out from my laptop with X-Lite and the call didn't initiate, just got error "request timeout" and a pretty female voice saying "the other party is busy" or something like that.
I switched on the XLite diagnostic log and saw:

[07-07-22]21:07:03.059 | Info | RESIP:DUM | "Got: SipResp: 408 tid=blahblah cseq=INVITE / 1 from(wire)" | 
[07-07-22]21:07:03.060 | Info | RESIP:DUM | "Failure:  error response: SipResp: 408 tid=blahblah cseq=INVITE / 1 from(wire)" |  

Quick surf identified Zone Alarm as the culprit blocking port 5060. I couldn't work out quickly how to setup my Zone Alarm (free version) to open port 5060, so here's an alternative.
Instead of opening port 5060, you can add the 2talk server to your trusted zone.
If you make a "failed" call then open up the ZoneAlarm Control Center, click Alerts and Logs, you'll see a Blocked (under Action Taken) Incoming (under Direction) with a source IP something like 202.180.xxx.yyy :5060. Right click on that line and choose Add to Zone: Trusted.
Note that making this change allows whatever your trusted zone settings are to/from the 2talk server. And 2talk may have more servers or change them, so you may need to do this again.

Voila... outgoing calls should now work.

Query: eBiz Setup of Books/Chart of Accounts keyflex segments

Thought I'd post a few of the common queries I run to save some retyping... here's the first! When I want a quick overview of the structure of clients chart of accounts, the following query comes in handy. Shows chart of accounts, set of books, accounting flexfield segments in use and their attributes, value sets for the key flexfield segments. Looks big, but quite a bit of repetition here to get the segment attributes.

select sob.name sob_name
,      sob.set_of_books_id sob_id
,      sob.chart_of_accounts_id coa_id
,      fifst.id_flex_structure_name struct_name
,      ifs.segment_name
,      ifs.application_column_name column_name
,      sav1.attribute_value  BALANCING
,      sav2.attribute_value COST_CENTER
,      sav3.attribute_value NATURAL_ACCOUNT
,      sav4.attribute_value  INTERCOMPANY
,      sav5.attribute_value SECONDARY_TRACKING
,      sav6.attribute_value GLOBAL
,      ffvs.flex_value_set_name
,      ffvs.flex_value_set_id
from   fnd_id_flex_structures fifs
,      fnd_id_flex_structures_tl fifst
,      fnd_segment_attribute_values  sav1
,      fnd_segment_attribute_values sav2
,      fnd_segment_attribute_values sav3
,      fnd_segment_attribute_values  sav4
,      fnd_segment_attribute_values sav5
,      fnd_segment_attribute_values sav6
,      fnd_id_flex_segments ifs
,      fnd_flex_value_sets ffvs
,      gl_sets_of_books sob
where  1=1
and    fifs.id_flex_code = 'GL#'
and    fifs.application_id =  fifst.application_id
and    fifs.id_flex_code = fifst.id_flex_code
and    fifs.id_flex_num = fifst.id_flex_num
and    fifs.application_id =  ifs.application_id
and    fifs.id_flex_code = ifs.id_flex_code
and    fifs.id_flex_num = ifs.id_flex_num
and    sav1.application_id =  ifs.application_id
and    sav1.id_flex_code = ifs.id_flex_code
and    sav1.id_flex_num = ifs.id_flex_num
and    sav1.application_column_name =  ifs.application_column_name
and    sav2.application_id =  ifs.application_id
and    sav2.id_flex_code = ifs.id_flex_code
and    sav2.id_flex_num = ifs.id_flex_num
and    sav2.application_column_name =  ifs.application_column_name
and    sav3.application_id =  ifs.application_id
and    sav3.id_flex_code = ifs.id_flex_code
and    sav3.id_flex_num = ifs.id_flex_num
and    sav3.application_column_name =  ifs.application_column_name
and    sav4.application_id =  ifs.application_id
and    sav4.id_flex_code = ifs.id_flex_code
and    sav4.id_flex_num = ifs.id_flex_num
and    sav4.application_column_name =  ifs.application_column_name
and    sav5.application_id =  ifs.application_id
and    sav5.id_flex_code = ifs.id_flex_code
and    sav5.id_flex_num = ifs.id_flex_num
and    sav5.application_column_name =  ifs.application_column_name
and    sav6.application_id =  ifs.application_id
and    sav6.id_flex_code = ifs.id_flex_code
and    sav6.id_flex_num = ifs.id_flex_num
and    sav6.application_column_name =  ifs.application_column_name
and    sav1.segment_attribute_type =  'GL_BALANCING'
and    sav2.segment_attribute_type = 'FA_COST_CTR'
and    sav3.segment_attribute_type = 'GL_ACCOUNT'
and    sav4.segment_attribute_type  = 'GL_INTERCOMPANY'
and    sav5.segment_attribute_type =  'GL_SECONDARY_TRACKING'
and    sav6.segment_attribute_type =  'GL_GLOBAL'
and    ifs.id_flex_num = sob.chart_of_accounts_id
and    ifs.flex_value_set_id = ffvs.flex_value_set_id
-- comment the next expression to show all books
-- currently it show the info for the site level set profile option value
and    sob.set_of_books_id = 
        nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.set_of_books_id)
order by  sob.name, sob.chart_of_accounts_id, ifs.application_column_name;

And just for good measure here's the sample output from 11.5.10.2 Vision Demo instance.

SOB_NAME                           SOB_ID     COA_ID STRUCT_NAME                    SEGMENT_NAME                   COLUMN_NAME                    B C N I S G FLEX_VALUE_SET_NAME                                          FLEX_VALUE_SET_ID
------------------------------ ---------- ---------- ------------------------------ ------------------------------ ------------------------------ - - - - - - ------------------------------------------------------------ -----------------
Vision Operations (USA)                 1        101 Operations Accounting Flex     Company                        SEGMENT1                       Y N N Y N Y Operations Company                                                     1002470
Vision Operations (USA)                 1        101 Operations Accounting Flex     Department                     SEGMENT2                       N Y N N N Y Operations Department                                                  1002471
Vision Operations (USA)                 1        101 Operations Accounting Flex     Account                        SEGMENT3                       N N Y N N Y Operations Account                                                     1002472
Vision Operations (USA)                 1        101 Operations Accounting Flex     Sub-Account                    SEGMENT4                       N N N N N Y Operations Sub-Account                                                 1002473
Vision Operations (USA)                 1        101 Operations Accounting Flex     Product                        SEGMENT5                       N N N N N Y Operations Product                                                     1002474

Nice.