Thursday, May 17, 2018

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.

  1. 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.


Monday, May 14, 2018

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

Best Blogger TipsGet Flower Effect