1 PACKAGE OE_Verify_Payment_PUB AS
2 /* $Header: OEXPVPMS.pls 120.6.12010000.2 2008/10/17 11:30:13 cpati ship $ */
3
4 -- Called By Booking, Pre Ship or Purchase Release Processing.
5 -- Also called by the Delayed requests logged due to Order or
6 -- Line level attribute changes.
7 --
8 -- Checks if Electronic Payment is associated to the Order.
9 -- IF Yes THEN
10 -- Calls OE_Verify_Payment_PUB.Payment_Request Authorization
11 -- ELSE
12 -- Calls OE_Credit_PUB.OE_Check_Available_Credit for Credit
13 -- Limit Checking
14
15 G_credit_check_rule VARCHAR(50); --ER#7479609
16 G_init_calling_action VARCHAR(50); --ER#7479609
17
18 PROCEDURE Verify_Payment
19 ( p_header_id IN NUMBER -- Unique Order Header Id
20 , p_calling_action IN VARCHAR2 DEFAULT NULL -- BOOKING or SHIPPING or NULL
21 , p_delayed_request IN VARCHAR2 DEFAULT NULL -- Identifies if this call is from a delayed request
22 --R12 CVV2
23 --comm rej, p_reject_on_auth_failure IN VARCHAR2 DEFAULT NULL
24 --comm rej, p_reject_on_risk_failure IN VARCHAR2 DEFAULT NULL
25 , p_risk_eval_flag IN VARCHAR2 DEFAULT NULL --'Y' bug 6805953
26 --R12 CVV2
27 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER -- Message Count
28 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Messages
29 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Success or Failure
30 );
31
32 -- Function to find out Rule to be applied for Verify Payment
33
34 FUNCTION Which_Rule
35 ( p_header_id IN NUMBER)
36 RETURN VARCHAR2 ;
37
38 -- Function returns 'Y' if the rule identified by calling action
39 -- has been defined at the Order Type setup else returns 'N'.
40
41 FUNCTION Check_Rule_Defined
42 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type
43 , p_calling_action IN VARCHAR2 )
44 RETURN VARCHAR2 ;
45
46 -- Returns 'Y' if the Order is being paid using a Credit Card
47
48 FUNCTION Is_Electronic_Payment
49 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type )
50 RETURN VARCHAR2 ;
51
52 -- Returns 'Y' if there is specific Credit Card Hold applied on the order.
53
54 PROCEDURE Hold_Exists
55 ( p_header_id IN NUMBER -- Unique Order Header Id
56 , p_hold_id IN NUMBER -- Seeded Id of Hold to be applied
57 , p_hold_exists OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- 'Y' or 'N'
58 );
59
60 -- Releases Verification Holds on the Order. Flag electronic
61 -- payment hold identifies the type of hold to release.
62
63 PROCEDURE Release_Verify_Hold
64 ( p_header_id IN NUMBER -- Unique Order Header Id
65 , p_epayment_hold IN VARCHAR2 -- Pass 'Y' if E Payment Holds to remove
66 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER -- Message Count
67 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Messages
68 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Success or Failure
69 );
70
71 -- Applies a Credit Checking, CC Auth Failure Hold OR CC Risk
72 -- Hold based on the Hold Id Passed as Input Parameter.
73
74 PROCEDURE Apply_Verify_Hold
75 ( p_header_id IN NUMBER -- Unique Order Header Id
76 , p_hold_id IN NUMBER -- Seeded Id of Hold to be applied
77 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER -- Message Count
78 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Messages
79 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Success or Failure
80 );
81
82 -- Main Procedure called for all Electronic Payment Processing.
83
84 PROCEDURE Payment_Request
85 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type -- Order Header Record
86 , p_trxn_type IN VARCHAR2 -- E Payment Transaction Type
87 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER -- Message Count
88 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Messages
89 , p_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- PASS, FAIL, RISK
90 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Success or Failure
91 );
92
93 -- Returns 'Y' if iPayment is Installed else returns 'N'.
94
95 FUNCTION Check_Ipayment_Installed
96 RETURN VARCHAR2;
97
98 -- Authorizes a Credit Card Payment Request for an Order.
99
100 PROCEDURE Authorize_Payment
101 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type -- Header Record Type
102 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER -- Message Count
103 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Messages
104 , p_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- PASS, FAIL, RISK
105 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Success or Failure
106 );
107
108 -- Returns
109 -- 1. Last Authorization Transactions Associated to the Order
110 -- 2. Authorization Transaction currently associated to the Order
111 -- 3. The Order Total of Outbound Lines
112 -- 4. Flag to indicate if Reauthorization is required or Not
113 -- 5. Flag to indicate if the Current Transaction is Automatic or Manual
114
115 PROCEDURE Check_Reauthorize_Order
116 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type -- Order Header Record
117 , p_void_trxn_id OUT NOCOPY /* file.sql.39 change */ NUMBER -- ID of Trxn to be voided
118 , p_outbound_total OUT NOCOPY /* file.sql.39 change */ NUMBER -- Total of outbound lines in the order
119 , p_reauthorize_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Reauthorize or not 'Y', 'N'
120 );
121
122 -- Returns FALSE if one of the Attributes required for Auhtorization is missing
123
124 FUNCTION Validate_Required_Attributes
125 ( p_header_rec IN OE_Order_Pub.Header_Rec_Type ) -- Order Header Record
126 RETURN BOOLEAN;
127
128 -- Returns Primary Payment Method for the Customer
129
130 FUNCTION Get_Primary_Pay_Method
131 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type -- Header Record Type
132 ) RETURN NUMBER;
133
134 -- Returns Payment Method Details for a given Receipt Method Id
135
136 PROCEDURE Get_Pay_Method_Info
137 ( p_pay_method_id IN NUMBER -- Method ID
138 , p_pay_method_name OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Method Name
139 , p_merchant_id OUT NOCOPY /* file.sql.39 change */ NUMBER -- Merchant ID associated to Method
140 );
141
142 -- Voids an uncaptured authorization transaction.
143
144 PROCEDURE Void_Payment
145 ( p_void_trxn_id IN NUMBER -- Id of Transaction to be voided
146 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER -- Message Count
147 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Messages
148 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Success or Failure
149 , p_void_supported OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Void Supported ('Y') or Not ('N')
150 );
151
152 -- Returns 'Y' if Authorization Trxn has already been captured else returns 'N'
153
154 FUNCTION Check_Trxn_Captured
155 ( p_trxn_id IN NUMBER)
156 RETURN VARCHAR2 ;
157
158 -- Returns 'Y' if this authorization was taken through iPayment else 'N'.
159
160 PROCEDURE Fetch_Authorization_Trxn
161 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type -- Order Header Record
162 , p_trxn_id OUT NOCOPY /* file.sql.39 change */ NUMBER -- iPayment Transaction ID
163 , p_automatic_auth OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- 'Y' or 'N'
164 );
165
166 -- Fetches the Current Authorization Transaction for the Order
167
168 PROCEDURE Fetch_Current_Auth
169 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type -- Order Header Record
170 , p_line_id IN NUMBER DEFAULT NULL
171 , p_auth_code IN VARCHAR2 DEFAULT NULL
172 , p_auth_date IN DATE DEFAULT NULL
173 , p_trxn_id OUT NOCOPY /* file.sql.39 change */ NUMBER -- Transaction ID
174 , p_tangible_id OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Unique Tangible ID
175 );
176
177 -- Fetches the Last Authorization Transaction for the Order
178
179 PROCEDURE Fetch_Last_Auth
180 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type -- Order Header Record
181 , p_trxn_id OUT NOCOPY /* file.sql.39 change */ NUMBER -- Transaction ID
182 , p_tangible_id OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Unique Tangible ID
183 , p_auth_code OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- CC Approval Code
184 , p_trxn_date OUT NOCOPY /* file.sql.39 change */ DATE -- CC Approval Date
185 , p_amount OUT NOCOPY /* file.sql.39 change */ NUMBER -- Authorized Amount
186 );
187
188
189 -- Updates Order Header with Authorized Amount, Authorization Code and Date
190
191 PROCEDURE Update_Authorization_Info
192 ( p_header_id IN NUMBER -- Order Header ID
193 , p_auth_amount IN NUMBER -- Authorized Amount
194 , p_auth_code IN VARCHAR2 -- Authorization Code
195 , p_auth_date IN DATE -- Authorization Date
196 , p_msg_count OUT NOCOPY /* file.sql.39 change */ VARCHAR2
197 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
198 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
199 );
200
201 -- Function to find out Total Amount Captured for the Order
202
203 FUNCTION Captured_Amount_Total
204 ( p_header_id IN NUMBER)
205 RETURN NUMBER ;
206
207 PROCEDURE Authorize_MultiPayments
208 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type -- Order Header Record
209 , p_line_id IN NUMBER DEFAULT null --bug3524209
210 , p_calling_action IN VARCHAR2
211 --comm rej, p_reject_on_auth_failure IN VARCHAR2 DEFAULT NULL --R12 CC Encryption
212 --comm rej, p_reject_on_risk_failure IN VARCHAR2 DEFAULT NULL
213 , p_risk_eval_flag IN VARCHAR2 DEFAULT NULL --bug 6805953 'Y'
214 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER -- Message Count
215 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Messages
216 , p_result_out OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- PASS, FAIL, RISK
217 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Success or Failure
218 );
219
220 PROCEDURE Release_Verify_Line_Hold
221 ( p_header_id IN NUMBER -- Unique Order Header Id
222 , p_line_id IN NUMBER -- Unique Order Line Id
223 , p_epayment_hold IN VARCHAR2 -- Pass 'Y' if E Payment Holds to remove
224 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER -- Message Count
225 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Messages
226 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Success or Failure
227 );
228
229 -- Applies a Credit Checking, CC Auth Failure Hold OR CC Risk
230 -- Hold based on the Hold Id Passed as Input Parameter.
231
232 PROCEDURE Apply_Verify_Line_Hold
233 ( p_header_id IN NUMBER -- Unique Order Header Id
234 , p_line_id IN NUMBER -- Unique Order Line Id
235 , p_hold_id IN NUMBER -- Seeded Id of Hold to be applied
236 , p_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER -- Message Count
237 , p_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Messages
238 , p_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 -- Success or Failure
239 );
240
241 --Added this function for the bug 3571485
242 FUNCTION Get_Format_Mask(p_currency_code IN VARCHAR2)
243 RETURN VARCHAR2;
244
245 --pnpl start
246 --Moved the declaration of these two functions to the spec.
247 FUNCTION Get_Line_Total
248 ( p_line_id IN NUMBER
249 , p_header_id IN NUMBER
250 , p_currency_code IN VARCHAR2
251 , p_level IN VARCHAR2
252 , p_amount_type IN VARCHAR2 DEFAULT NULL --pnpl
253 , p_to_exclude_commitment IN VARCHAR2 DEFAULT 'Y' --bug3225795
254 ) RETURN NUMBER;
255
256 --This function also needs to be modified later to consider partial invoicing
257 FUNCTION Outbound_Order_Total
258 ( p_header_id IN NUMBER
259 , p_to_exclude_commitment IN VARCHAR2 DEFAULT 'Y'
260 , p_total_type IN VARCHAR2 DEFAULT NULL --pnpl
261 ) RETURN NUMBER;
262 --pnpl end
263
264 PROCEDURE Create_New_Payment_Trxn
265 ( p_trxn_extension_id IN NUMBER
266 , p_org_id IN NUMBER
267 , p_site_use_id IN NUMBER
268 , p_line_id IN NUMBER DEFAULT NULL
269 , p_instrument_security_code IN VARCHAR2 DEFAULT NULL --bug 5028932
270 , x_trxn_extension_id OUT NOCOPY NUMBER
271 , x_msg_count OUT NOCOPY NUMBER
272 , x_msg_data OUT NOCOPY VARCHAR2
273 , x_return_status OUT NOCOPY VARCHAR2
274 );
275
276
277 END OE_Verify_Payment_PUB;