Explanation and Examples of Transaction Control Functionality in Projects

Overview

In the Projects Screen you set up transaction controls either at the project level or at the task level (Tasks -> Options -> Transaction controls). Task level controls override project level controls.

The Limit to Transaction Controls checkbox determines whether your transaction controls are exclusive or inclusive.

• Checked = Inclusive: Anything not listed is not chargeable
• Unchecked = Exclusive: Anything not listed is chargeable.

Within either type of transaction control, you can indicate whether a listed type of transaction is chargeable or not by checking or not checking the chargeable checkbox; however, generally for inclusive controls you will check the chargeable checkbox because you are listing the chargeable transactions, and conversely, for an exclusive control you will generally not check the chargeable box because you are listing the types of transactions that cannot be entered. Generally you will only reverse this option when you want to specify a specific exclusion or inclusion to a more general control line (e.g., all Labor transactions should be included except for a single expenditure type).

Examples of transaction controls:

Example 1

On the transaction control screen "Limit to Transaction Controls" = Checked

In the expenditure section you have the following defined:

LINE 1: Category="Labor" Employee="Marlin, Amy" Chargeable=checked
LINE 2: Category="Labor" Employee="Gray, Donald" Chargeable=checked

This would mean:

• Marlin and Gray can charge only labor.
• Other employees and types of charges are not allowed.

Example 2

On the transaction control screen "Limit to Transaction Controls" = Checked.

In the expenditure section you have the following defined:

LINE 1: Employee="Marlin, Amy" Chargeable=checked
LINE 2: Employee="Gray, Donald" Chargeable=checked
LINE 3: Category="Other Expense" Type="Entertainment" Chargeable=not checked

This would mean:

• Marlin and Gray can charge all types of transactions, except for Entertainment.
• No other employee can charge anything.

Example 3

On the transaction control screen "Limit to Transaction Controls" = NOT Checked.

In the expenditure section you have the following defined:

LINE 1: Employee="Marlin, Amy" Chargeable=checked
LINE 2: Employee="Gray, Donald" Chargeable=not checked
LINE 3: Category="Other Expense" Chargeable=not checked.

This would mean:

• Amy Marlin can charge all types of transactions.
• Gray can not charge anything.
• All other employees can charge everything except expenses.

Precedence Rules

The examples above illustrate that is is not always easy to determine the behavior when there are multiple rules that apply to a transaction.  For example in example 3, LINE 1 would allow all transactions to Amy Marlin, but LINE 3 prohibits all "Other Expenses" charges.  As we indicated in the results, in this case, LINE 1 takes precedence.

However, in example 2, we can see that in a similar situation with "Limit to Transaction Controls" checked it is LINE 3 that takes precedence.  The following table illustrates the results of testing each of the combinations that may come in conflict.

In the table, it is assumed that:

• If an item (for example, Exp Type) is populated in both the chargeable line (line 1) and the non-chargeable line (line 2) then the value for that item is the same in both lines
• The transaction we are trying to create matches all of the populated values (so that both lines are applicable to the transaction)

Limit to Txn	Line 1 - Chargeable	Line 2 - NON-Chargeable	Chargeable?
Yes	Employee	Employee AND Exp Category	No
Yes	Employee	Employee AND Exp Category AND Exp Type	No
Yes	Employee	Exp Category	No
Yes	Employee	Exp Category AND Exp Type	No
Yes	Employee AND Exp Category	Employee	Yes
Yes	Employee AND Exp Category	Employee AND Exp Cateogry AND Exp Type	No
Yes	Employee AND Exp Category	Exp Category	Yes
Yes	Employee AND Exp Category	Exp Category AND Exp Type	Yes
Yes	Employee AND Exp Category AND Exp Type	Employee	Yes
Yes	Employee AND Exp Category AND Exp Type	Employee AND Expenditure Category	Yes
Yes	Employee AND Exp Category AND Exp Type	Exp Category	Yes
Yes	Employee AND Exp Category AND Exp Type	Exp Category AND Exp Type	Yes
Yes	Exp Category	Employee	No
Yes	Exp Category	Employee AND Exp Category	No
Yes	Exp Category	Employee AND Exp Category AND Exp Type	No
Yes	Exp Category	Exp Category AND Exp Type	No
Yes	Exp Category AND Exp Type	Employee	No
Yes	Exp Category AND Exp Type	Employee AND Exp Category	No
Yes	Exp Category AND Exp Type	Employee AND Exp Category AND Exp Type	No
Yes	Exp Category AND Exp Type	Exp Category	Yes
No	Employee	Employee AND Exp Category	No
No	Employee	Employee AND Exp Category AND Exp Type	No
No	Employee	Exp Category	Yes
No	Employee	Exp Category AND Exp Type	Yes
No	Employee AND Exp Category	Employee	Yes
No	Employee AND Exp Category	Employee AND Exp Category AND Exp Type	No
No	Employee AND Exp Category	Exp Category	Yes
No	Employee AND Exp Category	Exp Category AND Exp Type	Yes
No	Employee AND Exp Category AND Exp Type	Employee	Yes
No	Employee AND Exp Category AND Exp Type	Employee AND Exp Category	Yes
No	Employee AND Exp Category AND Exp Type	Exp Cateogry	Yes
No	Employee AND Exp Category AND Exp Type	Exp Category AND Exp Type	Yes
No	Exp Category	Employee	No
No	Exp Category	Employee AND Exp Category	No
No	Exp Category	Employee AND Exp Category AND Exp Type	No
No	Exp Category	Exp Category AND Exp Type	No
No	Exp Category AND Exp Type	Employee	No
No	Exp Category AND Exp Type	Employee AND Exp Category	No
No	Exp Category AND Exp Type	Employee AND Exp Category AND Exp Type	No
No	Exp Category AND Exp Type	Exp Category	Yes

Note: The results in this table were validated in both Release 12.0 and 12.1.

It is difficult to lay out a single simple rule for the precedence results, however, here are a few points we can highlight:

1. If transaction control has all of the same values as another control PLUS additional values, then it's chargeable flag will take precedence on transactions to which they both apply.   So a line with Employee = Amy Marlin AND Expenditure Category = Labor will take precedence on transactions to which it applies over a line which just specifies the Expenditure Category = Labor.  This rule will cover the majority of cases where there are two or more rules which apply to the same transaction.

2. In cases where rule #1 does not apply (i.e., one line does not contain all the values of the other plus additional details), then in almost all cases, the line with an employee assigned will take precedence.  The only exception is when

·          

• "Limit to Transaction Controls" is checked
• Only the Employee is assigned on the Chargeable control line
• Expenditure Category (and optionally Expenditure Type) are assigned on the NON-Chargeable control line

In this case the non-chargeable line takes precedence over the line with the employee assigned.

Query to Get Oracle Projects Revenue and Billing Details

  SELECT ppc.class_code Company,

         haou.name Organization,

         ppa.project_type Project_Type,

         papf.full_Name Project_Manager,

         ppa.segment1 Project_Number,

         ppa.long_name Project_Name,

--         rc.customer_name Customer_Name,

         paa.agreement_num Agrement_Number,

         ppa.start_date Project_Start_Date,

         ppa.completion_date Project_End_Date,

         pps.project_status_name Status,

         paa.amount Funding,

         pe.event_type Event_Type,

         pe.event_num Event_Number,

         pt.task_name Task_Name,

         pe.completion_date Event_Date,

         pe.description Description,

         DECODE (pe.revenue_distributed_flag, 'Y', 0, pe.bill_trans_rev_amount) Revenue_Amount,

         DECODE (pe.billed_flag, 'Y', 0, pe.bill_trans_bill_amount) Bill_Amt

    FROM pa_project_classes ppc,

         pa_project_types_all ppta,

         hr_all_organization_units haou,

         pa_projects_all ppa,

         per_all_people_f papf,

--         ra_customers rc,

         pa_agreements_all paa,

         pa_project_statuses pps,

         pa_project_players ppp,

         pa_project_role_types pprt,

         pa_project_fundings ppf,

         pa_events pe,

         pa_tasks pt

   WHERE     ppa.project_id = ppc.project_id

         AND UPPER (ppta.project_type_class_code) = 'CONTRACT'

         AND UPPER (ppc.class_category) = 'SHIPPING ACCOUNT'

         AND pps.project_status_code = ppa.project_status_code

         AND ppa.CARRYING_OUT_ORGANIZATION_ID = haou.organization_id

         AND papf.person_id = ppp.person_id

         AND ppp.project_role_type = pprt.project_role_type

         AND UPPER (pprt.project_role_type) = 'PROJECT MANAGER'

         AND ppp.project_id = ppa.project_id

         AND ppa.project_id = ppf.project_id

         AND ppf.agreement_id = paa.agreement_id

--         AND paa.customer_id = rc.customer_id

         AND ppa.project_id = pe.project_id

         AND pe.task_id = pt.task_id(+)

         AND ppta.project_type = ppa.project_type

         AND ppa.org_id = ppta.org_id

--         AND UPPER (ppc.class_code) = UPPER (NVL (:p_company, ppc.class_code))

--         AND UPPER (haou.name) = UPPER (NVL (:p_organization, haou.name))

--         AND UPPER (ppa.project_type) = UPPER (NVL (:p_project_type, ppa.project_type))

--         AND UPPER (papf.full_name) = UPPER (NVL (:p_project_manager, papf.full_name))

--         AND pe.completion_date BETWEEN DECODE (:p_date_from,NULL, pe.completion_date,:p_date_from) AND DECODE (:p_date_to,NULL, pe.completion_date,:p_date_to)

         AND ( (pe.billed_flag = 'N' AND pe.revenue_distributed_flag = 'N')

              OR (    pe.billed_flag = 'Y'

                  AND pe.revenue_distributed_flag = 'N'

                  AND pe.bill_trans_rev_amount <> 0)

              OR (    pe.billed_flag = 'N'

                  AND pe.revenue_distributed_flag = 'Y'

                  AND pe.BILL_trans_bill_AMOUNT <> 0))

           AND ppa.segment1 = 'XX399-861'

GROUP BY ppa.project_id,

         ppc.class_code,

         haou.name,

         ppa.project_type,

         papf.full_Name,

         ppa.segment1,

         ppa.long_name,

--         rc.customer_name,

         paa.agreement_num,

         ppa.start_date,

         ppa.completion_date,

         pps.project_status_name,

         paa.amount,

         pe.event_type,

         pe.event_num,

         pt.task_name,

         pe.completion_date,

         pe.description,

         DECODE (pe.revenue_distributed_flag,'Y', 0,pe.bill_trans_rev_amount),

         DECODE (pe.billed_flag, 'Y', 0, pe.bill_trans_bill_amount)

ORDER BY ppc.class_code,

         haou.name,

         ppa.project_type,

         ppa.segment1,

         pe.event_num