Time Card Creation
Patcard Creation steps for i2 technologies client
As i2 do not have OTL implemented so all the time cards created will be saved in pa tables only.
Go to responsibility @webtimecards--- Create New Timecard
Select project and other details and enter the time details for the week and submit the time cards , you will get the Time card number once you submitted.
The above information is saves in Pa_expenditures_all table and the lines details are saved in the internediate interface tables once you go to the
PA Super User ---Expenditures---Transaction Import ---Import Transactions
here select 'PRC: Transaction Import' select Transaction source as "Oracle Self Service Time" and Btach Name we need to take from pa_expenditures_all table USER_BATCH_NAME and submit the program.
Once Submitted the records will be inserted into select * from pa_expenditure_items_all table
Wednesday, May 27, 2009
Tuesday, May 26, 2009
3 C's.(Set of books)
Set of books determines the functional Currency, Chart of account structure, and
accounting Calendar for each company or group of companies, which are known as the 3 C's.
accounting Calendar for each company or group of companies, which are known as the 3 C's.
Tuesday, May 12, 2009
Select data with a hierarchical (parent/child) relationship.
Select data with a hierarchical (parent/child) relationship.
Syntax:
SELECT...
[START WITH initial_condition]
CONNECT BY [nocycle] PRIOR recurse_condition
[ORDER SIBLINGS BY order_by_clause]
Key:
START WITH : The row(s) to be used as the root of the hierarchy
CONNECT BY : Condition that identifies the relationship between
parent and child rows of the hierarchy
NOCYCLE : Do not circle around loops (where the current row has
a child which is also its ancestor.)
ORDER SIBLINGS BY : Preserve ordering of the hierarchical query
then apply the order_by_clause to the sibling rows
Example:
create table test_connect_by (
Level1 varchar2(100),
Item varchar2(100)
);
Sample Data
LEVEL1 ITEM
0 123
1 345
2 678
1 987
0 666
Inserted above values in the table
insert into test_connect_by values(0,666);
select * from test_connect_by
LEVEL1 ITEM
0 123
1 345
2 678
1 987
0 666
select * from test_connect_by
connect by prior item=level1
LEVEL1 ITEM
0 123
0 666
1 345
1 987
2 678
=====================================================
Syntax:
SELECT...
[START WITH initial_condition]
CONNECT BY [nocycle] PRIOR recurse_condition
[ORDER SIBLINGS BY order_by_clause]
Key:
START WITH : The row(s) to be used as the root of the hierarchy
CONNECT BY : Condition that identifies the relationship between
parent and child rows of the hierarchy
NOCYCLE : Do not circle around loops (where the current row has
a child which is also its ancestor.)
ORDER SIBLINGS BY : Preserve ordering of the hierarchical query
then apply the order_by_clause to the sibling rows
Example:
create table test_connect_by (
Level1 varchar2(100),
Item varchar2(100)
);
Sample Data
LEVEL1 ITEM
0 123
1 345
2 678
1 987
0 666
Inserted above values in the table
insert into test_connect_by values(0,666);
select * from test_connect_by
LEVEL1 ITEM
0 123
1 345
2 678
1 987
0 666
select * from test_connect_by
connect by prior item=level1
LEVEL1 ITEM
0 123
0 666
1 345
1 987
2 678
=====================================================
Script to check Responsibilities assigned to particular user or users assigned for particular resposibility or all users and their responsibilities
Script to check Responsibilities assigned to particular user or users assigned for particular resposibility or all users and their responsibilities in oracle
SELECT fu.user_id, fu.user_name, fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu, fnd_user_resp_groups fur, fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
and user_name like 'SAIRAM_GOUD' --- for all user or for perticular user
-- AND fur.responsibility_application_id = 275 -- to check users for perticular responsibility
order by user_name
SELECT fu.user_id, fu.user_name, fur.responsibility_id,
fr.responsibility_name
FROM fnd_user fu, fnd_user_resp_groups fur, fnd_responsibility_vl fr
WHERE fu.user_id = fur.user_id
AND fr.application_id = fur.responsibility_application_id
AND fr.responsibility_id = fur.responsibility_id
AND TRUNC (SYSDATE) BETWEEN TRUNC (fr.start_date)
AND TRUNC (NVL ((fr.end_date - 1), SYSDATE))
AND TRUNC (SYSDATE) BETWEEN TRUNC (fur.start_date)
AND TRUNC (NVL ((fur.end_date - 1), SYSDATE))
and user_name like 'SAIRAM_GOUD' --- for all user or for perticular user
-- AND fur.responsibility_application_id = 275 -- to check users for perticular responsibility
order by user_name
Script To find Oracle API's for any module
Following script and get all the packages related to API in Oracle applications, from which you can select APIs that pertain to AP. You can change the name like to PA or AR and can check for different modules
select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'PA_%API%'
order by
a.owner, a.name;
select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like '%Header%'
and a.type = u.object_type
and a.name like 'PA_%API%'
order by
a.owner, a.name;
Thursday, May 7, 2009
Project Expense Report Account Generator workflow process
If using a custom Project Expense Report Account Generator workflow process:
To implement the solution, please execute the following steps:
1. Set the profile option 'Account Generator:Run in Debug Mode' = 'Yes'. This will persist the
workflow data so that the custom Account Generator workflow can be debugged.
2. Bounce the Apache server.
3. Reproduce the error. The custom Project Expense Report Account Generator workflow process can now be viewed through the workflow monitor.
4. Resolve the issue with the custom Project Expense Report Account Generator
5. After the issue has been resolved, the employees whose expense reports have failed with this
error should withdraw and resubmit their expense reports.
To implement the solution, please execute the following steps:
1. Set the profile option 'Account Generator:Run in Debug Mode' = 'Yes'. This will persist the
workflow data so that the custom Account Generator workflow can be debugged.
2. Bounce the Apache server.
3. Reproduce the error. The custom Project Expense Report Account Generator workflow process can now be viewed through the workflow monitor.
4. Resolve the issue with the custom Project Expense Report Account Generator
5. After the issue has been resolved, the employees whose expense reports have failed with this
error should withdraw and resubmit their expense reports.
Expense report workflow errors ( WFENG_NOTIFICATION_PERFORMER )
Expense report workflow errors ( WFENG_NOTIFICATION_PERFORMER )
Failed Activity Inform Individual of Expense Allocations Failure
Activity Type Notice
Error Name WFENG_NOTIFICATION_PERFORMER
Error Message 3120: Activity 'APEXP/301116' has no performer.
Error Stack Wf_Engine_Util.Notification_Send(APEXP, 550307, 301116, APEXP:OIE_AP_EXP_ALLOCATIONS_FAILURE) Wf_Engine_Util.Notification(APEXP, 550307, 301116, RUN)
For the above error we need to cross check the setup for that employee who has filed the expense report
select * from gl_sets_of_books
where CHART_OF_ACCOUNTS_ID=50184
select * from per_employees_x
where EMPLOYEE_ID=72546
select * from GL_CODE_COMBINATIONS_KFV
code_combination_id=298127
and from the above cross check concatnated segments all are defined correctly or not
and you can go to HR super user and find the employee and click on Assignment buttion and from their select purchase order information where you can find the segmnents change accordigly and now file new expense report which will be in success.
Failed Activity Inform Individual of Expense Allocations Failure
Activity Type Notice
Error Name WFENG_NOTIFICATION_PERFORMER
Error Message 3120: Activity 'APEXP/301116' has no performer.
Error Stack Wf_Engine_Util.Notification_Send(APEXP, 550307, 301116, APEXP:OIE_AP_EXP_ALLOCATIONS_FAILURE) Wf_Engine_Util.Notification(APEXP, 550307, 301116, RUN)
For the above error we need to cross check the setup for that employee who has filed the expense report
select * from gl_sets_of_books
where CHART_OF_ACCOUNTS_ID=50184
select * from per_employees_x
where EMPLOYEE_ID=72546
select * from GL_CODE_COMBINATIONS_KFV
code_combination_id=298127
and from the above cross check concatnated segments all are defined correctly or not
and you can go to HR super user and find the employee and click on Assignment buttion and from their select purchase order information where you can find the segmnents change accordigly and now file new expense report which will be in success.
Wednesday, May 6, 2009
Oracle Workflow tables:
Oracle Workflow tables:
WF_ACTIVITIES table stores the definition of an activity. Activities
can be processes, notifications, functions or folders. A process activity
is a modelled workflow process, which can be included as an activity in
other processes to represent a subprocess. A notification activity
sends a message to a performer. A functions activity performs an
automated function that is written as a PL/SQL stored procedure.
A folder activity is not part of a process, it provides a means of grouping activities.
WF_ITEMS is the runtime table for workflow processes. Each row
defines one work item within the system.
WF_ITEM_ATTRIBUTES table stores definitions of attributes
associated with a process. Each row includes the sequence in which
the attribute is used as well as the format of the attribute data.
WF_NOTIFICATIONS holds the runtime information about a specific
instance of a sent message. A new row is created in the table each time
a message is sent.
WF_ACTIVITIES table stores the definition of an activity. Activities
can be processes, notifications, functions or folders. A process activity
is a modelled workflow process, which can be included as an activity in
other processes to represent a subprocess. A notification activity
sends a message to a performer. A functions activity performs an
automated function that is written as a PL/SQL stored procedure.
A folder activity is not part of a process, it provides a means of grouping activities.
WF_ITEMS is the runtime table for workflow processes. Each row
defines one work item within the system.
WF_ITEM_ATTRIBUTES table stores definitions of attributes
associated with a process. Each row includes the sequence in which
the attribute is used as well as the format of the attribute data.
WF_NOTIFICATIONS holds the runtime information about a specific
instance of a sent message. A new row is created in the table each time
a message is sent.
Tuesday, May 5, 2009
AP Standard Expense Report Process (Expense report workflow error)
AP Standard Expense Report Process
Request Approval from APPROVER Error
Failed Activity Request Approval from APPROVER
Activity Type Notice
Error Name WFENG_NOTIFICATION_PERFORMER
Error Message 3120: Activity 'APEXP/301347' has no performer.
Error Stack Wf_Engine_Util.Notification_Send(APEXP, 549406, 301347, APEXP:OIE_REQ_EXPENSE_REPORT_APPRVL) Wf_Engine_Util.Notification(APEXP, 549406, 301347, RUN)
Resolution
For the errors with 'no performer' for 'Request Approval from APPROVER' Activity we should not restart the process, first we need to update the attribute values and then we can restart the process.
Once we open the workflow notification click on 'Activities History' and below we will have "Update Attributes" Button click on that and we will have fields with
Approver ID
Approver Name
Approver Display Name
Update above fields with proper values and Click on Apply Button , it will prompt with message
"Confirmation The workflow-level attribute values have been updated."
Now go to "Monitor Activities History" window and cilck the " Request Approval from APPROVER" Button and Re-start the workflow now the workfow will route to correct person and will be in Active state.
Request Approval from APPROVER Error
Failed Activity Request Approval from APPROVER
Activity Type Notice
Error Name WFENG_NOTIFICATION_PERFORMER
Error Message 3120: Activity 'APEXP/301347' has no performer.
Error Stack Wf_Engine_Util.Notification_Send(APEXP, 549406, 301347, APEXP:OIE_REQ_EXPENSE_REPORT_APPRVL) Wf_Engine_Util.Notification(APEXP, 549406, 301347, RUN)
Resolution
For the errors with 'no performer' for 'Request Approval from APPROVER' Activity we should not restart the process, first we need to update the attribute values and then we can restart the process.
Once we open the workflow notification click on 'Activities History' and below we will have "Update Attributes" Button click on that and we will have fields with
Approver ID
Approver Name
Approver Display Name
Update above fields with proper values and Click on Apply Button , it will prompt with message
"Confirmation The workflow-level attribute values have been updated."
Now go to "Monitor Activities History" window and cilck the " Request Approval from APPROVER" Button and Re-start the workflow now the workfow will route to correct person and will be in Active state.
Monday, April 27, 2009
setting the Password for PDF File sent through XML Publisher
setting the Password for PDF File sent through XML Publisher
Open the rtf for which you want to set password and do the following things
1) Open the .rtf
2) Go to File - > Properties
Create a new custom property
a) Name : xdo-pdf-open-password
Type : text
Value : Either Hard Code the value / or get the value for xml data
b) Name : xdo-pdf-security
Type : text
Value : true
Note : property name should always start with xdo- .
Open the rtf for which you want to set password and do the following things
1) Open the .rtf
2) Go to File - > Properties
Create a new custom property
a) Name : xdo-pdf-open-password
Type : text
Value : Either Hard Code the value / or get the value for xml data
b) Name : xdo-pdf-security
Type : text
Value : true
Note : property name should always start with xdo- .
Generating Excel Outputs from existing standard Oracle Reports 6i Output
Generating Excel Outputs from existing standard Oracle Reports 6i Output
Most end-users use Excel as a very generic tool, because of their familiarity and the control in analysing data using the power of MS Excel.
There is a trick to capture Oracle Reports output (text output) into a excel sheet.
Basic Steps :
1) Set the output format for the report to XML
2) Run the report to generate output.
3) Save the output file locally as a XML file.
4) Open the file using MS Excel.
5) To make it more beautiful, you may use a MS Excel Template.
The Excel Templates can be used to do complex data analysis and formatting. Displayed example below is a simple excel template, to make the point.
Most end-users use Excel as a very generic tool, because of their familiarity and the control in analysing data using the power of MS Excel.
There is a trick to capture Oracle Reports output (text output) into a excel sheet.
Basic Steps :
1) Set the output format for the report to XML
2) Run the report to generate output.
3) Save the output file locally as a XML file.
4) Open the file using MS Excel.
5) To make it more beautiful, you may use a MS Excel Template.
The Excel Templates can be used to do complex data analysis and formatting. Displayed example below is a simple excel template, to make the point.
Points be to Noted while doing AR Conversion
Conversion (Data Migration) of Invoices in Receivables
Whenever we are going in for implementation of Receivables module, we have to consider the necessity of bringing in customer open balances from the old system to Oracle Receivables.
Some of the key questions that needs to be addressed before we take up a conversion activity. This is just a sample list and not an exhaustive one:
1. What are the different types of invoices in existing system Provide invoice samples? (invoices, credit/ debit memos, commitments, chargebacks)
2. Do we need to migrate only open invoices?
3. Do we migrate closed invoices also, if yes, then for what time period?
4. Please explain the invoice numbering mechanism? Is it automatic?
5. What are the interfaces from/to your existing receivables system?
6. Will the old system still be in place for querying and reporting purpose?
One can adopt one of the following three strategies for conversion:
1. Consolidate all the open balances customer-wise and create a single open invoice for each customer in the new Oracle system. The advantage of this system is that it is quite easy and not data intensive and makes good business sense in case of small businesses with very few customers. The major demerit of this approach is that later on one cannot track the individual invoices which the customer had sent and can become an audit issue also. In case of dispute over payment, this invoice will remain open till the dispute is resolved. Also, aging of invoices and dunning history will be lost.
2. Bring in all the open and partially paid invoices, credit/debit memos into the new system. Migrate all the unapplied and partially applied receipts to the new system. The advantage of this process of conversion is that you can track all open invoices individually and apply the correct receipt to correct invoice. Also, the conversion effort will be moderately low compared to case if you migrate all open and closed invoices. The disadvantage of this approach is that you cannot have a track of closed invoices in the new system. Also, it would be tough to handle scenarios where there is a dispute regarding incorrect receipt application, etc. This is the most common approach taken for receivables invoice, credit/debit memo and receipt migration.
3. Migrate all open and closed invoices to the new system. Reapply the migrated receipts to invoices in the new system. This approach makes sense if your receivables data is quite small else the effort involved in migrating all closed invoices and credit memos to the new system does not make much business sense.
The next question that arises is how we should migrate the invoices, credit/debit memos and receipts to the new system. Oracle provides standard interfaces to load the same. We can also use tools like Dataloader or manually key in the data into Oracle.
In this article i will talk of invoice, credit/debit memo conversion only. Prior to invoice migration, customer migration should be over apart from other pre-requisites. Following is the list of pre-requisites that should be completed prior to invoice, credit/debit memo conversion:
•Set-up of Customer Payment Terms should be complete
•Set-up of Currencies should be complete (this is necessary in case you have foreign currency invoices also)
•Set-up of Transaction Types should be complete
•Set-up of Accounting Rules should be complete
•Set-up of Tax rates and Tax codes should be complete
•Set up for sales representative should be complete
•Set up for debtor area should be complete
•Set up for income category should be complete
•Automatic customer invoice numbering should be set to 'No'
•Customer and Customer address should be migrated in the system
•Disable the Invoice interface purge program so that the data successfully imported should not get purged in the interface table.
•Set up for invoice batch source name should be complete
In the next step extract Invoice data from the legacy files and using SQL loader populate the interface tables RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL. Submit the Auto Invoice open interface program. Data from the two interface tables will be uploaded to the following base tables using the Invoice open interface program:
•RA_CUSTOMER_TRX_ALL
•RA_CUSTOMER_TRX_LINES_ALL
•RA_CUST_TRX_LINE_GL_DIST_ALL
•RA_BATCHES_ALL
•RA_CUST_TRX_LINE_SALESREPS_ALL
•AR_RECEIVABLE_APPLICATIONS_ALL
•AR_PAYMENT_SCHEDULES_ALL
•RA_INTERFACE_ERRORS_ALL
Ensure that the Purge Interface check box is not checked when you submit the Autoinvoice program. In the Autoinvoice errors form you can see the error corresponding to failed records. Correct the errors in the interface table and rerun the Autoinvoice program. Submit the Autoinvoice Purge Program separately. Only records that have been successfully processed by Autoinvoice are purged.
Using autoinvoice you can migrate invoices, credit/debit memos and on-account credits into Oracle. However, you have to set grouping rules (Navigation > Setup > Transactions > Autoinvoice > Grouping Rule) to group lines to create one transaction and ordering rules (Navigation > Setup > Transactions > Autoinvoice > Line Ordering Rules) to determine the order of the transaction lines on a particular invoice.
Whenever we are going in for implementation of Receivables module, we have to consider the necessity of bringing in customer open balances from the old system to Oracle Receivables.
Some of the key questions that needs to be addressed before we take up a conversion activity. This is just a sample list and not an exhaustive one:
1. What are the different types of invoices in existing system Provide invoice samples? (invoices, credit/ debit memos, commitments, chargebacks)
2. Do we need to migrate only open invoices?
3. Do we migrate closed invoices also, if yes, then for what time period?
4. Please explain the invoice numbering mechanism? Is it automatic?
5. What are the interfaces from/to your existing receivables system?
6. Will the old system still be in place for querying and reporting purpose?
One can adopt one of the following three strategies for conversion:
1. Consolidate all the open balances customer-wise and create a single open invoice for each customer in the new Oracle system. The advantage of this system is that it is quite easy and not data intensive and makes good business sense in case of small businesses with very few customers. The major demerit of this approach is that later on one cannot track the individual invoices which the customer had sent and can become an audit issue also. In case of dispute over payment, this invoice will remain open till the dispute is resolved. Also, aging of invoices and dunning history will be lost.
2. Bring in all the open and partially paid invoices, credit/debit memos into the new system. Migrate all the unapplied and partially applied receipts to the new system. The advantage of this process of conversion is that you can track all open invoices individually and apply the correct receipt to correct invoice. Also, the conversion effort will be moderately low compared to case if you migrate all open and closed invoices. The disadvantage of this approach is that you cannot have a track of closed invoices in the new system. Also, it would be tough to handle scenarios where there is a dispute regarding incorrect receipt application, etc. This is the most common approach taken for receivables invoice, credit/debit memo and receipt migration.
3. Migrate all open and closed invoices to the new system. Reapply the migrated receipts to invoices in the new system. This approach makes sense if your receivables data is quite small else the effort involved in migrating all closed invoices and credit memos to the new system does not make much business sense.
The next question that arises is how we should migrate the invoices, credit/debit memos and receipts to the new system. Oracle provides standard interfaces to load the same. We can also use tools like Dataloader or manually key in the data into Oracle.
In this article i will talk of invoice, credit/debit memo conversion only. Prior to invoice migration, customer migration should be over apart from other pre-requisites. Following is the list of pre-requisites that should be completed prior to invoice, credit/debit memo conversion:
•Set-up of Customer Payment Terms should be complete
•Set-up of Currencies should be complete (this is necessary in case you have foreign currency invoices also)
•Set-up of Transaction Types should be complete
•Set-up of Accounting Rules should be complete
•Set-up of Tax rates and Tax codes should be complete
•Set up for sales representative should be complete
•Set up for debtor area should be complete
•Set up for income category should be complete
•Automatic customer invoice numbering should be set to 'No'
•Customer and Customer address should be migrated in the system
•Disable the Invoice interface purge program so that the data successfully imported should not get purged in the interface table.
•Set up for invoice batch source name should be complete
In the next step extract Invoice data from the legacy files and using SQL loader populate the interface tables RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL. Submit the Auto Invoice open interface program. Data from the two interface tables will be uploaded to the following base tables using the Invoice open interface program:
•RA_CUSTOMER_TRX_ALL
•RA_CUSTOMER_TRX_LINES_ALL
•RA_CUST_TRX_LINE_GL_DIST_ALL
•RA_BATCHES_ALL
•RA_CUST_TRX_LINE_SALESREPS_ALL
•AR_RECEIVABLE_APPLICATIONS_ALL
•AR_PAYMENT_SCHEDULES_ALL
•RA_INTERFACE_ERRORS_ALL
Ensure that the Purge Interface check box is not checked when you submit the Autoinvoice program. In the Autoinvoice errors form you can see the error corresponding to failed records. Correct the errors in the interface table and rerun the Autoinvoice program. Submit the Autoinvoice Purge Program separately. Only records that have been successfully processed by Autoinvoice are purged.
Using autoinvoice you can migrate invoices, credit/debit memos and on-account credits into Oracle. However, you have to set grouping rules (Navigation > Setup > Transactions > Autoinvoice > Grouping Rule) to group lines to create one transaction and ordering rules (Navigation > Setup > Transactions > Autoinvoice > Line Ordering Rules) to determine the order of the transaction lines on a particular invoice.
Troubleshooting the Projects to Receivables Interface
Troubleshooting the Projects to Receivables Interface
This feature is for anyone using Oracle Projects and Receivables 11.5.10 and beyond. There were several new features introduced to Projects 10.5.10 (Family Pack M), which now require additional configurations for the Receivables interface to work.Here are few quick tipts consolidated from the Metalink notes and user guides to remember what to do
Open Periods
Make sure to open respective accounting periods in Receivables. As Receivables Manager, navigate to Control > Accounting > Open/Close Periods.
Retention Error
You may not be using retention or multi-currency in Project Billing, but you still receive the “Transaction Code: TRA OU Level Retention Accounting flag N… Some transactions are disabled. Please Check auto-accounting setup” error when running the PRC: Interface Invoices to Receivables program (PATTAR).
To resolve the error you need to enable the AutoAccounting Assignments for Unbilled Retention Account and Rounding Account.
As Projects Billing Super User:
Navigate to Setup > AutoAccounting > Assign
Query up function ‘Revenue and Invoice Accounts’
Define Segment Rule Pairings for the Unbilled Retention Account
Define Segment Rule Pairings for the Rounding Account
Make sure to enable the Function Transactions!
Again, you need to do this even if you do not intend to enable retention and multi-currency billing in your Implementation Options.
Sales Credit Type Rejection
You might get a ‘No sales credit type at Implementation or Projec Level’ rejection when running the PRC: Interface Invoices to Receivables program (PATTAR).
In order to resolve this error as Projects Billing Super User:
Navigate to Implementation Options
Select ‘Exchange Rate Type’ in the Currency tab
Select ‘Sales Credit Type’ in the Billing tab
Transaction Source and Transaction Type Errors
The seeded Project Transaction Source and Project Transaction Types might be incomplete. Make sure to review and update these for your setting you defined in the Implementation Options > Billing tab. The source It is going to be ‘PROJECTS INVOICES’ (if new 11i or R12 implementation, or ‘PA INVOICES’ if upgraded from 10.7) and Transaction Types are going to be ‘Projects Invoice’ and ‘Projects Credit Memo’ (11i and beyond) or ‘PA Invoice’ and ‘PA Credit Memo’ (10.7) respectively.
As Receivables Manager:
Navigate to Transaction Sources: Setup > Transactions > Sources
Query up your Transactions Source you defined in the Implementation Options > Billing tab
Make sure the Reference Field Default Value field = ‘interface_header_attribute1′
Make sure the Standard Transaction Type = ‘Projects Invoice’
Make sure the Credit Meno Batch Source = ‘PROJECTS INVOICES’
Also navigate to Transaction Types: Setup > Transactions > Types
Query up ‘Projects Invoice’ transaction type
Make sure the Credit memo Type = ‘Projects Credit Memo’
This feature is for anyone using Oracle Projects and Receivables 11.5.10 and beyond. There were several new features introduced to Projects 10.5.10 (Family Pack M), which now require additional configurations for the Receivables interface to work.Here are few quick tipts consolidated from the Metalink notes and user guides to remember what to do
Open Periods
Make sure to open respective accounting periods in Receivables. As Receivables Manager, navigate to Control > Accounting > Open/Close Periods.
Retention Error
You may not be using retention or multi-currency in Project Billing, but you still receive the “Transaction Code: TRA OU Level Retention Accounting flag N… Some transactions are disabled. Please Check auto-accounting setup” error when running the PRC: Interface Invoices to Receivables program (PATTAR).
To resolve the error you need to enable the AutoAccounting Assignments for Unbilled Retention Account and Rounding Account.
As Projects Billing Super User:
Navigate to Setup > AutoAccounting > Assign
Query up function ‘Revenue and Invoice Accounts’
Define Segment Rule Pairings for the Unbilled Retention Account
Define Segment Rule Pairings for the Rounding Account
Make sure to enable the Function Transactions!
Again, you need to do this even if you do not intend to enable retention and multi-currency billing in your Implementation Options.
Sales Credit Type Rejection
You might get a ‘No sales credit type at Implementation or Projec Level’ rejection when running the PRC: Interface Invoices to Receivables program (PATTAR).
In order to resolve this error as Projects Billing Super User:
Navigate to Implementation Options
Select ‘Exchange Rate Type’ in the Currency tab
Select ‘Sales Credit Type’ in the Billing tab
Transaction Source and Transaction Type Errors
The seeded Project Transaction Source and Project Transaction Types might be incomplete. Make sure to review and update these for your setting you defined in the Implementation Options > Billing tab. The source It is going to be ‘PROJECTS INVOICES’ (if new 11i or R12 implementation, or ‘PA INVOICES’ if upgraded from 10.7) and Transaction Types are going to be ‘Projects Invoice’ and ‘Projects Credit Memo’ (11i and beyond) or ‘PA Invoice’ and ‘PA Credit Memo’ (10.7) respectively.
As Receivables Manager:
Navigate to Transaction Sources: Setup > Transactions > Sources
Query up your Transactions Source you defined in the Implementation Options > Billing tab
Make sure the Reference Field Default Value field = ‘interface_header_attribute1′
Make sure the Standard Transaction Type = ‘Projects Invoice’
Make sure the Credit Meno Batch Source = ‘PROJECTS INVOICES’
Also navigate to Transaction Types: Setup > Transactions > Types
Query up ‘Projects Invoice’ transaction type
Make sure the Credit memo Type = ‘Projects Credit Memo’
How to track Current Apps Versions
1)select product_version,patch_level from fnd_product_installations
Get current version ana Patch level information.
2)select * FROM V$VERSION
Database Version infomation.
3)select * from v$instance
Instance details
4)select WF_EVENT_XML.XMLVersion() XML_VERSION from sys.dual;
Current XML Parser Version info.
5)select TEXT from WF_RESOURCES where TYPE = 'WFTKN' and NAME = 'WF_VERSION'
Workflow version Number.
6)select home_url from icx_parameters
Oracle applications front end URL
7)SELECT VALUE FROM V$PARAMETER WHERE NAME=’USER_DUMP_DEST’
Get the Trace file location.
8) XML Publisher Vesion info.
$OA_JAVA/oracle/apps/xdo/common/MetaInfo.class.
Get current version ana Patch level information.
2)select * FROM V$VERSION
Database Version infomation.
3)select * from v$instance
Instance details
4)select WF_EVENT_XML.XMLVersion() XML_VERSION from sys.dual;
Current XML Parser Version info.
5)select TEXT from WF_RESOURCES where TYPE = 'WFTKN' and NAME = 'WF_VERSION'
Workflow version Number.
6)select home_url from icx_parameters
Oracle applications front end URL
7)SELECT VALUE FROM V$PARAMETER WHERE NAME=’USER_DUMP_DEST’
Get the Trace file location.
8) XML Publisher Vesion info.
$OA_JAVA/oracle/apps/xdo/common/MetaInfo.class.
Query to Display Module Wise Reports
Display Module Wise Reports
SELECT fa.application_short_name, fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type, program_type, printer_name, minimum_width,
minimum_length, concurrent_program_name, concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY 1
SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1
SELECT fa.application_short_name, fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type, program_type, printer_name, minimum_width,
minimum_length, concurrent_program_name, concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY 1
SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1
Sunday, April 26, 2009
File Types and Extensions in APPS
File Types and Extensions in APPS
.a Library files for C language code
.c C lang source code
.ctl DataMerge control file (Sql loader)
.dat DataMerge import/export (Data file)
.Drv Driver (patch related files)
.env Unix environment
.exp DataMerge export
.fmb Binary forms
.fmx Execuatble forms
.h C lang header file
.jar java archive
.lc C lang source to be archived
.lct Data loader control
.ldt Data loader datafile
.log Concurrent request log
.lpc PRO*C source to be archived
.msb Binary message
.msg Readable message
.o C lang object module
.odf Object description
.out Concurrent request output
.plb PL/SQL package body
.pll PL/SQL shared library (reports)
.pls PL/SQL package specs
.rdf Oracle D2k Reports
.req Oracle reports Executable
.sql SQL*Plus scripts
.a Library files for C language code
.c C lang source code
.ctl DataMerge control file (Sql loader)
.dat DataMerge import/export (Data file)
.Drv Driver (patch related files)
.env Unix environment
.exp DataMerge export
.fmb Binary forms
.fmx Execuatble forms
.h C lang header file
.jar java archive
.lc C lang source to be archived
.lct Data loader control
.ldt Data loader datafile
.log Concurrent request log
.lpc PRO*C source to be archived
.msb Binary message
.msg Readable message
.o C lang object module
.odf Object description
.out Concurrent request output
.plb PL/SQL package body
.pll PL/SQL shared library (reports)
.pls PL/SQL package specs
.rdf Oracle D2k Reports
.req Oracle reports Executable
.sql SQL*Plus scripts
Overview of Oracle HRMS
Overview of Oracle HRMS
Oracle HRMS enables you to enter and track people recors when they apply for a job or when they leave organization.HRMS allows you to enter , maintain,report of employess information.
Business Process in HRMS
The following modules are intergrated in Oracle HRMS
1.Hiring & Deployment
2.Recruitment
3.Career Management
4.Training & Development
5.Compensation & Benefits
6.Time & Labour
7.Payroll Processiong
8.Organization & Resource Planning
Work Structure
Work structure are independent of the people who work in enterprise. and it suppots Enterprise and employee structure such as Business groups and legal entities and also contains grade structure, pyroll groups,jobs and positions.
Location
Locations represents physical site where employees work.The location types could be
Global Availble across all BG
Business Group only availble in BG
Jobs and Positions
Jobs and positions to represents the different roles that a person can perform in enterprise.
Job is generic role within BG
They are independent of ant single organization.
Exists for all Organizations.
Positions
Positions is specific role or function exist in only one organization.Position includes Jobs and Organization.
Positions are used to show spcific posts within org.
Using position contril functionality can manage positions.
Grades
Grades indicates seniority level in an enterprise
Grades and Pay
HRMS supports direct and indirect relationship between grades and Pay
Person Types in HRMS
HRMS store information about different group of peoples such as employees,applicants and people external to your enterprise.Using person Types you can restrict access to records of certain group of people
HRMS uses a unique identifier called as Employee Number to identify every employee in BG.
Business Group
Business group defines a complete set of HR data and linked with one legislative processing like payrool and benefits . But oracle HRMS come with predefined Business groups which is useful who do not intedent to use HR in Apps.
Personal Information
Every enterprise must be able to record personal information for its employee,applicants and contacts,So HRMS allow you update and report employee information.
Addresses
Employee must have only one primary addres on record.But you can enter as many addresses ad mecessary for each person.Each Country has its own national address style.
Tracking
By using web-based Manager Self service to enter some information like employee history,roles and employee data etc...
Employee Assignment
The assignment represents the way employee work in enterprise.The assignment is the central concept that related to the structure in which employes works.
Each assignment has employess assignment number that uniqely identifies assignment. When employees change their locations or job these changes are dtaetracked.
Assignment stastuses
1.Active
2.Suspended
3.Terminated
4.Ended
Employee Development
You can Record work choice,relocation,preferred work hours and work schedule.
Person Types
HRMS supports with predefined set of system person types ease person typa has its own user name. These are 8 types.
Retiree
Former Spouse
Surviving Family Member
Surviving Spouse
Former Family Member
Beneficiary
Dependent
Partcipant
Terminate Employee
When employee leaves organization terminate the person record action changes the person type to Ex-Employee and automatically ends all assignments for the person.You cannot terminates an employee who has future-dated assignments.
Oracle HRMS enables you to enter and track people recors when they apply for a job or when they leave organization.HRMS allows you to enter , maintain,report of employess information.
Business Process in HRMS
The following modules are intergrated in Oracle HRMS
1.Hiring & Deployment
2.Recruitment
3.Career Management
4.Training & Development
5.Compensation & Benefits
6.Time & Labour
7.Payroll Processiong
8.Organization & Resource Planning
Work Structure
Work structure are independent of the people who work in enterprise. and it suppots Enterprise and employee structure such as Business groups and legal entities and also contains grade structure, pyroll groups,jobs and positions.
Location
Locations represents physical site where employees work.The location types could be
Global Availble across all BG
Business Group only availble in BG
Jobs and Positions
Jobs and positions to represents the different roles that a person can perform in enterprise.
Job is generic role within BG
They are independent of ant single organization.
Exists for all Organizations.
Positions
Positions is specific role or function exist in only one organization.Position includes Jobs and Organization.
Positions are used to show spcific posts within org.
Using position contril functionality can manage positions.
Grades
Grades indicates seniority level in an enterprise
Grades and Pay
HRMS supports direct and indirect relationship between grades and Pay
Person Types in HRMS
HRMS store information about different group of peoples such as employees,applicants and people external to your enterprise.Using person Types you can restrict access to records of certain group of people
HRMS uses a unique identifier called as Employee Number to identify every employee in BG.
Business Group
Business group defines a complete set of HR data and linked with one legislative processing like payrool and benefits . But oracle HRMS come with predefined Business groups which is useful who do not intedent to use HR in Apps.
Personal Information
Every enterprise must be able to record personal information for its employee,applicants and contacts,So HRMS allow you update and report employee information.
Addresses
Employee must have only one primary addres on record.But you can enter as many addresses ad mecessary for each person.Each Country has its own national address style.
Tracking
By using web-based Manager Self service to enter some information like employee history,roles and employee data etc...
Employee Assignment
The assignment represents the way employee work in enterprise.The assignment is the central concept that related to the structure in which employes works.
Each assignment has employess assignment number that uniqely identifies assignment. When employees change their locations or job these changes are dtaetracked.
Assignment stastuses
1.Active
2.Suspended
3.Terminated
4.Ended
Employee Development
You can Record work choice,relocation,preferred work hours and work schedule.
Person Types
HRMS supports with predefined set of system person types ease person typa has its own user name. These are 8 types.
Retiree
Former Spouse
Surviving Family Member
Surviving Spouse
Former Family Member
Beneficiary
Dependent
Partcipant
Terminate Employee
When employee leaves organization terminate the person record action changes the person type to Ex-Employee and automatically ends all assignments for the person.You cannot terminates an employee who has future-dated assignments.
Oracle HRMS Protected Descriptive Flexfields
Oracle HRMS Protected Descriptive Flexfields
In oracle HRMS the following DFF's not allows you to change the Definitions. These are used by product Development to deliver std functionality for HRMS.
Further Element information Pay_Element_types_f
Further Assignment Information Per_Assignment_Extra_info
Further Payment Method information Pay_org_payment_methods_f
Further Person Information Per_people_f
Further Job Information Per_jobs
Org Development DF Hr_organization_Information
In oracle HRMS the following DFF's not allows you to change the Definitions. These are used by product Development to deliver std functionality for HRMS.
Further Element information Pay_Element_types_f
Further Assignment Information Per_Assignment_Extra_info
Further Payment Method information Pay_org_payment_methods_f
Further Person Information Per_people_f
Further Job Information Per_jobs
Org Development DF Hr_organization_Information
Friday, April 24, 2009
Execution Sequences of sql clauses.
Execution Sequences of sql clauses.
a)Select…..
b)Group by…
c)Having…
d)Orderby..
a)Select…..
b)Group by…
c)Having…
d)Orderby..
Diff Between Implicit and Explicit Cursors
Diff Between Implicit and Explicit Cursors
1) Implicit: declared for all DML and pl/sql statements.
By default it selects one row only.
2) Explicit: Declared and named by the programmer.Use explicit cursor to individually process each row returned by a Multiple statements, is called ACTIVE SET. Allows the programmer to manually control explicit cursor in the
Pl/sql block
1) Implicit: declared for all DML and pl/sql statements.
By default it selects one row only.
2) Explicit: Declared and named by the programmer.Use explicit cursor to individually process each row returned by a Multiple statements, is called ACTIVE SET. Allows the programmer to manually control explicit cursor in the
Pl/sql block
Subscribe to:
Posts (Atom)