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.