Oracle Apps Ocean

Wednesday, April 5, 2017

Save Exception or Bulk Exceptions in bulk collections

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tab IS TABLE OF exception_test%ROWTYPE;

  l_tab          t_tab := t_tab();
  l_error_count  NUMBER;
  
  ex_dml_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(ex_dml_errors, -24381);
BEGIN
  -- Fill the collection.
  FOR i IN 1 .. 100 LOOP
    l_tab.extend;
    l_tab(l_tab.last).id := i;
  END LOOP;

  -- Cause a failure.
  l_tab(50).id := NULL;
  l_tab(51).id := NULL;
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE exception_test';

  -- Perform a bulk operation.
  BEGIN
    FORALL i IN l_tab.first .. l_tab.last SAVE EXCEPTIONS
      INSERT INTO exception_test
      VALUES l_tab(i);
  EXCEPTION
    WHEN ex_dml_errors THEN
      l_error_count := SQL%BULK_EXCEPTIONS.count;
      DBMS_OUTPUT.put_line('Number of failures: ' || l_error_count);
      FOR i IN 1 .. l_error_count LOOP
        DBMS_OUTPUT.put_line('Error: ' || i || 
          ' Array Index: ' || SQL%BULK_EXCEPTIONS(i).error_index ||
          ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
      END LOOP;
  END;
END;
/

Number of failures: 2
Error: 1 Array Index: 50 Message: ORA-01400: cannot insert NULL into ()
Error: 2 Array Index: 51 Message: ORA-01400: cannot insert NULL into ()

PL/SQL procedure successfully completed.

Bulk Binding concept with Example

The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. To test bulk binds using records we first create a test table.

CREATE TABLE forall_test (
  id           NUMBER(10),
  code         VARCHAR2(10),
  description  VARCHAR2(50));

ALTER TABLE forall_test ADD (
  CONSTRAINT forall_test_pk PRIMARY KEY (id));

ALTER TABLE forall_test ADD (
  CONSTRAINT forall_test_uk UNIQUE (code));

The following test compares the time taken to insert 10,000 rows using regular FOR..LOOP and a bulk bind.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

  l_tab    t_forall_test_tab := t_forall_test_tab();
  l_start  NUMBER;
  l_size   NUMBER            := 10000;
BEGIN
  -- Populate collection.
  FOR i IN 1 .. l_size LOOP
    l_tab.extend;

    l_tab(l_tab.last).id          := i;
    l_tab(l_tab.last).code        := TO_CHAR(i);
    l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
  END LOOP;

  EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

  -- Time regular inserts.
  l_start := DBMS_UTILITY.get_time;

  FOR i IN l_tab.first .. l_tab.last LOOP
    INSERT INTO forall_test (id, code, description)
    VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
  END LOOP;

  DBMS_OUTPUT.put_line('Normal Inserts: ' || 
                       (DBMS_UTILITY.get_time - l_start));
  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

  -- Time bulk inserts.  
  l_start := DBMS_UTILITY.get_time;

  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO forall_test VALUES l_tab(i);

  DBMS_OUTPUT.put_line('Bulk Inserts  : ' || 
                       (DBMS_UTILITY.get_time - l_start));

  COMMIT;
END;
/
Normal Inserts: 305
Bulk Inserts  : 14

PL/SQL procedure successfully completed.

Bulk Collect concept with Example

SET SERVEROUTPUT ON
DECLARE
  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab := t_bulk_collect_test_tab();
  l_start  NUMBER;
BEGIN
  -- Time a regular population.
  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT *
                  FROM   bulk_collect_test)
  LOOP
    l_tab.extend;
    l_tab(l_tab.last) := cur_rec;
  END LOOP;

  DBMS_OUTPUT.put_line('Regular (' || l_tab.count || ' rows): ' || 
                       (DBMS_UTILITY.get_time - l_start));
  
  -- Time bulk population.  
  l_start := DBMS_UTILITY.get_time;

  SELECT *
  BULK COLLECT INTO l_tab
  FROM   bulk_collect_test;

  DBMS_OUTPUT.put_line('Bulk    (' || l_tab.count || ' rows): ' || 
                       (DBMS_UTILITY.get_time - l_start));
END;
/
Regular (42578 rows): 66
Bulk    (42578 rows): 4

PL/SQL procedure successfully completed.

SQL>

Thursday, December 25, 2014

Fill employee Hierarchy (Approval Hierarchy) -functional setups

PO (Approval Hierarchy)

+=+=+=+=+=+=+=+=+=+=+

Step1 :- Create Employees & Users

step2 :- Create Job & Possition and Assingment

USER EMP_NAME JOB POSSITION APPROVAL_GROUP APPROVAL_ASSINGMENT

azzez123 azeez 007.purchasing 007.Clerk 10000 Standarad PO

raghu123 Raghu 007.purchasing 007.JrMgr 30000 Standarad PO

balu123 Balu 007.purchasing 007.SrMgr 50000 Standarad PO

Create Approval Hierarchy:-

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

REL PO AH

007.SrMgr

007.JrMgr

007.Clerk

Azeez is having Authority to approve up to 10000 if at all the PO is between 10000 and 30000 then PO has to go to Raghu Automatically.

Similarly 30000 to 50000 PO has go to Balu automatically.

Step3:- Run the Program 'Fill employee Hierarchy'

------------------------------------------------- Over of Process is Ends Here ------------------------------------------------------------

Step1:- Create employee

Switch Responsibility to HRMS

Navigation àWork Structure àJob àDescription à New

Job:- 007.Purchasing

Setp2:- Create Position and assign Job

Navigation àWork Structure à Position àDescription à New

Date Effective Name =

Position Number =007

Position Name = Clerk

Organization = REL BG [Business Group]

Job =007.Purchasing

Status = Active

Click on hiring Information FTE=1

Step 3:- Create employee assign Job &position

Job & position

Navigation àPeople àEnter and maintenanceà New

Last Name = Azeez

Gender = Male

Action = Create Employee

Person type of action = employee

National Identifier =123-0002-09

DOB = 10-JUN-1988

(SAVE)

Click on Assignments àPosition=007.CLERK àCorrection àYes [Job will be populate automatically and save]

Step 4- Create Approval Groups

Navigation àSetup à Approvals àApproval Groups

Name = CLERK_AG

Document Total =10,000

Actual Range =10,000 Low 00.000.0000

High99.999.9999

Step 5:-

Create Approval Assignments

Navigation àSetup àApproval àApproval Assignments

Position = 007.CLEARK

Document Type Approval Group

AP Prove Standard purchase Orders CLERK_AG 19-Nov-2013 Save.

Make the Employee has buyer

Navigation àSetup à Personal àBuyers

Add Buyer :-

Azeez (Save)

Step 6:-

Create the user

Switch Responsibility to System Administrator

Navigation à Security àUser à Define

User Name = Azeez123

Password = Welcome1323/Re-enter

Person=Azeez

Responsibility = REL Purchasing CSU.

Repeat above steps for two move employees.

Step 7

Create the Approval hierarchy

Switch Responsibility to HRMS

Navigation àWork Structures àPosition àHierarchy

Name = REL PO AH (Save)

Date =19-Nov-2013 (Save)

Query position Name with

007. SRMANAGER

Sub Ordinates

Name Name

007.JRMGR Raghu à Click Down

Against the Raghu

Name Name Number

007.Clerk Azeez 2

(Save) and Close

Step 8 :-

Assign position hierarchy to standard purchase order

Switch Responsibility to

“REL Purchasing CSU”

Navigation à setup à purchasing àDocument type

Click on update “Standard Purchase order”

Default hierarchy = REL PO AH

ü Owner can Approve

[How create PO can approve]

Step9:-

Run the program “Fill Employee Hierarchy”

Navigation àview àrequest àSubmit a request àok.

Name = Fill Employee Hierarcy

OU = REL OU

Submit ànoà fin.

Create standard purchase order.

Navigation àpurchase order àpurchase order

Supplier = select supplier

Site = select site

Currency = INR

Lines

Inter = Sony laptop – a029

Qrty=3 price 1000 àshipments

Num=1,org=RCI,ship_to=REL-India CI , UOM=Each, qutity =3, àdistributions.

Qty=3, po charge account =01.000.2225

Close àdistribution & shipments

Azeez can approve on its won because upto 10000 he can be approve.

Crete on more purchase order more than 10,000 less than 30,000

è Po=1010

àapproveàokàit won’t be approve as limit crosses ten thousands we need to forward this one to superior.

ü Forward

Below filed with be populated

Automatically.

Forward from = azeez

Path = REL PO AH

Query with PO=1010 and approve.

Thursday, December 18, 2014

Function to calculate Header fill rate in OM

CREATE

OR REPLACE FUNCTION APPS.xx_mother_rep(p_order_number varchar2)

RETURN NUMBER

IS

/*==================================================================================

||

|| PL/SQL Code

||

|| Code Type: function Body

||

|| Filename: APPS.xx_mother_rep

||

|| Author: Swapnil Waydande

||

|| Purpose: Caluculate Header Fill Rate For Mother Report

||

|| Parameters:

|| p_order_number in VARCHAR2

||

|| Version History:

||

|| Name Version Reason for Change Date

|| ------------ --------- --------------------- --------------------

|| Swapnil Waydande 1.0 Creation 12-Dec-2014

==================================================================================*/

l4
NUMBER;

l2
NUMBER;

l3
NUMBER;

l5
VARCHAR2 (20);

CURSOR cur

IS

SELECT SUM (a.reserved_quantity) l2, SUM (a.ordered_quantity) l3--, --a.order_number l5

FROM kvont_compr_so_rpt_line a

WHERE order_number = p_order_number--10043691

GROUP BY a.order_number;

BEGIN

FOR i IN cur

LOOP

if i.l2 != 0

THEN

l4
:=

TO_CHAR ((((i.l2) / (i.l3)

)

*
100

),

'9999999.99'

);

DBMS_OUTPUT.put_line
('fillrate'||i.l2);

ELSE

l4
:= (i.l2) * 100;

END if;

-- DBMS_OUTPUT.put_line ('order_number' || i.l5);

DBMS_OUTPUT.put_line
(l4);

END LOOP;

RETURN l4;

END;

/

Saturday, December 13, 2014

How to Develop Custom API

/************** Developing Custom API ****************/

Assume xxkl_fedi_tbl is table with following columns
fedi_id,file_name,doc_id,container_type,container_size,container_status,amount,job_status,creation_date,created_by,last_update_date,last_update

d_by

create or replace package xxkl_fedi_api
-- Specifications
is
-- ************** defining table record type ***************** ---
-- xxkl_fedi_row_ rec xxkl_fei_tbl%towtype;
-- ************** defining custom record type ***************** ---
type xxkl_fedi_type is record(fedi_id number,
file_name varchar2(8),
doc_id number,
container_type varchar2(90),
container_size number,
container_status varchar2(1),
amount number,
job_status varchar2(1),
creation_date date,
created_by number,
last_update_date date,
last_updated_by number
);
begin
procedure create_fedi_record(xxkl_fedi_rec in xxkl_fedi_type,x_return_status out varchar2,x_fedi_id out number);
proceudre validate_fedi_record(fedi_id in varchar2,xxkl_fedi_rec in xxkl_fedi_type,x_return_status out varchar2);
end xxkl_fedi_api;
/

create or replace package body xxkl_fei_api
-- Implementaion
is
begin
procedure create_fedi_record(xxkl_fedi_rec in xxkl_fedi_type,x_return_status out varchar2,x_fedi_id out number)
is
begin
--new record insert.
INSERT INTO xxkl_fedi_tbl(fedi_id,
file_name,
doc_id,
container_type,
container_size,
container_status,
amount,
job_status,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES(xxkl_fedi_S.nextval, --sequence
xxkl_fedi_rec.file_name,
xxkl_fedi_rec.doc_id number,
xxkl_fedi_rec.container_type,
xxkl_fedi_rec.container_size,
xxkl_fedi_rec.container_status,
xxkl_fedi_rec.amount,
xxkl_fedi_rec.job_status,
xxkl_fedi_rec.creation_date,
xxkl_fedi_rec.created_by,
xxkl_fedi_rec.last_update_date,
xxkl_fedi_rec.last_updated_by
);
x_return_status := 'S';
x_fedi_id := :currval.fedi_id;
-- implement logic if insert statement fail and display following message.
-- x_return_status := 'F - insert is failed due not some error message ';
--
end create_fedi_record;
/

proceudre validate_fedi_record(fedi_id in varchar2,xxkl_fedi_rec in xxkl_fedi_type,x_return_status out varchar2)
-- implement
is
begin
null;
end validate_fedi_record;
/
end xxkl_fedi_api;
/

Composite Types (Record type)

Composite Type:-
=============

Composite type allows you to group the variables. Composite type are two type.

1. Record type. (one row with n columns).
2. Collections or pl/sql tables.

/************************** Record Type ******************************/

It allows you to group the variables, all the variables may be same datatype or differenet datatype.

A)
create or replace procedure xx_po_record_row_type_prod(P_HEADER_ID number)
is
--************** defining table record type *****************---
po_headers_rec PO_HEADERS_ALL%rowtype;
--po_headers_rec is record type. PO_HEADERS_ALL%rowtype will create the variables with the same name of the columns in the

PO_HEADERS_ALL table and it will group all the variables into po_headers_rec
begin
SELECT * INTO po_headers_rec from PO_HEADERS_ALL where PO_HEADER_ID=P_PO_HEADER_ID; --only one row allowed.
-- here *(star) must be used as po_headers_rec is type of table_name%rowtype
dbms_output.put_line('PO Number :- ' || po_header_rec.segment1);
end xx_po_record_row_type_prod;
/

B)
create or replace procedure xx_vendor_record_type_prod(P_VENDOR_ID number)
is
--************** defining custom record type *****************---
type vendor_type is recode(vendor_id number,
segment1 varchar2(40), --vendor number
vendor_name varchar2(240),
vendor_type_lookup_code varchar2(240)
);
vendor_rec vendor_type;
--- vendor_type is user define type with custom columns.
begin
SELECT vendor_id,vendor_num,vendor_name,vendor_type_lookup_code INTO vendor_rec FROM ap_suppliers WHERE

venoder_id=p_vendor_id; --only one row allowed.
dbms_output.put_line('Supplier Num:- ' || vendor_rec.segment1);
dbms_output.put_line('Supplier Name:- ' || vendor_rec.vendor_name);
end xx_vendor_record_type_prod;
/