PL/SQL - FOR LOOP

BEGIN
  FOR someone IN (
    SELECT * FROM employees
    WHERE employee_id < 120
    ORDER BY employee_id
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE('First name = ' || someone.first_name ||
                         ', Last name = ' || someone.last_name);
  End Loop;
End;

OUTPUT:
 First name = Steven, Last name = King
First name = Neena, Last name = Kochhar
First name = Lex, Last name = De Haan
First name = Alexander, Last name = Hunold
First name = Bruce, Last name = Ernst

PL/SQL - ANONYMOUS BLOCK, UPDATE COLUMNS

Declare
   V_dept_id xxdtj_Employees.department_Id%Type:=10;
Begin
   Update Xxdtj_Employees Set Salary=Salary + 1000
   Where Department_Id=V_dept_id;
   Dbms_Output.Put_Line(V_DEPT_ID);
End;

OUTPUT :
   10

PL/SQL - SELECT statement using cursor

DECLARE
       CURSOR c_deptno IS SELECT first_name, salary, department_id
       FROM EMP;
BEGIN
     For x in c_deptno
    Loop
     dbms_output.put_line(x.first_name ||' '||x.salary||' '||x.department_id);
    End loop;
     close c_deptno;
End;

output:
        Steven 24000 90
        Neena 17000 90
        Lex 17000 90
        Alexander 9000 60

PL/SQL: SELECT Using Explicit Cursor to Fetch All Records

Note:

        An explicit cursor returns more than one row.

declare
       cursor v_cur is select employee_id,first_name,salary
       from emp;
       v_id emp.employee_id%type;
       v_name emp.first_name%type;
       v_sal emp.salary%type;
begin
      open v_cur;
      loop
      fetch v_cur into v_id,v_name,v_sal;
      exit when v_cur%notfound;
      dbms_output.put_line(v_id||' '||v_name||' '||v_sal);
      end loop;
      close v_cur;
end;

output:
100 Steven 24000
101 Neena 17000
102 Lex 17000
103 Alexander 9000
104 Bruce 6000
105 David 4800

PL/SQL Cursor to Return One Row with Multiple Columns: Example Guide

declare
       cursor s is select employee_id,first_name,salary
       from emp ;
       v_id emp.employee_id%type;
       v_name emp.first_name%type;
       v_sal emp.salary%type;
begin
      open s;
   
      fetch s into v_id,v_name,v_sal;
      dbms_output.put_line(v_id||' '||v_name||' '||v_sal);
   
end;

OUTPUT:
     100 Steven 24000

PL/SQL Anonymous Block to Return Multiple Columns: How-To Guide

declare
       v_id emp.employee_id%type:=&v_no;
       v_name emp.first_name%type;
       v_sal emp.salary%type;
begin
      select employee_id,first_name,salary
      into v_id,v_name,v_sal
      from emp
      where employee_id= v_id;
      dbms_output.put_line(v_id|| ' ' ||v_name||' '||v_sal);
end;

output :
 101 Neena 17000

PL/SQL Program to Accept Two Numbers and Print the Largest

declare

       x number;
       y number;
begin
      x :=&x;
      y :=&y;
      if( x > y)then
       dbms_output.put_line('x is largest than y');
       else
          dbms_output.put_line('y is largest than x');
   
      end if;
end;

PL/SQL REF CURSOR: Return Multiple Columns and Rows Easily

DECLARE
     TYPE ref_emp_cur IS REF CURSOR;
     var_emp_cur ref_emp_cur;
     varr emp%ROWTYPE;
BEGIN
    OPEN var_emp_cur
    FOR select * FROM Emp;
    LOOP
    FETCH  var_emp_cur INTO varr;
    EXIT WHEN var_emp_cur%NOTFOUND;
    dbms_output.put_line(varr.empno||' - '|| varr.ename ||' - '|| varr.sal);
    END LOOP;
   CLOSE var_emp_cur;
END;

PL/SQL: Return Multiple Rows Using REF CURSOR Explained

DECLARE
    type ref_cursor is ref cursor;
    v_party ref_cursor;
    v_party_name  hz_parties.party_name%type;
BEGIN
    open v_party for select party_name from hz_parties;
    loop
     fetch v_party into v_party_name;
       dbms_output.put_line(v_party_name);
     end loop;
      close v_party;
END;

PL/SQL: How to SELECT Multiple Rows with Examples

declare
          type party_id_type is table of hz_parties.party_id%type;
          type party_name_type is table of hz_parties.party_name%type;
           party_id_tab   party_id_type;
           party_name_tab party_name_type;
begin
     SELECT party_id,party_name
     BULK COLLECT INTO party_id_tab, party_name_tab
     FROM hz_parties;
   
    FOR i IN party_id_tab.FIRST..party_id_tab.LAST
    LOOP
       DBMS_OUTPUT.PUT_LINE ('party_id: '||party_id_tab(i));
       DBMS_OUTPUT.PUT_LINE ('party_name: '||party_name_tab(i));
    END LOOP;
end;

PL/SQL: Selecting Multiple Columns from a Table Explained

DECLARE
      CURSOR  hzp_cur IS
        SELECT party_id, party_name
        FROM hz_parties;
BEGIN
     FOR i IN hzp_cur
     LOOP
       DBMS_OUTPUT.PUT_LINE ('party_id: '||i.party_id);
       DBMS_OUTPUT.PUT_LINE ('party_name: '||i.party_name);
     
     END LOOP;
END;

PL/SQL Procedure to Select Multiple Columns: Step-by-Step Guide

create or replace procedure MUTL_GET_EMP_ADDRESS(p_segment1 in varchar2)

is

  p_address_line1 varchar2(300);

  p_city          varchar2(100);

  p_state         varchar2(300);

  p_zip           varchar2(300);

     

      CURSOR c_address IS

        SELECT   assa.address_line1,

                 assa.city,

                 assa.state,

                 assa.zip      

        FROM     ap_supplier_sites_all assa,

                ap_suppliers aps

      WHERE    assa.vendor_id = aps.vendor_id

      AND      aps.segment1 =p_segment1;

BEGIN

      OPEN c_address;

        FETCH c_address INTO p_address_line1,p_city,p_state,p_zip;

        DBMS_OUTPUT.put_line (  p_address_line1 || '-' || p_city || '-' || p_state || '-' || p_zip); 

     CLOSE c_address;

END;

--Package spec

 create or replace package  MUTL_GET_EMP_ADDRESS_PKG

Is

  procedure MUTL_GET_EMP_ADDRESS(p_segment1 in varchar2);

End MUTL_GET_EMP_ADDRESS_PKG;

--Package body

  create or replace package body MUTL_GET_EMP_ADDRESS_PKG

is

  procedure MUTL_GET_EMP_ADDRESS(p_segment1 in varchar2)

is

  p_address_line1 varchar2(300);

  p_city          varchar2(100);

  p_state         varchar2(300);

  p_zip           varchar2(300);

     

      CURSOR c_address IS

        SELECT   assa.address_line1,

                 assa.city,

                 assa.state,

                 assa.zip      

        FROM     ap_supplier_sites_all assa,

                ap_suppliers aps

      WHERE    assa.vendor_id = aps.vendor_id

      AND      aps.segment1 =p_segment1;

BEGIN

      OPEN c_address;

        FETCH c_address INTO p_address_line1,p_city,p_state,p_zip;

        DBMS_OUTPUT.put_line (  p_address_line1 || '-' || p_city || '-' || p_state || '-' || p_zip); 

     CLOSE c_address;

 END MUTL_GET_EMP_ADDRESS;

end MUTL_GET_EMP_ADDRESS_PKG;

PL/SQL: How to Return Multiple Columns from a Function

create or replace type emp_type_o as object

(address_line1 varchar2(240),

   city          varchar2(60),

   state         varchar2(150),

   zip           varchar2(60)

   );

create type emp_type_t is table of emp_type_o;

PL/SQL FUNCTION:

  create or replace function get_emp_list(p_segment1 varchar2)

     return emp_type_t

 as

     v_address_line_1 emp_type_t;

 begin

      select emp_type_o(address_line1,city,state,zip)

      bulk collect into v_address_line_1

      from ap_supplier_sites_all assa,ap_suppliers aps

      where assa.vendor_id = aps.vendor_id

      and   aps.segment1 = p_segment1;

      

      return v_address_line_1;

  end;

PL/SQL FUNCTION RUN OUTPUT:

select * from table(get_emp_list(16896));

Package Spec:

 Create Or Replace Package Get_emp_address_Pkg

    Is

    Function get_emp_list(p_segment1 varchar2)

    Return emp_type_t;

 End ;

Package Body:

 create or replace package body Get_emp_address_Pkg

  is

  function get_emp_list(p_segment1 varchar2)

  return emp_type_t

  as

     v_address_line_1 emp_type_t;

begin

      select emp_type_o(address_line1,city,state,zip)

      bulk collect into v_address_line_1

      from ap_supplier_sites_all assa,ap_suppliers aps

      where assa.vendor_id = aps.vendor_id

      and   aps.segment1 = p_segment1;

      return v_address_line_1;

   end get_emp_list;

end get_emp_address_pkg;

Run the Package:

     select get_emp_address_pkg.get_emp_list(16896) from dual;

How to Create Quote Anonymous Blocks in Oracle EBS

DECLARE
   l_org_id                        VARCHAR2 (1254);
   li_inventory_item_id            NUMBER;
   li_currency_code                VARCHAR2 (1254);
   li_uom_code                     VARCHAR2 (1254);
   li_quantity                     NUMBER;
   li_quote_category_code          VARCHAR2 (1254);
   li_line_list_price              VARCHAR2 (2000);
   li_order_type_id                VARCHAR2 (2000);
   li_price_list_id                NUMBER;
   li_quote_source_code            VARCHAR2 (2000);
   li_party_id                     NUMBER;
   li_payment_term_id              NUMBER;
   li_cust_account_id              NUMBER;
   li_invoice_to_cust_account_id   NUMBER;
   li_invoice_to_party_site_id     NUMBER;
   li_invoice_to_party_id          NUMBER;
   li_invoice_to_cust_party_id     NUMBER;
   li_total_quote_price            NUMBER;
   li_total_list_price             NUMBER;
   li_total_adjusted_amount        NUMBER;
   ln_line_number                  NUMBER;
   l_quote_line_id                 NUMBER;
   l_ref_line_id                   NUMBER;
   x_relationship_id               NUMBER;
   x_return_status                 VARCHAR2 (2000);
   x_msg_count                     VARCHAR2 (2000);
   x_msg_data                      VARCHAR2 (2000);
   l_px_quote_line_detail_id       NUMBER;
   px_quote_line_detail_id         NUMBER;
---create quote
   l_quote_status_id               NUMBER;
   l_control_rec                   aso_quote_pub.control_rec_type;
   l_qte_header_rec                aso_quote_pub.qte_header_rec_type;
   l_qte_line_rec                  aso_quote_pub.qte_line_rec_type;
   l_qte_line_tbl                  aso_quote_pub.qte_line_tbl_type;
   l_qte_line_dtl_tbl              aso_quote_pub.qte_line_dtl_tbl_type;
   -- l_hd_Price_Attr_Tbl ASO_QUOTE_PUB.Price_Attributes_Tbl_Type;
   l_hd_payment_tbl                aso_quote_pub.payment_tbl_type;
   l_payment_rec                   aso_quote_pub.payment_rec_type;
   l_hd_shipment_rec               aso_quote_pub.shipment_rec_type;
   -- l_hd_freight_charge_tbl ASO_QUOTE_PUB.Freight_Charge_Tbl_Type;
   l_hd_tax_detail_tbl             aso_quote_pub.tax_detail_tbl_type;
   l_tax_detail_rec                aso_quote_pub.tax_detail_rec_type;
   l_tax_control_rec               aso_tax_int.tax_control_rec_type;
   l_line_attr_ext_tbl             aso_quote_pub.line_attribs_ext_tbl_type;
   l_line_rltship_tbl              aso_quote_pub.line_rltship_tbl_type;
   l_price_adjustment_tbl          aso_quote_pub.price_adj_tbl_type;
   l_price_adj_attr_tbl            aso_quote_pub.price_adj_attr_tbl_type;
   l_price_adj_rltship_tbl         aso_quote_pub.price_adj_rltship_tbl_type;
   l_ln_price_attr_tbl             aso_quote_pub.price_attributes_tbl_type;
   l_ln_payment_tbl                aso_quote_pub.payment_tbl_type;
   l_ln_shipment_tbl               aso_quote_pub.shipment_tbl_type;
   l_ln_freight_charge_tbl         aso_quote_pub.freight_charge_tbl_type;
   l_ln_tax_detail_tbl             aso_quote_pub.tax_detail_tbl_type;
   lx_qte_header_rec               aso_quote_pub.qte_header_rec_type;
   lx_qte_line_tbl                 aso_quote_pub.qte_line_tbl_type;
   lx_qte_line_dtl_tbl             aso_quote_pub.qte_line_dtl_tbl_type;
   lx_hd_price_attr_tbl            aso_quote_pub.price_attributes_tbl_type;
   lx_hd_payment_tbl               aso_quote_pub.payment_tbl_type;
   lx_hd_shipment_rec              aso_quote_pub.shipment_rec_type;
   lx_hd_freight_charge_tbl        aso_quote_pub.freight_charge_tbl_type;
   lx_hd_tax_detail_tbl            aso_quote_pub.tax_detail_tbl_type;
   lx_line_attr_ext_tbl            aso_quote_pub.line_attribs_ext_tbl_type;
   lx_line_rltship_tbl             aso_quote_pub.line_rltship_tbl_type;
   lx_price_adjustment_tbl         aso_quote_pub.price_adj_tbl_type;
   lx_price_adj_attr_tbl           aso_quote_pub.price_adj_attr_tbl_type;
   lx_price_adj_rltship_tbl        aso_quote_pub.price_adj_rltship_tbl_type;
   lx_ln_price_attr_tbl            aso_quote_pub.price_attributes_tbl_type;
   lx_ln_payment_tbl               aso_quote_pub.payment_tbl_type;
   lx_ln_shipment_tbl              aso_quote_pub.shipment_tbl_type;
   lx_ln_freight_charge_tbl        aso_quote_pub.freight_charge_tbl_type;
   lx_ln_tax_detail_tbl            aso_quote_pub.tax_detail_tbl_type;
   lx_hd_shipment_tbl              aso_quote_pub.shipment_tbl_type;
   lx_return_status                VARCHAR2 (1);
   lx_msg_count                    NUMBER;
   l_quote_number                  NUMBER;
   lx_msg_data                     VARCHAR2 (2000);
   my_message                      VARCHAR2 (2000);
   l_file                          VARCHAR2 (2000);
   lnx_quote_line_id               NUMBER;
   l_hd_sales_credit_tbl           aso_quote_pub.sales_credit_tbl_type
                                     := aso_quote_pub.g_miss_sales_credit_tbl;
   l_ln_sales_credit_tbl           aso_quote_pub.sales_credit_tbl_type
                                     := aso_quote_pub.g_miss_sales_credit_tbl;
   lx_hd_sales_credit_tbl          aso_quote_pub.sales_credit_tbl_type;
   lx_quote_party_tbl              aso_quote_pub.quote_party_tbl_type;
   lx_ln_sales_credit_tbl          aso_quote_pub.sales_credit_tbl_type;
   lx_ln_quote_party_tbl           aso_quote_pub.quote_party_tbl_type;
--l_payment_rec ASO_QUOTE_PUB.Payment_Rec_Type;
   l_shipment_rec                  aso_quote_pub.shipment_rec_type;
--l_tax_detail_rec ASO_QUOTE_PUB.Tax_Detail_Rec_Type;
   lx_hd_attr_ext_tbl              aso_quote_pub.line_attribs_ext_tbl_type;
   l_qte_line_dtl_rec              aso_quote_pub.qte_line_dtl_rec_type;
   lc_last_update_date             DATE;
---relatioship
   l_line_rtlship_rec              aso_quote_pub.line_rltship_rec_type;
   qte_lin                         NUMBER;
   my_message                      VARCHAR2 (2000);
BEGIN
--l_org_id := fnd_profile.VALUE('ORG_ID');
   l_org_id := 1;
   DBMS_APPLICATION_INFO.set_client_info (1);
   fnd_global.apps_initialize (1066, 50378, 279);
--user_id,responsibility_id,application id
   mo_global.init ('QOT');
   mo_global.set_policy_context ('S', 1);                   --For Multiple OU
--commit;
   l_control_rec.calculate_tax_flag := 'Y';
   l_control_rec.calculate_freight_charge_flag := 'Y';
   l_control_rec.pricing_request_type := 'ASO';
   l_control_rec.header_pricing_event := 'BATCH';
   l_qte_header_rec.quote_name := 'Payload Quote Testing';
   l_qte_header_rec.quote_source_code := 'Order Capture Quotes';
   l_qte_header_rec.currency_code := 'USD';
   l_qte_header_rec.party_id := 1241;
   l_qte_header_rec.price_list_id := 7040991;
----------------- LINES ---------------------------------
   l_qte_line_tbl (1).organization_id := 4;
   l_qte_line_tbl (1).operation_code := 'CREATE';
   l_qte_line_tbl (1).inventory_item_id := 28931;
   l_qte_line_tbl (1).quantity := 1;
   l_qte_line_tbl (1).uom_code := 'EA';
   l_qte_line_tbl (1).org_id := 1;
---------create quote
  dbms_output.put_line('begin for the create quote header id ');
   aso_quote_pub.create_quote
                        (p_api_version_number           => 1.0,
                         p_init_msg_list                => fnd_api.g_true,
                         p_control_rec                  => l_control_rec,
                         p_qte_header_rec               => l_qte_header_rec,
                         p_qte_line_tbl                 => l_qte_line_tbl,
                         p_hd_payment_tbl               => l_hd_payment_tbl,
                         p_hd_tax_detail_tbl            => l_hd_tax_detail_tbl,
                         x_qte_header_rec               => lx_qte_header_rec,
                         x_qte_line_tbl                 => lx_qte_line_tbl,
                         x_qte_line_dtl_tbl             => lx_qte_line_dtl_tbl,
                         x_hd_price_attributes_tbl      => lx_hd_price_attr_tbl,
                         x_hd_payment_tbl               => lx_hd_payment_tbl,
                         x_hd_shipment_rec              => lx_hd_shipment_rec,
                         x_hd_freight_charge_tbl        => lx_hd_freight_charge_tbl,
                         x_hd_tax_detail_tbl            => lx_hd_tax_detail_tbl,
                         x_line_attr_ext_tbl            => lx_line_attr_ext_tbl,
                         x_line_rltship_tbl             => lx_line_rltship_tbl,
                         x_price_adjustment_tbl         => lx_price_adjustment_tbl,
                         x_price_adj_attr_tbl           => lx_price_adj_attr_tbl,
                         x_price_adj_rltship_tbl        => lx_price_adj_rltship_tbl,
                         x_ln_price_attributes_tbl      => lx_ln_price_attr_tbl,
                         x_ln_payment_tbl               => lx_ln_payment_tbl,
                         x_ln_shipment_tbl              => lx_ln_shipment_tbl,
                         x_ln_freight_charge_tbl        => lx_ln_freight_charge_tbl,
                         x_ln_tax_detail_tbl            => lx_ln_tax_detail_tbl,
                         x_return_status                => lx_return_status,
                         x_msg_count                    => lx_msg_count,
                         x_msg_data                     => lx_msg_data
                        );

   fnd_msg_pub.count_and_get (p_encoded      => 'F',
                              p_count        => lx_msg_count,
                              p_data         => lx_msg_data
                             );
--   COMMIT;
   DBMS_OUTPUT.put_line ('no. of FND messages :' || lx_msg_count);

   FOR k IN 1 .. lx_msg_count
   LOOP
      lx_msg_data := fnd_msg_pub.get (p_msg_index => k, p_encoded => 'F');
      DBMS_OUTPUT.put_line ('Error msg: ' || SUBSTR (lx_msg_data, 1, 240));
   END LOOP;

   DBMS_OUTPUT.put_line ('Return Status: ' || lx_return_status);
   DBMS_OUTPUT.put_line ('msg count:' || TO_CHAR (lx_msg_count));
   DBMS_OUTPUT.put_line ('Message Data: ' || lx_msg_data);
   DBMS_OUTPUT.put_line (   'qte_header_id: '
                         || TO_CHAR (lx_qte_header_rec.quote_header_id)
                        );
   DBMS_OUTPUT.put_line ('end');
--   COMMIT;
END;