Friday, 15 June 2012

How to find Company Address in a database function

/***** Company Ship to Location *********/
Create Or Replace Function COMPANY_ADDRESS(P_LOCATION_ID NUMBER)
RETURN VARCHAR2
AS
COMPANY VARCHAR2(500);
BEGIN
SELECT   Decode (hl.ADDRESS_LINE_1 , null, '', hl.ADDRESS_LINE_1||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_2 , null, '', hl.ADDRESS_LINE_2||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_3 , null, '', hl.ADDRESS_LINE_3||''||CHR(10) )||''||
         Decode (hl.LOC_INFORMATION14 , null, '', hl.LOC_INFORMATION14||''||CHR(10) ) ||''||
         Decode (hl.LOC_INFORMATION15 , null, '',hl.LOC_INFORMATION15)||' - '||hl.POSTAL_CODE INTO COMPANY
FROM HR_LOCATIONS HL
WHERE SHIP_TO_LOCATION_ID = P_LOCATION_ID;
RETURN COMPANY;
EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

/***** Company Bill to Location *********/

Create Or Replace Function COMPANY_ADDRESS(P_LOCATION_ID NUMBER)
RETURN VARCHAR2
AS
COMPANY VARCHAR2(500);
BEGIN
SELECT   Decode (hl.ADDRESS_LINE_1 , null, '', hl.ADDRESS_LINE_1||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_2 , null, '', hl.ADDRESS_LINE_2||''||CHR(10) )||''||
         Decode (hl.ADDRESS_LINE_3 , null, '', hl.ADDRESS_LINE_3||''||CHR(10) )||''||
         Decode (hl.LOC_INFORMATION14 , null, '', hl.LOC_INFORMATION14||''||CHR(10) ) ||''||
         Decode (hl.LOC_INFORMATION15 , null, '',hl.LOC_INFORMATION15)||' - '||hl.POSTAL_CODE INTO COMPANY
FROM HR_LOCATIONS HL
WHERE LOCATION_ID = P_LOCATION_ID;
RETURN COMPANY;
EXCEPTION
WHEN OTHERS THEN
      raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;


/***** Find the Multiple Operating Unit *********/
SELECT LOCATION_ID FROM APPS.HR_ORGANIZATION_UNITS
 WHERE ORGANIZATION_ID =
       (SELECT ORGANIZATION_ID FROM APPS.HR_OPERATING_UNITS HOU
        WHERE HOU.NAME = NVL(:P_OPERATING_UNIT_NAME,HOU.NAME))

1 comment:

  1. Dear Jayanta

    I have 10 OU .So how can I find the address of each OU from your query.

    ReplyDelete