1 PACKAGE AP_WEB_CC_VALIDATIONS_PKG as
2 /* $Header: apwccvls.pls 120.4 2006/05/02 22:59:09 abordia noship $ */
3
4 ----------------------------------------------------------------
5 -- This package has four sets of procedures/functions
6 -- 1) Define the "set" of transaction records to process
7 -- 2) Defaulting/Conversion routines
8 -- 3) Validation routines
9 -- 4) Utility routines
10 --
11 -- (1) "Set" routines
12 -- Of these, 1-3 work together. The "set" (1) routines are used to define the
13 -- set of credit card transaction records to process for steps (2) and (3).
14 -- These functions include:
15 -- o set_all_row_set
16 -- o set_row_set
17 --
18 -- (2) Defaulting/Conversion routines
19 -- These routines are used to default or convert values for the credit card
20 -- transaction records. They include the following functions:
21 -- o default_org_id
22 -- o set_request_id
23 -- o default_folio_type
24 -- o default_country_code
25 -- o default_payment_flag
26 -- o convert_currency_code
27 -- o get_locations
28 -- o default_merchant_name
29 --
30 -- (3) Validation routines
31 -- This performs individual validation checks. Each function is responsible
32 -- validating one piece of information.
33 -- They include the following functions:
34 -- o duplicate_trx
35 -- o invalid_billed_amount
36 -- o invalid_billed_currency_code
37 -- o invalid_billed_date
38 -- o invalid_card_number
39 -- o invalid_merchant_name
40 -- o invalid_posted_currency_code
41 -- o invalid_trx_amount
42 -- o invalid_trx_date
43 -- o check_employee_termination
44 -- o valid_trx
45 -- o invalid_sic_code
46 -- (valid_trx is a special one and this routine must be the last routine to
47 -- be called from (2) and (3)).
48 --
49 -- NOTE: All routines in (2) and (3) take a parameter - p_valid_only. If you
50 -- pass true for this value, it will only perform the operation on
51 -- the subset of records that have not yet failed any validation errors.
52 -- NOTE2: Routines in (1) return the number of rows in the set.
53 -- Routines in (2) return the number of rows that they defaulted/converted
54 -- Routines in (3) return the number of rows that failed validation
55 --
56 -- (4) Utility routines
57 -- These functions include:
58 -- o get_min_date
59 -- o assign_employee
60 ------------------------------------------------------------------------------
61
62 --------------------------------- (1) ----------------------------------------
63 -- Sets the context to all credit card transactions.
64 -- (This should probably used sparingly since it really chooses everything
65 -- - even across orgs)
66 function set_all_row_set return number;
67 -- Sets the context to the following criteria
68 -- (start/end dates are on the transaction date)
69 function set_row_set(p_request_id in number,
70 p_card_program_id in number,
71 p_start_date in date,
72 p_end_date in date) return number;
73 -- Sets the context to one specific transaction
74 function set_row_set(p_trx_id in number) return number;
75
76 --------------------------------- (2) ----------------------------------------
77 -- Default org_id - based on card program
78 function default_org_id(p_valid_only in boolean) return number;
79 --function set_request_id(p_request_id in number, p_valid_only in boolean) return number;
80 function set_request_id(p_request_id in number) return number;
81 -- Default folio type using folio type mapping rules
82 function default_folio_type(p_valid_only in boolean) return number;
83 -- Default eLocation country code using elocation mapping rules
84 function default_country_code(p_valid_only in boolean) return number;
85 -- Assign payment flags (based on card specific info)
86 function default_payment_flag(p_valid_only in boolean) return number;
87 -- Convert numeric currency codes into FND currency codes
88 function convert_currency_code(p_valid_only in boolean) return number;
89 -- eLocation integration
90 function get_locations(p_valid_only in boolean) return number;
91 -- Stamp CC Transactions with Payment Scenario of Card Program
92 function set_payment_scenario(p_valid_only in boolean) return number;
93
94 --------------------------------- (3) ----------------------------------------
95 -- Check for duplication transactions (card program, card number, reference number)
96 function duplicate_trx(p_valid_only in boolean) return number;
97 -- Check for non-zero, non-null billed amount
98 function invalid_billed_amount(p_valid_only in boolean) return number;
99 -- Check for valid billed currency code
100 function invalid_billed_currency_code(p_valid_only in boolean) return number;
101 -- Check for non-null billed date
102 function invalid_billed_date(p_valid_only in boolean) return number;
103 -- Check for inactive card number
104 function inactive_card_number(p_valid_only in boolean) return number;
105 -- Check for existing card number
106 function invalid_card_number(p_valid_only in boolean) return number;
107 -- Check for non-null merchant name
108 function invalid_merchant_name(p_valid_only in boolean) return number;
109 -- Check for valid posted currency code
110 function invalid_posted_currency_code(p_valid_only in boolean) return number;
111 -- Check for non-zero, non-null transaction amount
112 function invalid_trx_amount(p_valid_only in boolean) return number;
113 -- Check for non-null transaction date
114 function invalid_trx_date(p_valid_only in boolean) return number;
115 -- Check for transaction date after termination date
116 function check_employee_termination(p_valid_only in boolean) return number;
117
118 -- Marks the rows that are still valid as valid, and returns the number of
119 -- rows that are still valid
120 function valid_trx return number;
121
122 -- sic_code is required if transaction_type code is 10,11,20,22,80
123 -- sic_code Must be equal to 6010, 6011, 6012, 6050 or 6051 if the
124 -- transaction type code is 20,22,80
125 -- The validation is required for Visa VCF 4.0 Format
126 function invalid_sic_code(p_valid_only in boolean) return number;
127 --------------------------------- (4) ----------------------------------------
128 -- Returns the lesser of date1 and date2. Null values are considered to
129 -- be a date in the infinite future.
130 function get_min_date(date1 in date, date2 in date) return date;
131 -- Assign the employee to the card and activate it.
132 procedure assign_employee(p_card_id in number, p_employee_id in number, p_full_name in varchar2);
133 procedure assign_employee(p_card_id in number, p_employee_id in number);
134 -- Check to see if a unique candidate exists for the card.
135 -- If one exists, assign the employee to the card and activate it.
136
137
138 function validate_trx_detail_amount return number;
139
140
141 --------------------------------- (2) ----------------------------------------
142 -- Default merchant name for AMEX for trxn types 01,02,03,06,09,10,11,12
143 -- based on card program
144 function default_merchant_name(p_valid_only in boolean) return number;
145
146 function delete_invalid_rows(p_valid_only in boolean, card_program_id in number ) return number;
147 END ap_web_cc_validations_pkg;