key flexfields and descriptive flexfields

---key flexfield tables and links

select *from FND_ID_FLEXS

application_id      =   800

id_flex_code        =   pos

id_flex_name        =   position flexfield


select *From FND_ID_FLEX_STRUCTURES_VL where application_id=800 and id_flex_code ='POS'




select *from per_position_definitions where  position_definition_id      =   65889


id_flex_num

position_definition_id      =   65887


select *from hr_positions_f where trunc(creation_date)=trunc(sysdate)

position_id

position_definition_id      =    65887




select *from FND_ID_FLEXS where id_flex_code='POS'

application_id

id_flex_code

id_flex_name


select *From FND_ID_FLEX_SEGMENTS where application_id=800 and  id_flex_num=101





select *From FND_ID_FLEX_STRUCTURES where id_flex_code='POS' and id_flex_structure_code like 'INDIA_POSITIONS'



ID_FLEX_CODE    =       POS

ID_FLEX_NUM     =       101

APPLICATION_ID  =       800


id_flex_structure_code =  POSITION_FLEXFIELD




select *from FND_FLEX_VALUES where trunc(creation_date)=trunc(sysdate)


select *From FND_FLEX_VALUE_HIERARCHIES

========================================



select fif.application_id,
fif.id_flex_code,
fif.id_flex_name,
fifs.id_flex_num,
fis.id_flex_num,
ppd.SEGMENT1,
ppd.segment3,
hpf.position_id
from
fnd_id_flexs fif,
fnd_id_flex_structures fifs,
fnd_id_flex_segments fis,
per_position_definitions ppd,
hr_positions_f hpf
where hpf.position_definition_id=ppd.position_definition_id
and ppd.id_flex_num=fis.id_flex_num
and fifs.id_flex_structure_code='POSITION_FLEXFIELD'
and fifs.id_flex_code=fif.id_flex_code
and fifs.id_flex_num=fis.id_flex_num
and ppd.position_definition_id      =   65887




select fif.application_id,
fif.id_flex_code,
fif.id_flex_name,
fifs.id_flex_num,
fis.id_flex_num
from
fnd_id_flexs fif,
fnd_id_flex_structures fifs,
fnd_id_flex_segments fis
where
and fifs.id_flex_code=fif.id_flex_code
and fifs.id_flex_num=fis.ID_FLEX_num
and fifs.id_flex_structure_code='POSITION_FLEXFIELD'

select fif.application_id,
fif.id_flex_code,
fifs.id_flex_num,
fifs.id_flex_structure_code,
hpf.position_id,
fis.application_column_name,
ppd.position_definition_id

select *FROM
fnd_id_flex_segments fis,
fnd_id_flex_structures fifs,
fnd_id_flexs fif,
per_position_definitions ppd,
hr_positions_f hpf
where fis.id_flex_code=fifs.id_flex_code
and fis.id_flex_num=fifs.id_flex_num
and fifs.id_flex_structure_code='POSITION_FLEXFIELD'
and fif.id_flex_code=fis.id_flex_code
and ppd.position_definition_id=hpf.position_definition_id
and fifs.id_flex_num=ppd.id_flex_num
and hpf.position_id=64472




select *from fnd_id_flexs where  application_id=800 and id_flex_code='POS'

select *From fnd_id_flex_structures where id_flex_code='POS' and id_flex_structure_code='POSITION_FLEXFIELD'

SELECT *fROM FND_ID_FLEX_SEGMENTS WHERE ID_FLEX_CODE='POS' AND ID_FLEX_NUM=101




==================================



---descriptive flexfield tables and links

select *from apps.FND_DESCRIPTIVE_FLEXS where application_id=201

descriptive_flexfield_name      =       PO_REQUISITION_LINES

APPLICATION_ID                  =       201

APPLICATION_TABLE_NAME          =       PO_REQUISITION_LINES_ALL



select *from apps.FND_DESCR_FLEX_CONTEXTS where  descriptive_flexfield_name='PO_REQUISITION_LINES'

DESCRIPTIVE_FLEXFIELD_NAME      =       PO_REQUISITION_LINES

APPLICATION_ID                  =       201


SELECT *FROM apps.FND_DESCR_FLEX_COLUMN_USAGES WHERE DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_LINES'


APPLICATION_COLUMN_NAME

END_USER_COLUMN_NAME

DESCRIPTIVE_FLEX_CONTEXT_CODE

DESCRIPTIVE_FLEXFIELD_NAME



SELECT *FROM apps.FND_APPLICATION_TL


APPLICATION_ID

APPLICATION_NAME



SELECT FAT.APPLICATION_ID,FAT.APPLICATION_NAME,FDF.DESCRIPTIVE_FLEXFIELD_NAME,FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE
,FDFCU.APPLICATION_COLUMN_NAME ,
FDFCU.END_USER_COLUMN_NAME
FROM
FND_APPLICATION_TL FAT,
FND_DESCRIPTIVE_FLEXS FDF,
FND_DESCR_FLEX_CONTEXTS FDFC,
FND_DESCR_FLEX_COLUMN_USAGES FDFCU
WHERE FAT.APPLICATION_ID=FDF.APPLICATION_ID
AND FAT.APPLICATION_NAME='Purchasing'
AND FDF.DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_LINES'
AND FDFC.DESCRIPTIVE_FLEXFIELD_NAME=FDF.DESCRIPTIVE_FLEXFIELD_NAME
AND FDFCU.DESCRIPTIVE_FLEX_CONTEXT_CODE=FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND FDFCU.DESCRIPTIVE_FLEXFIELD_NAME=FDFC.DESCRIPTIVE_FLEXFIELD_NAME

pl-sql programs

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

create package by using functions and procedures and execute packages

 CREATE OR REPLACE PACKAGE inv_pck_spec as
        FUNCTION inv_count(qty integer) RETURN integer;
        PROCEDURE inv_adjust(qty integer);
   END inv_pck_spec;
   

desc employee
------------------------------------------------------

 CREATE OR REPLACE PACKAGE BODY inv_pck_spec is
       FUNCTION inv_count(qty integer)RETURN integer is
           new_qty integer;
       BEGIN
           new_qty:= qty*6;
            INSERT into employee (id,salary) values ('01',new_qty);
           RETURN(new_qty);
       END inv_count;

      PROCEDURE inv_adjust(qty integer) is
     BEGIN
        DELETE from employee WHERE salary<qty;
      END;
     BEGIN  -- package initialization begins here
         INSERT into employee (id, first_name)values('01', 'new');

   END inv_pck_spec;
   /
   show error
----------call function
declare
a integer;
begin
a:=inv_pck_spec.inv_count(2);
dbms_output.put_line(a);
end;

-------------call procedures
begin
inv_pck_spec.inv_adjust(2000);
end;
-----------
select *From employee
-----------------------

create package and execute packages

create or replace package pkg_test1
    is
        function getArea (i_rad NUMBER) return NUMBER;
        procedure p_print (a in number,b in number,c out number);
    end pkg_test1;

--------------------------------------
create or replace package body pkg_test1
    is
        function getArea (i_rad NUMBER)return NUMBER
        is
          v_pi NUMBER:=3.14;
       begin
          return v_pi * (i_rad ** 2);
       end;

       procedure p_print(a in number,b in number,c out number)
       is
       begin
           c:=a+b;
       end;
   end;
   ------------------------------------------
   \
   --execution of packages:
   declare
   a NUMBER; 
   i number;
   g number;
   h number;
   begin
   a:=pkg_test1.getarea(&num);
   pkg_test1.p_print(&g,&h,i);
   dbms_output.put_line('the area value is='||a||'********'||i||g);
   end;

auditing by using of triggers

CREATE TABLE DEPT12
  ( DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13)
  );

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

 INSERT INTO DEPT12 VALUES
  (20, 'RESEARCH', 'DALLAS' );
 INSERT INTO DEPT12 VALUES
  (30, 'SALES', 'CHICAGO' );
 INSERT INTO DEPT12 VALUES
  (40, 'OPERATIONS', 'BOSTON' );
 CREATE TABLE DEPT$AUDIT
  (
    DEPTNO      NUMBER           ,
    DNAME       VARCHAR2(14 byte),
    LOC         VARCHAR2(13 byte),
    CHANGE_TYPE VARCHAR2(1 byte) ,
    CHANGED_BY  VARCHAR2(30 byte),
    CHANGED_TIME DATE
  );
 
select *From dept$audit



 ---------------------------------------------------
 CREATE OR REPLACE TRIGGER auditDEPTAR AFTER
 INSERT OR
 UPDATE OR
 DELETE ON DEPT12 FOR EACH ROW DECLARE my DEPT$audit%ROWTYPE;
BEGIN
  IF inserting THEN
    my.change_type := 'I';
  elsif updating THEN
    my.change_type :='U';
  ELSE
    my.change_type := 'D';
  END IF;
  my.changed_by   := USER;
  my.changed_time := sysdate;
  CASE my.change_type
  WHEN 'I' THEN
    my.DEPTNO := :new.DEPTNO;
    my.DNAME  := :new.DNAME;
    my.LOC    := :new.LOC;
  ELSE
    my.DEPTNO := :old.DEPTNO;
    my.DNAME  := :old.DNAME;
    my.LOC    := :old.LOC;
  END CASE;
   INSERT INTO DEPT$AUDIT VALUES my ;
END;
-------------------------------------
INSERT INTO DEPT12 VALUES
  (70, 'ACCOUNTING', 'NEW YORK'
  );
  -------------------------
  TRIGGER auditDEPTAR Compiled.
1 rows inserted
DEPTNO                 DNAME          LOC           CHANGE_TYPE CHANGED_BY                     CHANGED_TIME             
---------------------- -------------- ------------- ----------- ------------------------------ -------------------------
70                     ACCOUNTING     NEW YORK      I           APPS                           01-OCT-11                

before insert or delete trigger

create table Employee(
     ID                 VARCHAR2(4 BYTE)         NOT NULL primary key,
     First_Name         VARCHAR2(10 BYTE),
    Last_Name          VARCHAR2(10 BYTE),
    Start_Date         DATE,
     End_Date           DATE,
     Salary             Number(8,2),
     City               VARCHAR2(10 BYTE),
    Description        VARCHAR2(15 BYTE)
  )
 
  insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values ('01','Jason',    'Martin',  to_date('19960725','YYYYMMDD'), to_date('20060725','YYYYMMDD'), 1234.56, 'Toronto',  'Programmer')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('02','Alison',   'Mathews', to_date('19760321','YYYYMMDD'), to_date('19860221','YYYYMMDD'), 6661.78, 'Vancouver','Tester')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('03','James',    'Smith',   to_date('19781212','YYYYMMDD'), to_date('19900315','YYYYMMDD'), 6544.78, 'Vancouver','Tester')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('04','Celia',    'Rice',    to_date('19821024','YYYYMMDD'), to_date('19990421','YYYYMMDD'), 2344.78, 'Vancouver','Manager')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('05','Robert',   'Black',   to_date('19840115','YYYYMMDD'), to_date('19980808','YYYYMMDD'), 2334.78, 'Vancouver','Tester')
  /
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('06','Linda',    'Green',   to_date('19870730','YYYYMMDD'), to_date('19960104','YYYYMMDD'), 4322.78,'New York',  'Tester')
  /
 
  /
 
 create or replace trigger emp_bid
     before insert or delete
     on employee
     referencing new as new old as old
    begin
       if to_char(sysdate,'Dy') in ('Sat','Sun') then
            raise_application_error(-20999,'No create/delete employees on weekend!');
        end if;
    end;
   /
  --After creating trigger when you are inserting into the tables means that
 
   insert into Employee(ID,  First_Name, Last_Name, Start_Date,End_Date,Salary,  City,Description)
  values('07','David',    'Larry',   to_date('19901231','YYYYMMDD'), to_date('19980212','YYYYMMDD'), 7897.78,'New York',  'Manager')
  /
  Error report:
SQL Error: ORA-20999: No create/delete employees on weekend!
ORA-06512: at "APPS.EMP_BID", line 3
ORA-04088: error during execution of trigger 'APPS.EMP_BID'

implicit cursor and using of sql%rowcount

declare
no NUMBER;
begin
delete from studs;
no:=sql%rowcount;
end;

answers:
It returns no of row in the studs table