[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Delete_Payment_at_header(p_header_id in number,
p_line_id in number := null,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2);
l_rowtype_rec.LAST_UPDATED_BY := FND_API.G_MISS_NUM;
l_rowtype_rec.LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
l_rowtype_rec.LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
l_rowtype_rec.PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
x_rowtype_rec.LAST_UPDATED_BY := p_header_Payment_rec.LAST_UPDATED_BY;
x_rowtype_rec.LAST_UPDATE_DATE := p_header_Payment_rec.LAST_UPDATE_DATE;
x_rowtype_rec.LAST_UPDATE_LOGIN := p_header_Payment_rec.LAST_UPDATE_LOGIN;
x_rowtype_rec.PROGRAM_UPDATE_DATE := p_header_Payment_rec.PROGRAM_UPDATE_DATE;
x_api_rec.LAST_UPDATED_BY := p_record.LAST_UPDATED_BY;
x_api_rec.LAST_UPDATE_DATE := p_record.LAST_UPDATE_DATE;
x_api_rec.LAST_UPDATE_LOGIN := p_record.LAST_UPDATE_LOGIN;
x_api_rec.PROGRAM_UPDATE_DATE := p_record.PROGRAM_UPDATE_DATE;
IF NOT OE_GLOBALS.Equal(p_x_Header_Payment_rec.last_updated_by,p_old_Header_Payment_rec.last_updated_by)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_HEADER_PAYMENT_UTIL.G_LAST_UPDATED_BY;
IF NOT OE_GLOBALS.Equal(p_x_Header_Payment_rec.last_update_date,p_old_Header_Payment_rec.last_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_HEADER_PAYMENT_UTIL.G_LAST_UPDATE_DATE;
IF NOT OE_GLOBALS.Equal(p_x_Header_Payment_rec.last_update_login,p_old_Header_Payment_rec.last_update_login)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_HEADER_PAYMENT_UTIL.G_LAST_UPDATE_LOGIN;
IF NOT OE_GLOBALS.Equal(p_x_Header_Payment_rec.program_update_date,p_old_Header_Payment_rec.program_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_HEADER_PAYMENT_UTIL.G_PROGRAM_UPDATE_DATE;
ELSIF p_attr_id = G_LAST_UPDATED_BY THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_HEADER_PAYMENT_UTIL.G_LAST_UPDATED_BY;
ELSIF p_attr_id = G_LAST_UPDATE_DATE THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_HEADER_PAYMENT_UTIL.G_LAST_UPDATE_DATE;
ELSIF p_attr_id = G_LAST_UPDATE_LOGIN THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_HEADER_PAYMENT_UTIL.G_LAST_UPDATE_LOGIN;
ELSIF p_attr_id = G_PROGRAM_UPDATE_DATE THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_HEADER_PAYMENT_UTIL.G_PROGRAM_UPDATE_DATE;
ELSIF l_dep_attr_tbl(I) = OE_HEADER_PAYMENT_UTIL.G_LAST_UPDATED_BY THEN
p_x_Header_PAYMENT_rec.LAST_UPDATED_BY := FND_API.G_MISS_NUM;
ELSIF l_dep_attr_tbl(I) = OE_HEADER_PAYMENT_UTIL.G_LAST_UPDATE_DATE THEN
p_x_Header_PAYMENT_rec.LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
ELSIF l_dep_attr_tbl(I) = OE_HEADER_PAYMENT_UTIL.G_LAST_UPDATE_LOGIN THEN
p_x_Header_PAYMENT_rec.LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
ELSIF l_dep_attr_tbl(I) = OE_HEADER_PAYMENT_UTIL.G_PROGRAM_UPDATE_DATE THEN
p_x_Header_PAYMENT_rec.PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
Procedure Delete_PaymentType_Hold
(
p_header_id IN NUMBER
, p_payment_number IN NUMBER
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR order_paytype_hold IS
Select OH.Order_hold_id,NVL(OH.hold_release_id,0)
FROM OE_HOLD_SOURCES HS,OE_ORDER_HOLDS OH,OE_PAYMENTS OP
WHERE HS.hold_source_id=OH.hold_source_id
AND OH.header_id=OP.header_id
AND OP.payment_number=p_payment_number
AND ((HS.hold_entity_code= 'P' AND HS.hold_entity_id=OP.payment_type_code)
OR (HS.hold_entity_code2 = 'P' AND HS.hold_entity_id2=OP.payment_type_code))
AND OH.header_id=p_header_id
AND OH.line_id IS NULL
AND OP.line_id IS NULL;
oe_debug_pub.add( 'Entering Delete_PaymentType_Hold' , 3 ) ;
DELETE FROM OE_ORDER_HOLDS
WHERE order_hold_id = l_order_hold_id;
DELETE FROM OE_HOLD_RELEASES
WHERE HOLD_RELEASE_ID = l_hold_release_id
AND ORDER_HOLD_ID = l_order_hold_id;
oe_debug_pub.add( 'Exiting Delete_PaymentType_Hold' , 3 ) ;
END Delete_PaymentType_Hold;
l_payments_update_flag VARCHAR2(1) := 'N';
l_delete_payment_hold VARCHAR2(1) := 'N';
l_delete_pmt_param1 VARCHAR2(30);
select ooh.payment_type_code,
ooh.payment_amount,
ooh.check_number,
ite.card_issuer_code,
ite.card_holder_name,
ite.card_number,
ite.card_expirydate
from oe_order_headers_all ooh,
oe_payments op,
iby_trxn_extensions_v ite
where ooh.header_id = p_x_header_payment_rec.header_id
and ooh.header_id = op.header_id
and op.trxn_extension_id = ite.trxn_extension_id;
select ooh.payment_type_code,
ooh.payment_amount,
ooh.check_number,
c.card_issuer_code,
c.chname,
c.masked_cc_number,
c.expirydate
from oe_order_headers_all ooh,
oe_payments op,
iby_creditcard c,
iby_pmt_instr_uses_all u,
iby_fndcpt_tx_extensions x
where ooh.header_id = p_x_header_payment_rec.header_id
and ooh.header_id = op.header_id
and op.trxn_extension_id = x.trxn_extension_id
and x.instr_assignment_id = u.instrument_payment_use_id(+)
and u.instrument_id = c.instrid(+);
IF NOT OE_GLOBALS.Equal(p_x_Header_Payment_rec.last_updated_by,p_old_Header_Payment_rec.last_updated_by)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_x_Header_Payment_rec.last_update_date,p_old_Header_Payment_rec.last_update_date)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_x_Header_Payment_rec.last_update_login,p_old_Header_Payment_rec.last_update_login)
THEN
NULL;
l_payments_update_flag := 'Y';
l_payments_update_flag := 'Y';
l_payments_update_flag := 'Y';
l_payments_update_flag := 'Y';
l_payments_update_flag := 'Y';
l_payments_update_flag := 'Y';
l_payments_update_flag := 'Y';
IF (p_x_Header_Payment_rec.operation ='UPDATE'
AND OE_GLOBALS.Equal(p_x_Header_Payment_rec.payment_type_code,p_old_Header_Payment_rec.payment_type_code)) THEN
l_verify_payment_flag:='Y';
l_payments_update_flag := 'Y';
l_payments_update_flag := 'Y';
SELECT booked_flag INTO l_booked_flag
FROM oe_order_headers_all
WHERE header_id=p_x_header_Payment_rec.header_id;
l_payments_update_flag := 'Y';
AND p_x_header_payment_rec.operation = 'UPDATE'
THEN
l_delete_payment_hold := 'Y';
l_payments_update_flag := 'Y';
AND p_x_header_payment_rec.operation = 'UPDATE'
THEN
l_delete_payment_hold := 'Y';
SELECT booked_flag INTO l_booked_flag
FROM oe_order_headers_all
WHERE header_id=p_x_header_Payment_rec.header_id;
SELECT credit_check_flag INTO l_cc_flag_old
FROM oe_payment_types_vl
WHERE payment_type_code=p_old_Header_Payment_rec.payment_type_code;
SELECT credit_check_flag INTO l_cc_flag_new
FROM oe_payment_types_vl
WHERE payment_type_code=p_x_Header_Payment_rec.payment_type_code;
IF NOT OE_GLOBALS.Equal(p_x_Header_Payment_rec.program_update_date,p_old_Header_Payment_rec.program_update_date)
THEN
NULL;
IF l_payments_update_flag = 'Y' and
OE_PrePayment_UTIL.IS_MULTIPLE_PAYMENTS_ENABLED = TRUE then
--bug3781675 start
/* get header payment info */
open header_payment_cur;
p_x_header_payment_rec.operation = 'UPDATE')) then
--bug3781675 end
oe_debug_pub.add('logging synch from UHPMB.pls payment delayed request', 1);
p_request_type => OE_GLOBALS.G_UPDATE_HDR_PAYMENT,
p_param1 => 'UPDATE_HEADER',
x_return_status => l_return_status);
IF l_delete_payment_hold = 'Y' THEN
oe_debug_pub.add('logging request to release payment related holds', 3);
l_delete_pmt_param1 := 'PREPAYMENT';
l_delete_pmt_param1 := 'VERIFY_PAYMENT';
p_request_type => OE_GLOBALS.G_DELETE_PAYMENT_HOLD,
p_param1 => l_delete_pmt_param1,
x_return_status => l_return_status);
IF p_x_Header_Payment_rec.last_updated_by = FND_API.G_MISS_NUM THEN
p_x_Header_Payment_rec.last_updated_by := p_old_Header_Payment_rec.last_updated_by;
IF p_x_Header_Payment_rec.last_update_date = FND_API.G_MISS_DATE THEN
p_x_Header_Payment_rec.last_update_date := p_old_Header_Payment_rec.last_update_date;
IF p_x_Header_Payment_rec.last_update_login = FND_API.G_MISS_NUM THEN
p_x_Header_Payment_rec.last_update_login := p_old_Header_Payment_rec.last_update_login;
IF p_x_Header_Payment_rec.program_update_date = FND_API.G_MISS_DATE THEN
p_x_Header_Payment_rec.program_update_date := p_old_Header_Payment_rec.program_update_date;
IF p_x_Header_Payment_rec.last_updated_by = FND_API.G_MISS_NUM THEN
p_x_Header_Payment_rec.last_updated_by := NULL;
IF p_x_Header_Payment_rec.last_update_date = FND_API.G_MISS_DATE THEN
p_x_Header_Payment_rec.last_update_date := NULL;
IF p_x_Header_Payment_rec.last_update_login = FND_API.G_MISS_NUM THEN
p_x_Header_Payment_rec.last_update_login := NULL;
IF p_x_Header_Payment_rec.program_update_date = FND_API.G_MISS_DATE THEN
p_x_Header_Payment_rec.program_update_date := NULL;
PROCEDURE Update_Row
( p_Header_Payment_rec IN OUT NOCOPY OE_Order_PUB.Header_Payment_Rec_Type
)
IS
l_lock_control NUMBER;
SELECT lock_control
INTO l_lock_control
FROM OE_PAYMENTS
WHERE payment_number = p_Header_Payment_rec.payment_number
AND header_id = p_Header_Payment_rec.header_id
AND line_id IS NULL;
/* OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_Hdr_pmt_rec =>p_header_Payment_rec,
p_hdr_pmt_id => p_header_Payment_rec.payment_number,
x_index => l_index,
x_return_status => l_return_status);*/
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_HEADER_Payment_UTIL.UPDATE_ROW IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.UPDATE_ROW' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_HEADER_Payment_UTIL.UPDATE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.UPDATE_ROW' , 1 ) ;
UPDATE OE_PAYMENTS
SET ATTRIBUTE1 = p_Header_Payment_rec.attribute1
, ATTRIBUTE2 = p_Header_Payment_rec.attribute2
, ATTRIBUTE3 = p_Header_Payment_rec.attribute3
, ATTRIBUTE4 = p_Header_Payment_rec.attribute4
, ATTRIBUTE5 = p_Header_Payment_rec.attribute5
, ATTRIBUTE6 = p_Header_Payment_rec.attribute6
, ATTRIBUTE7 = p_Header_Payment_rec.attribute7
, ATTRIBUTE8 = p_Header_Payment_rec.attribute8
, ATTRIBUTE9 = p_Header_Payment_rec.attribute9
, ATTRIBUTE10 = p_Header_Payment_rec.attribute10
, ATTRIBUTE11 = p_Header_Payment_rec.attribute11
, ATTRIBUTE12 = p_Header_Payment_rec.attribute12
, ATTRIBUTE13 = p_Header_Payment_rec.attribute13
, ATTRIBUTE14 = p_Header_Payment_rec.attribute14
, ATTRIBUTE15 = p_Header_Payment_rec.attribute15
, CONTEXT = p_Header_Payment_rec.context
--16309734 start
/*
, CREATED_BY = p_Header_Payment_rec.created_by
, CREATION_DATE = p_Header_Payment_rec.creation_date
*/
--16309734 End
, LAST_UPDATED_BY = p_Header_Payment_rec.last_updated_by
, LAST_UPDATE_DATE = p_Header_Payment_rec.last_update_date
, LAST_UPDATE_LOGIN = p_Header_Payment_rec.last_update_login
, CHECK_NUMBER = p_Header_Payment_rec.check_number
/*, CREDIT_CARD_APPROVAL_CODE = p_Header_Payment_rec.credit_card_approval_code --R12 CC Encryption
, CREDIT_CARD_APPROVAL_DATE = p_Header_Payment_rec.credit_card_approval_date
, CREDIT_CARD_CODE = p_Header_Payment_rec.credit_card_code
, CREDIT_CARD_EXPIRATION_DATE = p_Header_Payment_rec.credit_card_expiration_date
, CREDIT_CARD_HOLDER_NAME = p_Header_Payment_rec.credit_card_holder_name
, CREDIT_CARD_NUMBER = p_Header_Payment_rec.credit_card_number*/ --R12 CC Encryption
, PAYMENT_LEVEL_CODE = p_Header_Payment_rec.payment_level_code
, COMMITMENT_APPLIED_AMOUNT = p_Header_Payment_rec.commitment_applied_amount
, COMMITMENT_INTERFACED_AMOUNT = p_Header_Payment_rec.commitment_interfaced_amount
, PAYMENT_NUMBER = p_Header_Payment_rec.payment_number
, HEADER_ID = p_Header_Payment_rec.header_id
, LINE_ID = p_Header_Payment_rec.line_id
, PAYMENT_AMOUNT = p_Header_Payment_rec.payment_amount
, PAYMENT_PERCENTAGE = p_Header_Payment_rec.payment_percentage -- Added for bug 8478559
, PAYMENT_COLLECTION_EVENT = p_Header_Payment_rec.payment_collection_event
, PAYMENT_TRX_ID = p_Header_Payment_rec.payment_trx_id
, PAYMENT_TYPE_CODE = p_Header_Payment_rec.payment_type_code
, PAYMENT_SET_ID = p_Header_Payment_rec.payment_set_id
, PREPAID_AMOUNT = p_Header_Payment_rec.prepaid_amount
, PROGRAM_APPLICATION_ID = p_Header_Payment_rec.program_application_id
, PROGRAM_ID = p_Header_Payment_rec.program_id
, PROGRAM_UPDATE_DATE = p_Header_Payment_rec.program_update_date
, RECEIPT_METHOD_ID = p_Header_Payment_rec.receipt_method_id
, REQUEST_ID = p_Header_Payment_rec.request_id
--, TANGIBLE_ID = p_Header_Payment_rec.tangible_id --R12 CC Encryption
, DEFER_PAYMENT_PROCESSING_FLAG = p_Header_Payment_rec.defer_payment_processing_flag
, TRXN_EXTENSION_ID = p_Header_Payment_rec.trxn_extension_id
, LOCK_CONTROL = l_lock_control
WHERE PAYMENT_NUMBER = p_Header_Payment_rec.payment_number
AND HEADER_ID = p_Header_Payment_rec.header_id
AND LINE_ID IS NULL;
, 'Update_Row'
);
END Update_Row;
PROCEDURE Insert_Row
( p_Header_Payment_rec IN OUT NOCOPY OE_Order_PUB.Header_Payment_Rec_Type
)
IS
l_lock_control NUMBER:= 1;
oe_debug_pub.add( 'ENTERING OE_HEADER_Payment_UTIL.INSERT_ROW' , 1 ) ;
INSERT INTO OE_PAYMENTS
( ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, CONTEXT
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, CHECK_NUMBER
/*, CREDIT_CARD_APPROVAL_CODE --R12 CC Encryption
, CREDIT_CARD_APPROVAL_DATE
, CREDIT_CARD_CODE
, CREDIT_CARD_EXPIRATION_DATE
, CREDIT_CARD_HOLDER_NAME
, CREDIT_CARD_NUMBER*/
, PAYMENT_LEVEL_CODE
, COMMITMENT_APPLIED_AMOUNT
, COMMITMENT_INTERFACED_AMOUNT
, PAYMENT_NUMBER
, HEADER_ID
, LINE_ID
, PAYMENT_AMOUNT
, PAYMENT_PERCENTAGE -- Added for bug 8478559
, PAYMENT_COLLECTION_EVENT
, PAYMENT_TRX_ID
, PAYMENT_TYPE_CODE
, PAYMENT_SET_ID
, PREPAID_AMOUNT
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, RECEIPT_METHOD_ID
, REQUEST_ID
--, TANGIBLE_ID
, DEFER_PAYMENT_PROCESSING_FLAG
, TRXN_EXTENSION_ID
, LOCK_CONTROL
, ORIG_SYS_PAYMENT_REF --bug# 14354772
)
VALUES
( p_Header_Payment_rec.attribute1
, p_Header_Payment_rec.attribute2
, p_Header_Payment_rec.attribute3
, p_Header_Payment_rec.attribute4
, p_Header_Payment_rec.attribute5
, p_Header_Payment_rec.attribute6
, p_Header_Payment_rec.attribute7
, p_Header_Payment_rec.attribute8
, p_Header_Payment_rec.attribute9
, p_Header_Payment_rec.attribute10
, p_Header_Payment_rec.attribute11
, p_Header_Payment_rec.attribute12
, p_Header_Payment_rec.attribute13
, p_Header_Payment_rec.attribute14
, p_Header_Payment_rec.attribute15
, p_Header_Payment_rec.context
, p_Header_Payment_rec.created_by
, p_Header_Payment_rec.creation_date
, p_Header_Payment_rec.last_updated_by
, p_Header_Payment_rec.last_update_date
, p_Header_Payment_rec.last_update_login
, p_Header_Payment_rec.check_number
/*, p_Header_Payment_rec.credit_card_approval_code --R12 CC Encryption
, p_Header_Payment_rec.credit_card_approval_date
, p_Header_Payment_rec.credit_card_code
, p_Header_Payment_rec.credit_card_expiration_date
, p_Header_Payment_rec.credit_card_holder_name
, p_Header_Payment_rec.credit_card_number*/ --R12 CC Encryption
, p_Header_Payment_rec.payment_level_code
, p_Header_Payment_rec.commitment_applied_amount
, p_Header_Payment_rec.commitment_interfaced_amount
, p_Header_Payment_rec.payment_number
, p_Header_Payment_rec.header_id
, p_Header_Payment_rec.line_id
, p_Header_Payment_rec.payment_amount
, p_Header_Payment_rec.payment_percentage -- Added for bug 8478559
, p_Header_Payment_rec.payment_collection_event
, p_Header_Payment_rec.payment_trx_id
, p_Header_Payment_rec.payment_type_code
, p_Header_Payment_rec.payment_set_id
, p_Header_Payment_rec.prepaid_amount
, p_Header_Payment_rec.program_application_id
, p_Header_Payment_rec.program_id
, p_Header_Payment_rec.program_update_date
, p_Header_Payment_rec.receipt_method_id
, p_Header_Payment_rec.request_id
--, p_Header_Payment_rec.tangible_id --R12 CC Encryption
, p_Header_Payment_rec.defer_payment_processing_flag
, p_Header_Payment_rec.trxn_extension_id
, l_lock_control
, p_header_Payment_rec.ORIG_SYS_PAYMENT_REF --bug# 14354772
);
OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_old_hdr_pmt_rec => NULL,
p_Hdr_pmt_rec =>p_header_Payment_rec,
p_hdr_pmt_id => p_header_Payment_rec.payment_number,
x_index => l_index,
x_return_status => l_return_status); */
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_HEADER_Payment_UTIL.INSERT_ROW IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.INSERT_ROW' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_HEADER_Payment_UTIL.INSERT_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.INSERT_ROW' , 1 ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.INSERT_ROW' , 1 ) ;
, 'Insert_Row'
);
END Insert_Row;
PROCEDURE Delete_Row
( p_payment_number IN NUMBER :=
FND_API.G_MISS_NUM
, p_header_id IN NUMBER :=
FND_API.G_MISS_NUM
)
IS
l_return_status VARCHAR2(30);
SELECT payment_number, payment_collection_event,trxn_extension_id,payment_type_code --R12 CC Encryption
FROM OE_PAYMENTS
WHERE HEADER_ID = p_header_id
AND line_id is NULL;
oe_debug_pub.add( 'ENTERING OE_HEADER_Payment_UTIL.DELETE_ROW' , 1 ) ;
/* Set the operation on the record so that globals are updated as well */
l_new_header_Payment_rec.operation := OE_GLOBALS.G_OPR_DELETE;
/* OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_Hdr_pmt_rec =>l_new_header_Payment_rec,
p_hdr_pmt_id => payment_number,
x_index => l_index,
x_return_status => l_return_status); */
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_HEADER_Payment_UTIL.DELETE_ROW IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_HEADER_Payment_UTIL.DELETE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.DELETE_ROW' , 1 ) ;
--Calling delete API of Oracle Payments only if the payment
--type is credit card ach or direct debit.
IF l_pmt.payment_type_code in ('CREDIT_CARD','ACH','DIRECT_DEBIT') THEN
--Passing the invoice to org id as null to Delete_Payment_trxn API
--as the old invoice to org id needs to be passed to this api in
--case of deleting the payments due to bill-to site change.
/*select invoice_to_org_id into l_invoice_to_org_id --Verify
from oe_order_headers_all where
header_id = p_header_id;*/
OE_PAYMENT_TRXN_UTIL.Delete_Payment_Trxn
(p_header_id => p_header_id,
p_line_id => null,
p_payment_number=> null,
P_site_use_id => l_invoice_to_org_id,
p_trxn_extension_id => l_pmt.trxn_extension_id,
x_return_status =>l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
oe_debug_pub.add( 'Delete Payment at Header - UNEXPECTED ERROR' ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( 'Delete Payment at Header - OE_HEADER_Payment_UTIL.DELETE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.DELETE_ROW' , 1 ) ;
OE_Delayed_Requests_Pvt.Delete_Reqs_for_deleted_entity(
p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_PAYMENT,
p_entity_id => p_header_id,
x_return_status => l_return_status
);
DELETE FROM OE_PAYMENTS
WHERE HEADER_ID = p_header_id
AND LINE_ID IS NULL;
oe_debug_pub.add('1: after executing delete statement against oe_payments - before delete_payment_at_header');
Delete_Payment_at_header(p_header_id => p_header_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/* Set the operation on the record so that globals are updated as well */
l_new_header_Payment_rec.operation := OE_GLOBALS.G_OPR_DELETE;
/* OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_Hdr_pmt_rec =>l_new_header_Payment_rec,
p_hdr_pmt_id => p_payment_number,
x_index => l_index,
x_return_status => l_return_status); */
Delete_PaymentType_Hold
(
p_header_id => p_header_id
, p_payment_number => p_payment_number
, x_msg_count => l_msg_count
, x_msg_data => l_msg_data
, x_return_status => l_return_status
);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_HEADER_Payment_UTIL.DELETE_ROW IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_HEADER_Payment_UTIL.DELETE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.DELETE_ROW' , 1 ) ;
OE_Delayed_Requests_Pvt.Delete_Reqs_for_deleted_entity(
p_entity_code => OE_GLOBALS.G_ENTITY_HEADER_Payment,
p_entity_id => p_header_id,
x_return_status => l_return_status
);
SELECT payment_collection_event,trxn_extension_id,payment_type_code --R12 CC Encryption
INTO l_pmt_collection_event,l_trxn_extension_id,l_payment_type_code
FROM oe_payments
WHERE payment_number = p_payment_number
AND header_id = p_header_id
AND line_id is null;
--Passing the invoice to org id as null to Delete_Payment_trxn API
--as the old invoice to org id needs to be passed to this api in
--case of deleting the payments due to bill-to site change.
/*select invoice_to_org_id into l_invoice_to_org_id
from oe_order_headers_all where header_id = p_header_id;*/
OE_PAYMENT_TRXN_UTIL.Delete_Payment_Trxn
(p_header_id => p_header_id,
p_line_id => null,
p_payment_number=> p_payment_number,
P_site_use_id => l_invoice_to_org_id,
p_trxn_extension_id => l_trxn_extension_id,
x_return_status =>l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
oe_debug_pub.add( '2:Delete Payment at Header - UNEXPECTED ERROR' ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( '2:Delete Payment at Header - OE_HEADER_Payment_UTIL.DELETE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_HEADER_Payment_UTIL.DELETE_ROW' , 1 ) ;
DELETE FROM OE_PAYMENTS
WHERE HEADER_ID = p_header_id
AND PAYMENT_NUMBER = p_payment_number
AND LINE_ID IS NULL
;
oe_debug_pub.add('2: after executing delete statement against oe_payments - before delete_payment_at_header');
Delete_Payment_at_header(p_header_id => p_header_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
SELECT count(*)
INTO l_prepay_count
FROM oe_payments
WHERE nvl(payment_collection_event, 'PREPAY') = 'PREPAY'
AND header_id = p_header_id
AND line_id is null;
oe_debug_pub.add('exiting oe_header_Payment_util.delete_row ',1);
, 'Delete_Row'
);
END Delete_Row;
SELECT ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, CONTEXT
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, CHECK_NUMBER --R12 CC Encryption Verify
, CREDIT_CARD_APPROVAL_CODE
, CREDIT_CARD_APPROVAL_DATE
, CREDIT_CARD_CODE
, CREDIT_CARD_EXPIRATION_DATE
, CREDIT_CARD_HOLDER_NAME
, CREDIT_CARD_NUMBER --R12 CC Encryption Verify
, PAYMENT_LEVEL_CODE
, COMMITMENT_APPLIED_AMOUNT
, COMMITMENT_INTERFACED_AMOUNT
, HEADER_ID
, LINE_ID
, PAYMENT_AMOUNT
, PAYMENT_PERCENTAGE -- Added for bug 8478559
, PAYMENT_COLLECTION_EVENT
, PAYMENT_TRX_ID
, PAYMENT_TYPE_CODE
, PAYMENT_SET_ID
, PREPAID_AMOUNT
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, RECEIPT_METHOD_ID
, REQUEST_ID
, TANGIBLE_ID --R12 CC Encryption Verify
, LOCK_CONTROL
, PAYMENT_NUMBER
, DEFER_PAYMENT_PROCESSING_FLAG
, TRXN_EXTENSION_ID
FROM OE_PAYMENTS
WHERE ( PAYMENT_NUMBER = p_payment_number
AND HEADER_ID = p_header_id
AND LINE_ID IS NULL);
x_Header_Payment_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
x_Header_Payment_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
x_Header_Payment_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
x_Header_Payment_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
SELECT ATTRIBUTE1
, ATTRIBUTE2
, ATTRIBUTE3
, ATTRIBUTE4
, ATTRIBUTE5
, ATTRIBUTE6
, ATTRIBUTE7
, ATTRIBUTE8
, ATTRIBUTE9
, ATTRIBUTE10
, ATTRIBUTE11
, ATTRIBUTE12
, ATTRIBUTE13
, ATTRIBUTE14
, ATTRIBUTE15
, CONTEXT
, CREATED_BY
, CREATION_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATE_LOGIN
, CHECK_NUMBER --R12 CC Encryption Verify
, CREDIT_CARD_APPROVAL_CODE
, CREDIT_CARD_APPROVAL_DATE
, CREDIT_CARD_CODE
, CREDIT_CARD_EXPIRATION_DATE
, CREDIT_CARD_HOLDER_NAME
, CREDIT_CARD_NUMBER --R12 CC Encryption Verify
, PAYMENT_LEVEL_CODE
, COMMITMENT_APPLIED_AMOUNT
, COMMITMENT_INTERFACED_AMOUNT
, HEADER_ID
, LINE_ID
, PAYMENT_AMOUNT
, PAYMENT_PERCENTAGE -- Added for bug 8478559
, PAYMENT_COLLECTION_EVENT
, PAYMENT_TRX_ID
, PAYMENT_TYPE_CODE
, PAYMENT_SET_ID
, PREPAID_AMOUNT
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, RECEIPT_METHOD_ID
, REQUEST_ID
, TANGIBLE_ID --R12 CC Encryption Verify
, LOCK_CONTROL
, PAYMENT_NUMBER
, DEFER_PAYMENT_PROCESSING_FLAG
, TRXN_EXTENSION_ID
FROM OE_PAYMENTS
WHERE HEADER_ID = p_header_id
AND LINE_ID IS NULL
;
l_Header_Payment_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
l_Header_Payment_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
l_Header_Payment_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
l_Header_Payment_rec.program_update_date := l_implicit_rec.PROGRAM_UPDATE_DATE;
SELECT payment_number
INTO l_payment_number
FROM oe_payments
WHERE payment_number = l_payment_number
AND header_id = l_header_id
AND line_id is null
FOR UPDATE NOWAIT;
oe_debug_pub.add( 'SELECTED FOR UPDATE' , 1 ) ;
FND_MESSAGE.SET_NAME('ONT','OE_LOCK_ROW_DELETED');
SELECT payment_number
FROM oe_payments
WHERE header_id = p_header_id
AND line_id is NULL
FOR UPDATE NOWAIT;
SELECT payment_number
INTO l_payment_number
FROM OE_PAYMENTS
WHERE payment_number = p_payment_number
AND header_id = p_header_id
AND line_id is NULL
FOR UPDATE NOWAIT;
fnd_message.set_name('ONT','OE_LOCK_ROW_DELETED');
l_update_card_flag VARCHAR2(1) := 'N';
select ordered_date, transactional_curr_code, invoice_to_org_id
into l_ordered_date, l_transactional_curr_code, l_invoice_to_org_id
from oe_order_headers
where header_id=p_x_header_Payment_rec.header_id;
SELECT customer_id
INTO l_hdr_inv_to_cust_id
FROM oe_invoice_to_orgs_v
WHERE organization_id = l_invoice_to_org_id;
ELSIF p_x_header_payment_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add('trxn id'||p_x_header_payment_rec.trxn_Extension_id);
--credit card payment type, the operation would be update but the
--transaction extension id would be null.
ELSIF p_x_header_payment_rec.trxn_extension_id is null or
p_x_header_payment_rec.trxn_extension_id =FND_API.G_MISS_NUM THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'getting payment_trx_id as cc details changed');
oe_debug_pub.add('Operation is Update and trxn extension id is null');
l_update_card_flag := 'Y';
l_update_card_flag := 'N';
l_update_card_flag := 'Y';
p_update_card_flag => l_update_card_flag,
p_instrument_id => p_x_header_payment_rec.cc_instrument_id,
p_instrument_assignment_id => p_x_header_payment_rec.cc_instrument_assignment_id,
X_return_status => l_return_status,
X_msg_count => l_msg_count,
X_msg_data => l_msg_data);
--payment transaction id already exists, so need to update the IBY transaction
--extenstion table
BEGIN
-- validate cc security code
/*IF p_x_Header_Payment_rec.payment_type_code = 'CREDIT_CARD' THEN
IF OE_Payment_Trxn_Util.g_CC_Security_Code_Use IS NULL THEN
OE_Payment_Trxn_Util.g_CC_Security_Code_Use := OE_Payment_Trxn_Util.Get_CC_Security_Code_Use;
l_update_card_flag := 'Y';
l_update_card_flag := 'N';
l_update_card_flag := 'Y';
oe_debug_pub.add('b4 call to update pay...');
OE_PAYMENT_TRXN_UTIL.Update_Payment_TRXN
(P_header_id => p_x_header_Payment_rec.header_id,
p_line_id => p_x_header_payment_rec.line_id,
p_cust_id => l_hdr_inv_to_cust_id,
P_site_use_id => l_invoice_to_org_id,
P_payment_trx_id => p_x_header_payment_rec.payment_trx_id,
P_payment_type_code => p_x_header_payment_rec.payment_type_code,
p_payment_number => p_x_header_payment_rec.payment_number,
p_card_number => p_x_header_payment_rec.credit_card_number,
p_card_code => p_x_header_payment_rec.credit_card_code,
p_card_holder_name => p_x_header_payment_rec.credit_card_holder_name,
p_exp_date => p_x_header_payment_rec.credit_card_expiration_date,
p_receipt_method_id => p_x_header_payment_rec.receipt_method_id,
p_instrument_security_code => p_x_header_payment_rec.instrument_security_code,
--Bug 7460481
p_credit_card_approval_code => p_x_header_payment_rec.credit_card_approval_code,
p_credit_card_approval_date => p_x_header_payment_rec.credit_card_approval_date,
--Bug 7460481
p_trxn_extension_id => p_x_header_payment_rec.trxn_extension_id,
p_update_card_flag => l_update_card_flag,
p_instrument_id => p_x_header_payment_rec.cc_instrument_id,
p_instrument_assignment_id => p_x_header_payment_rec.cc_instrument_assignment_id,
X_return_status => l_return_status,
X_msg_count => l_msg_count,
X_msg_data => l_msg_data);
oe_debug_pub.add('trxn extension id after calling Update_Payment_TRXN:'||p_x_header_payment_rec.trxn_extension_id);
oe_debug_pub.add('Update_Payment_TRXN error....exc');
oe_debug_pub.add('After call to Update_Payment_TRXN'||l_return_status);
oe_debug_pub.add('After call to Update_Payment_TRXN --> Unexpected error');
oe_debug_pub.add('Need to call update_card API');
l_update_card_flag := 'Y';
oe_debug_pub.add('Need not call update_card API');
l_update_card_flag := 'N';
oe_debug_pub.add('Need to call update_card API 1');
l_update_card_flag := 'Y';
p_update_card_flag => l_update_card_flag,
p_instrument_id => p_x_header_payment_rec.cc_instrument_id,
p_instrument_assignment_id => p_x_header_payment_rec.cc_instrument_assignment_id,
X_return_status => l_return_status,
X_msg_count => l_msg_count,
X_msg_data => l_msg_data);
p_x_header_payment_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND p_old_header_Payment_rec.trxn_extension_id IS NOT NULL THEN
--For update operation, the trxn extension id of the original
--transaction needs to be deleted if it already exists as
--there is no trxn extension id for check payments.
IF l_debug_level > 0 THEN
oe_debug_pub.add(' Before calling delete payment trxn....');
oe_debug_pub.add(' Trxn extension to delete'||p_old_header_Payment_rec.trxn_extension_id);
OE_PAYMENT_TRXN_UTIL.Delete_Payment_Trxn
(p_header_id => p_x_header_Payment_rec.header_id,
p_line_id => p_x_header_Payment_rec.line_id,
p_payment_number => p_x_header_Payment_rec.payment_number,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_trxn_extension_id => p_old_header_Payment_rec.trxn_extension_id,
P_site_use_id => null
);
oe_debug_pub.add('Id that was deleted from iby tables...'||p_old_header_Payment_rec.trxn_extension_id);
oe_debug_pub.add('Return status after delete trxn in pre write process '|| l_return_status);
--be updated in oe_payments table during updation.
p_x_header_Payment_rec.trxn_extension_id := null;
Procedure Delete_Payment_at_header(p_header_id in number,
p_line_id in number := null,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2) is
p_count number := -1;
select count(payment_type_code) into p_count
from oe_payments
where header_id = p_header_id
and nvl(payment_collection_event, 'PREPAY') = 'INVOICE'
and line_id is null;
if p_count = 0 then --there was only one record and it was deleted
-- Set up the Header record
OE_Header_Util.Lock_Row
(p_header_id => p_header_id
,p_x_header_rec => l_old_header_rec
,x_return_status => l_return_status
);
update oe_order_headers_all
set payment_type_code = null,
payment_amount = null,
check_number = null,
credit_card_code = null,
credit_card_holder_name = null,
credit_card_number = null,
credit_card_expiration_date = null,
credit_card_approval_code = null
where header_id = p_header_id;
, 'Delete_Payment_at_Header'
);
END Delete_Payment_at_header;