How to download /upload WorkFlow

WorkFlow Commands and Instructions:

WorkFlow Commands and Instructions:
------------
The workflow definitions are stored into the database. The standard workflow files (wft) files are in
the $PA_TOP/patch/115/import/US/ and $PO_TOP/patch/115/import/US. The custom files are located in
$PA_TOP/patch/115/import and $PO_TOP/patch/115/import respectively.

When ever any patch is applied that impacts the workflow customizations, follow the below steps to
reapply the customizations.

1) Log on to the environment on which the customizations need to be applied.
2) Set the environment.
Ex: ./oracle/devl/app/prodappl/APPSDEVL_mmpsodevl.env
3) Fire the workflow definition download command to download the workflow definition from the database

How to Download Workflow from Oracle Applications?

DOWNLOAD WORKFLOW:

WFLOAD apps/apps 0 Y DOWNLOAD $HOME/PAAPINVW.wft PAAPINVW

WFLOAD apps/apps 0 Y DOWNLOAD $HOME/poxwfpag.wft POWFPOAG

The above commands creates the workflow definition files (wft) in the local directory $HOME/

4) Verify whether the workflow changes are intact.

5) If the workflow definitions are overwritten then apply the custom workflow definitions from the
mentioned directory.


How to Download Workflow from Oracle Applications?
UPLOAD WORKFLOW:

WFLOAD apps/apps 0 Y UPLOAD $PA_TOP/patch/115/import/PAAPINVW.wft

WFLOAD apps/apps 0 Y UPLOAD $PO_TOP/patch/115/import/poxwfpag.wft

verify the log and output file for any errors.

Note: If the customizations needed to be appended to the standard workflow then, modify the standard
workflow and add the customizations on top of it.

HOW TO load raw data in table using SQL LOADER (SQLLDR)

AIM--->>
"we have need to load data into TABLE from ".CSV" file using "sql loader".
Basically this kind of operation is need when client want to load "RAW Data" in his databse manually,
that time this technic is used.
Actually there are lots of s/w which is used for this operation but use of this method is  a sign of
standard programming.
------------------------------------------------------------------------------------------------------
TASK--->>
load the data of "abc.csv" file into table "abc" using "abc.ctl"
---------------------------------------------------
create table "abc" in databse.
ex-
create table abc(
id number,
name varchar2(25)
);

----------------abc.ctl----------------------------
OPTIONS ( SKIP=1)
LOAD DATA
INFILE '/u02/dev/abc.csv'
APPEND
INTO TABLE abc
fields terminated by ';' TRAILING NULLCOLS
( id,
  name
)
--------------------------------------------------
--------abc.csv contain following data------------
ID;NAME
1;aaa
2;rohit
3;rat
4;rani
---------------------------------------------------------------

How to execute--->
1-go on Putty.
2-type following command
SQLLDR apps/apps abc.ctl

syntax--> Sqlldr username/password(of database) ".ctl_file_name"

Here my database username=apps
                            password=apps


After executing this command see the table "ABC" you will find all data of "abc.csv "file in that table.
ex    --select * from abc
o/p
id name

1  aaa
2  rohit
3  rat
4  rani
------------------------------------------------------------
in above all exmple i usesame name for each file..means..abc.ctl,,abc.csv, table name is abc....
so it is not necessory to use same name u can use diff names......

----------------------------------------------------------------

Delete Concurrent program from back end

****Script TO DELETE registered concurrent program FROM back END******
**NOTE Never delet it from base table directly***********

BEGIN
      fnd_program.delete_program('Concurrent_prog_SHORTNAME','APPLICATION_short name');
      fnd_program.delete_executable('Concurrent_prog_SHORTNAME','APPLICATION_short name');
END;

****Script TO DELETE registered REQUEST SET FROM back END******

BEGIN

   FND_SET.DELETE_SET ('req_set_shortName','application_short_name');

END;

How to retrive the data from two or more database tables with the help of alias and joins

Here i am retriving the data from 3 tables. there must be a one column which is comman in all table that is called as foreign key.

in our table "EMP_NO" is foreign key for "TABLE1 & TABLE2" and "EMP_DEPARTMENT_ID " is foreign key  for "TABLE2 & TABLE3"

TABLE1

--------------------------------------

EMP_NO       EMP_NAME

1            MUNNI

2            SHEELA

3            SHALU

4                  JILEBIBAI

-------------------------------------- 

TABLE2

-------------------------------------------------------------------

EMP_NO   EMP_DEPARTMENT_ID     EMP_DEPT

1        10                    IT

2      20                   COMP

3        30                   MECH

4                    50                  BOLLYWOOD

----------------------------------------------------------------------------------

TABLE3

-----------------------------------------------------------------------------------------------

EMP_DEPARTMENT_ID       EMP_ADDRESS

      10         DAHIWADI 1

      20         DAHIWADI 2

      30         DAHIWADI 3

      40                               DAHIWADI 4

----------------------------------------------------------------------------------------------

Query->

 SELECT      a.emp_name

           , b.emp_dept

                , c.emp_address

          

 FROM        table1 a

               , table2 b

               , table3 c

 WHERE  

                 a.emp_id=b.emp_id

     and      b.emp_departmet_id=c.emp_departmet_id;

----------------------------------------------------------------------------------------------    

In above query a, b, c are the alias.

and conditons given in where clause are the simple joins.

Actualy there are main 3 types of join in oracle.

1 LEFT Join.

2 RIGHT Join.

3 NATURAL Join

In oracle joins are declared using (+) sign.

for ex--

Left Join---

 WHERE  

                 a.emp_id (+)=b.emp_id

     and      b.emp_departmet_id(+)=c.emp_departmet_id;

RIGHT Join---

 WHERE  

                 a.emp_id=b.emp_id(+)

     and      b.emp_departmet_id=c.emp_departmet_id(+);

***When u use (+) sign it means that query retrive all records from that table and only saimilar records from other  table**

NOTE---Joins syntax in SQL and ORACLE are diffrent.

to see JOINS of SQL u can VISIT THIS site....

http://www.w3schools.com/sql/sql_join_left.asp

I hope u will get more info from there.

i also refer that site.

Thank you............................

VIEW's in sql /oracle

Basic Info of VIEWè

·         A view is a virtual table.

·         It is based on the result-set of an SQL statement.

·         A view contains rows and columns, just like a  table.

·         View’s are created from table or another view.

·         The fields in a view are fields from one or more l tables in the database.

·         You can add SQL functions, WHERE, and JOIN statements to a view.

Difference Between TABLE and VIEWè

·         A table has a set of definition, and it physically stores the data.

·         A view also has a set of definitions, which is build from  table or other view,  

and it does not physically store the data.

SYNTAX->

CREATE or replace VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

EXAMPLE->

CREATE or replace VIEW  test1_v

AS

Select empno , ename ,salary

From emp

Where salary>5000;

- The above example create a view(test1_v) from table (emp).

-  Like table you can select view using

  “Select * from view_name”

Ex- select * from test1_v;

Result=>

EMPNO          ENAME          SALARY

1                      AMOL             15000

2                      ROHIT             10000

3                      VEENA           12000

Assign the "responsibility" to user using plsql code.(this blog is refered from another blog ORACLE GURU")

-----------------------------------------------------------------------------------------------------
DECLARE
  l_user_id NUMBER;
CURSOR cur_rec
IS
SELECT * FROM fnd_responsibility_vl
                           WHERE UPPER (responsibility_name) = UPPER ('AE HRMS Manager');
BEGIN
    SELECT user_id INTO  l_user_id   FROM fnd_user
    WHERE user_name like '%ROHIDAS%123';

    FOR rec_cur IN cur_rec
    LOOP
    fnd_user_resp_groups_api.insert_assignment (
                                                  user_id                                => l_user_id,
                                                  responsibility_id                   => rec_cur.responsibility_id,
                                                  responsibility_application_id => rec_cur.application_id,
                                                  security_group_id                => 0,
                                                  start_date                            => SYSDATE - 1,
                                                  end_date                             => NULL,
                                                  description                           => NULL
                                                 );
                                            COMMIT;
    END LOOP;
END;

------------------------------------------------------------------------------------------------------------

How to register your own table in oracle apps...database

Hi frnd the following code(plsql procedure) is useful to you to register your own table in oracle apps.(Mostly it is usful to you when you work in alert management in oracle and want to creat alert on your own table)
---------------------------------------------------------------------------------------------------------

STEP--1-first you create your table. and add data in table.
Ex---
create table my_alert(item varchar2(10), qty number);


------------------------------------------------------------------------------
STEP--2-then run the following code..it will creat the procedure  "Proc_ad_ad".

CREATE OR REPLACE PROCEDURE proc_ad_ad (ap_sname VARCHAR2, ptable_name VARCHAR2)
  IS
     CURSOR c1  IS
      SELECT ROWNUM, column_name, data_type, data_length
      FROM all_tab_columns
      WHERE table_name = upper(ptable_name);

  va c1%ROWTYPE;
  BEGIN
      ad_dd.register_table (ap_sname, ptable_name, 'T');

    OPEN c1;

      LOOP
          FETCH c1  INTO va;

      EXIT WHEN c1%NOTFOUND;
         ad_dd.register_column(ap_sname,ptable_name,va.column_name,va.ROWNUM,va.data_type,va.data_length,'Y','N');
DBMS_OUTPUT.put_line ( va.ROWNUM ||va.column_name||va.data_type||va.data_length);
      END LOOP;

    CLOSE c1;

COMMIT;
END proc_ad_ad;
/
show error;
------------------------------------------------------------------------------------------------------------------
STEP-3
register table using following code.

begin
proc_ad_ad('PO', 'my_alert');
end;
--------------------------------------------------------

we provide 2 parameter to this procedure
1.Application Name (Ex.PO,AP,etc)
2.Table Name.(Ex-My_aalert).
-------------------------------------------------------

How to find Suppliers/vendors Bank name(and other bank related info). ..in PAYABLE MANGER responsibility

Required Tables  =>
        1) ap_bank_account_uses_all
        2) ap_bank_accounts_all

        3) ap_bank_branches 

        4) po_vendors 

        5) po_vendor_sites_all

-----------------------------------------------------------------
SELECT
       pv.vendor_name
       ,pvs.vendor_site_code
       ,abb.bank_name bank_name


FROM
        ap_bank_account_uses_all   abau
       ,ap_bank_accounts_all          aba
       ,ap_bank_branches               abb
       ,po_vendors                          pv
       ,po_vendor_sites_all              pvs


WHERE
            abau.external_bank_account_id = aba.bank_account_id
AND    aba.bank_branch_id        = abb.bank_branch_id
AND    abau.vendor_id            = pv.vendor_id
AND    abau.vendor_id            = pvs.vendor_id
AND    abau.vendor_site_id       = pvs.vendor_site_id

Find Concurrent Program information in ORACLE APPS using concurrent "program id"

 --------------------------------------------------------------

You will get the follwing inforamtion.=>>

 

execution_file_name
executable_id

concurrent_program_id
concurrent_program_name

responsibility_name

Useful Tables:--

1-FND_CONCURRENT_PROGRAMS 

2-,FND_EXECUTABLES 

3-fnd_request_group_units

4-fnd_responsibility_tl 

-------------------------------------------------------------------
select distinct
        b.executable_name
       ,b.execution_file_name
       ,b.executable_id
       ,a.concurrent_program_id
       ,a.concurrent_program_name
       ,d.responsibility_name
from
       FND_CONCURRENT_PROGRAMS  a
      ,FND_EXECUTABLES b
      ,fnd_request_group_units c
      ,fnd_responsibility_tl d
where
           a.executable_id=b.executable_id
    and c.application_id=d.application_id
    and a.concurrent_program_id=198703;

--PROGRAM TO READ DATA FROM FILE BY USING utl_file----

HI frndz,,here we c how to read file data in plsql........
--In SHORT THIS IS A FILE HANDLING---
---THIS IS SAME AS .NET ,,c,FILE HANDLING----
--PROGRAM TO READ DATA FROM FILE BY USING utl_file----
------------------------------------------------------------------------------------------------------------------------------------
/*first create a diretctory name as My_DIR(giv any name)  */
CREATE OR REPLACE DIRECTORY My_DIR AS '/u01/dbtst/db/tech_st/11.1.0/appsutil/outbound/TST_pn-vm000001';  (run this script)
/*after creating directory assign some permition to tht directory so any user can access tht directory*/
GRANT read, write ON DIRECTORY ROHIT_DIR TO PUBLIC; ( run this script)
then put your file in that directoy wich u want to read... (here we use "test.txt" file)

----------------------------------------------------------------------------------------------
here is the plsql code to read file data wich is present in directory "My_DIR".
 ------------------------------------------------------------------------------------------------
set serveroutput on
DECLARE
------here we create one object of utl_file.file_type, it name as "handle"(step 1)----

handle  utl_file.file_type;
line  VARCHAR2(250);
BEGIN

--here we open the file in read mode on wich we want to work and assign tht to the our object "handle"(step 2)--
  handle := utl_file.fopen('My_DIR', 'test.txt', 'R');
  LOOP
    BEGIN
  
    --we use utl_file.get_line() function to read file line by line and save tht line data in variable "line" wich is declared above(step 3)--
      utl_file.get_line(handle, line);
      dbms_output.put_line(line);
      --utl_file.get_line() funcion used to write the read data()--
     
    EXCEPTION
      WHEN OTHERS THEN
        EXIT;
    END;
  END LOOP;
 
  --after all operation close the file(step 4)--
  utl_file.fclose(handle);
END fopen;
/

create a query to List all the Customers and Customer sites specific to each Operating Unit...

Hi frndz if u want to find customer details of each operating unit in oracle apps then u can use following query..
----------------------------------------------------------------------------------------------------------------------   
select  distinct
         -- ,b.org_id
         --a.organization_id
         -- ,c.party_id
         a.name                     as OPERATING_UNIT
        ,d.party_name               AS CUSTOMER_NAME
        ,d.party_number             AS CUSTOMER_NUMBER
        ,c.PARTY_SITE_NAME          AS SITE_NAME
        ,d.ADDRESS1                 AS ADDRESS
        ,d.CITY
        ,d.STATE
        ,d.POSTAL_CODE
        ,d.STATUS
from
          HR_OPERATING_UNITS a
         ,HZ_CUST_ACCT_SITES_ALL b
         ,HZ_PARTY_SITES c
         ,hz_parties d
where
         a.organization_id=b.org_id
    and  b.PARTY_SITE_ID= c.PARTY_SITE_ID
    and  c.party_id=d.party_id
    and  c.party_site_name is not null
order by
         d.party_name              
         ,d.party_number
        ,d.status;

Create a simple cursor

Cursor is nothing but th pointer..
stpes to create and use the cursor
1) DECLARE THE CURSOR.
2)OPEN THE CURSOR
3)FETCH THE CURSOR
4)CLOSE THE CURSOR.

Ex---create a cursor to disply the all name of emplpoee form emp table..


set serveroutput on
-------declare cursor-------------declare
cursor emp_cur
IS select ename from emp;
emp_rec emp_cur%rowtype;
begin
---------open the cursor----------open emp_cur;
----------Fetch the cursor--------fetch emp_cur into emp_rec;
dbms_output.put_line('NAME=>'||emp_rec.ename);
-----------close the cursor---------
close emp_cur;
end;
/
--------------------------------

Query to find any Highest no.

Here i write a simple query to find any highest no from a list ..

for example--
Table_name---EMP

ename     salary
1            10,000
2             2000
3            5000
4            6000
5            7000

if u want to find 2nd highest salary from  emp table then...
-------------------------------------------------------------------
select min(sal)
           from ( select sal from emp order by sal desc)
  where rownum<=:2;
----------------------------------------------------------------------
output will show lyk this
SAL
7000.
----------------------------------------------------------------------
Detail explanation--

1)..1st u can sort the all record in descending order
at this stage SAL  look lyk this
10000
7000
6000
5000
2000

2) then u get th "highest" salary at 1st row,,,2nd hieghest at 2nd row, 3rdhieghest at 3rd row,onwords...

3)then select only 2nd row ...coz it disply 2nd hieghest salary....



IF U WANT TO FIND 3RD HIEGHEST SALARY THEN INSTEAD OF ROWNUM:=2 U CAN PUT ROWNUM:=3 IN QUERY ......

But Problem will occure If there are same value in two rows..

EX-
 salary
10000
7000
7000

5000
2000
 in above table2nd and 3rd column contain same value...
and if u need to show both row
then u can use following query-----
-------------------------------------------------------------------------- 
select sal
    from emp
    where sal=(
                       select min(sal)
                            from ( select sal from emp order by sal desc)
                               where rownum<=:2
                     );
-----------------------------------------------------------------------------------------------





hi frndz i hop this query will help u if any one ask u to find any heighest salary......

this is comman question asked for fresher during technical interview.....