1 PACKAGE AP_WEB_CREDIT_CARD_WF AS
2 /* $Header: apwccwfs.pls 120.17.12010000.3 2008/08/19 06:54:34 sodash 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
119
120 /*
121 Purpose:
122 To send Workflow notifications to a manager to remind him/her to process a
123 submitted expense report.
124 Input:
125 p_managerId: Manager Id
126 p_expenseReportId: Expense Report (Header) id
127 p_Amount: Total Amount of unsumitted expenses
128 Output:
129 None
130 Input Output:
131 None
132 Assumption:
133 None.
134 Usage
135 To be called by the Unapproved Report process. See the 11i Credit Card
136 functional specs for details.
137 Date:
138 10/14/99
139 */
140 PROCEDURE sendUnapprovedExpReportNote(
141 p_expenseReportId IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
142 p_current_approver IN AP_EXPENSE_REPORT_HEADERS.expense_current_approver_id%TYPE); --2628468
143
144
145 /*
146 Purpose:
147 To send Workflow notifications to an employee to remind him/her to
148 resolve outstanding disputed charges.
149 Input:
150 p_employeeId: Employee Id
151 p_cardProgramId: Card Program ID
152 p_billedStartDate: Start date for billed date
153 p_billedEndDate: End date for billed date
154 p_minimumAmount: Minimum billed amount to be processed
155 Output:
156 None
157 Input Output:
158 None
159 Assumption:
160 None.
161 Usage
162 To be called by the Disputed Report process. See the 11i Credit Card
163 functional specs for details.
164 Date:
165 10/14/99
166 */
167 PROCEDURE sendDisputedChargesNote(p_employeeId IN NUMBER,
168 p_cardProgramId IN AP_CARD_PROGRAMS.card_program_id%TYPE,
169 p_billedStartDate in date,
170 p_billedEndDate in date,
171 p_minimumAmount IN NUMBER);
172
173
174 PROCEDURE GenerateList(document_id IN VARCHAR2,
175 display_type IN VARCHAR2,
176 document IN OUT NOCOPY VARCHAR2,
177 document_type IN OUT NOCOPY VARCHAR2);
178
179 PROCEDURE GenerateUnsubmittedList(document_id IN VARCHAR2,
180 display_type IN VARCHAR2,
181 document IN OUT NOCOPY VARCHAR2,
182 document_type IN OUT NOCOPY VARCHAR2);
183
184 PROCEDURE GenerateDunningList(document_id IN VARCHAR2,
185 display_type IN VARCHAR2,
186 document IN OUT NOCOPY VARCHAR2,
187 document_type IN OUT NOCOPY VARCHAR2);
188
189 PROCEDURE getNumofDunningrecords(document_id IN VARCHAR2,
190 display_type IN VARCHAR2,
191 document IN OUT NOCOPY VARCHAR2,
192 document_type IN OUT NOCOPY VARCHAR2);
193
194 PROCEDURE getNumofUnsubmittedrecords(document_id IN VARCHAR2,
195 display_type IN VARCHAR2,
196 document IN OUT NOCOPY VARCHAR2,
197 document_type IN OUT NOCOPY VARCHAR2);
198
199 --Amulya Mishra :Notifications Esc Project
200
201 PROCEDURE SendNotifications(p_item_type IN VARCHAR2,
202 p_item_key IN VARCHAR2,
203 p_actid IN NUMBER,
204 p_funmode IN VARCHAR2,
205 p_result OUT NOCOPY VARCHAR2);
206
207 PROCEDURE GenerateDunningClobList( document_id IN VARCHAR2,
208 display_type IN VARCHAR2,
209 document IN OUT NOCOPY CLOB,
210 document_type IN OUT NOCOPY VARCHAR2);
211
212 PROCEDURE GenerateManagerDunningList( document_id IN VARCHAR2,
213 display_type IN VARCHAR2,
214 document IN OUT NOCOPY CLOB,
215 document_type IN OUT NOCOPY VARCHAR2);
216
217 PROCEDURE GenerateNextManagerDunningList( document_id IN VARCHAR2,
218 display_type IN VARCHAR2,
219 document IN OUT NOCOPY CLOB,
220 document_type IN OUT NOCOPY VARCHAR2);
221
222
223 --Amulya Mishra: Notification Esc project
224
225 FUNCTION GetEmployeeCursor(
226 p_supervisor_id IN NUMBER,
227 p_employee_cursor OUT NOCOPY EmployeeCursor)
228 RETURN BOOLEAN;
229
230 --Amulya Mishra: Notification Esc project
231
232 --5049215 -- Added the dunning number to the function level as only records of this dunning level are processed later.
233 FUNCTION GetHierarchialEmployeeCursor(
234 p_supervisor_id IN NUMBER,
235 p_employee_cursor OUT NOCOPY EmployeeCursor,
236 p_level_id IN NUMBER DEFAULT NULL)
237 RETURN BOOLEAN;
238
239
240 --AMulya Mishra: Notification Esc project
241
242 PROCEDURE GetTotalOutstandingAttribute(
243 p_employee_id IN NUMBER,
244 p_cardProgramId IN NUMBER,
245 p_min_bucket IN NUMBER,
246 p_max_bucket IN NUMBER,
247 p_grace_days IN NUMBER,
248 p_total_amount OUT NOCOPY NUMBER);
249
250 --Amulya Mishra: Notification Esc project
251
252 PROCEDURE GetHierTotalOutstandingAttr(
253 p_supervisor_id IN NUMBER,
254 p_cardProgramId IN NUMBER,
255 p_min_bucket IN NUMBER,
256 p_max_bucket IN NUMBER,
257 p_grace_days IN NUMBER,
258 p_dunning_number IN NUMBER,
259 p_total_amount OUT NOCOPY NUMBER);
260
261 --Amulya Mishra: Notification Esc project
262
263 PROCEDURE IsNotificationRepeated(p_item_type IN VARCHAR2,
264 p_item_key IN VARCHAR2,
265 p_actid IN NUMBER,
266 p_funmode IN VARCHAR2,
267 p_result OUT NOCOPY VARCHAR2);
268
269 --Amulya Mishra: Notification Esc project Direct project
270
271
272 PROCEDURE IsFirstDunning(p_item_type IN VARCHAR2,
273 p_item_key IN VARCHAR2,
274 p_actid IN NUMBER,
275 p_funmode IN VARCHAR2,
276 p_result OUT NOCOPY VARCHAR2);
277
278
279 --Bug 3337443
280 FUNCTION GetDirectReport(
281 p_employee_id IN NUMBER,
282 p_final_manager_id IN NUMBER)
283 RETURN VARCHAR2;
284
285 PROCEDURE GenUnsubmittedClobList(document_id IN VARCHAR2,
286 display_type IN VARCHAR2,
287 document IN OUT NOCOPY CLOB,
288 document_type IN OUT NOCOPY VARCHAR2);
289
290
291 PROCEDURE GetWebNextEscManager(p_itemType IN VARCHAR2,
292 p_itemKey IN VARCHAR2);
293
294 PROCEDURE GetWebEscManager(p_itemType IN VARCHAR2,
295 p_itemKey IN VARCHAR2);
296
297 PROCEDURE SendDeactivatedNotif(p_employeeId IN NUMBER,
298 p_cardProgramId IN NUMBER,
299 p_endDate IN VARCHAR2);
300
301 ------------------------------------------------------------------------
302 PROCEDURE CallbackFunction( p_item_type IN VARCHAR2,
303 p_item_key IN VARCHAR2,
304 p_actid IN NUMBER,
305 p_funmode IN VARCHAR2,
306 p_result OUT NOCOPY VARCHAR2);
307 ------------------------------------------------------------------------
308
309 END AP_WEB_CREDIT_CARD_WF;