[Home] [Help]
PACKAGE BODY: APPS.MTL_RELATED_ITEMS_PKG1
Source
1 PACKAGE BODY MTL_RELATED_ITEMS_PKG1 as
2 /* $Header: INVISRIB.pls 120.3 2005/08/16 04:28:23 anmurali noship $ */
3
4
5 FUNCTION site_to_address(X_site_id IN NUMBER) return varchar2 IS
6 address_name varchar2(240);
7 BEGIN
8
9 if X_site_id is null then
10 return null;
11 end if;
12
13 /* Changing the query as RA_ADDRESSES has been scrapped -Anmurali
14 select raa.address1
15 into address_name
16 from ra_addresses_all raa,
17 ra_site_uses_all rasu
18 where rasu.site_use_id = X_site_id
19 and raa.address_id = rasu.address_id; */
20
21 SELECT LOC.ADDRESS1
22 INTO ADDRESS_NAME
23 FROM HZ_PARTY_SITES PARTY_SITE, HZ_LOC_ASSIGNMENTS LOC_ASSIGN,
24 HZ_LOCATIONS LOC, HZ_CUST_ACCT_SITES_ALL ACCT_SITE , HZ_CUST_SITE_USES_ALL SITE_USER
25 WHERE SITE_USER.SITE_USE_ID = X_site_id
26 AND ACCT_SITE.CUST_ACCT_SITE_ID = SITE_USER.CUST_ACCT_SITE_ID
27 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
28 AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
29 AND LOC.LOCATION_ID = LOC_ASSIGN.LOCATION_ID
30 AND NVL(ACCT_SITE.ORG_ID, -99) = NVL(LOC_ASSIGN.ORG_ID, -99)
31 AND NVL(SITE_USER.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO'),1,1),' ', NULL,
32 SUBSTRB( USERENV('CLIENT_INFO'),1,10))),-99)) =
33 NVL(TO_NUMBER(DECODE(SUBSTRB( USERENV('CLIENT_INFO'),1,1),' ', NULL,
34 SUBSTRB( USERENV( 'CLIENT_INFO'),1,10))),-99);
35
36
37 return address_name;
38
39 EXCEPTION when no_data_found THEN
40 return null;
41
42 END site_to_address;
43
44 END MTL_RELATED_ITEMS_PKG1;