Bulk Binds

You should probably be reading the update of this article here.
Oracle uses two engines to process PL/SQL code. All procedural code is handled by the PL/SQL engine while all SQL is handled by the SQL engine. There is an overhead associated with each context switch between the two engines. If PL/SQL code loops through a collection performing the same DML operation for each item in the collection it is possible to reduce context switches by bulk binding the whole collection to the DML statement in one operation.
First we create a test table.

CREATE TABLE test1(
  id           NUMBER(10),
  description  VARCHAR2(50));
  
ALTER TABLE test1 ADD (
  CONSTRAINT test1_pk PRIMARY KEY (id));
  
SET TIMING ON

The time taken to insert, update then delete 10,000 rows using regular FOR..LOOP statements is approximately 34 seconds on my test server.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := i;
    t_description(t_description.last) := 'Description: ' || To_Char(i);
  END LOOP;
  
  FOR i IN t_id.first .. t_id.last LOOP
    INSERT INTO test1 (id, description)
    VALUES (t_id(i), t_description(i));
  END LOOP;
    
  FOR i IN t_id.first .. t_id.last LOOP
    UPDATE test1
    SET    description = t_description(i)
    WHERE  id = t_id(i);
  END LOOP;
    
  FOR i IN t_id.first .. t_id.last LOOP
    DELETE test1
    WHERE  id = t_id(i);
  END LOOP;
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:38.00

Using the FORALL construct to bulk bind the inserts this time is reduced to 18 seconds.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := i;
    t_description(t_description.last) := 'Description: ' || To_Char(i);
  END LOOP;
  
  FORALL i IN t_id.first .. t_id.last
    INSERT INTO test1 (id, description)
    VALUES (t_id(i), t_description(i));
    
  FORALL i IN t_id.first .. t_id.last
    UPDATE test1
    SET    description = t_description(i)
    WHERE  id = t_id(i);
    
  FORALL i IN t_id.first .. t_id.last
    DELETE test1
    WHERE  id = t_id(i);
  
  COMMIT;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.05

A collection must be defined for every column bound to the DML which can make the code rather long winded, but the performance improvements more than make up for this.
Bulk binds can also improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. To show this we must first load our table with some data.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
BEGIN
  FOR i IN 1 .. 10000 LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := i;
    t_description(t_description.last) := 'Description: ' || To_Char(i);
  END LOOP;
  
  FORALL i IN t_id.first .. t_id.last
    INSERT INTO test1 (id, description)
    VALUES (t_id(i), t_description(i));

  COMMIT;
END;
/

Populating two collections with 10,000 rows using a FOR..LOOP takes approximately 1.02 seconds.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type          := id_type();
  t_description  description_type := description_type();
  
  CURSOR c_data IS
    SELECT *
    FROM   test1;
BEGIN
  FOR cur_rec IN c_data LOOP
    t_id.extend;
    t_description.extend;
    
    t_id(t_id.last)                   := cur_rec.id;
    t_description(t_description.last) := cur_rec.description;
  END LOOP;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.02

Using the BULK COLLECT INTO construct reduces this time to approximately 0.01 seconds.

DECLARE
  TYPE id_type          IS TABLE OF test1.id%TYPE;
  TYPE description_type IS TABLE OF test1.description%TYPE;
  
  t_id           id_type;
  t_description  description_type;
BEGIN
  SELECT id, description 
  BULK COLLECT INTO t_id, t_description FROM test1;
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

Bulk binding reduces the context switches between SQL and pl/SQL engines. It enhances the performance but thr memory consumption would be high.

Package for calling Bursting Program for Data template & reports

create or replace PACKAGE      XXJML_ASN_DOLLARS_20170724 AS
   CUST_NO  VARCHAR2(10);
   SHIPPING_DATE  DATE;
 function AfterReport(p_request_id number) return boolean;
END XXJML_ASN_DOLLARS_20170724;



PACKAGE BODY
==============
create or replace PACKAGE BODY      XXJML_ASN_DOLLARS_20170724 AS
function AfterReport(p_request_id number) return boolean
   is
     l_req_id number;
   begin
     l_req_id := fnd_request.submit_request('XDO', 'XDOBURSTREP', '','', FALSE, 'Y', p_request_id, 'Y', CHR(0));
     commit;
     if l_req_id =0
     then
       return false;
     else
       return true;
     end if ;
   exception
     when others
     then
       return FALSE;
   end AfterReport;
END XXJML_ASN_DOLLARS_20170724;

Query to get the Request Group for concurrent program

SELECT
  RG.APPLICATION_ID "Request Group Application ID",
  RG.REQUEST_GROUP_ID "Request Group - Group ID",
  RG.REQUEST_GROUP_NAME,
  RG.DESCRIPTION,
  rgu.unit_application_id,
  rgu.request_group_id "Request Group Unit - Group ID",
  rgu.request_unit_id,cp.concurrent_program_id,
  cp.concurrent_program_name,
  cpt.user_concurrent_program_name,
  DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
FROM
  fnd_request_groups rg,
  fnd_request_group_units rgu,
  fnd_concurrent_programs cp,
  FND_CONCURRENT_PROGRAMS_TL CPT
WHERE rg.request_group_id = rgu.request_group_id
  AND rgu.request_unit_id = cp.concurrent_program_id
  AND cp.concurrent_program_id = cpt.concurrent_program_id
  AND cpt.user_concurrent_program_name ='Packing Slip Report PDF Output';

to separate the column data into segments based on -

  SELECT
    rtrim(substr('80409-005-00W', 1, instr('80409-005-00W', '-')),'-') first_name ,
    rtrim(substr(ltrim(substr('80409-005-00W', instr('80409-005-00W', '-')),'-'),1,instr(ltrim(substr('80409-005-00W', instr('80409-005-00W', '-')),'-'),'-')),'-') middle_name,
    SUBSTR('80409-005-00W', Instr('80409-005-00W', '-', -1, 1) +1) part2
     FROM  dual;

CONCURRENT PROGRAM - ‘COPY TO ' Option

Copying the existing report  that  must be customized new report , by using 'Copy to ' method

Step1 :

Open the existing Concurrent program (Report name) in Application developer form

 Application Developer---> Concurrent Program

Then Query your existing concurrent program. Like 

                                                            Figure1: Open Existing Report Program

 Step2:

Click on “Copy Program button” selecting check box option “Copy Parameters”. This will help you copy    the current program definition to custom version of the program.
Also note down the name of the executable as it appears in concurrent program definition screen.

                                                           Figure2 : Concurrent Program Copy to Option

                                                             

                                                          Figure3 : Concurrent Program - Parameter

Then click ok , save it 

 Note: Note down the application within which it is registered. If the application is Oracle Purchase, then you must go to the database server and get hold the file named XXXX.rdf in $PO_TOP/reports/US.

Calling Request Set from PLSQL on Specific Time

CREATE OR REPLACE PROCEDURE XXAP_SUBMIT_REQUEST_SET (
P_errbuf    OUT VARCHAR2,
P_retcode   OUT NUMBER)
AS
V_REQUEST_SET_EXIST   BOOLEAN := FALSE;
req_id                INTEGER := 0;
l_CONC_PROG_SUBMIT    BOOLEAN := FALSE;
srs_failed            EXCEPTION;
submitprog_failed     EXCEPTION;
submitset_failed      EXCEPTION;
l_start_date          VARCHAR2(250);
BEGIN
fnd_file.put_line (fnd_file.LOG, 'Calling set_request_set…');
V_REQUEST_SET_EXIST :=
FND_SUBMIT.set_request_set (application   => 'EC',
request_set   => 'JM856ASNOUTBOUND');

IF (NOT V_REQUEST_SET_EXIST)
THEN
RAISE srs_failed;
END IF;

fnd_file.put_line (fnd_file.LOG, 'Calling submit program first stage');
l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('EC',
'JM856OUTBOUND_NEW2',
'All Reports');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('EC',
'JM856OUTBOUND_NEW1',
'All Reports');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('EC',
'XXJM856OUTBOUND_WLMRT',
'All Reports');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;

/*l_CONC_PROG_SUBMIT :=
fnd_submit.submit_program ('XXAP',
'XXAP_FOURTH_PROGRAM',
'STAGE40');

IF (NOT l_CONC_PROG_SUBMIT)
THEN
RAISE submitprog_failed;
END IF;*/

fnd_file.put_line (fnd_file.LOG, 'Calling submit_set…');

--–l_start_date is to schedule the request
SELECT TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS')
  into l_start_date
FROM dual
WHERE 1                            =1
AND TO_CHAR( SYSdate,'HH24:MI:SS') > '13:00:00'
AND TO_CHAR( SYSdate,'HH24:MI:SS') < '14:30:00'
AND  TO_CHAR(SYSDATE,'DY')         IN('MON','TUE','WED','THU','FRI');

req_id :=
FND_SUBMIT.submit_set (start_time    => l_start_date,
sub_request   => FALSE);

IF (req_id = 0)
THEN
RAISE submitset_failed;
END IF;
EXCEPTION
WHEN srs_failed
THEN
p_errbuf := 'Call to set_request_set failed: ' || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN submitprog_failed
THEN
p_errbuf := 'Call to submit_program failed: ' || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN submitset_failed
THEN
p_errbuf := 'Call to submit_set failed: ' || fnd_message.get;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
WHEN OTHERS
THEN
p_errbuf := 'Request set submission failed – unknown error: ' || SQLERRM;
p_retcode := 2;
fnd_file.put_line (fnd_file.LOG, p_errbuf);
END;

Account Payables (A.P) Module:-

           Account payables will be used to do the payment transactions. A.P Module is integrated with both P.O and G.L Modules. In Account Payables we will create the invoices and we will approve once invoice is approved successfully we will make the payment. Once payment is over we will move the transactions from A.P to G.l.

1.  Without supplier we cannot create Invoice.

2.  Without invoice we cannot make Payment.                                      

From the company point of view a person or Organization who is going to receive amount we will call as Supplier.

Types of Invoices:-

1.     Standard

2.     Credit Memo

3.     Debit Memo

4.     With Holding Tax                                                                                   

5.     Po Default

6.     Mixed

7.     Pre Payment

8.     Expense Report

9.     Recurring Invoices

10.  Quick Match                                                                                

Standard Invoice:-    We will create the Standard Invoice to particular Supplier and Supplier site we will enter the invoice amount, invoice date and soon……..

Credit Memo & Debit Memo Invoices:- Both Invoices has got negative (-ve) amount and adjusted against Standard Invoice. Credit Memo will be created whenever Supplier is giving discount. Debit Memo will be created if buyer is going to deduct the amount.

With Holding Tax Invoice:-      If supplier is not registered supplier then buyer will make the Income Tax to the government on behalf of supplier.

Po Default Invoice:-    Here we will create the Invoice as per Purchase Order amount. We will give the Po number system will retrieve PO amount and Invoice will be created as per PO details.

Prepayment Invoice:-    When ever we want make payment to supplier in advance that tome we will create this Prepayment Invoice and we make the Payment.

Expense Reports Invoice:-     It will be created for employee expenses as per the employee grade, position this Invoices will be calculated.

Recurring Invoice:-      For some of the Invoices we will not be having supplier invoice that time we will create Recurring Invoices.

Ex:-  For rent account we will be creating Invoice which has got fixed amount and fixed rate (duration).

 Quick Match Invoice:- While creating Purchase Order we will be giving the match approval option as per that match approval we will create the Invoice and the Invoice type is Quick Match Invoice.

Mixed Invoice:- Mixed Invoices will be created for miscellaneous expenses. Once we create the invoice you have to do following 3 activities.

1.     Validate Invoice

2.     Approve the Invoice

3.     Create Accounting entries for Invoice 

 INVOICES

Here we will select the Invoice type and we will give the Supplier number, name, site invoice date, invoice number, invoice currencies, and amount. Select Distributions button to distribute the Invoice amount into different accounts.

1.     Invoice total should be equal to the distributions total then we will call it as Invoice validated successfully.

2.     Select Actions…1 button chooses approve check box press OK then system will approve the Invoice.

3.     Select Actions…1 button choose create accounting check box press OK button it will create the accounting entries we can see all this accounting transactions from tools view accounting option.

SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_NUM='INV4516'  --INVOICE_ID=63379 ,--VENDOR_ID(LINK B/WAP INVOICE AND PO_VENDORS
)
SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID=63379

Invoice Holds:-    If invoice is not approved then that invoice will be keeping under hold status. By selecting holds button in invoice form we can see the holds details.

For view Invoice holds details:

           Select * from ap_holds_all

For view release the Invoice holds names:

           Select * from ap_holds_release_name_v;

PAYMENTS:

Payments:-     Once the Invoice is approved then we can go for payments. The Payments are or 3 types. They were

1.     Manual

2.     Quick

3.     Refund

Manual:-    Here we will issue the checks manually to the supplier and we will capture that information in the payment scheme by using manual payment option.

Quick:-     Through the Quick Payment type we can generate checks through the system and we can have the transactions directly in the system.

Refund:-   When ever company is going to give advance back to the customer that time we will select payment type as Refund.

Navigation steps for Payments:-

                 payments  ==> payments

For view list of payments:

           Select * from ap_invoice_payments_all;

           Select * from ap_payment_schedules_all;

For check’s information:

           Select * from ap_checks_all;

For check format:

           Select * from ap_check_formats;

           Select * from ap_checkrun_conc_processes_all;

Distribution Set:-     It is one of the option is available in Invoices Screen. While creating the Invoice we will attach distribution set. System will automatically create the transactions in distributions forms as per the distribution set.

 Navigation:

 set-up =>invoice=> distribution set

To view Distribution sets at header level:

           Select * from ap_distribution_sets_all;

To view Distribution sets at lines level:

           Select * from ap_distribution_set_lines_all;

Transferring Transactions from AP to GL:-

           We will execute the concurrent program from SRS Window. This program will transfer all the payment transactions into the G.L Module. It will take following parameters.

Program Name:-   Payables Transfer to General Ledger

Parameters:-

           Set of Books Name

           Transfer Reporting Book(s)

           From Date

           To Date

           Journal Category

Validate Accounts

Transfer To GL Interface

Submit Journal Import :  yes  (It should be always YES)

To view from AP to GL:

           Select * from gl_interface;

To view journal import details:

           Select * from gl_je_headers  à       for Headers

           Select * from gl_je_lines      à       for Lines

           Select * from gl_je_batches  à       for Batches

To view posting:

          

           Select * from gl_balances;

          

           After submitting the request select viewà output button. It will shows number of transactions has been transferred to G.L. then select G.L Module (General Ledger, Vision Operations (USA)).

SELECT * FROM GL_JE_HEADERS

SELECT * FROM GL_JE_LINES

SELECT * FROM GL_JE_BATCHES

SELECT * FROM GL_BALANCES