Archive for the ‘Oracle Project Accounting’ Category

Project Accounting – Auto Accounting Rule – Extend Labour Control Account

Friday, March 4th, 2016

We had a requirement to extend the labour control accounts to use different accounts depending on the capitalisation status of the transaction. This enclosed SQL statement is an example of how the issues was resolved. This allowed us to specify a different account depending on the type of project.

SELECT DECODE(project_type_class_code,'CAPITAL', DECODE(billable_flag, 'Y','CAPITAL CoA Segement Value', 'NON-CAPITAL CoA Segment Value') ,'NON-CAPITAL CoA Segment Value') "Value"
FROM pa_expenditure_items_all pei,
  pa_projects_all ppa,
  PA_PROJECT_TYPES_ALL ppt
WHERE 1                 =1
AND expenditure_item_id = :1
AND pei.project_id      = ppa.project_id
AND ppa.project_type    = ppt.project_type;

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

Friday, 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

Friday, 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;