1 PACKAGE AP_WEB_CREDIT_CARD_WF AUTHID CURRENT_USER AS
2 /* $Header: apwccwfs.pls 120.20.12020000.2 2012/07/05 14:11:44 rveliche ship $ */
3 -- Constant definitions
4 c_voidPayment varchar2(20) := 'VOID_PAYMENT_PROCESS'; -- Bug 7187680(sodash)
5 c_paymentToCardIssuer varchar2(20) := 'PAY_TO_CARD_ISSUER';
6 c_paymentToEmployee varchar2(20) := 'PAY_TO_EMPLOYEE';
7 c_check varchar2(20):= 'CHECK';
8 c_directDeposit varchar2(20):= 'DIRECT_DEPOSIT';
9
10 TYPE EmployeeCursor IS REF CURSOR; --Amulya Mishra : Notification Esc Project
11 /*
12 Purpose:
13 To send Workflow notifications to employee when AP makes Expense Report payments to:
14 - employee
15 - credit card issuer
16 Input:
17 p_checkNumber: Check number
18 p_employeeId: Employee id
19 p_paymentCurrency: Currency of the payment
20 p_invoiceNumber: Expense report invoice number
21 p_paidAmount: Paid amount
22 p_paymentTo: Flag indicate to whom payment is made. Use the predefined constants
23 c_paymentToCardIssuer or c_paymentToCardEmployee
24 p_paymentMethod: Flag to indicate if the payment is direct deposit or by check. This
25 applies when the payment is to employee only. Use the predefined constants
26 c_check or c_directDeposit.
27 p_account: Employee's bank account
28 p_bankName: Employee's bank name
29 p_cardIssuer: Card issuer name
30 p_paymentDate: Formatted date of payment
31 p_deferred: Boolean flag to indicate if the WF process(notification) is deferred. This
32 parameter should be set to TRUE when the procedure is called by a database
33 trigger.
34 Output:
35 None
36 Input Output:
37 None
38 Assumption:
39 None.
40 Usage
41 Since this procedure is used for both cases where payment is made to employee or credit card
42 company, the NULL value could be passed for some of the parameters as described in the following:
43 - When payment to employee:
44 p_paymentTo flag = c_paymentToCardEmployee
45 p_paymentMethod = c_check or c_directDeposit
46 p_cardIssuer = null
47 - When payment to credit card company:
48 p_paymentTo = c_paymentToIssuer
49 p_paymentMethod = null
50 p_account = null
51 p_bankName = null
52
53 All the other parmaters are required.
54
55 Date:
56 10/14/99
57 */
58 PROCEDURE sendPaymentNotification(p_checkNumber IN NUMBER,
59 p_employeeId IN NUMBER,
60 p_paymentCurrency IN VARCHAR2,
61 p_invoiceNumber IN VARCHAR2,
62 p_paidAmount IN NUMBER,
63 p_paymentTo IN VARCHAR2,
64 p_paymentMethod IN VARCHAR2,
65 p_account IN VARCHAR2,
66 p_bankName IN VARCHAR2,
67 p_cardIssuer IN VARCHAR2,
68 p_paymentDate IN VARCHAR2,
69 p_deferred IN BOOLEAN default TRUE);
70
71 /*
72 Purpose:
73 To send Workflow notifications to an employee to remind him/her to submit expense report
74 for unsubmitted expenses incurred during a period( of p_date1 and p_date2).
75 Input:
76 p_employeeId: Employee Id
77 p_Amount: Total Amount of unsumitted expenses
78 p_currency: Currency
79 p_cardIssuer: Card issuer name
80 p_Date1: Start date of the period(formatted)
81 p_Date2: End date of the period(formatted)
82 Output:
83 None
84 Input Output:
85 None
86 Assumption:
87 None.
88 Usage
89 To be called by the Unsubmitted Report process. See the 11i Credit Card
90 functional specs for details.
91 Date:
92 10/14/99
93 */
94 PROCEDURE sendUnsubmittedChargesNote(p_employeeId IN NUMBER,
95 p_Amount IN NUMBER,
96 p_currency IN VARCHAR2,
97 p_cardIssuer IN VARCHAR2,
98 p_date1 IN VARCHAR2,
99 p_date2 IN VARCHAR2,
100 p_charge_type IN VARCHAR2,
101 p_send_notifications IN VARCHAR2 DEFAULT 'EM', -- default Emp and Mgr Bug 6026927
102 p_min_amount IN NUMBER DEFAULT null); -- Bug 6886855 (sodash) setting the wf attribute MIN_AMOUNT
103
104
105 PROCEDURE SendDunningNotifications
106 (p_employeeId IN NUMBER,
107 p_cardProgramId IN AP_CARD_PROGRAMS.card_program_id%TYPE,
108 p_amount IN NUMBER,
109 p_currency IN VARCHAR2,
110 p_min_bucket IN NUMBER,
111 p_max_bucket IN NUMBER,
112 p_dunning_number IN NUMBER,
113 p_send_notifications IN VARCHAR2,
114 p_esc_level IN NUMBER,
115 p_grace_days IN NUMBER,
116 p_manager_notified IN VARCHAR2);
117
118 PROCEDURE SendDunningNotifications
119 (p_employeeId IN NUMBER,
120 p_cardProgramId IN AP_CARD_PROGRAMS.card_program_id%TYPE,
121 p_amount IN NUMBER,
122 p_currency IN VARCHAR2,
123 p_min_bucket IN NUMBER,
124 p_max_bucket IN NUMBER,
125 p_dunning_number IN NUMBER,
126 p_send_notifications IN VARCHAR2,
127 p_esc_level IN NUMBER,
128 p_grace_days IN NUMBER,
129 p_manager_notified IN VARCHAR2,
130 p_surrogate_mgr_id IN NUMBER);
131
132
133
134 /*
135 Purpose:
136 To send Workflow notifications to a manager to remind him/her to process a
137 submitted expense report.
138 Input:
139 p_managerId: Manager Id
140 p_expenseReportId: Expense Report (Header) id
141 p_Amount: Total Amount of unsumitted expenses
142 Output:
143 None
144 Input Output:
145 None
146 Assumption:
147 None.
148 Usage
149 To be called by the Unapproved Report process. See the 11i Credit Card
150 functional specs for details.
151 Date:
152 10/14/99
153 */
154 PROCEDURE sendUnapprovedExpReportNote(
155 p_expenseReportId IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
156 p_current_approver IN AP_EXPENSE_REPORT_HEADERS.expense_current_approver_id%TYPE); --2628468
157
158
159 /*
160 Purpose:
161 To send Workflow notifications to an employee to remind him/her to
162 resolve outstanding disputed charges.
163 Input:
164 p_employeeId: Employee Id
165 p_cardProgramId: Card Program ID
166 p_billedStartDate: Start date for billed date
167 p_billedEndDate: End date for billed date
168 p_minimumAmount: Minimum billed amount to be processed
169 Output:
170 None
171 Input Output:
172 None
173 Assumption:
174 None.
175 Usage
176 To be called by the Disputed Report process. See the 11i Credit Card
177 functional specs for details.
178 Date:
179 10/14/99
180 */
181 PROCEDURE sendDisputedChargesNote(p_employeeId IN NUMBER,
182 p_cardProgramId IN AP_CARD_PROGRAMS.card_program_id%TYPE,
183 p_billedStartDate in date,
184 p_billedEndDate in date,
185 p_minimumAmount IN NUMBER);
186
187
188 PROCEDURE GenerateList(document_id IN VARCHAR2,
189 display_type IN VARCHAR2,
190 document IN OUT NOCOPY VARCHAR2,
191 document_type IN OUT NOCOPY VARCHAR2);
192
193 PROCEDURE GenerateUnsubmittedList(document_id IN VARCHAR2,
194 display_type IN VARCHAR2,
195 document IN OUT NOCOPY VARCHAR2,
196 document_type IN OUT NOCOPY VARCHAR2);
197
198 PROCEDURE GenerateDunningList(document_id IN VARCHAR2,
199 display_type IN VARCHAR2,
200 document IN OUT NOCOPY VARCHAR2,
201 document_type IN OUT NOCOPY VARCHAR2);
202
203 PROCEDURE getNumofDunningrecords(document_id IN VARCHAR2,
204 display_type IN VARCHAR2,
205 document IN OUT NOCOPY VARCHAR2,
206 document_type IN OUT NOCOPY VARCHAR2);
207
208 PROCEDURE getNumofUnsubmittedrecords(document_id IN VARCHAR2,
209 display_type IN VARCHAR2,
210 document IN OUT NOCOPY VARCHAR2,
211 document_type IN OUT NOCOPY VARCHAR2);
212
213 --Amulya Mishra :Notifications Esc Project
214
215 PROCEDURE SendNotifications(p_item_type IN VARCHAR2,
216 p_item_key IN VARCHAR2,
217 p_actid IN NUMBER,
218 p_funmode IN VARCHAR2,
219 p_result OUT NOCOPY VARCHAR2);
220
221 PROCEDURE GenerateDunningClobList( document_id IN VARCHAR2,
222 display_type IN VARCHAR2,
223 document IN OUT NOCOPY CLOB,
224 document_type IN OUT NOCOPY VARCHAR2);
225
226 PROCEDURE GenerateManagerDunningList( document_id IN VARCHAR2,
227 display_type IN VARCHAR2,
228 document IN OUT NOCOPY CLOB,
229 document_type IN OUT NOCOPY VARCHAR2);
230
231 PROCEDURE GenerateNextManagerDunningList( document_id IN VARCHAR2,
232 display_type IN VARCHAR2,
233 document IN OUT NOCOPY CLOB,
234 document_type IN OUT NOCOPY VARCHAR2);
235
236
237 --Amulya Mishra: Notification Esc project
238
239 FUNCTION GetEmployeeCursor(
240 p_supervisor_id IN NUMBER,
241 p_employee_cursor OUT NOCOPY EmployeeCursor)
242 RETURN BOOLEAN;
243
244 --Amulya Mishra: Notification Esc project
245
246 --5049215 -- Added the dunning number to the function level as only records of this dunning level are processed later.
247 FUNCTION GetHierarchialEmployeeCursor(
248 p_supervisor_id IN NUMBER,
249 p_employee_cursor OUT NOCOPY EmployeeCursor,
250 p_level_id IN NUMBER DEFAULT NULL)
251 RETURN BOOLEAN;
252
253
254 --AMulya Mishra: Notification Esc project
255
256 PROCEDURE GetTotalOutstandingAttribute(
257 p_employee_id IN NUMBER,
258 p_cardProgramId IN NUMBER,
259 p_min_bucket IN NUMBER,
260 p_max_bucket IN NUMBER,
261 p_grace_days IN NUMBER,
262 p_total_amount OUT NOCOPY NUMBER);
263
264 --Amulya Mishra: Notification Esc project
265
266 PROCEDURE GetHierTotalOutstandingAttr(
267 p_supervisor_id IN NUMBER,
268 p_cardProgramId IN NUMBER,
269 p_min_bucket IN NUMBER,
270 p_max_bucket IN NUMBER,
271 p_grace_days IN NUMBER,
272 p_dunning_number IN NUMBER,
273 p_total_amount OUT NOCOPY NUMBER);
274
275 --Amulya Mishra: Notification Esc project
276
277 PROCEDURE IsNotificationRepeated(p_item_type IN VARCHAR2,
278 p_item_key IN VARCHAR2,
279 p_actid IN NUMBER,
280 p_funmode IN VARCHAR2,
281 p_result OUT NOCOPY VARCHAR2);
282
283 --Amulya Mishra: Notification Esc project Direct project
284
285
286 PROCEDURE IsFirstDunning(p_item_type IN VARCHAR2,
287 p_item_key IN VARCHAR2,
288 p_actid IN NUMBER,
289 p_funmode IN VARCHAR2,
290 p_result OUT NOCOPY VARCHAR2);
291
292
293 --Bug 3337443
294 FUNCTION GetDirectReport(
295 p_employee_id IN NUMBER,
296 p_final_manager_id IN NUMBER)
297 RETURN VARCHAR2;
298
299 PROCEDURE GenUnsubmittedClobList(document_id IN VARCHAR2,
300 display_type IN VARCHAR2,
301 document IN OUT NOCOPY CLOB,
302 document_type IN OUT NOCOPY VARCHAR2);
303
304
305 PROCEDURE GetWebNextEscManager(p_itemType IN VARCHAR2,
306 p_itemKey IN VARCHAR2);
307
308 PROCEDURE GetWebEscManager(p_itemType IN VARCHAR2,
309 p_itemKey IN VARCHAR2);
310
311 PROCEDURE SendDeactivatedNotif(p_employeeId IN NUMBER,
312 p_cardProgramId IN NUMBER,
313 p_endDate IN VARCHAR2);
314
315 ------------------------------------------------------------------------
316 PROCEDURE CallbackFunction( p_item_type IN VARCHAR2,
317 p_item_key IN VARCHAR2,
318 p_actid IN NUMBER,
319 p_funmode IN VARCHAR2,
320 p_result OUT NOCOPY VARCHAR2);
321 ------------------------------------------------------------------------
322
323 END AP_WEB_CREDIT_CARD_WF;
[Home] [Help]