Transaction Controls in Projects

This section briefs about the Transaction Controls that can be used for a Project. We have 2 types of Transaction Controls to be implemented to a Project as listed below,

Inclusive Transaction Controls: Inclusive transaction controls limit charges to only the transaction controls entered; Oracle Projects then rejects any charges that are not listed as chargeable in the transaction controls. You make your transaction controls inclusive by checking the Limit to Transaction Controls box on the Transaction Controls window. Herein is the example screenshot where we allow only Labour Charges for the Project ‘P1’ and Task ‘T1’.




Exclusive transaction controls: Exclusive transaction controls allow all charges except those that are specified as non-chargeable in the transaction controls. Oracle Projects
defaults to exclusive transaction controls, and Limit to Transaction Controls is Unchecked as below,




Print This Post

My Useful Queries

I use the following queries very frequently on my use of Oracle E-Business suite 11i,

1. Identify Buyers without a Position assigned if using Position Hierarchy

SELECT paf.person_id, ppf.full_name
FROM per_all_assignments_f paf
,po_agents poa
,per_people_f ppf
,per_assignment_status_types past
WHERE trunc(sysdate) between TRUNC(paf.effective_start_date) AND TRUNC(paf.effective_end_date)
AND paf.person_id = poa.agent_id
AND paf.person_id = ppf.person_id
AND TRUNC(sysdate) between TRUNC(ppf.effective_start_date) and TRUNC(ppf.effective_end_date)
AND paf.position_id is null
AND paf.job_id is not null
AND paf.assignment_status_type_id = past.assignment_status_type_id
AND past.user_status = 'Active Assignment'
AND TRUNC(sysdate) between TRUNC(poa.start_date_active) AND TRUNC(NVL(poa.end_date_active, sysdate))
ORDER BY paf.person_id;

2. Retrive Timecard details with Resource, and Timecard Start time and End Time

SELECT DISTINCT PP.SEGMENT1 "Project Number", PT.TASK_NUMBER "Task number", HTA.ATTRIBUTE3, DAY.START_TIME, DET.MEASURE, DET.APPROVAL_STATUS, DET.COMMENT_TEXT
FROM
(SELECT TIME_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_ID ,
PARENT_BUILDING_BLOCK_OVN ,
OBJECT_VERSION_NUMBER ,
MEASURE ,
RESOURCE_ID ,
APPROVAL_STATUS ,
COMMENT_TEXT, TRANSLATION_DISPLAY_KEY
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DETAIL'
) DET ,
(SELECT TIME_BUILDING_BLOCK_ID,
PARENT_BUILDING_BLOCK_ID ,
PARENT_BUILDING_BLOCK_OVN ,
OBJECT_VERSION_NUMBER ,
START_TIME ,
APPROVAL_STATUS, RESOURCE_ID
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DAY'
) DAY ,
(SELECT TIME_BUILDING_BLOCK_ID,
START_TIME ,
APPROVAL_STATUS ,
OBJECT_VERSION_NUMBER ,
COMMENT_TEXT, RESOURCE_ID
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'TIMECARD'
) TC
,HXC_TIME_ATTRIBUTE_USAGES HTAU
,HXC_TIME_ATTRIBUTES HTA
,PA_PROJECTS_ALL PP
,PA_TASKS PT
WHERE DAY.PARENT_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID
AND DAY.PARENT_BUILDING_BLOCK_OVN = TC.OBJECT_VERSION_NUMBER
AND DET.PARENT_BUILDING_BLOCK_ID = DAY.TIME_BUILDING_BLOCK_ID
AND DET.PARENT_BUILDING_BLOCK_OVN = DAY.OBJECT_VERSION_NUMBER
AND TRUNC(DAY.START_TIME) BETWEEN &START_TIME AND &END_TIME
AND DAY.RESOURCE_ID = &resource_id
AND TC.OBJECT_VERSION_NUMBER =
(SELECT MAX(OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'TIMECARD'
AND TIME_BUILDING_BLOCK_ID = TC.TIME_BUILDING_BLOCK_ID
)
AND DET.OBJECT_VERSION_NUMBER =
(SELECT MAX(OBJECT_VERSION_NUMBER)
FROM HXC_TIME_BUILDING_BLOCKS
WHERE SCOPE = 'DETAIL'
AND TIME_BUILDING_BLOCK_ID = DET.TIME_BUILDING_BLOCK_ID
)
AND DET.TIME_BUILDING_BLOCK_ID = HTAU.TIME_BUILDING_BLOCK_ID
AND DET.OBJECT_VERSION_NUMBER = HTAU.TIME_BUILDING_BLOCK_OVN
AND HTAU.TIME_ATTRIBUTE_ID = HTA.TIME_ATTRIBUTE_ID
AND HTA.ATTRIBUTE1 = TO_CHAR(PP.PROJECT_ID)
AND HTA.ATTRIBUTE2 = TO_CHAR(PT.TASK_ID)
AND HTA.ATTRIBUTE5 = 'ST'
ORDER BY 4 ;


Print This Post