PACKAGE BODY: APPS.IEC_DNC_PVT

Source

1 PACKAGE BODY IEC_DNC_PVT AS
2 /* $Header: IECVDNCB.pls 115.15 2003/10/03 15:22:32 koswartz ship $ */
3 
4 PROCEDURE IS_CALLABLE
5   (P_SOURCE_ID        IN            NUMBER
6   ,P_VIEW_NAME        IN            VARCHAR2
7   ,P_LIST_ENTRY_ID    IN            NUMBER
8   ,P_LIST_HEADER_ID   IN            NUMBER
9   ,P_RETURNS_ID       IN            NUMBER
10   ,X_CALLABLE_FLAG    IN OUT NOCOPY VARCHAR2
11   )
12 IS
13 
14   work_cursor FETCH_CURSOR;
15 
16   l_rlse_stmt VARCHAR2(4000);
17   l_contact_restriction_type HZ_CONTACT_PREFERENCES.CONTACT_LEVEL_TABLE%TYPE;
18   l_counter NUMBER := 0;
19   l_dont_use NUMBER := 0;
20   l_record_id NUMBER := 0;
21 
22 BEGIN
23 
24   Begin
25 
26    -- Build the sql statement.
27    X_CALLABLE_FLAG := 'Y';
28 
29    l_rlse_stmt := 'select /*+ index ( HZ_CONTACT_PREFERENCES, HZ_CONTACT_PREFERENCES_N1 ) */ /*+ index ( AMS_LIST_ENTRIES, AMS_LIST_ENTRIES_N6 ) */ b.contact_level_table '
30                 || ' from ams_list_entries a, hz_contact_preferences b, iec_g_return_entries c '
31                 || ' where c.returns_id = :returnId '
32                 || ' and c.list_entry_id = a.list_entry_id '
33                 || ' and c.list_header_id = a.list_header_id '
34                 || ' and nvl( b.preference_code, ''DO'') = ''DO_NOT'' '
35                 || ' and ( '
36                 || '   ( b.contact_level_table_id = nvl(a.party_id, -1 ) and b.contact_level_table = ''HZ_PARTIES'' ) '
37                 || '   or '
38                 || '   ( b.contact_level_table_id = c.CONTACT_POINT_ID and b.contact_level_table = ''HZ_CONTACT_POINTS'' ) '
39                 || ' ) '
40                 || ' and ( nvl( b.contact_type, ''CALL'' ) = ''CALL'' '
41                 || ' OR  b.contact_type = ''ALL'' )'
42                 || ' and ( '
43                 || '       ( nvl(b.preference_start_date, sysdate - 1) < sysdate '
44                 || '       and nvl( b.preference_end_date, sysdate + 1 ) > sysdate ) '
45                 || ' ) ';
46 
47 
48    open work_cursor for l_rlse_stmt USING P_RETURNS_ID;
49    loop
50       fetch work_cursor into l_contact_restriction_type;
51 
52       exit WHEN work_cursor%NOTFOUND;
53       X_CALLABLE_FLAG := 'N';
54 
55    end loop;
56 
57    CLOSE work_cursor;
58 
59    IF X_CALLABLE_FLAG = 'N'
60    THEN
61       iec_returns_util_pvt.update_entry( P_RETURNS_ID
62                                        , -1
63                                        , to_char(null)
64                                        , to_char(null)
65                                        , to_char(null)
66                                        , 31
67                                        , 0
68                                        , 0
69                                        , 'N');
70 
71      END IF;
72 
73   Exception
74     WHEN NO_DATA_FOUND THEN
75       -- dbms_output.put_line( 'IEC_DNC_PVT:IS_CALLABLE: No data found.. All are callable per HZ_CONTACT_PREFERENCES..' );
76       null;
77   End;
78 
79   return;
80 
81 END IS_CALLABLE;
82 
83 
84 END IEC_DNC_PVT;