This Blog is moved to http://www.oracleappselearn.blogspot.com
Sorry for the inconvenience

PO Line Cancel

Po Line which the organizations no longer wanted to receive an Invoice from the Supplier should be cancelled to avoid any open Commitments to the Project PO Line is associated with, and In cancelling the PO line it could raise an error as below,

"Line # Shipment # Quantity Billed # greated than Quantity received #".

The problem could be that the PO Line is Matched ina 3-Way Functionality, and inroder to cancel the line that has been billed and not received, the line should have match approval level set to "2-Way" so that it does not expect any receipt by setting the receipt_required_flag='N'.

If Match Approval Level is 3-way or 4-way, application checks whether any of the shipments on PO or Release is having billed quantity greater than received or delivered quantity. If so, user is restricted from canceling the PO or the Release.

Process to Cancel the PO Line,

1. Query the PO in PO Summary Form.
2. Query the Line to Cancel
3. Go to Tools --> Control --> Cancel the PO Line.

Print This Post

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

Changing an Internal Name of a Workflow Item Type

For changing a Workflow Internal name of an Item Type, use wfchitt.sql located at $FND_TOP/sql and the syntax is as below,

sqlplus usr/pwd @wfchitt.sql old_item_type new_item_type

Please refer to Oracle Administrator guide for further information on this.

Print This Post

Concurrent Program Responsibility

The below query provides the Responsibility and REquest group associated with the provided concurrent program,

SELECT frg.request_group_name, frpt.responsibility_name
FROM FND_CONCURRENT_PROGRAMS_TL fcpl
,FND_REQUEST_GROUP_UNITS frgu
,FND_REQUEST_GROUPS frg
,FND_RESPONSIBILITY frp
,FND_RESPONSIBILITY_TL frpt
WHERE fcpl.user_concurrent_program_name like 'Positions - Outstanding Receivables / Payables Report'
AND fcpl.language = USERENV('LANG')
AND fcpl.concurrent_program_id = frgu.request_unit_id
AND frgu.request_group_id = frg.request_group_id
AND frp.request_group_id = frg.request_group_id
AND frp.responsibility_id = frpt.responsibility_id
AND frpt.language = USERENV('LANG')
AND NVL(frp.end_date, SYSDATE) >= SYSDATE;

Print This Post

Invoice By Bill Transaction Currency Checkbox update with 'FRM-40200: Field is protected against update'

We will be able to modify the 'Invoice By Bill Transaction Currency' Checkbox unless there is baselined funding on the Project and following conditions represent whether this flag is updateable, with the error 'FRM-40200: Field is protected against update',

Non-zero baselined funding amount exists for the project

SELECT sum(nvl(allocated_amount,0))

FROM PA_PROJECT_FUNDINGS

WHERE PROJECT_ID = &p_project_id

AND budget_type_code = 'BASELINE';

Draft funding lines exist for the project

SELECT 'Y'

FROM PA_PROJECT_FUNDINGS

WHERE PROJECT_ID = &p_project_id

AND budget_type_code = 'DRAFT';

PA events exist on the project

SELECT 'Y'

FROM dual

WHERE exists( SELECT project_id

FROM pa_events

WHERE project_id = &p_project_id);

Any expenditure items exist on the project

SELECT 'Y'

FROM dual

WHERE exists( SELECT T.project_id

FROM pa_expenditure_items_all E

, pa_tasks T

WHERE T.project_id = &p_project_id

AND E.task_id = T.task_id);

The project type is flagged for use in InterCompany Billing ( cc_prvdr_flag = 'Y') . This columns is found in pa_project_types_all

SELECT pt.cc_prvdr_flag

FROM pa_projects_all p

, pa_project_types_all

WHERE p.project_id = &p_project_id

AND pt.project_type = p.project_type

AND nvl(pt.org_id, -999) = nvl(p.org_id, -999);

And sometimes there could be a draft invoice being generated in the other than the transaction currency and we want to generate the draft invoice in the transaction currency, then we need to undo the Draft invoice update the 'Invoice By Bill Transaction Currency' Checkbox with the following query,

UPDATE pa_projects_all

SET INV_BY_BILL_TRANS_CURR_FLAG = 'N'

WHERE project_id = &p_project_id;

Print This Post