create or replace function get_emp_addr(p_vendor_id NUMBER,p_vendor_site_id NUMBER)
return varchar2
as
l_ret_address VARCHAR2(32000);
begin
select address_line1 ||','||chr(10)|| nvl2(address_line2, address_line2||','||chr(10),NULL)||city||','||nvl2(state, state||','||chr(10),NULL)||
nvl2(province, province||','||chr(10),NULL)|| ftt.TERRITORY_SHORT_NAME ||','||assa.zip
INTO l_ret_address
from ap_supplier_sites_all assa,ap_suppliers aps , fnd_territories_tl ftt
where assa.vendor_id = aps.vendor_id
and ftt.territory_code = assa.country
and aps.vendor_id = p_vendor_id
and assa.vendor_site_id = p_vendor_site_id ;
return l_ret_address;
end get_emp_addr;
PACKAGE SPEC :
CREATE OR REPLACE package APPS.mutl_get_emp_address_Pkg
is
function get_emp_addr(p_vendor_id NUMBER,p_vendor_site_id NUMBER)
return varchar2;
End ;
PACKAGE BODY :
CREATE OR REPLACE package body APPS.mutl_get_emp_address_Pkg
is
function get_emp_addr(p_vendor_id NUMBER,p_vendor_site_id NUMBER)
return varchar2
as
l_ret_address VARCHAR2(32000);
begin
select address_line1 ||','||chr(10)|| nvl2(address_line2, address_line2||','||chr(10),NULL)||city||','||nvl2(state, state||','||chr(10),NULL)||
nvl2(province, province||','||chr(10),NULL)|| ftt.TERRITORY_SHORT_NAME ||','||assa.zip
INTO l_ret_address
from ap_supplier_sites_all assa,ap_suppliers aps , fnd_territories_tl ftt
where assa.vendor_id = aps.vendor_id
and ftt.territory_code (+) = assa.country
and aps.vendor_id = p_vendor_id
and assa.vendor_site_id = p_vendor_site_id ;
return l_ret_address;
end get_emp_addr;
end mutl_get_emp_address_pkg;
RUN the package :
DECLARE
RetVal varchar2(32000);
P_VENDOR_ID NUMBER;
P_VENDOR_SITE_ID NUMBER;
BEGIN
P_VENDOR_ID := 1887660;
p_vendor_site_id :=127676;
RetVal := APPS.GET_EMP_ADDRESS_PKG.GET_EMP_LIST ( P_VENDOR_ID, p_vendor_site_id );
dbms_output.put_line(substr(retval,1,255));
COMMIT;
END;
return varchar2
as
l_ret_address VARCHAR2(32000);
begin
select address_line1 ||','||chr(10)|| nvl2(address_line2, address_line2||','||chr(10),NULL)||city||','||nvl2(state, state||','||chr(10),NULL)||
nvl2(province, province||','||chr(10),NULL)|| ftt.TERRITORY_SHORT_NAME ||','||assa.zip
INTO l_ret_address
from ap_supplier_sites_all assa,ap_suppliers aps , fnd_territories_tl ftt
where assa.vendor_id = aps.vendor_id
and ftt.territory_code = assa.country
and aps.vendor_id = p_vendor_id
and assa.vendor_site_id = p_vendor_site_id ;
return l_ret_address;
end get_emp_addr;
PACKAGE SPEC :
CREATE OR REPLACE package APPS.mutl_get_emp_address_Pkg
is
function get_emp_addr(p_vendor_id NUMBER,p_vendor_site_id NUMBER)
return varchar2;
End ;
PACKAGE BODY :
CREATE OR REPLACE package body APPS.mutl_get_emp_address_Pkg
is
function get_emp_addr(p_vendor_id NUMBER,p_vendor_site_id NUMBER)
return varchar2
as
l_ret_address VARCHAR2(32000);
begin
select address_line1 ||','||chr(10)|| nvl2(address_line2, address_line2||','||chr(10),NULL)||city||','||nvl2(state, state||','||chr(10),NULL)||
nvl2(province, province||','||chr(10),NULL)|| ftt.TERRITORY_SHORT_NAME ||','||assa.zip
INTO l_ret_address
from ap_supplier_sites_all assa,ap_suppliers aps , fnd_territories_tl ftt
where assa.vendor_id = aps.vendor_id
and ftt.territory_code (+) = assa.country
and aps.vendor_id = p_vendor_id
and assa.vendor_site_id = p_vendor_site_id ;
return l_ret_address;
end get_emp_addr;
end mutl_get_emp_address_pkg;
RUN the package :
DECLARE
RetVal varchar2(32000);
P_VENDOR_ID NUMBER;
P_VENDOR_SITE_ID NUMBER;
BEGIN
P_VENDOR_ID := 1887660;
p_vendor_site_id :=127676;
RetVal := APPS.GET_EMP_ADDRESS_PKG.GET_EMP_LIST ( P_VENDOR_ID, p_vendor_site_id );
dbms_output.put_line(substr(retval,1,255));
COMMIT;
END;
No comments:
Post a Comment