How to assign the values to a DB Collection (Array ) When it is defined as Out Parameter in the Procedure/Functions

-- Define the Object Types , Collections as Below 

CREATE OR REPLACE TYPE XX_SERIAL_NUMBER_OBJ AS OBJECT(
   SERIAL_NUMBER                   VARCHAR2(240)
 , STATIC FUNCTION GET_SERIAL_NUMBER
      RETURN XX_SERIAL_NUMBER_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_SERIAL_NUMBER_OBJ
IS
   STATIC FUNCTION GET_SERIAL_NUMBER
      RETURN XX_SERIAL_NUMBER_OBJ
   IS
   BEGIN
      RETURN XX_SERIAL_NUMBER_OBJ(NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_SERIAL_NUMBER_TBL AS TABLE OF XX_SERIAL_NUMBER_OBJ;
/
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE XX_ORG_REASSIGN_STATUS_OBJ AS OBJECT(
   ORG_ID                   VARCHAR2(50)
 , SERIAL_NUMBER           VARCHAR2(240)
 , PROCESS_STATUS       VARCHAR2(40)
 , ERROR_ID               VARCHAR2(240)
 , ERROR_MESSAGE           VARCHAR2(2000)
 , STATIC FUNCTION ORG_REASSIGN_STATUS_OBJ
      RETURN XX_ORG_REASSIGN_STATUS_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_ORG_REASSIGN_STATUS_OBJ
IS
   STATIC FUNCTION ORG_REASSIGN_STATUS_OBJ
      RETURN XX_ORG_REASSIGN_STATUS_OBJ
   IS
   BEGIN
      RETURN XX_ORG_REASSIGN_STATUS_OBJ(NULL
                                        , NULL
                                        , NULL
                                        , NULL
                                        , NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_ORG_REASSIGN_STATUS_TBL AS TABLE OF XX_ORG_REASSIGN_STATUS_OBJ;

--Define Procedure to assign the values to the Collection type

PRocedure xx_test_proc (
  p_serial_number_tbl IN xxrb_serial_number_tbl
, x_org_reassignment_status_tbl OUT NOCOPY xxrb_org_reassign_status_tbl)
IS 
--Define Local variable for the Object Type as below
org_reassign_status_obj xx_org_reassign_status_obj;

--Define Local variable for the Collection Type as below
xx_org_reassign_sts_tbl xx_org_reassign_status_tbl;
BEGIN
-- Initialize the local object type and DB table type 
org_reassign_status_obj := 

xx_org_reassign_status_obj.org_reassign_status_obj;

xx_org_reassign_sts_tbl:= xx_org_reassign_status_tbl( org_reassign_status_obj);

For i in p_serial_number_tbl.FIRST..p_serial_number_tbl.LAST 
LOOP

-- Write your core logic which will derive the values for the Columns which needs to assign to the object type as shown below
org_reassign_status_obj.org_id       := 102;
org_reassign_status_obj.serial_number:= 'XYZ';
org_reassign_status_obj.process_status := 'S';
org_reassign_status_obj.error_id       := 'ORA-123';

-- Use the extend Function foe the Array as shown below
 
xx_org_reassign_sts_tbl.EXTEND;

-- Assign the Local record type declared in the declare section as -- below 

xx_org_reassign_sts_tbl( xx_org_reassign_sts_tbl.COUNT) := org_reassign_status_obj;

END LOOP;

x_org_reassignment_status_tbl :=
xx_org_reassign_sts_tbl

EXCEPTION
WHEN OTHERS 
THEN
DBMS_OUTPUT.PUT_LINE('UNEXP_ERROR : '||SUBSTR(SQLERRM, 1, 250));
END;

How to Invoke the Database Object Types in Anonymous Blocks

 -- Create DB Object Types and Collections

CREATE OR REPLACE TYPE XX_SYNC_ORG_OBJ AS OBJECT(
   ORG_ID                   VARCHAR2(50)
 , STATIC FUNCTION GET_OBJECT
      RETURN XX_CC_SYNC_ORG_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_SYNC_ORG_OBJ
IS
   STATIC FUNCTION GET_OBJECT
      RETURN XX_SYNC_ORG_OBJ
   IS
   BEGIN
      RETURN XX_SYNC_ORG_OBJ(NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_SYNC_ORG_TBL AS TABLE OF XX_SYNC_ORG_OBJ;
/
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE XX_EBS_SYNC_OBJ AS OBJECT(
   INSTANCE_REFERENCE       VARCHAR2(50)
 , SERIAL_NUMBER            VARCHAR2(240)
 , SERVICE_START_DATE       DATE
 , SERVICE_END_DATE         DATE
 , SUPPORT_LEVEL            VARCHAR2(50)
 , PARTNER                  VARCHAR2(1)
 , NODE_CATEGORY            VARCHAR2(50)
 , ORG_ID                   VARCHAR2(50)
 , STATIC FUNCTION GET_OBJ
      RETURN XX_EBS_SYNC_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_EBS_SYNC_OBJ
IS
   STATIC FUNCTION GET_OBJ
      RETURN XX_EBS_SYNC_OBJ
   IS
   BEGIN
      RETURN XX_EBS_SYNC_OBJ(NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_EBS_SYNC_TBL AS TABLE OF XX_EBS_SYNC_OBJ;
/
--  Invoke it in anonymous Block...

DECLARE
   p_cc_org_tbl   xx_cc_sync_org_tbl := xx_cc_sync_org_tbl();
   x_ebs_sync_tbl xx_ebs_cc_sync_tbl;
   l_index           NUMBER := 1;
BEGIN
   -- Use Below Method When Using Hard coding
   -- p_cc_org_tbl.EXTEND;
   -- p_cc_org_tbl( 1):= xx_cc_sync_org_obj( '1212121');
   -- p_cc_org_tbl.EXTEND;
   -- p_cc_org_tbl( 2):= xx_cc_sync_org_obj( '123123213213');
   -- p_cc_org_tbl.EXTEND;
   -- p_cc_org_tbl( 3):= xx_cc_sync_org_obj( '1231232132131');

   -- Use Below Method When Using Dynamic fetch
    FOR i IN (SELECT DISTINCT attribute21
               FROM csi_item_instances
              WHERE NVL(active_end_date, SYSDATE) >= SYSDATE
                AND attribute21 IS NOT NULL)
   LOOP
     p_cc_org_tbl.EXTEND;
     p_cc_org_tbl( l_index) := xx_cc_sync_org_obj( i.attribute21);
     l_index                                   := l_index + 1;
   END LOOP;

   DBMS_OUTPUT.put_line(   'Table Count : '
                        || p_cc_org_tbl.COUNT);
 
   ebs_ks_cc_sync(p_cc_org_tbl, x_ebs_cc_sync_tbl);
END;

How to setup the Java For Oracle Application R12

https://drive.google.com/open?id=0B4o8DOFa11efRDQ3eVV1UkpDak0

https://drive.google.com/file/d/0B4o8DOFa11efRDQ3eVV1UkpDak0/view?usp=sharing

Script (API ) To add and revoke the User Roles in User Managment

1. Assign role to a user using an API

To assign role to a user using APIs, use the following API wf_local_synch.PropagateUserRole.

Select * from UMX_ALL_ROLE_VL --> Gives List of all available Roles with Role Key.

Example:

Begin
wf_local_synch.PropagateUserRole(
     p_user_name => '&USER_NAME',
     p_role_name => '&ROLE_KEY');
commit;
end;

2. Revoke an Indirect Responsibility

To revoke an indirect responsiblity (roles assigned using UMX) assignment to a user using APIs, use the following API Wf_local_synch.PropagateUserRole.

Example:

Begin
Wf_local_synch.PropagateUserRole(
       p_user_name => '&USER_NAME',
       p_role_name => '&ROLE_KEY',
       p_start_date=>'&Start_Date',
       p_expiration_date=>'&End_Date');
commit;
End;

Steps to get the Access to Integration Administrtor and SOA Monitor In Integration Repository for Integrated SOA Gateqway

1. Log in as a user with User Management Responsibility Like   Sysadmin.
2. Click “User”.
3. Put the User under “User Name” > go.
4. Then click “Update” button (Pencil).
5. Click “Assign Role”.
6. Under “Search By > Roles and Responsibilities” Put the Responsibilities like “System Integration Analyst” and Click “go”.
7. Check mark and click “Select”.
8. Put “Justification” and click “Save”.
9. Keep doing for from Step 5 to 8 for “System Integration Developer” and “Irep Administrator”.
10. Log out from Self Service and Log in as a user who been     assigned 3 roles.
11. Select Integrated SOA Gateway and should have the following    under “Integrated SOA      Gateway” menu.
* Integration Repository
* SOA Monitor
12. The function for Integration Repository and SOA Monitor should be visible.

Integrated SOA Gateway (Integration Repository)