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