1 PACKAGE IBY_DBCCARD_PVT AUTHID CURRENT_USER AS
2 /*$Header: ibyvdbcs.pls 120.2 2005/10/30 05:51:30 appldev noship $*/
3
4 ------------------------------------------------------------------------
5 -- Constants Declaration
6 ------------------------------------------------------------------------
7 C_INSTRTYPE_CREDITCARD CONSTANT VARCHAR2(20) := 'CREDITCARD';
8 C_INSTRTYPE_PURCHASECARD CONSTANT VARCHAR2(20) := 'PURCHASECARD';
9
10 C_PERIOD_DAILY CONSTANT VARCHAR2(10) := 'DAILY';
11 C_PERIOD_WEEKLY CONSTANT VARCHAR2(10) := 'WEEKLY';
12 C_PERIOD_MONTHLY CONSTANT VARCHAR2(10) := 'MONTHLY';
13
14 -- Bug 3714173: C_TO_CURRENCY is from the profile option after the fix
15 -- C_TO_CURRENCY CONSTANT VARCHAR2(5) := 'USD';
16
17 C_STATUS_SUCCESS CONSTANT VARCHAR2(10) := 'SUCCESS';
18 C_STATUS_FAILED CONSTANT VARCHAR2(10) := 'FAILED';
19 C_STATUS_PENDING CONSTANT VARCHAR2(10) := 'PENDING';
20 C_STATUS_UNKNOWN CONSTANT VARCHAR2(10) := 'UNKNOWN';
21
22 -------------------------------------------------------------------------
23 --**Defining all DataStructures required by the procedures**--
24 -- The following input and output PL/SQL record/table types are defined
25 -- to store the objects (entities) necessary for the Instrument Registration
26 -- PL/SQL APIs.
27 -------------------------------------------------------------------------
28
29 --INPUT and OUTPUT DataStructures
30 --1. Record Types
31
32 TYPE Summary_rec_type IS RECORD (
33 columnId NUMBER(2) := 0,
34 totalTrxn NUMBER := 0,
35 totalAmt NUMBER := 0
36 );
37
38 TYPE TrxnSum_rec_type IS RECORD (
39 columnId NUMBER(2) := 0,
40 totalReq NUMBER := 0,
41 totalSuc NUMBER := 0,
42 totalFail NUMBER := 0,
43 totalPend NUMBER := 0
44 );
45
46 TYPE TrxnFail_rec_type IS RECORD (
47 columnId NUMBER(2) := 0,
48 status NUMBER(15) := 0,
49 cause VARCHAR2(80),
50 totalTrxn NUMBER := 0,
51 totalAmt NUMBER := 0
52 );
53
54 --2. Table Types
55
56 TYPE Summary_tbl_type IS TABLE OF Summary_rec_type
57 INDEX BY BINARY_INTEGER;
58
59 TYPE TrxnSum_tbl_type IS TABLE OF TrxnSum_rec_type
60 INDEX BY BINARY_INTEGER;
61
62 TYPE TrxnFail_tbl_type IS TABLE OF TrxnFail_rec_type
63 INDEX BY BINARY_INTEGER;
64
65 --------------------------------------------------------------------------------------
66 -- API Signatures--
67 --------------------------------------------------------------------------------------
68 -- 1. Get_Trxn_Summary
69 -- Start of comments
70 -- API name : Get_Trxn_Summary
71 -- Type : Private
72 -- Pre-reqs : None
73 -- Function : Fetches the information for a transactions.
74 -- Parameters :
75 -- IN : payee_id IN VARCHAR2
76 -- period IN VARCHAR2 Required
77 -- summary_tbl OUT Summary_tbl_type
78 -- trxnSum_tbl OUT TrxnSum_tbl_type
79 -- End of comments
80 --------------------------------------------------------------------------------------
81 Procedure Get_Trxn_Summary ( payee_id IN VARCHAR2,
82 period IN VARCHAR2,
83 summary_tbl OUT NOCOPY Summary_tbl_type,
84 trxnSum_tbl OUT NOCOPY TrxnSum_tbl_type
85 );
86
87 --------------------------------------------------------------------------------------
88 -- 2. Get_Failure_Summary
89 -- Start of comments
90 -- API name : Get_Failure_Summary
91 -- Type : Private
92 -- Pre-reqs : None
93 -- Function : Fetches the information for failures.
94 -- Parameters :
95 -- IN : payee_id IN VARCHAR2
96 -- period IN VARCHAR2 Required
97 -- authFail_tbl OUT TrxnFail_tbl_type
98 -- settFail_tbl OUT TrxnFail_tbl_type
99 -- End of comments
100 --------------------------------------------------------------------------------------
101 Procedure Get_Failure_Summary ( payee_id IN VARCHAR2,
102 period IN VARCHAR2,
103 authFail_tbl OUT NOCOPY TrxnFail_tbl_type,
104 settFail_tbl OUT NOCOPY TrxnFail_tbl_type
105 );
106
107 --------------------------------------------------------------------------------------
108 -- 3. Get_CardType_Summary
109 -- Start of comments
110 -- API name : Get_CardType_Summary
111 -- Type : Private
112 -- Pre-reqs : None
113 -- Function : Fetches the information for Card sub types.
114 -- Parameters :
115 -- IN : payee_id IN VARCHAR2
116 -- period IN VARCHAR2 Required
117 -- cardType_tbl OUT TrxnFail_tbl_type
118 -- End of comments
119 --------------------------------------------------------------------------------------
120 Procedure Get_CardType_Summary ( payee_id IN VARCHAR2,
121 period IN VARCHAR2,
122 cardType_tbl OUT NOCOPY TrxnFail_tbl_type
123 );
124
125 --------------------------------------------------------------------------------------
126 -- 4. Get_Processor_Summary
127 -- Start of comments
128 -- API name : Get_Processor_Summary
129 -- Type : Private
130 -- Pre-reqs : None
131 -- Function : Fetches the information for Processors.
132 -- Parameters :
133 -- IN : payee_id IN VARCHAR2
134 -- period IN VARCHAR2 Required
135 -- Processor_tbl OUT TrxnFail_tbl_type
136 -- End of comments
137 --------------------------------------------------------------------------------------
138 Procedure Get_Processor_Summary ( payee_id IN VARCHAR2,
139 period IN VARCHAR2,
140 Processor_tbl OUT NOCOPY TrxnFail_tbl_type
141 );
142
143 --------------------------------------------------------------------------------------
144 -- 5. Get_Risk_Summary
145 -- Start of comments
146 -- API name : Get_Risk_Summary
147 -- Type : Private
148 -- Pre-reqs : None
149 -- Function : Fetches the information for Risks.
150 -- Parameters :
151 -- IN : payee_id IN VARCHAR2
152 -- period IN VARCHAR2 Required
153 -- total_screened OUT NUMBER
154 -- total_risky OUT NUMBER
155 -- End of comments
156 --------------------------------------------------------------------------------------
157 Procedure Get_Risk_Summary ( payee_id IN VARCHAR2,
158 period IN VARCHAR2,
159 total_screened OUT NOCOPY NUMBER,
160 total_risky OUT NOCOPY NUMBER
161 );
162
163 /*
164 The following function is a wrapper on a GL function that returns a converted amount.
165 If the rate is not found or the currency does not exist, GL functions a negative number.
166 */
167 FUNCTION Convert_Amount ( from_currency VARCHAR2,
168 to_currency VARCHAR2,
169 eff_date DATE,
170 amount NUMBER,
171 conv_type VARCHAR2
172 ) RETURN NUMBER;
173 END IBY_DBCCARD_PVT;