How to use VArray in PLSQL

--Example for VArray-------------
==========================================
DECLARE
  TYPE abc IS VARRAY(20) OF NUMBER;
  v1 abc;
BEGIN
  v1 := abc();
  v1.extend();
  v1(1) := 1234;
  v1.extend();
  v1(2) := 5678;
  v1.extend();
  v1(3) := 7890;
  dbms_output.put_line('FIRST VALUE ' || v1(1));
  dbms_output.put_line('SECOND VALUE ' || v1(2));
  dbms_output.put_line('THIRD VALUE ' || v1(3));
END;
--------
Output
--------
FIRST VALUE 1234
SECOND VALUE 5678
THIRD VALUE 7890



===========================================
DECLARE
  TYPE abc IS VARRAY(20) OF VARCHAR2(100);
  v1 abc;
BEGIN
  v1 := abc();
  v1.extend();
  v1(1) := 'DEMO FOR';
  v1.extend();
  v1(2) := 'VARRAY';
  v1.extend();
  v1(3) := 'EXAMPLE';
  dbms_output.put_line('FIRST VALUE: ' || v1(1));
  dbms_output.put_line('SECOND VALUE: ' || v1(2));
  dbms_output.put_line('THIRD VALUE: ' || v1(3));
END;
------
Output
------
FIRST VALUE: DEMO FOR
SECOND VALUE: VARRAY
THIRD VALUE: EXAMPLE



===========================================
-- USING LOOP--
DECLARE
  CURSOR c1 is
    select vendor_name from ap_suppliers where rownum <= 20;
  --
  TYPE abc IS VARRAY(20) of VARCHAR2(100);
  v1 abc;
  n  number := 1;
  --
BEGIN
  v1 := abc();
  FOR i in c1 loop
    v1.extend();
    v1(n) := i.vendor_name;
    dbms_output.put_line('Vendor Name ' || v1(n));
    n := n + 1;
  END LOOP;
END;
------
Output
------
Vendor Name: Paresh Consulting
Vendor Name: Price Service Co.
Vendor Name: Umesh Damle
Vendor Name: Vijay Kadam
Vendor Name: Kerala Spa
Vendor Name: The Great Punjab Hotel
Vendor Name: Metal Trading Company
Vendor Name: Satish Cartes
Vendor Name: George Ltd
Vendor Name: Hetal Group Ltd
Vendor Name: Human Resource Consultancy
Vendor Name: MRF Ltd
Vendor Name: Vertex Interactive
Vendor Name: Steve Follie
Vendor Name: Communications Services Inc
Vendor Name: J D Service Co.
Vendor Name: J M Consulting
Vendor Name: Vishwas Patil
Vendor Name: Shirish kadam
Vendor Name: Jivan anand

============================================
DECLARE
  TYPE abc is VARRAY(20) OF NUMBER(10);
  v1 abc;
  n  NUMBER := 1;
BEGIN
  v1 := abc();
  for i in 1 .. 10 loop
    v1.extend();
    v1(i) := i;
    dbms_output.put_line(v1(i));
    dbms_output.put_line('count: ' || v1.count);
    dbms_output.put_line('First: ' || v1.first);
    dbms_output.put_line('Last: ' || v1.last);
  END LOOP;
END;

------
Output
------
1
count: 1
First: 1
Last: 1
2
count: 2
First: 1
Last: 2
3
count: 3
First: 1
Last: 3
4
count: 4
First: 1
Last: 4
5
count: 5
First: 1
Last: 5
6
count: 6
First: 1
Last: 6
7
count: 7
First: 1
Last: 7
8
count: 8
First: 1
Last: 8
9
count: 9
First: 1
Last: 9
10
count: 10
First: 1
Last: 10

AP (Payable) Invoice and Payment details Query

Below given query will fetch details for Payable invoice along with its payments details.
It will fetch details like invoice number, currency code, date, supplier name, invoice amount, line amount, term name, check number ,PO number, payment details, operating unit and ledger details.

Query :

select aia.invoice_num,      
       aia.invoice_currency_code,
       aia.invoice_date,
       aps.vendor_name,
       aila.line_number,
       aia.invoice_amount,
       aila.amount line_amount,
       pha.segment1 po_number,
       aila.line_type_lookup_code,
       apt.name Term_name,      
       gcc.concatenated_segments distributed_code_combinations,
       aca.check_number,
       aipa.amount payment_amount,
       apsa.amount_remaining,
       aipa.invoice_payment_type,
       hou.name operating_unit,
       gl.name ledger_name  
  from apps.ap_invoices_all         aia,
       ap_invoice_lines_all         aila,
       ap_invoice_distributions_all aida,
       ap_suppliers aps,
       po_headers_all pha,
       gl_code_combinations_kfv gcc,
       ap_invoice_payments_all aipa,
       ap_checks_all aca,
       ap_payment_schedules_all apsa,
       ap_terms apt,
       hr_operating_units hou,
       gl_ledgers gl
 where aia.invoice_id = aila.invoice_id
   and aila.invoice_id = aida.invoice_id
   and aila.line_number = aida.invoice_line_number
   and aia.vendor_id=aps.vendor_id
   and aia.po_header_id=pha.po_header_id(+)
   and aida.dist_code_combination_id=gcc.code_combination_id
   and aipa.invoice_id(+)=aia.invoice_id
   and aca.check_id   (+)=aipa.check_id
   and apsa.invoice_id=aia.invoice_id
   and apt.term_id=aia.terms_id
   and hou.organization_id=aia.org_id
   and gl.ledger_id=aia.set_of_books_id
   and aia.invoice_num=&p_invoice_num

 
 
 


 
 
 

Query to find Short text documents (attachment)

Below given query will give you details for short text document attachment details.
In this query i passed 2 parameter p_entity_name and p_prim_key_id.
One can pass p_entity_name parameter value as "OE_ORDER_HEADERS" then we must pass p_prim_key_id parameter value as header_id from OE_ORDER_HEADERS_ALL table.

Similarly you can pass p_entity_name value as 'PO_HEADERS', 'PO_LINES', 'OE_ORDER_HEADERS', 'OE_ORDER_LINES','AP_INVOICES','AP_CHECKS'  etc. to get details for short text attached to this entity.

Query :
SELECT fad.seq_num,
       fdct.user_name,
       fdot.title,
       fdot.description,
       fdst.short_text,
       fad.entity_name,
       fdct.user_name
  FROM fnd_attached_documents     fad,
       fnd_documents              fdob,
       fnd_documents_tl           fdot,
       fnd_document_categories_tl fdct,
       fnd_documents_short_text   fdst
 WHERE fad.document_id = fdob.document_id
   AND fad.document_id = fdot.document_id
   AND fdob.document_id = fdot.document_id
   AND fdob.category_id = fdct.category_id
   AND fdot.language = userenv('LANG')
   AND fdct.language = userenv('LANG')
   AND fad.entity_name = &p_entity_name
   AND fad.pk1_value = TO_CHAR(&p_prim_key_id)
   AND fdst.media_id = fdob.media_id;

Screenshot for sample attachment :

Query to find Request group name to which concurrent program is attached.

Below given query will give you details for concurrent program and request group to which CP is attached details.

Query :

SELECT fcpt.user_concurrent_program_name,
       DECODE(frgu.request_unit_type,
              'P', 'Program',
              'S','Set',
              frgu.request_unit_type) Request_Unit_Type,
       fcp.concurrent_program_name short_name,
       frg.application_id,
       frg.request_group_name,
       fat.application_name,
       fa.application_short_name,
       fa.basepath
  FROM fnd_request_groups         frg,
       fnd_request_group_units    frgu,
       fnd_concurrent_programs    fcp,
       fnd_concurrent_programs_tl fcpt,
       fnd_application            fa,
       fnd_application_tl         fat
 WHERE frg.request_group_id = frgu.request_group_id
   AND frgu.request_unit_id = fcp.concurrent_program_id
   AND fcp.concurrent_program_id = fcpt.concurrent_program_id
   AND frg.application_id = fat.application_id
   AND fa.application_id = fat.application_id
   AND fcpt.language = USERENV('LANG')
   AND fat.language = USERENV('LANG')
   AND UPPER(fcpt.user_concurrent_program_name) like
       UPPER('Trial Balance - Detail');