/***** 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))
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))