[Home] [Help]
PACKAGE: APPS.AP_WEB_DB_CCARD_PKG
Source
1 PACKAGE AP_WEB_DB_CCARD_PKG AS
2 /* $Header: apwdbccs.pls 120.17 2006/05/04 07:21:44 sbalaji ship $ */
3
4 /* Constant */
5 c_personal CONSTANT varchar(8) := 'PERSONAL';
6 c_business CONSTANT varchar(8) := 'BUSINESS';
7 c_disputed CONSTANT varchar(8) := 'DISPUTED';
8 c_credit CONSTANT varchar(8) := 'CREDIT';
9 c_matched CONSTANT varchar(8) := 'MATCHED';
10 c_deactivated CONSTANT varchar(12) := 'DEACTIVATED';
11
12 /* AP Credit Card Transactions */
13 ---------------------------------------------------------------------------------------------------
14 SUBTYPE ccTrxn_trxID IS AP_CREDIT_CARD_TRXNS.trx_id%TYPE;
15 SUBTYPE ccTrxn_validateCode IS AP_CREDIT_CARD_TRXNS.validate_code%TYPE;
16 SUBTYPE ccTrxn_cardProgID IS AP_CREDIT_CARD_TRXNS.card_program_id%TYPE;
17 SUBTYPE ccTrxn_expensedAmt IS AP_CREDIT_CARD_TRXNS.expensed_amount%TYPE;
18 SUBTYPE ccTrxn_cardId IS AP_CREDIT_CARD_TRXNS.card_id%TYPE;
19 SUBTYPE ccTrxn_refNum IS AP_CREDIT_CARD_TRXNS.reference_number%TYPE;
20 SUBTYPE ccTrxn_folioType IS AP_CREDIT_CARD_TRXNS.folio_type%TYPE;
21 SUBTYPE ccTrxn_category IS AP_CREDIT_CARD_TRXNS.category%TYPE;
22 SUBTYPE ccTrxn_headerID IS AP_CREDIT_CARD_TRXNS.report_header_id%TYPE;
23 SUBTYPE ccTrxn_expenseStatus IS AP_CREDIT_CARD_TRXNS.expense_status%TYPE;
24 SUBTYPE ccTrxn_billedCurrCode IS AP_CREDIT_CARD_TRXNS.billed_currency_code%TYPE;
25 SUBTYPE ccTrxn_billedDate IS AP_CREDIT_CARD_TRXNS.billed_date%TYPE;
26 SUBTYPE ccTrxn_companyPrepaidInvID IS AP_CREDIT_CARD_TRXNS.company_prepaid_invoice_id%TYPE;
27 SUBTYPE ccTrxn_merchantName1 IS AP_CREDIT_CARD_TRXNS.merchant_name1%TYPE;
28 SUBTYPE ccTrxn_merchantCity IS AP_CREDIT_CARD_TRXNS.merchant_city%TYPE;
29 SUBTYPE ccTrxn_billedAmount IS AP_CREDIT_CARD_TRXNS.billed_amount%TYPE;
30 SUBTYPE ccTrxn_postedCurrCode IS AP_CREDIT_CARD_TRXNS.posted_currency_code%TYPE;
31 SUBTYPE ccTrxn_trxnAmount IS AP_CREDIT_CARD_TRXNS.transaction_amount%TYPE;
32 SUBTYPE ccTrxn_merchantProv IS AP_CREDIT_CARD_TRXNS.merchant_province_state%TYPE;
33 SUBTYPE ccTrxn_transDate IS AP_CREDIT_CARD_TRXNS.transaction_date%TYPE;
34 SUBTYPE ccTrxn_paymentDueFromCode IS AP_CREDIT_CARD_TRXNS.payment_due_from_code%TYPE;
35
36
37 ---------------------------------------------------------------------------------------------------
38 /* AP Card Programs */
39 ---------------------------------------------------------------------------------------------------
40 SUBTYPE cardProgs_cardProgID IS AP_CARD_PROGRAMS.card_program_id%TYPE;
41 SUBTYPE cardProgs_cardProgName IS AP_CARD_PROGRAMS.card_program_name%TYPE;
42 SUBTYPE cardProgs_cardProgCurrCode IS AP_CARD_PROGRAMS.card_program_currency_code%TYPE;
43 SUBTYPE cardProgs_vendorID IS AP_CARD_PROGRAMS.vendor_id%TYPE;
44 SUBTYPE cardProgs_vendorSiteID IS AP_CARD_PROGRAMS.vendor_site_id%TYPE;
45 SUBTYPE cardProgs_cardTypeLookupCode IS AP_CARD_PROGRAMS.card_type_lookup_code%TYPE;
46 ---------------------------------------------------------------------------------------------------
47
48 /* AP Card Profiles */
49 ---------------------------------------------------------------------------------------------------
50 SUBTYPE cardProf_profileName IS AP_CARD_PROFILES.profile_name%TYPE;
51 SUBTYPE cardProf_directAcctEntryFlag IS AP_CARD_PROFILES.direct_acct_entry_flag%TYPE;
52 SUBTYPE cardProf_cardGLSetID IS AP_CARD_PROFILES.card_gl_set_id%TYPE;
53 SUBTYPE cardProf_empNotifLookupCode IS AP_CARD_PROFILES.emp_notification_lookup_code%TYPE;
54 SUBTYPE cardProf_mgrApprvlLookupCode IS AP_CARD_PROFILES.mgr_approval_lookup_code%TYPE;
55 ---------------------------------------------------------------------------------------------------
56
57 /* AP Cards */
58 ---------------------------------------------------------------------------------------------------
59 SUBTYPE cards_employeeID IS AP_CARDS.employee_id%TYPE;
60 SUBTYPE cards_cardId IS AP_CARDS.card_id%TYPE;
61
62 TYPE CreditCardCategoryCursor IS REF CURSOR;
63 TYPE CreditCardTrxnCursor IS REF CURSOR;
64 TYPE CreditCardInfoCursor IS REF CURSOR;
65 TYPE UnpaidCreditCardTrxnCursor IS REF CURSOR;
66 TYPE DisputedCCTrxnCursor IS REF CURSOR;
67 TYPE UnsubmittedCCTrxnCursor IS REF CURSOR;
68 TYPE DunningCCTrxnCursor IS REF CURSOR;
69 TYPE DisputeCCTrxnCursor IS REF CURSOR;--Notification Esc Prj
70 TYPE TotalCCTrxnCursor IS REF CURSOR;
71
72 TYPE CCardTrxnInfoRec IS RECORD (
73 trxn_id ccTrxn_trxID,
74 trxn_date ccTrxn_transDate,
75 folio_type ccTrxn_folioType,
76 merchant_name ccTrxn_merchantName1,
77 merchant_city ccTrxn_merchantCity,
78 merchant_prov ccTrxn_merchantProv,
79 billed_amount ccTrxn_billedAmount,
80 posted_curr_code ccTrxn_postedCurrCode,
81 trxn_amount ccTrxn_trxnAmount,
82 card_id ccTrxn_cardId,
83 category ccTrxn_category,
84 card_prog_id ccTrxn_cardProgID
85 );
86
87 --------------------------------------------------------------------------
88 FUNCTION GetCCardTrxnInfoForTrxnId(
89 p_trxn_id IN ccTrxn_trxID,
90 p_trxn_info_rec OUT NOCOPY CCardTrxnInfoRec
91 ) RETURN BOOLEAN;
92
93 ------------------------------------------------------
94 -- Name: GetExpRptCCTrxnCategoryCursor
95 -- Desc: get the cursor of the credit card categories for the given expense report
96 -- Params: p_ReportHeaderID - the given report header id
97 -- p_category - the returned cursor
98 -- Returns: true - succeeded
99 -- false - failed
100 -------------------------------------------------------
101 FUNCTION GetExpRptCCTrxnCategoryCursor(
102 P_ReportHeaderID IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
103 p_category OUT NOCOPY CreditCardCategoryCursor
104 ) RETURN BOOLEAN;
105 -------------------------------------------------------
106
107 -------------------------------------------------------
108 FUNCTION GetCreditCardTrxnCursor(
109 p_user_id IN cards_employeeID,
110 p_reimb_curr_code IN ccTrxn_billedCurrCode,
111 p_card_prog_id IN cardProgs_cardProgID,
112 p_card_id IN cards_cardId,
113 p_paymentDueFrom IN varchar2,
114 p_cc_trxn_cursor OUT NOCOPY CreditCardTrxnCursor
115 ) RETURN BOOLEAN;
116 --------------------------------------------------------
117
118 --------------------------------------------------------
119 -- Name: GetCreditCardInfoCursor
120 -- Desc: get the cursor of the misc. credit card info for the given employee id and card type
121 -- Params: p_user_id - the given card user id
122 -- p_card_type - the given card type
123 -- p_cc_info_cursor - the returned ccard info cursor
124 -- Returns: true - succeeded
125 -- false - failed
126 -------------------------------------------------------
127 FUNCTION GetCreditCardInfoCursor(
128 p_user_id IN cards_employeeID,
129 p_card_type IN cardProgs_cardTypeLookupCode DEFAULT 'TRAVEL',
130 p_cc_info_cursor OUT NOCOPY CreditCardInfoCursor
131 )
132 RETURN BOOLEAN;
133 --------------------------------------------------------
134
135 --------------------------------------------------------------------------------
136 -- Name: GetUnpaidCreditCardTrxnCursor
137 -- Desc: get the cursor of the un-paid credit card trxns for the given card id, payment due code, trxn start date and end date
138 -- Params: p_card_prog_id - the given card program id
139 -- p_payment_due_code -
140 FUNCTION GetUnpaidCreditCardTrxnCursor(
141 p_unpaid_cctrxn_cursor OUT NOCOPY UnpaidCreditCardTrxnCursor,
142 p_card_prog_id IN ccTrxn_cardProgID,
143 p_payment_due_code IN VARCHAR2,
144 p_start_date IN DATE DEFAULT NULL,
145 p_end_date IN DATE DEFAULT NULL
146
147 ) RETURN BOOLEAN;
148
149
150 --------------------------------------------------------------------------------
151 FUNCTION GetDisputedCcardTrxnCursor(
152 p_cardProgramId IN ccTrxn_cardProgID,
153 p_minimumAmount IN ccTrxn_billedAmount,
154 p_employeeId IN cards_employeeID,
155 p_billedStartDate IN ccTrxn_billedDate,
156 p_billedEndDate IN ccTrxn_billedDate,
157 p_disputed_cursor OUT NOCOPY DisputedCCTrxnCursor
158 ) RETURN BOOLEAN;
159 ---------------------------------------------------------------------------
160
161 --------------------------------------------------------------------------------
162 FUNCTION GetUnsubmittedCcardTrxnCursor(
163 p_cardProgramId IN ccTrxn_cardProgID,
164 p_employeeId IN cards_employeeID,
165 p_billedStartDate IN ccTrxn_billedDate,
166 p_billedEndDate IN ccTrxn_billedDate,
167 p_minimumAmount IN ccTrxn_billedAmount,
168 p_chargeType IN VARCHAR2,
169 p_unsubmitted_cursor OUT NOCOPY UnsubmittedCCTrxnCursor
170 ) RETURN BOOLEAN;
171 ---------------------------------------------------------------------------
172
173 --------------------------------------------------------------------------------
174 FUNCTION GetTotalUnsubmittedCCCursor(
175 p_cardProgramId IN ccTrxn_cardProgID,
176 p_employeeId IN cards_employeeID,
177 p_billedStartDate IN ccTrxn_billedDate,
178 p_billedEndDate IN ccTrxn_billedDate,
179 p_minimumAmount IN ccTrxn_billedAmount,
180 p_chargeType IN VARCHAR2,
181 p_total_cursor OUT NOCOPY UnsubmittedCCTrxnCursor
182 ) RETURN BOOLEAN;
183 ---------------------------------------------------------------------------
184
185 --------------------------------------------------------------------------------
186 FUNCTION GetDunningCcardTrxnCursor(
187 p_cardProgramId IN ccTrxn_cardProgID,
188 p_employeeId IN cards_employeeID,
189 p_min_bucket IN NUMBER,
190 p_max_bucket IN NUMBER,
191 p_dunning_cursor OUT NOCOPY DunningCCTrxnCursor
192 ) RETURN BOOLEAN;
193 ---------------------------------------------------------------------------
194
195 --------------------------------------------------------------------------------
196 FUNCTION GetTotalCcardTrxnCursor(
197 p_cardProgramId IN ccTrxn_cardProgID,
198 p_employeeId IN cards_employeeID,
199 p_min_bucket IN NUMBER,
200 p_max_bucket IN NUMBER,
201 p_total_cursor OUT NOCOPY TotalCCTrxnCursor
202 ) RETURN BOOLEAN;
203 ---------------------------------------------------------------------------
204
205 -------------------------------------------------------------------
206 FUNCTION GetCardProgramCurrencyCode(
207 p_card_prog_id IN cardProgs_cardProgID,
208 p_curr_code OUT NOCOPY cardProgs_cardProgCurrCode
209 ) RETURN BOOLEAN;
210 -------------------------------------------------------------------
211
212 -------------------------------------------------------------------
213 FUNCTION GetCCTrxnCategory(
214 p_trx_id IN ccTrxn_trxID,
215 p_category OUT NOCOPY ccTrxn_category
216 ) RETURN BOOLEAN;
217 -------------------------------------------------------------------
218
219 -------------------------------------------------------------------
220 FUNCTION GetCompPrepaidInvID(
221 p_trx_id IN ccTrxn_trxID,
222 p_prepaid_invoice_id OUT NOCOPY AP_EXPENSE_REPORT_LINES.company_prepaid_invoice_id%TYPE)
223 RETURN BOOLEAN;
224 -------------------------------------------------------------------
225
226 -------------------------------------------------------------------------------
227 FUNCTION GetExpensedAmt(
228 p_id IN ccTrxn_trxID,
229 p_amt OUT NOCOPY ccTrxn_expensedAmt
230 ) RETURN BOOLEAN;
231 --------------------------------------------------------------------------------
232
233 -------------------------------------------------------------------
234 FUNCTION SetCCTrxnReportHeaderID(
235 p_report_header_id IN NUMBER,
236 p_new_report_id IN NUMBER
237 ) RETURN BOOLEAN;
238 -------------------------------------------------------------------
239
240 --------------------------------------------------------------------------------
241 FUNCTION GetCardProgramInfo(
242 p_card_prog_id IN cardProgs_cardProgID,
243 p_vendor_id OUT NOCOPY cardProgs_vendorID,
244 p_vendor_site_id OUT NOCOPY cardProgs_vendorSiteID,
245 p_invoice_curr_code OUT NOCOPY cardProgs_cardProgCurrCode
246 ) RETURN BOOLEAN;
247 --------------------------------------------------------------------------------
248
249 -------------------------------------------------------------------
250 FUNCTION GetVendorIDs(
251 p_report_header_id IN ccTrxn_headerID,
252 p_vendor_id OUT NOCOPY cardProgs_vendorID,
253 p_vendor_site_id OUT NOCOPY cardProgs_vendorSiteID )
254 RETURN BOOLEAN;
255 -------------------------------------------------------------------
256
257 -------------------------------------------------------------------
258 FUNCTION GetCardProgramName(
259 p_cardProgramID IN cardProgs_cardProgID,
260 p_cardProgramName OUT NOCOPY cardProgs_cardProgName)
261 RETURN BOOLEAN;
262 -------------------------------------------------------------------
263
264
265 -------------------------------------------------------------------
266 FUNCTION GetCardProgramId(
267 p_cardProgramName IN cardProgs_cardProgName,
268 p_cardProgramID OUT NOCOPY cardProgs_cardProgID)
269 RETURN BOOLEAN;
270 -------------------------------------------------------------------
271
272
273 --------------------------------------------------------------------------------
274 FUNCTION CompanyHasTravelCardProgram(
275 p_companyHasCardProgram OUT NOCOPY VARCHAR2 )
276 RETURN boolean;
277 --------------------------------------------------------------------------------
278
279
280 --------------------------------------------------------------------------------
281 FUNCTION UserHasCreditCard(
282 p_userId IN cards_employeeID,
283 p_userHasCreditCard OUT NOCOPY VARCHAR2
284 ) RETURN BOOLEAN;
285 --------------------------------------------------------------------------------
286
287 --------------------------------------------------------------------------------
288 FUNCTION SetCCPolicyShortpaidReportID(
289 p_orig_expense_report_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
290 p_new_expense_report_id IN ccTrxn_headerID )
291 RETURN BOOLEAN;
292 --------------------------------------------------------------------------------
293
294 --------------------------------------------------------------------------------
295 FUNCTION SetCCReceiptShortpaidReportID(p_orig_expense_report_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE,
296 p_new_expense_report_id IN ccTrxn_headerID)
297 RETURN BOOLEAN;
298 --------------------------------------------------------------------------------
299
300 -------------------------------------------------------------------------------
301 FUNCTION UpdateExpensedAmount(
302 p_trxn_id IN ccTrxn_trxID,
306 --------------------------------------------------------------------------------
303 p_report_id IN ccTrxn_headerID,
304 p_expensed_amount IN ccTrxn_expensedAmt
305 ) RETURN BOOLEAN;
307
308 --------------------------------------------------------------------------------
309 FUNCTION ResetCCLines(
310 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE)
311 RETURN BOOLEAN;
312 --------------------------------------------------------------------------------
313
314 --------------------------------------------------------------------------------
315 FUNCTION SetStatus(
316 p_report_header_id IN ccTrxn_headerID,
317 p_status IN ccTrxn_expenseStatus
318 ) RETURN BOOLEAN;
319 --------------------------------------------------------------------------------
320
321
322 --------------------------------------------------------------------------------
323 FUNCTION ResetCCMgrRejectedCCLines(
324 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE
325 ) RETURN BOOLEAN;
326 --------------------------------------------------------------------------------
327
328 --------------------------------------------------------------------------------
329 FUNCTION ResetPersonalTrxns(
330 p_reportID IN ccTrxn_headerID
331 ) RETURN BOOLEAN;
332 --------------------------------------------------------------------------------
333 --------------------------------------------------------------------------------
334 FUNCTION ResetMgrRejectPersonalTrxns(
335 p_report_header_id IN AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE
336 ) RETURN BOOLEAN;
337 --------------------------------------------------------------------------------
338
339 --------------------------------------------------------------------------------
340 FUNCTION SetCCTrxnInvoiceId(
341 p_card_trxn_id IN ccTrxn_trxID,
342 p_invoice_id IN ccTrxn_companyPrepaidInvID
343 ) RETURN BOOLEAN;
344
345
346 --------------------------------------------------------------------------------
347 FUNCTION UpdateCCardCategory(
348 p_trxn_id IN ccTrxn_trxID,
349 p_category IN ccTrxn_category
350 ) RETURN BOOLEAN;
351
352 ------------------------------------------------------------------------------
353 FUNCTION GetExpensedAmountForTrxnId(
354 p_trxn_id IN ccTrxn_trxID,
355 p_exp_amount OUT NOCOPY ccTrxn_expensedAmt
356 ) RETURN BOOLEAN;
357
358 ------------------------------------------------------------------------------
359 FUNCTION isMultPayments(
360 p_cardProgramID IN cardProgs_cardProgID,
361 p_card_id IN cards_cardId
362 ) RETURN BOOLEAN;
363
364 ------------------------------------------------------------------------------
365 FUNCTION getPaymentDueCodeFromTrxn(
366 p_trxn_id IN ccTrxn_trxID)
367 RETURN VARCHAR2;
368 ------------------------------------------------------------------------------
369
370 FUNCTION getFirstLineWithCCTrxId(
371 p_expLines IN AP_WEB_DFLEX_PKG.ExpReportLines_A,
372 p_personalReceipts IN AP_WEB_DFLEX_PKG.ExpReportLines_A)
373 RETURN NUMBER;
374 ------------------------------------------------------------------------------
375
376 ------------------------------------------------------------------------------
377 PROCEDURE getAlertsForHomepage(
378 p_employee_id IN cards_employeeID,
379 p_cc_flag OUT NOCOPY VARCHAR2,
380 p_num_days OUT NOCOPY NUMBER,
381 p_num_old OUT NOCOPY NUMBER,
382 p_num_disputed OUT NOCOPY NUMBER,
383 p_num_credits OUT NOCOPY NUMBER,
384 p_delegate_flag OUT NOCOPY VARCHAR2);
385 ------------------------------------------------------------------------------
386
387 --Amulya Mishra : Notification Esc Project
388
389 FUNCTION GetDisputeCcardTrxnCursor(
390 p_cardProgramId IN ccTrxn_cardProgID,
391 p_employeeId IN cards_employeeID,
392 p_min_bucket IN NUMBER,
393 p_max_bucket IN NUMBER,
394 p_grace_days IN NUMBER,
395 p_dispute_cursor OUT NOCOPY DisputeCCTrxnCursor
396 ) RETURN BOOLEAN;
397 ------------------------------------------------------------------------------
398
399 --Amulya Mishra : Notification Esc Project
400
401 FUNCTION GetTotalCreditCardAmount(
402 p_cardProgramId IN ccTrxn_cardProgID,
403 p_employeeId IN cards_employeeID,
404 p_totalAmount OUT NOCOPY NUMBER
405 ) RETURN BOOLEAN;
406 ---------------------------------------------------------------------------
407
408 --Amulya Mishra : Notification Esc Project
409
410 FUNCTION GetTotalNumberOutstanding(
411 p_cardProgramId IN ccTrxn_cardProgID,
412 p_employeeId IN cards_employeeID,
413 p_min_bucket IN NUMBER,
414 p_max_bucket IN NUMBER,
415 p_total_outstanding OUT NOCOPY NUMBER,
416 p_total_amt_outstanding OUT NOCOPY NUMBER
417 ) RETURN BOOLEAN ;
418 -----------------------------------------------------------------------------
419
420 --Amulya Mishra : Notification Esc Project
421
422 FUNCTION GetTotalNumberDispute(
423 p_cardProgramId IN ccTrxn_cardProgID,
424 p_employeeId IN cards_employeeID,
425 p_min_bucket IN NUMBER,
426 p_max_bucket IN NUMBER,
427 p_grace_days IN NUMBER,
428 p_total_dispute OUT NOCOPY NUMBER,
429 p_total_amt_dispute OUT NOCOPY NUMBER
430 ) RETURN BOOLEAN ;
431 -----------------------------------------------------------------------------
432 -----------------------------------------------------------------------------
433 FUNCTION GetOldestAvailPostedDate(
434 p_cardProgramId IN ccTrxn_cardProgID,
435 p_cardId IN cards_cardId,
436 p_paymentDueFromCode IN ccTrxn_paymentDueFromCode,
437 p_reimb_curr_code IN ccTrxn_billedCurrCode,
438 p_report_header_id IN ccTrxn_headerID
439 ) RETURN DATE ;
440 -----------------------------------------------------------------------------
441
442
443 --AMulya Mishra : Bug 3562287
444 ----------------------------------------------------------------------------
445 FUNCTION GetLocation(
446 merchant_city IN VARCHAR2,
447 merchant_province_state IN VARCHAR2
448 ) RETURN VARCHAR2;
449 ----------------------------------------------------------------------------
450
451 ---------------------------------------------------------------------------
452 FUNCTION isCreditCardEnabled(
453 p_employee_id IN cards_employeeID,
454 p_user_id IN NUMBER DEFAULT NULL)
455 RETURN VARCHAR2;
456 ----------------------------------------------------------------------------
457
458 END AP_WEB_DB_CCARD_PKG;