PACKAGE BODY: APPS.MSC_ATP_UTILITY

Source

1 PACKAGE BODY MSC_ATP_UTILITY AS
2 /* $Header: MSCASDUB.pls 120.1 2007/12/12 10:19:22 sbnaik ship $ */
3 
4 PROCEDURE Close_DbLink(p_DbLink varchar2) IS
5   lv_sql_stmt          VARCHAR2(2000);
6   DBLINK_NOT_OPEN      EXCEPTION;
7   PRAGMA               EXCEPTION_INIT(DBLINK_NOT_OPEN, -2081);
8 BEGIN
9   IF p_DbLink <> ' ' then
10     -- mark distributed transaction boundary
11     -- will need to do a manual clean up (commit) of the distributed
12     -- operation, else subsequent operations fail w/ ora-02080 (bug 2218999)
13     commit;
14 
15     lv_sql_stmt := 'alter session close database link ' ||p_DbLink;
16 
17     EXECUTE IMMEDIATE lv_sql_stmt;
18 
19   END IF;
20 EXCEPTION
21   WHEN DBLINK_NOT_OPEN THEN
22     NULL;
23 END Close_DbLink;
24 
25 
26 PROCEDURE Derive_Profile_Values_Frm_Dest (p_SqlErrM OUT NOCOPY VARCHAR2
27                                , p_Profile_Values   OUT NOCOPY VARCHAR2
28                                , p_Profile_Names     IN VARCHAR2
29                                , p_Delimiter         IN VARCHAR2)
30 IS
31   l_Start_Location PLS_INTEGER    := 1;
32   l_Length         PLS_INTEGER    := 1;
33   l_Profile_Names  VARCHAR2(1000) := LTRIM(p_Profile_Names||p_Delimiter,p_Delimiter);
34   l_Profile_values VARCHAR2(1000) := NULL;
35   l_Delimiter      VARCHAR2(10)   := NVL(p_Delimiter,',');
36 BEGIN
37   LOOP
38     l_Length := INSTR(l_Profile_Names,l_Delimiter,l_Start_Location,1) - l_Start_Location;
39     EXIT WHEN l_Length < 1;
40     l_Profile_Values := l_Profile_Values||FND_PROFILE.VALUE(SUBSTR(l_Profile_Names,l_Start_Location,l_Length))||l_Delimiter;
41     l_Start_Location := l_Start_Location + l_Length + 1;
42   END LOOP;
43   p_Profile_Values := l_Profile_Values;
44 EXCEPTION
45   WHEN OTHERS THEN
46     p_SqlErrM := sqlerrm||' (Problem at destination)';
47 END Derive_Profile_Values_Frm_Dest;
48 
49 
50 PROCEDURE Derive_Profile_Values_Frm_Sour (p_SqlErrM OUT NOCOPY VARCHAR2
51                                  , p_Profile_Values OUT NOCOPY VARCHAR2
52                                    , p_Profile_Names IN VARCHAR2
53                                        , p_Delimiter IN VARCHAR2
54                                           , p_DbLink IN VARCHAR2)
55 IS
56   l_SqlStmt VARCHAR2(2000);
57 BEGIN
58   p_SqlErrM := NULL;
59 
60   l_SqlStmt :=
61  'BEGIN MSC_ATP_UTILITY.Derive_Profile_Values_Frm_Dest'||p_DbLink||' (:p_sqlerrm, :l_Profile_Values, :l_Profile_Names, :l_Delimiter); END;';
62 
63   EXECUTE IMMEDIATE l_SqlStmt
64   USING out p_SqlErrM, out p_Profile_Values, p_Profile_Names, p_Delimiter;
65 
66   Close_DbLink(p_DbLink);
67 EXCEPTION
68   WHEN OTHERS THEN
69     p_SqlErrM := sqlerrm||' (Problem at source)';
70     Close_DbLink(p_DbLink);
71 END Derive_Profile_Values_Frm_Sour;
72 
73 END MSC_ATP_UTILITY;