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;

Oracle Apps R12: Customer Details and Order Management Guide

SELECT
     h.order_number
    ,h.sold_to_org_id bill_cust_account_id
    ,h.ship_to_org_id ship_to_site_use_id
    ,h.invoice_to_org_id bill_to_site_use_id
    ,hp.party_name "Customer Name"
    ,hca.account_name
    ,hca.org_id
    ,hcasab.orig_system_reference   BILL_TO_ORIG_REF
    ,hpb.status                     BILL_TO_STATUS
    ,'ADDRESS1 - '||bill_loc.address1||','||CHR(10)||
     'ADDRESS2 - '||bill_loc.address2||','||CHR(10)||
     'ADDRESS3 - '||bill_loc.address3||','||CHR(10)||
     'CITY     - '||bill_loc.city||','||CHR(10)||
     'POSTAL CD- '||bill_loc.postal_code||','||CHR(10)||
     'COUNTRY  - '|| bill_loc.country  BILL_TO_ADDRESS
    ,hcasas.orig_system_reference      SHIP_TO_ORIG_REF
    ,hps.status                        SHIP_TO_STATUS
    ,'ADDRESS1 - '||ship_loc.address1||','||CHR(10)||
     'ADDRESS2 - '||ship_loc.address2||','||CHR(10)||
     'ADDRESS3 - '||ship_loc.address3||','||CHR(10)||
     'CITY     - '||ship_loc.city||','||CHR(10)||
     'POSTAL CD- '||ship_loc.postal_code||','||CHR(10)||
     'COUNTRY  - '|| ship_loc.country  SHIP_TO_ADDRESS
FROM oe_order_headers_all h
    ,hz_parties hp
    ,hz_cust_accounts hca
    ,hz_cust_acct_sites_all hcasab
    ,hz_cust_acct_sites_all hcasas
    ,hz_cust_site_uses_all hzsuab
    ,hz_cust_site_uses_all hzsuas
    ,hz_party_sites hps
    ,hz_party_sites hpb
    ,hz_locations bill_loc
    ,hz_locations ship_loc
WHERE 1 =1
AND hp.party_id             = hca.party_id
AND hca.CUST_ACCOUNT_ID     = h.sold_to_org_id
AND hcasab.cust_account_id  = hca.cust_account_id
AND hcasas.cust_account_id  = hca.cust_account_id
AND hpb.location_id         = bill_loc.location_id
AND hps.location_id         = ship_loc.location_id
AND hcasab.party_site_id    = hpb.party_site_id
AND hcasas.party_site_id    = hps.party_site_id
AND hcasab.cust_acct_site_id= hzsuab.cust_acct_site_id
AND hcasas.cust_acct_site_id= hzsuas.cust_acct_site_id
AND h.ship_to_org_id        = hzsuas.site_use_id
AND h.invoice_to_org_id     = hzsuab.site_use_id
AND h.order_number          = '&order_number';

Oracle Apps R12 Trading Community Architecture (TCA) Process Flow Explained