1 PACKAGE AP_WEB_CC_VALIDATIONS_PKG AUTHID CURRENT_USER as
2 /* $Header: apwccvls.pls 120.4.12010000.4 2010/04/07 12:26:46 meesubra ship $ */
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 function set_validate_request_id(p_request_id in number) return number;
82 -- Default folio type using folio type mapping rules
83 function default_folio_type(p_valid_only in boolean) return number;
84 -- Default Detail folio type using Detail folio type mapping rules
85 function default_detail_folio_type(p_valid_only in boolean) return number;
86 -- Default eLocation country code using elocation mapping rules
87 function default_country_code(p_valid_only in boolean) return number;
88 -- Assign payment flags (based on card specific info)
89 function default_payment_flag(p_valid_only in boolean) return number;
90 -- Convert numeric currency codes into FND currency codes
91 function convert_currency_code(p_valid_only in boolean) return number;
92 -- eLocation integration
93 function get_locations(p_valid_only in boolean) return number;
94 -- Stamp CC Transactions with Payment Scenario of Card Program
95 function set_payment_scenario(p_valid_only in boolean) return number;
96
97 --------------------------------- (3) ----------------------------------------
98 -- Check for duplication transactions (card program, card number, reference number)
99 function duplicate_trx(p_valid_only in boolean) return number;
100 -- Check for non-zero, non-null billed amount
101 function invalid_billed_amount(p_valid_only in boolean) return number;
102 -- Check for valid billed currency code
103 function invalid_billed_currency_code(p_valid_only in boolean) return number;
104 -- Check for non-null billed date
105 function invalid_billed_date(p_valid_only in boolean) return number;
106 -- Check for inactive card number
107 function inactive_card_number(p_valid_only in boolean) return number;
108 -- Check for existing card number
109 function invalid_card_number(p_valid_only in boolean) return number;
110 -- Check for non-null merchant name
111 function invalid_merchant_name(p_valid_only in boolean) return number;
112 -- Check for valid posted currency code
113 function invalid_posted_currency_code(p_valid_only in boolean) return number;
114 -- Check for non-zero, non-null transaction amount
115 function invalid_trx_amount(p_valid_only in boolean) return number;
116 -- Check for non-null transaction date
117 function invalid_trx_date(p_valid_only in boolean) return number;
118 -- Check for transaction date after termination date
119 function check_employee_termination(p_valid_only in boolean) return number;
120
121 -- Marks the rows that are still valid as valid, and returns the number of
122 -- rows that are still valid
123 function valid_trx return number;
124
125 -- sic_code is required if transaction_type code is 10,11,20,22,80
126 -- sic_code Must be equal to 6010, 6011, 6012, 6050 or 6051 if the
127 -- transaction type code is 20,22,80
128 -- The validation is required for Visa VCF 4.0 Format
129 function invalid_sic_code(p_valid_only in boolean) return number;
130 --------------------------------- (4) ----------------------------------------
131 -- Returns the lesser of date1 and date2. Null values are considered to
132 -- be a date in the infinite future.
133 function get_min_date(date1 in date, date2 in date) return date;
134 -- Assign the employee to the card and activate it.
135 procedure assign_employee(p_card_id in number, p_employee_id in number, p_full_name in varchar2);
136 procedure assign_employee(p_card_id in number, p_employee_id in number);
137 -- Check to see if a unique candidate exists for the card.
138 -- If one exists, assign the employee to the card and activate it.
139
140
141 function validate_trx_detail_amount return number;
142
143
144 --------------------------------- (2) ----------------------------------------
145 -- Default merchant name for AMEX for trxn types 01,02,03,06,09,10,11,12
146 -- based on card program
147 function default_merchant_name(p_valid_only in boolean) return number;
148
149 function delete_invalid_rows(p_valid_only in boolean, card_program_id in number ) return number;
150
151 function duplicate_global_trx(p_valid_only in boolean) return NUMBER;
152 END ap_web_cc_validations_pkg;
[Home] [Help]