Migrating workflow between instances

Hi All,
There are two way of migrating workflow definitions between instances.
1.open the workflow in the workflow builder and using save as in to the target system


second way is using wfload

select * from wf_item_types_tl to the get the workflow short name

WFLOAD apps/apps 0 Y DOWNLOAD REQAPPRV --download eqappv_final.wft

ftp the reqappv_final.wft to the target instance

WFLOAD apps/apps 0 Y UPGRADE

Oracle workflow standard activities

Hi All,
here i a putting all standard activities of workflow builder from the developer guide..
this is basically for a quick refernce(interview perpective)..just go through all once so that you know what all can be done easily in the workfkow..for details
plz check the oracle workflow developer guide ..

AND/OR Activities:
In cases where multiple parallel branches transition to a single node,
you can decide whether that node should transition forward when any
of those parallel branches complete or when all of the parallel branches
complete.


Comparison Activities:
compare date: Use to compare the value of an item type attribute of type Date with a constant date.
compare time:Use to compare the value of an item type attribute of type Number with a constant number.
comparte text: Use to compare the value of two item type attributes of type Text.



Compare Execution Time Activity:
The Compare Execution Time activity provides a standard way to compare the elapsed execution time of a process with a constant test
time.


Wait Activity:
The Wait activity pauses the process for the time you specify. You can
either wait until:
• a specific date
• a given day of the month
• a given day of the week
• a period of time after this activity is encountered



Block Activity:
The Block activity lets you pause a process until some external program or manual step completes and makes a call to the CompleteActivity
Workflow Engine API.

Defer Thread Activity:
The Defer Thread activity defers the subsequent process thread to the background queue without requiring you to change the cost of each
activity in that thread to a value above the Workflow Engine threshold.


Launch Process Activity:
The Launch Process activity lets you launch another workflow process from the current process. This activity calls the PL/SQL procedure
named WF_STANDARD.LAUNCHPROCESS.


Noop Activity:
The Noop activity acts as a place holder activity that performs no action. You can use this activity anywhere you want to place a node
without performing an action.

Loop Counter Activity:
Use the Loop Counter activity to limit the number of times the Workflow Engine transitions through a particular path in a process.
The Loop Counter activity can have a result of Loop or Exit.


Start Activity:
The Start activity marks the start of a process and does not perform any action. Although it is not necessary, you may include it in your process
diagram to visually mark the start of a process as a separate node.

End Activity:
The End activity marks the end of a process and does not perform any action. You can use it to return a result for a completed process by
specifying a Result Type for the activity.


Role Resolution Activity:
The Role Resolution activity lets you identify a single user from a role comprised of multiple users. In a process diagram, place the Role
Resolution activity in front of a notification activity and specify the performer of that notification activity to be a role consisting of several
users.

Notify Activity:
The Notify function activity lets you send a notification, where the message being sent is determined dynamically at runtime by a prior
function activity. To use the Notify activity, you must model a prerequisite function activity into the process that selects one of several
predefined messages for the Notify activity to send.


Vote Yes/No Activity:
The Vote Yes/No activity lets you send a notification to a group of users in a role and tally the Yes/No responses from those users. The
results of the tally determine the activity that the process transitions to next.


Master/Detail Coordination Activities:
lets the master/detail process wait for a master/detail process to wait
Wait for Flow Activity:
Continue Flow Activity:


Assign Activity: The Assign activity lets you assign a value to an item attribute. This activity calls the PL/SQL procedure named WF_STANDARD.ASSIGN.

Get Monitor URL Activity:
The Get Monitor URL activity generates the URL for the Workflow Monitor diagram window and stores it in an item attribute that you
specify. This activity calls the PL/SQL procedure named WF_STANDARD.GETURL.

Multiple oraganizations Access control(MOAC)-R12--Part1

Hi ALL,
Here i am trying to put the MOAC techical architecture in simple terms..

we are might have heard the term multi organization till 11.5.10 prior it is only one organization

what does multiorganization mean??
Managing multiple organizations data in a single system...putting it in lay man terms
For example lets take GEfinancial
It is headquarted in US and operations in india also..assume it declares the Profit and loss results in both the countries..
Indian accounting rules, financial Calendar is different between the US and India..
since it belongs to same company with in one system I define two operating units one for us and one for India
different ledgers and calendars...if I want I can have different chart of accounts also..

to manage data for both the organizations with in single instance oracle introduced the column org_id in all table which holds organization specific data..

For data security purpose people of a organization should able to see their own data to achieve this
they created view on the base table like _all _B which restricts the data of the organization to which the user/user responsibility is attached.
for this purpose they defined a profile MO:operating unit which is set at the responsibility/user level
based on this value the system context org_id is set to the operating unitid set at the profile level
and a additional conditiion is added to the where clause of the views like

'org_id = substrb(userenv(''CLIENT_INFO''),1,10)'

By this way they are able to restrict data to the one organization in all the forms,reports,concprograms

fnd_global.apps_intialize--will set the applciation context org_id

thats the resason when ever we try to access the data from toad/sql plus for single org specific views we will not get any results..

select * from po_headers--no data found

if we set the application context we will able to see data for that org_id

execute dbms_Application_info.set_client_info('101');
or
fnd_global.apps_intialize(userid,responsibilityid,applicationid) --this inturn will get the org_id and set the context


From R12:Multiple organization access control

Till 11.5.10.2 one responsibility is able to see data of only one operating unit..
when ever we want to see another operating unit data we need to change resposnsibility..
From R12 oracle introduced the concept of Multiple organization access control(MOAC) so that being in the same responsibility the user should
be able to see the data for which he is give access..
let see how they achieved it..

1.First we will define a policy which will have access for different operating units
2.they attach that policy to the responsibility/user at profile option level

technically..till 11.5.10 the query is getting chagned as org_id=101
if we have to access multiple organization it should changed to org_id in (101,102) or exits...

for achieving this oracle used the concept of Virtual Private Database (VPD)..
VPD:
The Virtual Private Database (VPD) feature allows developers to enforce security by attaching a security policy to database objects such as tables, views and synonyms. It attaches a predicate function to every SQL statement to the objects by applying security policies. When a user directly or indirectly accesses the secure objects, the database rewrites the user's SQL statement to include conditions set by security policy that are visible to the user.

First lets decide whethere the access mode to a responsibility is single or multiple or all.Based on the security policy oracle decides the access mode
let assume our security policy which is attached to our respobility has access to the two operating units.
now oracle will populate the global temporary table(session controlled) mo_glob_org_access_tmp with all the operating units attached to the security policy
of yours..

based on the vpd a concept from oracle 9i onwards they defined a policy 'ORG_SEC' which call the function 'MO_GLOBAL.ORG_SECURITY' for all the objects to which the policy is attached..



MO_GLOBAL.Org_Security function:

FUNCTION org_security(obj_schema VARCHAR2
obj_name VARCHAR2)RETURN VARCHAR2
IS
BEGIN
--
-- Returns different predicates based on the access_mode
-- The codes for access_mode are
-- M - Multiple OU Access
-- A - All OU Access
-- S - Single OU Access
-- Null - Backward Compatibility - CLIENT_INFO case
--
IF g_access_mode IS NOT NULL THEN
IF g_access_mode = 'M' THEN
RETURN 'EXISTS (SELECT 1
FROM mo_glob_org_access_tmp oa
WHERE oa.organization_id = org_id)';
ELSIF g_access_mode = 'A' THEN -- for future use
RETURN NULL;
ELSIF g_access_mode = 'S' THEN
RETURN 'org_id = sys_context(''multi_org2'',''current_org_id'')';
END IF;
ELSE
RETURN 'org_id = substrb(userenv(''CLIENT_INFO''),1,10)';
END IF;
END org_security;


let see what happend po_headers view(11.5.10.2) in R12

1.Dropped the view po_headers
2.Created a synonym for the base table
create synnonym for po_headers for po_headers_all

3.attach the security policy org_sec to this synonym


so when ever we access this synonym from any where the policy will call the mo_global.org_Security funcion to all a condition to the select statement we isssued

select * from po_headers will be changed as

select * from po_headerS_all where exists 'EXISTS (SELECT 1
FROM mo_glob_org_access_tmp oa
WHERE oa.organization_id = org_id)'

By this they are able to access all data of all organizations assigned to the security policy.

Dynamic IN clause

Hi ALl,
This is one good ways of writing dynamic in clause..

if you have a select stament where you are uing in clause to restrict the data.if you are not sure on the inclause statment
what you will do..
what i used to do is make a dynamica sql build inclause on run time and then execute it ..But there is another smart way of building it dynamically..

Example1:

cursor c_cust_info is
select * from hz_cust_accounts where customer_class_code in ('CEMP','CEMP20','INDIV')

for making the inclause to by dynamic


cusror c_cust_info(c_in_statement varchar2)
select * from hz_cust_accounts where instr(c_in_statement,customer_class_code)>0

and open the cusor with the in clause you require..like

for rec in c_cust_info('CEMP,CEMP20') loop

end loop;

QP_CUSTOM--OE_MSG_PUB

Hi All
QP_CUSTOM is a standard package given by oracle to customize the price derivation based on our custom logic.
Recently i customisee this package to get freight charge based on the wegith of the line items.
This was not so tough to achieve but the user want to show some messages when ever any exception occurs....But the qp_custom is supposed to return a number..
This api is called from dynanic formual on some event like saving entering ..
The solution we thought of is form personalization by returning some constant number an throw exception when we encouter that values...But still i didn't work...the possible ways form personalization works are far less than the ways it didnt work...

after sorm R&D on google and metalink i found that there is api oe_msg_pub to put error messages...
But unfortunately i didnt find right code to use it properly after some hit and try
i figured out that
oe_msg_pub.add_text will add a message
oe_msg_pub.add will put the message on the stack..This will display messages in the
message window in order entry screen
i thought it would be better if we display message in a modal window
so i gave a blind try like define a message in application developer message window


fnd_message.set_name ('XXCUST', 'XX_ERROR_MSG');
fnd_message.set_token ('MESSAGE', 'No Shipping Charges defined');
oe_msg_pub.ADD;
"Message is a token in the message"
This really works...every time you change qp_custom package close the order entry form ,reload it and try...

Date parameter in Concurrent programs-FND_DATE

Hi All,
Normally when ever we want to use date parameter for pl/sql stored procedures we ecounter a common error saying that format string doenst match...
then we do a data conversion to_date(p_date,'RRRR/MM/DD HH24:MI:SS') and then use it..

The better way of doing it is define the data parameter as varchar2 and use the function FND_DATE.date_to_canonical(p_date).This will direclty convert the text string comming from the concurrent programs to date value

ln_processing_date := NVL (fnd_date.canonical_to_date (p_processing_date), SYSDATE);

Oracle Apps-Changing LOV Query-Form Pesonalization

How to change a LOV query from Forms Personalization??

This is one of the requrirement that we want to add a additional condition to the LOV query or you want to populate the LOV all together from your SQL.
Its a simple four step process

Step1:
Get the Query that of the LOV of the standard form. You can do it by opening the standard form in the form builder or Get the query by taking the trace of the form and getting the query from the trace..

Step2:
If you want to add only a few more conditions to the query just add the conditions
In other case create a view with column same as in the standard query.

Step3:
In this step on when new form instance we will create the record Group from query

Step4:
Attach the record group to the LOV you want to change…

Step1:Get the query
The standard query is

select event_name, description from fa_maint_schedule_dtl WHERE EVENT_NAME LIKE :1

I want to populate from my own sql so I create a view

create or replace view XX_AMC_EVENT_DETAILS as
select 'SERVICE' EVENT_name ,'ATM SERVICE' DESCRIPTION from dual
union
select 'AMC' ,'AMC CONTRACT' from dual
union
select 'TEST1' ,'TEST2' from dual

Step2:
Plz find teh screen shots attached..

Terminating Concurrent program sessions

Hi ALL,
One way of finding the session id for concurrent is through fnd_cocurrent_request...as discussed in my earlier post
But there is another easy way...


select sid,serial#,MODULE,ACTION,status from V$session

the module will contain the concurrent program short name...If only one instance of the concurrent program is running
we can easily identify the sessions of it..

If we terminate the program and the session not got released..simply find th session

and use the command
alter sytem kill session 'SID,SERIAL#'
Please the values you got from the above query

7 Ways for a Better LOG--My View..Suggestions Welcomed

Hi All,
we all use the log files for debugging issues by writting text into them when ever a exception occurs or to know the process flow of our code...Here are few steps that will make us to have better log and make our life easy during supporting phase..

7 ways for writing a Better Log
1.Always Display the Name of the package/procedure in the LOG File.
The main advantage of doing this is no need query the concurrent program name and executable to find the procedure of
concurrent program

2.It is always better to have a common package having the log function.So that every time we want to write into a log
there is no need to type entire fnd_file.put_line(fnd_file.log,'message'
it can be easily done like
xxab_util_pkg.log('message');
it is better to have out also in the same way...

3.If the code is running very long number of lines define a stage variables and set it at different levels..and the dispaly the stage completed value when ever the exception occurs...This helps to debug the issue faster...

4.Display all the derived values in the log

5.When ever you are displaying statements like no data found for any sqlstatments in the code display all the values passed to the SQL along with nodatafound or too many row exception.This inturn will reduce a lot of time during support...

6.Always make sure you attach the SQLERRM variable with the when other exceptions handled...and diplay the section name which is causing this exception
exception
when others then
fnd_File.put_line('Unhandled exception occured in XX_GL_INTF_PKG.Check_PERIOD:'||SQLERRM);

7.Always End the concurrent program either in ERROR or warning when ever a exception occurs in the programs unless it is a business requirement.

Entering Query Conditions in Standard Forms

Recently i want to query on a stadard form for records having reversal date as null
i remember that i need to give is null but it didn't work...some how i manager to do it from backend..Even i searched developer guide with no use...So how i get this from the form guide and i am publishing for easy reference..these things will be very useful..and makes our life easy..

1. to enter query by example
2. to recall the last query parameters
3. to Execute Query
4. Query : Count Matching Records (after putting in the query parameters)

Query Wild Card Characters
% one or more characters
_ exactly one character

Query Operators
#between and --need to give #
#is null
#is not null
> Greater Than
>= Greater Than or Equal
< Less Than
<= Less Than or Equal
= Equal
!= Not Equal

RETCODE & ERRBUFF

Hi ALL,
As we all know there are two mandatory parameters that need to be pased for all the procedures called
1.ERRBUFF
2.RETCODE..
They are very useful parameters if used properly..like setting the status of the conucrrent program and putting the log messages...
i think it is better we make some rule like we make the program end in Error when ever there is a unidentified exception occurs based on your business rule...

define ERRBUFF as the first parameter and Retcode as the second one...Dont forget to specify the out variable type in it...

CREATE PROCEDURE SAMPLE_PROC (ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
p_1 varchar2)

The retcode has three values returned by the concurrent manager
0--Success
1--Success & warning
2--Error

we can set our concurrent program to any of the three status by using these parameters...
for example
Begin
.....
Exception
when others then
fnd_File.put_line(fnd_file.log,'Unhandled exception occurred in package.procedure'||SQLERRM);
retcode='2';
end;
Even you can use fnd_concurrent.set_completion_Status to send the concurrent program to more status than success,error and warning.....

Shell script for File Movement between Servers

From very long i used to get a common requirement that some system will place the files in a server.
the system should get the files and process them
for achieving this we need to have shell script which will connect to the server and ftp the files to the source server..
Plz find the sample script for doing this..
after writing the script we have to schedule the script in unix using a cronjob...

--Shell script starting
#! /usr/bin/ksh
HOST=egltest.test.co.in
USER=applprod
PASSWD=applprod

chmod -R 777 /egltestarch1/oracle/EGLTEST/test/coa/*.txt

exec 4>&1
ftp -nv >&4 2>&4 |&

print -p open $HOST
print -p user $USER $PASSWD
print -p bin
print -p lcd /egltestarch1/oracle/EGLTEST/test/coa
print -p cd /egltestapps1/home/test/coa
print -p mget *.txt
print -p bye

exit 0
--Shell script ending name it as ftpcode.sh

this shell script is saved in /egltestarch1/oracle/EGLTEST/dba/sh/ftpcode.sh
After writing the script we need to a cronjon entry..this entry will run the script on the schedule we have given..

for this
crontab -e
using vi editior commands to make this entry below..this will run the script every minute and log is the written to the file specified on the right side...



# ftp files from application server to database server
* * * * * /eglprodarch1/oracle/EGLPROD/dba/sh/ftpcode.sh > /egltestarch1/oracle/EGLtest/dba/log/ftpcode.log 2>&1

once the files are dowloaded make sure to delete or move to other folders to automate the FTP process...

Tracing techniques in oracle applications

Hi,This is one of the good documents from metalink..good reference document when you want to do tracing....

WHAT TO SET UP BEFORE GENERATING THE TRACE

These steps must be performed by the DBA on the database server.

1. Set TIMED_STATISTICS to TRUE.
For performance issues, make sure TIMED_STATISTICS is turned on,
before attempting to generate the trace.
Set the following in the init.ora file:
TIMED_STATISTICS=TRUE
OR
in SQL*Plus:
ALTER SYSTEM SET TIMED_STATISTICS=TRUE;

2. Set the location of the trace output.
Set the following in the init.ora file:
USER_DUMP_DEST = <preferred directory for the trace output>

3. Create the PLAN_TABLE to hold the output of the explain plan. Run
the SQL script called UTLXPLAN.SQL to create this in the apps schema.
This script is usually in $ORACLE_HOME/rdbms/admin.

4. If the init.ora file has been updated, you must shut down and restart the database before the changes will take effect.
TYPES OF TRACE - HOW TO TURN TRACE ON
Regardless of the type of trace file you create, make sure you note the time
that you create it.

1. Form Trace

Toggle trace on/off on the form, to trace specific application functions.
Make sure you go in fresh (sign off/on to the application), since somequeries are cached and may not be executed on subsequent visits to theform.

From the menu, select Help..Diagnostics..Trace (Release 11i) to turn
trace on (when checked, it is on).
Release 11 and 10.7 GUI/NCA --> Help..Tools..Trace
Perform the action to be traced.
From the menu, turn trace off, by selecting Help..Diagnostics..Trace

(it should now be unchecked).Release 11 and 10.7 GUI/NCA --> Help..Tools..Trace

2. Concurrent Program Trace

A. This will turn trace on for each execution of this program.
In Release 11.0 and lli, check the Enable Trace checkbox for the
concurrent program on the Concurrent Programs form. After running the
program to be traced, make sure you uncheck the Enable Trace checkbox.
Select the System Administrator responsibility.
Navigation =
Concurrent -> Programs -> Define. Query the concurrent program you
want to trace. Check the Enable Trace checkbox and save.
OR
B. How to generate a raw trace file with binds and/or waits
for 11.5.10:
1. Log into applications as System Administrator and Navigate to the
System Profile Values Form. Select the profile called
Concurrent: Allow Debugging and change the value to Yes at the appropriate level.
2. Allow pop-ups on your browser.
3. Navigate to the Submit a New Request form and select a job and
enter all parameters for that job
4. Select the Debug Options Button and this will take you to Create
Debug Rule in Oracle Application Manager
5. Select the appropriate Debug Option Value for SQL Trace only and then check the box
6. Hit OK twice and then Submit the job
7. Raw trace file with options selected will be located in the
appropriate directory.

3. Database Level Trace
This will turn trace on for all processes that are running in the
instance and should only have to be used in Release 10.7, for
concurrent programs. (This has to be done by the DBA.)
Set the following in the init.ora file:SQL_TRACE=TRUE

Shut down and restart the database.After generating the trace file, shut down and restart the database with the original init.ora.

.
4. Report Trace
If you are on 10.7 and need to trace an Oracle Report, you can modify the report to turn trace on for that session.
a. Convert the report from rdf to rex:
$ORACLE_HOME/bin/r25convm batch=yes userid=<apps username/pwd>> stype=rdffile source=REPORT_NAME.rdf dtype=rexfile overwrite=yes
b. Edit the rexfile and search for the beforerep trigger in the report.
Locate the following code:
IF (:p_trace_switch = 'Y') THEN
SRW.DO_SQL('alter session set sql_trace TRUE');
END IF;
Comment out the IF and END IF lines.
c. Save the report.
d. Convert the report from rex to rdf:

$ORACLE_HOME/bin/r25convm batch=yes userid=<apps username/pwd> \
> stype=rexfile source=REPORT_NAME.rex dtype=rdffile overwrite=yes

5. Self Service page (like a Forms trace, but for self service web apps)
a. Set the FND:Diagnostics profile:
Responsibility = System Administrator
Navigation: Profile > System
User: Enter User name
Query the Profile: 'FND:Diagnostics'
Set the 'FND:Diagnostics' profile to Yes at User level

b. Login to Self Service under the same user the profile was set for.
c. Turn Trace on:
Click the Diagnostic link at the top of the page
It shows two options: Show Log and Set Trace Level
Select 'Set Trace Level'
Click Go.
Select one of the following options:
Disable Trace - used to end the trace
Trace (Regular) - just like a forms trace
Trace with Binds - record the bind variables in the trace
Trace with waits - Good for performance issues
Trace with binds and waits - combines both of the above
Click Save.
d. Perform the action to be traced in Self Service.
Multiple trace files may be generated in the usual trace directory.
e. Turn trace off:
Select the Diagnostic link
Click on option: Set Trace Level
Note all of the trace numbers listed
Click Go
Select: Disable Trace
Click Save

WHAT TO DO AFTER GENERATING THE TRACE FILE

These steps should be performed by the DBA, on the database server.

1. Find the trace directory.
Get the location of user_dump_dest.
Log into SQL*Plus as the apps user.

select value from V$PARAMETER where name = 'user_dump_dest'
2. Find the trace file for your process.
Go to the directory you found in step 1 (in UNIX, use cd).
Look for a file (.trc) that was created at the time you started
your process (in UNIX, use ls -ltr).

3. Run tkprof with explain plan.
Go to a directory in which you have write privilege (in UNIX, use cd).

Run tkprof:

tkprof <full path to trace file> <output file name> explain=<apps username/apps password>

HOW TO GET AN EXPLAIN PLAN FOR A SQL STATEMENT

Sometimes you may have a need to get an explain plan for a specific SQL
statement. If you have the sql statement, you can get the explain plan for
it.
This should be run on the same instance that the sql statement came from.
In your apps account ---
Run the following script:
delete from plan_table
where statement_id = 'tmp'

explain plan

set statement_id = 'tmp'
for
<put sql statement here>
/
set pages 100
col operation format a36
col options format a11
col object_name format a30

select lpad(' ',2*(level-1))operation operation,

options, object_name
from plan_table
where statement_id = 'tmp'
connect by prior id = parent_id
and statement_id = 'tmp'
start with id = 1
and statement_id = 'tmp'
order by id
/
delete from plan_table
where statement_id = 'tmp'
/
commit;

HRMS Interface -Employee Creation and updation

Hi All,
this is basic to sink employee information between two instances....


CREATE OR REPLACE PROCEDURE APPS.XX_HRMS_INTF_PROC
(retcode VARCHAR2
, errbuff VARCHAR2)
AS l_emp_num VARCHAR2 (30);
l_person_id NUMBER;
l_assignment_id NUMBER;
l_per_object_version_number NUMBER;
l_asg_object_version_number NUMBER;
l_per_effective_start_date DATE;
l_per_effective_end_date DATE;
l_full_name VARCHAR2 (240);
l_per_comment_id NUMBER;
l_assignment_sequence NUMBER;
l_assignment_number VARCHAR2 (30);
l_name_combination_warning BOOLEAN;
l_assign_payroll_warning BOOLEAN;
l_orig_hire_warning BOOLEAN;
l_employee_number VARCHAR2 (20);
l_effective_start_date DATE;
l_effective_end_date DATE;
l_comment_id NUMBER;
lv_rec_exixsts VARCHAR2 (1) := 'N';
l_last_std_process_date_out DATE;
l_supervisor_warning BOOLEAN;
l_event_warning BOOLEAN;
l_interview_warning BOOLEAN;
l_review_warning BOOLEAN;
l_recruiter_warning BOOLEAN;
l_asg_future_changes_warning BOOLEAN;
l_entries_changed_warning VARCHAR2 (200);
l_pay_proposal_warning BOOLEAN;
l_dod_warning BOOLEAN;
l_alu_change_warning VARCHAR2 (200);
l_acutal_temination_date_ewgl timestamp;
l_business_group_id number:=101;
l_ser_object_version_number number;
l_last_run_date timestamp ;
l_service_id number;
CURSOR c_emp_cur_hrms(c_last_run_Date date) IS
SELECT * FROM xx_hrms_intf_v@uat_new
where last_update_date>=c_last_run_Date or creation_Date>=c_last_run_Date;
BEGIN
select max(actual_completion_date)
into l_last_run_date from FND_CONCURRENT_REQUESTS fcr, fnd_concurrent_programs fcp where fcr.concurrent_program_id=fcp.concurrent_program_id
and concurrent_program_name='XX_HRMS_INTF'
and fcr.STATUS_CODE='C';
fnd_file.PUT_LINE(fnd_file.log,'last run successful date:'l_last_run_date); --Creating New Employee
FOR emp_rec IN c_emp_cur_hrms(l_last_run_date) LOOP
fnd_file.put_line (fnd_file.LOG, 'HRMS employee NO:' emp_rec.employee_number);
BEGIN
SELECT 'Y' INTO lv_rec_exixsts
FROM per_all_people_f
WHERE employee_number = emp_rec.employee_number
AND SYSDATE BETWEEN effective_start_date AND effective_end_date
and business_group_id=l_business_group_id;
EXCEPTION
WHEN OTHERS THEN
lv_rec_exixsts := 'N';
END;
IF lv_rec_exixsts = 'N' THEN
hr_employee_api.create_employee
(p_hire_date => emp_rec.start_date ,
p_business_group_id => l_business_group_id,
p_last_name => emp_rec.last_name ,
p_first_name => emp_rec.first_name ,
p_title => emp_rec.title, p_sex => emp_rec.sex ,
p_employee_number => emp_rec.employee_number ,
p_date_of_birth => emp_rec.date_of_birth ,
p_person_id => l_person_id ,
p_assignment_id => l_assignment_id ,
p_per_object_version_number => l_per_object_version_number ,
p_asg_object_version_number => l_asg_object_version_number ,
p_per_effective_start_date => l_per_effective_start_date ,
p_per_effective_end_date => l_per_effective_end_date ,
p_full_name => l_full_name ,
p_per_comment_id => l_per_comment_id ,
p_assignment_sequence => l_assignment_sequence ,
p_assignment_number => l_assignment_number ,
p_name_combination_warning => l_name_combination_warning ,
p_assign_payroll_warning => l_assign_payroll_warning ,
p_orig_hire_warning => l_orig_hire_warning);
fnd_file.put_line (fnd_file.LOG, 'Creating New employee:' emp_rec.employee_number); ELSIF lv_rec_exixsts = 'Y' THEN
SELECT papf.person_id ,
papf.object_version_number ,
ppos.actual_termination_date ,
ppos.OBJECT_VERSION_NUMBER ,
ppos.PERIOD_OF_SERVICE_ID INTO l_person_id ,
l_per_object_version_number ,
l_acutal_temination_date_ewgl ,
l_ser_object_version_number ,
l_service_id
FROM per_all_people_f papf, per_periods_of_service ppos
WHERE employee_number = emp_rec.employee_number
AND papf.person_id = ppos.person_id
and papf.business_group_id=l_business_group_id
AND SYSDATE BETWEEN EFFECTIVE_START_dATE AND EFFECTIVE_END_DATE;
l_employee_number := emp_rec.employee_number;
fnd_file.put_line(fnd_file.log,'person_id:'l_person_id);
fnd_file.put_line(fnd_file.log,'Object Version Number:'l_per_object_version_number); fnd_file.put_line(fnd_file.log,'EWGL Termination date:'l_acutal_temination_date_ewgl); fnd_file.put_line(fnd_file.log,'Last Update date:'emp_rec.last_update_date); fnd_file.put_line(fnd_file.log,'HRMS Employee Actual Termination Date:'emp_rec.actual_termination_date); f
nd_file.put_line(fnd_file.log,'Service Object Version:'l_ser_object_version_number);
IF emp_rec.last_update_date>=l_last_run_date
AND l_acutal_temination_date_ewgl IS NULL and emp_rec.actual_termination_date is null THEN
hr_person_api.update_person (p_effective_date => emp_rec.effective_start_date
,p_datetrack_update_mode => 'CORRECTION'
,p_person_id => l_person_id
,p_object_version_number => l_per_object_version_number
,p_employee_number => l_employee_number
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_full_name => l_full_name
,p_last_name => emp_rec.last_name
,p_first_name => emp_rec.first_name
,p_date_of_birth => emp_rec.date_of_birth
,p_comment_id => l_comment_id
,p_name_combination_warning => l_name_combination_warning
,p_assign_payroll_warning => l_assign_payroll_warning
,p_orig_hire_warning => l_orig_hire_warning);
fnd_file.put_line (fnd_file.LOG, 'UPDATING New employee:' emp_rec.employee_number);
END IF;
IF emp_rec.actual_termination_date IS NOT NULL and emp_rec.last_update_date>=l_last_run_date THEN
l_last_std_process_date_out := emp_rec.final_process_date; hr_ex_employee_api.actual_termination_emp
(p_effective_date => emp_rec.effective_start_date
,p_period_of_service_id => l_service_id
,p_object_version_number => l_ser_object_version_number
,p_actual_termination_date => emp_rec.actual_termination_date
,p_person_type_id=>null
,p_last_std_process_date_out => l_last_std_process_date_out
,p_supervisor_warning => l_supervisor_warning
,p_event_warning => l_event_warning
,p_interview_warning => l_interview_warning
,p_review_warning => l_review_warning
,p_recruiter_warning => l_recruiter_warning
,p_asg_future_changes_warning => l_asg_future_changes_warning
,p_entries_changed_warning => l_entries_changed_warning ,
p_pay_proposal_warning => l_pay_proposal_warning ,
p_dod_warning => l_dod_warning ,
p_alu_change_warning => l_alu_change_warning);
fnd_file.put_line (fnd_file.LOG, 'Terminating New employee:' emp_rec.employee_number);
END IF;
IF l_acutal_temination_date_ewgl IS NOT NULL
AND emp_rec.actual_termination_date IS NULL and emp_rec.last_update_date>=l_last_run_date THEN hr_employee_api.re_hire_ex_employee
(p_hire_date => emp_rec.effective_start_date ,
p_person_id => l_person_id ,
p_per_object_version_number => l_per_object_version_number ,
p_rehire_reason => ' ', p_assignment_id => l_assignment_id ,
p_asg_object_version_number => l_asg_object_version_number ,
p_per_effective_start_date => l_per_effective_start_date ,
p_per_effective_end_date => l_per_effective_end_date ,
p_assignment_sequence => l_assignment_sequence ,
p_assignment_number => l_assignment_number ,
p_assign_payroll_warning => l_assign_payroll_warning);
fnd_file.put_line (fnd_file.LOG, 'RE Hiring New employee:' emp_rec.employee_number); END IF; END IF; END LOOP;
commit;
END;

Copying a request Group Content to another request group

Hi All,
Recently i got a requirement to create request and attach all the standard reports of GL to these request groups...it seems a lot tedious task to do manually...so i developed this script to copy the request group content to other request groups..THis is very useful if you have to create multiple requestgroups and attach reports to all,....

declare
cursor z isselect b.CONCURRENT_PROGRAM_NAME PROGRAMNAME,c.APPLICATION_NAME APPNAME from FND_REQUEST_GROUP_UNITS a
,fnd_concurrent_programs b
,fnd_application_tl c
,FND_REQUEST_GROUPS d
where a.request_Group_id=d.request_Group_id and a.REQUEST_UNIT_ID=b.CONCURRENT_PROGRAM_ID
and b.APPLICATION_ID=c.APPLICATION_ID
and d.request_Group_name='GL Concurrent Program Group';
begin
FOR REC IN Z LOOP
FND_PROGRAM.ADD_TO_GROUP(program_short_name =>REC.PROGRAMNAME
,program_application=>REC.APPNAME
,request_group=>'XX_GL_MOCA'
, group_application=>'Custom Application');
END LOOP;
END;

Concurrent Manager-1

Hi All.
We all are using concurrent programs ..but might have never put much thought into concurrent manager details....
We know that there are concurrent managers running in background which will be processing the concurrent programs submitted.
Basically they are three managers
1.Internal Manager
2.Standard Manager
3.Conflict manager
First let us see few scenarios..
1.In a instance when ever we are submitting multiple request only few are running.. why?
2.I have some reports which need to be run only in month end .how should I design this??
3.If two modules are submitting programs..and one is submitting more..provison should be made to make others are also submit program and they are get processed.
One user of GL submitted 100 reports…which takes time.. at the same time user2 need a few of reports also processed…How to setup so that both can run their reports

For developer point of view the most concerned one should be standard manager and Defining New Manager
First look at standard manager options..then i will come to how to handle the above scenarios..


Let see what is cache size..it is the number of concurrent programs it will select and keep in the queue.that means even you submit a program with higer priority it will be queues after the cache sized program only…
Press the workshift button which will say when the concurrent manager will be running…
In this note the number of process, it will be decide how many concurren program will be running at a time..dont increase number substantially high it depends on lot of parameters
Like server size and number of nodes…check with you DBA before changing to very high value…


Press the specialization rules button to see the programs which need to be excluded or included in the concurrent manager queue..by default all the custom concurrent programs will be running under standard manager;….

Concurrent Program LOCKs..

Hi All,

Now a days i am developing a concurrent program...while developing in some scenarios the concurrent program is not completingso i want to terminate,make some corrections and resubmit my program..even i terminate, the lock on my object are not getting releasedso i need to find the locked session and terminate the session...i thought of getting the information of session which i am currently using from oracle applications GUI itself but no information of session is available in front end...then i figured out that oraclesession and oracle_process_id are two columns in fnd_concurrent requests.
query1:select concurrent_program_name,fcr.REQUESTED_START_DATE, oracle_process_id,oracle_Session_id from FND_CONCURRENT_REQUESTS fcr,fnd_concurrent_programs fcp,fnd_logins fl,fnd_user fuwhere fcr.CONCURRENT_PROGRAM_ID=fcp.CONCURRENT_PROGRAM_ID and fcr.CONC_LOGIN_ID=fl.login_idand fl.user_id=fu.user_id--and fcr.request_id=441792and fu.user_name='MGANJAM'order by fl.start_time desc
query2select sql_text, sid, SERIAL# from v$session vs,v$sqltext vsql,applsys.fnd_concurrent_requests fcrwhere vs.sql_hash_value=vsql.hash_valueand fcr.ORACLE_SESSION_ID=vs.AUDSIDand fcr.request_id = '442686'
This query will give the the sql it is running and the SID and serial#
Select * from v$locked_objects
check where any lock exists for the session
then use the alter system kill session to kill the session and the lock will be release
Now you will be able to compile the object...

SQL Loader in Oracle Applications(Draft)

SQL Loader in Oracle Applications…

Step1:create a Sqlloader file.(the ctl file)

Step2:Create a executable with Execution method type as SQLLOADER

Step3:move .ctl file to executable application top bin folder
For example for GL it is GL_TOP/bin
Step4.Create a program for the executable with paramertes like file name..

Step6:Attach the concurrent program to a request group and the run the program..

The one point that comes in mind how to acess the concurrent program parameters in sqlloader..it is using %1


LOAD DATA
INFILE '%1'
BADFILE '%1.bad'
DISCARDFILE '%1.dis'
TRUNCATE
INTO TABLE GL_DOW_TRANS
(
VOTE POSITION(01:16) INTEGER EXTERNAL,
AMOUNT POSITION(18:30) INTEGER EXTERNAL,
PERIOD POSITION(35:38) INTEGER EXTERNAL
)

Copying Files using PLSQL

Hi all
Most of the time we have requirement of moving files….we normally use the Shell script to move the files…
It is not mandatory we need to use shell scripts.
There are plsql api’s which can move files from one location to other location..

I think every one is aware of UTL_FILE .even it has some limitations…like the directory strucuture used by it should be in the UTL_FILE_PATH in init.ora …or Directory object should exist for that path…

For copying the file..
Utl_File.Fcopy ( src_location => p_file_location
,src_filename => p_file_name
, dest_location => p_arch_location,
dest_filename => p_arch_file );

For removing the file..:

UTL_FILE.Fremove(p_file_location,lc_datafile_name);

We can use this package when the directories are fixed and only file movement should happen..if you want to create directories at run time
I think shell scripting is a better option…

FND_GLOBAL PACKAGE

Recently i come accross a requirement to capture the requestidof the concurrent with in the concurrent program session...Even i am aware of the FND_GLOBAL package...But it didnt strike'at first go.....so i am writing this article for people who are not even awarethat this package exists..
FND_GLOBAL package gives the most of the enviroment values
THis package should be used in database side only...use FND_PROFILE package to get these values in Forms.
1.FND_GLOBAL.USERID --Returns userid

2.FND_GLOBAL.APPS_INTIALIZEprocedure APPS_INITIALIZE(user_id in number,resp_id in number,resp_appl_id in number);
This is used to set the values userid and responsibilityid for a session
3.FND_GLOBAL.LOGIN_ID -Gives login id
4.FND_GLOBAL.CONC_LOGIN_ID--Not sure how to use this
5.FND_GLOBAL.PROG_APPL_ID--Concurrent program application id
6.FND_GLOBAL.CONC_PROGRAM_ID--Concurrent program id
7.FND_GLOBAL.CONC_REQUEST_ID (Server)This will give the concurrent request id of the program which is calling the plsql package..

ln_Request_id:=FND_GLOBAL.CONC_REQUEST_ID;

How to Register a shell script in oracle Applications???

I am putting this basics for people like me who are poor in unix…and not confident in doing shell scripting..:)
we dont need to develop huge scripts in apps..most of the times it will be moving files between servers or deleting files after completion…
Recently I developed a few scripts for moving files after program completion like output to some other folder…

$1 connect String
$2 Userid
$3-
$4 Concurrent request id


Step1:
Create a shell script .If the shell script is supposed to get values from the concurrent program parameters defined ,they can be accessed using $5,$6 onwards

Step2:Create a executable in application
With execution method as host

Step3:Create the concurrent program for this executable

Step 4:Attach the Program to a request group
Step5:Now move the shell script to the bin folder for the corresponding application top of the executable..make sure that file type is .prog not .sh
For example : General ledger application executable.. then GL_TOP/bin

Step6:Change the permission on the file..
Chmod 775 file if it doesn’t work chmod 777 XX_TEST.prog

Step7: Create a soft link between the shell the shell script and the concurrent manager

ln -s $FND_TOP/bin/fndcpesr XX_TEST

Step7:Run the concurrent program


One more thing if you want the message to seen for debugging it can be done using
Echo command….
echo $MONTH