Project Accounting – Example AA Rule for Classifications when you have more than 1 AA Classification

March 4th, 2016

Example Auto Accounting Lookup Rule. Used in the event that you have more than 1 classification setup to be used in auto accounting. Dont forget you’ll need to modify this for your own uses.
SELECT ppc.CLASS_CODE
FROM pa_projects_all ppa,
PA_PROJECT_CLASSES ppc,
PA_CLASS_CODES PCC
WHERE ppa.project_id = ppc.project_id
AND ppa.project_id = :ProjectID
AND PPC.CLASS_CATEGORY = PCC.CLASS_CATEGORY
AND PPC.CLASS_CATEGORY = :CategoryName
AND PPC.CLASS_CODE = PCC.CLASS_CODE
AND TRUNC(SYSDATE) BETWEEN PCC.START_DATE_ACTIVE AND NVL(PCC.END_DATE_ACTIVE,SYSDATE+1) ;

Oracle Projects: Resources – SQL Auto-Accounting Validation Scripts

March 4th, 2016

Having setup a number of operating units extremely quickly, and finding the standard Oracle Reports layout difficult to make easy comparisons, the enclosed SQL statement will help you to extract the configuration for all OU and accounting rules.

This script was tested and confirmed working in R12.2.4.

SELECT ho.name "OU",
pf.function_name,
pf.description,
pft.function_transaction_name,
pft.enabled_flag,
psr.segment_num,
pr.rule_name
FROM PA_FUNCTION_TRANSACTIONS_ALL pft,
hr_all_organization_units ho,
PA_SEGMENT_RULE_PAIRINGS_ALL psr,
PA_RULES pr,
PA_FUNCTIONS pf
WHERE pft.org_id = ho.organization_id
AND pft.org_id = psr.org_id
AND psr.function_code = pft.function_code
AND psr.function_transaction_code = pft.function_transaction_code
AND pr.rule_id = psr.rule_id
AND pf.function_code = pft.function_code
ORDER BY
pf.function_name,
function_transaction_Name,
segment_num;

Oracle – EBS – Database Variables

March 4th, 2016

The purpose of the ALTER SESSION statement is “to specify or modify any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database.”
ALTER SESSION SET NLS_LANGUAGE= 'AMERICAN' NLS_TERRITORY= 'AMERICA' NLS_CURRENCY= '$' NLS_ISO_CURRENCY= 'AMERICA' NLS_NUMERIC_CHARACTERS= '.,' NLS_CALENDAR= 'GREGORIAN' NLS_DATE_FORMAT= 'DD-MON-RR' NLS_DATE_LANGUAGE= 'AMERICAN' NLS_SORT= 'BINARY';

UPS vs. FedEx

October 8th, 2014

Fascinating insight into the logistic business delivery services.

The Importance of Shredders at Home!

September 22nd, 2014

Unlike digital documents, paper is a really easy medium to share and distribute – we all love paper, its done wonders for mankind to get us to this point. However, the risks associated with paper to individual users are massive in the modern world. Risks are everywhere, your postman happily delivers a pile of junk, as well as really important paperwork through your door almost every day, you accumulate reams of paper with your personal information on it all the time. Data on paper you might think its pretty boring, who’s going to want your junk mail from “another highstreet” lender?

Well, think about the detail in that junk. Lots of companies pre-complete forms with your basic information. You throw that in the bin. Combined that with all the other documents you’ve thrown away, someone who really wants to know about you can easily swipe it.

We even make it easier for thieves. If you recycle, and I hope you all do, you’ll likely separate out paper, plastics and glass. You may have a recycle bin that collects everything. But, your always asked to clean the waste food and ensure the bin is (within reason) clean.

This makes it a thief’s dream! You’re regularly throwing away personal bits of your life.

So, should you shred your documents. Most definitely. Unless you’ve got an intelligence agency on your tracks, and lets face it, if they were, they’d skip your bin and go straight to other aspects of your life, You should be disposing of your personal and work data safely. A home shredder is a valuable investment, that can give you peace of mind that someone isn’t swiping that personal information straight out of your recycling.

You get what you pay for. I can happily recommend the following Fellowes Powershred DS-1. While it makes a mess sometimes if it gets too close to the top – I empty when I see it at about 70% full. It does shift through everything I throw at it without stopping. 12 Months on its still going.

I’m sure you’re just as capable of reading reviews, and picking your own, but, what ever you do, invest in a little personal security at home. I’ll update you soon on other shredders after buying them for various members of the family.

Many UK businesses unhappy with their ERP… Really, WHY?

September 17th, 2014

A recent ERPNews article really made my blood boil. LINK: http://www.erpnews.net/622/uk-businesses-unhappy-erp/

Taking the systems implementer approach to this issue, regardless of your chosen flavor (you did pick Oracle eBusiness Suite didn’t you?), you generally find many of the whizzy, snazzy or down right useful features are delayed until later releases. Generally it I’ve found that they’re scoped planned and then shelved as excellent ideas that just haven’t got off the ground because;

  1. They cost to much in this years budget
  2. They don’t deliver returns quickly enough
  3. Leaders don’t have or haven’t had the vision communicated well enough to them to see the benefits
  4. Quite simply… I didn’t know it did that?

Most of the time, your ERP was chosen for all the right reasons, you may have changed, adapted or grown. This doesn’t mean the your application isn’t suitable, it just means that you may need to explore different ways to exploit it. Unless your using a ERP platform from the ark, most new functionality could easily be within your reach. If its not, have you looked at using Oracle eBusiness suite, I understand its really good.

If you are using technology from the ark, then your justified in your unhappiness, you may want to talk to these people? LINK  (Remember other vendors are available).

How to get to those benefits

  1. Undertaking a review of your current processes is a excellent start to understanding how you operate, what you actually “do”, and how you go about it.
  2. Measure it… how can you justify something has worked unless you can measure it. Sometimes the measurement may be in resource harmony, most of the time you expect a performance or process improvement, but make sure you have some kind of metric to understand what success looks like.
  3. Talk to your vendor, read up on new releases since you’ve implemented the solution, visit user groups. Yes this may require effort, it may be that you’ve had someone in support interested for years in resolving the issues, they’ve just not been aware you wanted to do something about it.
  4. Too big, or too much to do yourself? Don’t be afraid to ask others for information and experience, you may have to invest in some consulting time, but if the goals worth and you’ve measured it, the justification might just pay for its self.
  5. Don’t forget your ERP. Stick with the times, you’ve invested significantly in the application, you should ensure you’ve got the right level of support and improvement projects on going – staying up to date helps, enables you to more easily exploit new functionality.

Oracle EBS SQL: OTL

December 20th, 2013

The following SQL statement can be used for debugging the Oracle Time and Labor (OTL) and Projects (PA) interface.
This was created during a recent R12 upgrade where attributes where somehow resulting in a quantity in projects multiplied by the project ID.

SELECT   hts.timecard_id, hts.resource_id, hts.start_time,
hts.stop_time, hts.submission_date,
htb1.start_time each_day, hta.attribute1 project_id,
hta.attribute2 task_id, htb2.measure, pt.task_name, 
hts.approval_item_key, hts.transferred_to,
htb.unit_of_measure
,ppa.segment1, pt.task_number
/**                Funny Attributes
  These should be cleared during the upgrade Process. They are still populated post upgrade. 
**/                
,hta.attribute25
,hta.attribute26
,hta.attribute27
,hta.attribute29
FROM hxc_time_building_blocks htb,
hxc_time_building_blocks htb1,
hxc_time_building_blocks htb2,
hxc_time_attribute_usages htau,
hxc_time_attributes hta,
pa_projects_all ppa,
hxc_timecard_summary hts,
pa_tasks pt
WHERE 1=1
AND htb1.parent_building_block_id = htb.time_building_block_id
AND htb1.parent_building_block_ovn = htb.object_version_number
AND htb2.parent_building_block_id = htb1.time_building_block_id
AND htb2.parent_building_block_ovn = htb1.object_version_number              
AND htb.SCOPE = 'TIMECARD'
AND htb1.SCOPE = 'DAY'
AND htb2.SCOPE = 'DETAIL'
AND htau.time_building_block_id = htb2.time_building_block_id
AND htau.time_building_block_ovn = htb2.object_version_number
AND htau.time_attribute_id = hta.time_attribute_id
AND hts.start_time = htb.start_time
AND hts.resource_id = htb.resource_id 
AND hta.attribute_category = 'PROJECTS'
AND hta.attribute2 = pt.task_id
AND hta.attribute1 = pt.project_id
AND ppa.project_id = hta.attribute1              
AND hts.timecard_id = :p_timecard_id              
and ppa.segment1 like 'YOUR_PROJECT_NUMBER'
--AND hts.approval_status <> 'APPROVED'
--AND trunc(hts.submission_date) = trunc(sysdate-360)
--AND hts.transferred_to is null        
--and ppa.project_id <> hta.attribute25

Oracle EBS SQL: GL Headers and Lines

December 19th, 2013

The following SQL Statement is useful to find information about your GL Journals:

Note, this SQL statement is designed to link attributes 1 and 2 on the GL Journal lines to Projects, as such should be removed for any use elsewhere.

SELECT gjh.je_header_id jnl_hdr_id ,
gjct.user_je_category_name category ,
gjst.user_je_source_name source ,
gjb.status post_status ,
gjb.name batch_name ,
gjh.period_name ,
gjh.name journal_name ,
gjh.external_reference REF ,
gjh.date_created ,
gjh.description ,
gjh.running_total_dr ttl ,
gjh.creation_date ,
fu.description created_by ,
gjl.je_line_num ,
gjl.description line_descr ,
gjl.reference_1 line_ref ,
gjl.reference_5 ap_inv_num ,
gjl.reference_6 line_src ,
gjl.reference_10 line_type ,
GCC.SEGMENT1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| GCC.SEGMENT4
|| '-'
|| gcc.segment5 cgh_acct ,
GJL.ACCOUNTED_DR DR ,
GJL.ACCOUNTED_CR CR ,
GJL.ATTRIBUTE1 PROJECT_ID ,
GJL.ATTRIBUTE2 TASK_ID ,
PPA.SEGMENT1 PROJECTNUMBER,
PT.TASK_NUMBER TASKNUMBER
FROM GL.GL_JE_HEADERS GJH ,
GL.GL_JE_BATCHES GJB ,
GL.GL_JE_LINES GJL ,
GL.GL_JE_SOURCES_TL GJST ,
GL.GL_JE_CATEGORIES_TL GJCT ,
GL.GL_CODE_COMBINATIONS GCC ,
APPLSYS.FND_USER FU ,
PA.PA_PROJECTS_ALL PPA ,
PA.PA_TASKS pt
WHERE GJH.JE_BATCH_ID                                            = GJB.JE_BATCH_ID(+)
AND gjh.je_header_id                                             = gjl.je_header_id
AND GJH.JE_SOURCE                                                = GJST.JE_SOURCE_NAME
AND GJH.JE_CATEGORY                                              = GJCT.JE_CATEGORY_NAME
AND GJH.CREATED_BY                                               = FU.USER_ID
AND GJL.CODE_COMBINATION_ID                                      = GCC.CODE_COMBINATION_ID
-- These allow for duff information from other sources to be excluded.
AND DECODE(GJL.CONTEXT,'Capital Contribution',GJL.ATTRIBUTE1,-1) = PPA.PROJECT_ID(+)
AND DECODE(GJL.CONTEXT,'Capital Contribution',GJL.ATTRIBUTE2,-1) = PT.TASK_ID (+)
AND GJH.JE_CATEGORY                                              = 'Capital Contribution'
ORDER BY GJH.CREATION_DATE DESC ,
GJB.NAME ,
GJH.NAME ,
GJL.JE_LINE_NUM;

* All SQL statements are provided without warranty or support. Use at your own risk!

LastPass

April 25th, 2013

I know I’m late, in fact, scrub that, very late to the game on this one, however, never the less, I think it merits a little bit of effort. I’ve started to use LastPass in replacement of the standard features available in browsers. Maybe its old age, or simply, the continuous growth in the number of accounts, passwords and various other elements involved with using the internet. My password systems and memory methods were getting a little bit painful and slow.

I was previously a very strong advocate of PasswordCard.org its simple effective and you can make your own rules on top of a simple bit of paper. Mine involved various patterns and string depending on the websites purpose and importance.

I admit there is some risk with online password managers, but, at the end of the day, no matter how secure a system is, if it relies on you need to know some kind of fact to get in, there is going to be away to crack it. You as a user just need to reduce the risks, and take as many security steps as you can to prevent your exposure.

If you are looking for a method to store your passwords – and other data – safely, you won’t find many password managers that are more secure than LastPass.

Heard of BI?

July 22nd, 2012

So you’ve heard the term BI, Business Intelligence. You think, expense, high cost, and poor return on investment. I hate to be the bearer of bad news, but you’re wrong. Business intelligence isn’t the preserve of the large overbearing corporate entities, the emergence of analytics software can support smaller SME, and that does include those non-technical companies.

According to Gartner, the worldwide market for BI platform, analytic applications and performance management software generated revenues of $12.2bn in 2011. That’s 16% up on 2010, making BI the second fastest growing sector in the overall worldwide enterprise software market. More vendors than you can shake a stick at are appearing in the market with tools that can enable your businesses BI.

This growth could be attributed to a number of sources. Cloud, mobile and social media are all enablers for companies, leveraging and analysing the outputs and availabilities of these sources are often more difficult. How many companies have we seen with there own Facebook & Linkedin page? Do you use Twitter?

Why are these factors helping BI implementation, its down to targeting users and markets. BI tools often enable you to do more than write reports on your finance system. They often aren’t vendor or application specific.

SME’s as small, often market specific channels are in excellent positions to quickly move and understand these market shifts, they can leverage “Cloud” and SaaS principles to expand functions quickly to accommodate new trends. But spotting and understanding the trends is often more luck than insight.

So you’re an SME (or even large organisation) just starting out on the BI path. Where do you go what are your first moves?

Firstly, understand your data estate and processes. I can’t stress this enough, systems and technology in business are your businesses lifeblood. Seriously how much of your business works without technology? How many of your processes depend on users, customers or stakeholders utilising your technology? Understanding where technology is used, what data it depends on and the mediums it users are essential.

Secondly, many of your current systems have some kind of reports as well as a presentation layer? Establish what your users do with the data? Are they extracting it to publish, doing lookups or validation to non-linked data sets?

Thirdly, and this might be difficult, decide what your business strategy is? Can’t, haven’t or don’t have one? Well, tell me what you want to improve, become faster leaner or more focused? Do you have lots of customers and marketing data, do you understand relationships

4th (Fourthly didn’t sound right), leverage and evaluate what you have, map and out your data, relationships and (potential) linkages. Relate information to external markets and indexes.

5th, understand your users and capabilities, there is no point buying a Global Corporate grade BI solution if you can’t use it. Remember, its child, it will grow, start small and targeted, grow your own skills and capabilities. Understand where contractors can be applied to initially setup the basics.

6th choose your platform, if you’ve evaluated your current applications and providers offerings which are normally the quickest to implement. Generally they also offer a close match to your budget too. If that doesn’t suit your going to have to examine the market and approach providers who understand the technologies you need. Normally, if your in the cheap seats, move people head for Crystal Reporting type products for there high scalability, and downright cheap price point for capabilities. You can often find developers of many flavours who can use it. Even many jumped up junior programmers can use it, as its built-in with many MS Visual Studio installs for the last few generations.

7th, the mash up. You’ve got your data, you’ve got some super tools. You know need to get a little creative. Experiment, relate data together, look for correlations and patterns. The greatest part of the BI toolset is the interpretation engine that you the user has. Your wetware is the key to unlocking the valuable information behind your reports.

In conclusion, buying a BI product isn’t the end, you will learn, you will want to explore more, you stop, your business stops learning. BI is like a educating a child, as it grows, it becomes more powerful, sometimes you will have to change schools for it to specialize in new areas, sometimes you have to get a tutor and sometimes you simply have to let it make mistakes. Each is a new experience.