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;