[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure Delete_Payment_at_line(p_header_id in number := null,
p_line_id in number,
p_payment_number in number := null,
p_payment_type_code in varchar2 := null,
p_del_commitment in number := 0,
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_line_Payment_rec.LAST_UPDATED_BY;
x_rowtype_rec.LAST_UPDATE_DATE := p_line_Payment_rec.LAST_UPDATE_DATE;
x_rowtype_rec.LAST_UPDATE_LOGIN := p_line_Payment_rec.LAST_UPDATE_LOGIN;
x_rowtype_rec.PROGRAM_UPDATE_DATE := p_line_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_Line_Payment_rec.last_updated_by,p_old_Line_Payment_rec.last_updated_by)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_LINE_PAYMENT_UTIL.G_LAST_UPDATED_BY;
IF NOT OE_GLOBALS.Equal(p_x_Line_Payment_rec.last_update_date,p_old_Line_Payment_rec.last_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_LINE_PAYMENT_UTIL.G_LAST_UPDATE_DATE;
IF NOT OE_GLOBALS.Equal(p_x_Line_Payment_rec.last_update_login,p_old_Line_Payment_rec.last_update_login)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_LINE_PAYMENT_UTIL.G_LAST_UPDATE_LOGIN;
IF NOT OE_GLOBALS.Equal(p_x_Line_Payment_rec.program_update_date,p_old_Line_Payment_rec.program_update_date)
THEN
l_index := l_index + 1;
l_src_attr_tbl(l_index) := OE_LINE_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_LINE_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_LINE_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_LINE_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_LINE_PAYMENT_UTIL.G_PROGRAM_UPDATE_DATE;
ELSIF l_dep_attr_tbl(I) = OE_LINE_PAYMENT_UTIL.G_LAST_UPDATED_BY THEN
p_x_Line_PAYMENT_rec.LAST_UPDATED_BY := FND_API.G_MISS_NUM;
ELSIF l_dep_attr_tbl(I) = OE_LINE_PAYMENT_UTIL.G_LAST_UPDATE_DATE THEN
p_x_Line_PAYMENT_rec.LAST_UPDATE_DATE := FND_API.G_MISS_DATE;
ELSIF l_dep_attr_tbl(I) = OE_LINE_PAYMENT_UTIL.G_LAST_UPDATE_LOGIN THEN
p_x_Line_PAYMENT_rec.LAST_UPDATE_LOGIN := FND_API.G_MISS_NUM;
ELSIF l_dep_attr_tbl(I) = OE_LINE_PAYMENT_UTIL.G_PROGRAM_UPDATE_DATE THEN
p_x_Line_PAYMENT_rec.PROGRAM_UPDATE_DATE := FND_API.G_MISS_DATE;
Procedure Delete_Line_PaymentType_Hold
(
p_header_id IN NUMBER
, p_line_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 line_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 OH.line_id=OP.line_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=p_line_id;
oe_debug_pub.add( 'Entering Delete_Line_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_Line_PaymentType_Hold' , 3 ) ;
END Delete_Line_PaymentType_Hold;
l_payments_update_flag VARCHAR2(1) := 'N';
l_delete_pmt_hold VARCHAR2(1) := 'N';
IF NOT OE_GLOBALS.Equal(p_x_Line_Payment_rec.last_updated_by,p_old_Line_Payment_rec.last_updated_by)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_x_Line_Payment_rec.last_update_date,p_old_Line_Payment_rec.last_update_date)
THEN
NULL;
IF NOT OE_GLOBALS.Equal(p_x_Line_Payment_rec.last_update_login,p_old_Line_Payment_rec.last_update_login)
THEN
NULL;
IF p_x_Line_Payment_rec.operation ='UPDATE' AND OE_GLOBALS.Equal(p_x_Line_Payment_rec.payment_type_code,p_old_Line_Payment_rec.payment_type_code) THEN
l_verify_payment_flag:='Y';
l_payments_update_flag := 'Y';
IF (p_x_Line_Payment_rec.operation ='UPDATE'AND p_x_Line_Payment_rec.payment_type_code<>'CREDIT_CARD')
THEN
l_verify_payment_flag:='Y';
SELECT 'Y' INTO l_header_inv_cc_payment
FROM oe_payments
WHERE header_id=p_x_Line_Payment_rec.header_id
AND PAYMENT_COLLECTION_EVENT ='INVOICE'
AND PAYMENT_TYPE_CODE='CREDIT_CARD'
AND rownum<2;
AND p_x_Line_Payment_rec.operation = 'UPDATE' THEN
l_delete_pmt_hold := 'Y';
/*SELECT booked_flag INTO l_booked_flag
FROM oe_order_headers_all
WHERE header_id=p_x_Line_Payment_rec.header_id; */
SELECT credit_check_flag INTO l_cc_flag_old
FROM oe_payment_types_vl
WHERE payment_type_code=p_old_Line_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_Line_Payment_rec.payment_type_code;
Delete_Payment_at_line(p_line_id => p_x_Line_Payment_rec.line_id,
p_payment_number => p_x_Line_Payment_rec.payment_number,
p_payment_type_code => 'COMMITMENT',
p_del_commitment => 1,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
IF NOT OE_GLOBALS.Equal(p_x_Line_Payment_rec.program_update_date,p_old_Line_Payment_rec.program_update_date)
THEN
NULL;
IF l_payments_update_flag = 'Y' and
OE_PrePayment_UTIL.IS_MULTIPLE_PAYMENTS_ENABLED = TRUE then
oe_debug_pub.add('logging synch from ULPMB.pls payment delayed request', 1);
p_request_type => OE_GLOBALS.G_UPDATE_HDR_PAYMENT,
p_param1 => 'UPDATE_LINE',
p_param2 => p_x_Line_Payment_rec.header_id,
x_return_status => l_return_status);
IF l_delete_pmt_hold = 'Y' THEN
IF l_debug_level > 0 then
oe_debug_pub.add('OEXULPMB: Logging delayed request for Delete_Payment_Hold.', 3);
p_request_type => OE_GLOBALS.G_DELETE_PAYMENT_HOLD,
p_param1 => 'VERIFY_PAYMENT',
p_param2 => p_x_Line_Payment_rec.header_id,
x_return_status => l_return_status);
IF p_x_Line_Payment_rec.last_updated_by = FND_API.G_MISS_NUM THEN
p_x_Line_Payment_rec.last_updated_by := p_old_Line_Payment_rec.last_updated_by;
IF p_x_Line_Payment_rec.last_update_date = FND_API.G_MISS_DATE THEN
p_x_Line_Payment_rec.last_update_date := p_old_Line_Payment_rec.last_update_date;
IF p_x_Line_Payment_rec.last_update_login = FND_API.G_MISS_NUM THEN
p_x_Line_Payment_rec.last_update_login := p_old_Line_Payment_rec.last_update_login;
IF p_x_Line_Payment_rec.program_update_date = FND_API.G_MISS_DATE THEN
p_x_Line_Payment_rec.program_update_date := p_old_Line_Payment_rec.program_update_date;
IF p_x_Line_Payment_rec.last_updated_by = FND_API.G_MISS_NUM THEN
p_x_Line_Payment_rec.last_updated_by := NULL;
IF p_x_Line_Payment_rec.last_update_date = FND_API.G_MISS_DATE THEN
p_x_Line_Payment_rec.last_update_date := NULL;
IF p_x_Line_Payment_rec.last_update_login = FND_API.G_MISS_NUM THEN
p_x_Line_Payment_rec.last_update_login := NULL;
IF p_x_Line_Payment_rec.program_update_date = FND_API.G_MISS_DATE THEN
p_x_Line_Payment_rec.program_update_date := NULL;
PROCEDURE Update_Row
( p_Line_Payment_rec IN OUT NOCOPY OE_Order_PUB.Line_Payment_Rec_Type
)
IS
l_lock_control NUMBER;
SELECT lock_control
INTO l_lock_control
FROM OE_PAYMENTS
WHERE payment_number = p_Line_Payment_rec.payment_number
AND header_id = p_Line_Payment_rec.header_id
AND line_id = p_Line_Payment_rec.line_id;
/* OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_Line_pmt_rec =>p_line_Payment_rec,
p_line_pmt_id => p_line_Payment_rec.payment_number,
x_index => l_index,
x_return_status => l_return_status); */
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_LINE_PAYMENT_UTIL.UPDATE_ROW IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_LINE_PAYMENT_UTIL.UPDATE_ROW' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_LINE_PAYMENT_UTIL.UPDATE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_LINE_PAYMENT_UTIL.UPDATE_ROW' , 1 ) ;
UPDATE OE_PAYMENTS
SET ATTRIBUTE1 = p_Line_Payment_rec.attribute1
, ATTRIBUTE2 = p_Line_Payment_rec.attribute2
, ATTRIBUTE3 = p_Line_Payment_rec.attribute3
, ATTRIBUTE4 = p_Line_Payment_rec.attribute4
, ATTRIBUTE5 = p_Line_Payment_rec.attribute5
, ATTRIBUTE6 = p_Line_Payment_rec.attribute6
, ATTRIBUTE7 = p_Line_Payment_rec.attribute7
, ATTRIBUTE8 = p_Line_Payment_rec.attribute8
, ATTRIBUTE9 = p_Line_Payment_rec.attribute9
, ATTRIBUTE10 = p_Line_Payment_rec.attribute10
, ATTRIBUTE11 = p_Line_Payment_rec.attribute11
, ATTRIBUTE12 = p_Line_Payment_rec.attribute12
, ATTRIBUTE13 = p_Line_Payment_rec.attribute13
, ATTRIBUTE14 = p_Line_Payment_rec.attribute14
, ATTRIBUTE15 = p_Line_Payment_rec.attribute15
, CONTEXT = p_Line_Payment_rec.context
, CREATED_BY = p_Line_Payment_rec.created_by
, CREATION_DATE = p_Line_Payment_rec.creation_date
, LAST_UPDATED_BY = p_Line_Payment_rec.last_updated_by
, LAST_UPDATE_DATE = p_Line_Payment_rec.last_update_date
, LAST_UPDATE_LOGIN = p_Line_Payment_rec.last_update_login
, CHECK_NUMBER = p_Line_Payment_rec.check_number
/*, CREDIT_CARD_APPROVAL_CODE = p_Line_Payment_rec.credit_card_approval_code --R12 CC Encryption
, CREDIT_CARD_APPROVAL_DATE = p_Line_Payment_rec.credit_card_approval_date
, CREDIT_CARD_CODE = p_Line_Payment_rec.credit_card_code
, CREDIT_CARD_EXPIRATION_DATE = p_Line_Payment_rec.credit_card_expiration_date
, CREDIT_CARD_HOLDER_NAME = p_Line_Payment_rec.credit_card_holder_name
, CREDIT_CARD_NUMBER = p_Line_Payment_rec.credit_card_number*/
, PAYMENT_LEVEL_CODE = p_Line_Payment_rec.payment_level_code
, COMMITMENT_APPLIED_AMOUNT = p_Line_Payment_rec.commitment_applied_amount
, COMMITMENT_INTERFACED_AMOUNT = p_Line_Payment_rec.commitment_interfaced_amount
, PAYMENT_NUMBER = p_Line_Payment_rec.payment_number
, HEADER_ID = p_Line_Payment_rec.header_id
, LINE_ID = p_Line_Payment_rec.line_id
, PAYMENT_AMOUNT = p_Line_Payment_rec.payment_amount
, PAYMENT_COLLECTION_EVENT = p_Line_Payment_rec.payment_collection_event
, PAYMENT_TRX_ID = p_Line_Payment_rec.payment_trx_id
, PAYMENT_TYPE_CODE = p_Line_Payment_rec.payment_type_code
, PAYMENT_SET_ID = p_Line_Payment_rec.payment_set_id
, PREPAID_AMOUNT = p_Line_Payment_rec.prepaid_amount
, PROGRAM_APPLICATION_ID = p_Line_Payment_rec.program_application_id
, PROGRAM_ID = p_Line_Payment_rec.program_id
, PROGRAM_UPDATE_DATE = p_Line_Payment_rec.program_update_date
, RECEIPT_METHOD_ID = p_Line_Payment_rec.receipt_method_id
, REQUEST_ID = p_Line_Payment_rec.request_id
--, TANGIBLE_ID = p_Line_Payment_rec.tangible_id --R12 CC Encryption
, DEFER_PAYMENT_PROCESSING_FLAG = p_Line_Payment_rec.defer_payment_processing_flag
, TRXN_EXTENSION_ID = p_Line_Payment_rec.trxn_extension_id
, LOCK_CONTROL = l_lock_control
WHERE PAYMENT_NUMBER = p_Line_Payment_rec.payment_number
AND HEADER_ID = p_Line_Payment_rec.header_id
AND LINE_ID = p_Line_Payment_rec.line_id
;
, 'Update_Row'
);
END Update_Row;
PROCEDURE Insert_Row
( p_Line_Payment_rec IN OUT NOCOPY OE_Order_PUB.Line_Payment_Rec_Type
)
IS
l_lock_control NUMBER:= 1;
oe_debug_pub.add( 'ENTERING OE_LINE_PAYMENT_UTIL.INSERT_ROW' , 1 ) ;
SELECT l.transaction_phase_code
INTO l_transaction_phase_code
FROM oe_order_lines_all l
WHERE l.line_id = p_line_Payment_rec.line_id;
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*/ --R12 CC Encryption
, PAYMENT_LEVEL_CODE
, COMMITMENT_APPLIED_AMOUNT
, COMMITMENT_INTERFACED_AMOUNT
, PAYMENT_NUMBER
, HEADER_ID
, LINE_ID
, PAYMENT_AMOUNT
, 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
, DEFER_PAYMENT_PROCESSING_FLAG
, TRXN_EXTENSION_ID
, LOCK_CONTROL
, ORIG_SYS_PAYMENT_REF --5892425
)
VALUES
( p_Line_Payment_rec.attribute1
, p_Line_Payment_rec.attribute2
, p_Line_Payment_rec.attribute3
, p_Line_Payment_rec.attribute4
, p_Line_Payment_rec.attribute5
, p_Line_Payment_rec.attribute6
, p_Line_Payment_rec.attribute7
, p_Line_Payment_rec.attribute8
, p_Line_Payment_rec.attribute9
, p_Line_Payment_rec.attribute10
, p_Line_Payment_rec.attribute11
, p_Line_Payment_rec.attribute12
, p_Line_Payment_rec.attribute13
, p_Line_Payment_rec.attribute14
, p_Line_Payment_rec.attribute15
, p_Line_Payment_rec.context
, p_Line_Payment_rec.created_by
, p_Line_Payment_rec.creation_date
, p_Line_Payment_rec.last_updated_by
, p_Line_Payment_rec.last_update_date
, p_Line_Payment_rec.last_update_login
, p_Line_Payment_rec.check_number
/*, p_Line_Payment_rec.credit_card_approval_code --R12 CC Encryption
, p_Line_Payment_rec.credit_card_approval_date
, p_Line_Payment_rec.credit_card_code
, p_Line_Payment_rec.credit_card_expiration_date
, p_Line_Payment_rec.credit_card_holder_name
, p_Line_Payment_rec.credit_card_number*/ --R12 CC Encryption
, p_Line_Payment_rec.payment_level_code
, p_Line_Payment_rec.commitment_applied_amount
, p_Line_Payment_rec.commitment_interfaced_amount
, p_Line_Payment_rec.payment_number
, p_Line_Payment_rec.header_id
, p_Line_Payment_rec.line_id
, p_Line_Payment_rec.payment_amount
, p_Line_Payment_rec.payment_collection_event
, p_Line_Payment_rec.payment_trx_id
, p_Line_Payment_rec.payment_type_code
, p_Line_Payment_rec.payment_set_id
, p_Line_Payment_rec.prepaid_amount
, p_Line_Payment_rec.program_application_id
, p_Line_Payment_rec.program_id
, p_Line_Payment_rec.program_update_date
, p_Line_Payment_rec.receipt_method_id
, p_Line_Payment_rec.request_id
--, p_Line_Payment_rec.tangible_id --R12 CC Encryption
, p_Line_Payment_rec.defer_payment_processing_flag
, p_Line_Payment_rec.trxn_extension_id
, l_lock_control
, p_Line_Payment_rec.ORIG_SYS_PAYMENT_REF --5892425
);
/* OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_old_lin_pmt_rec => NULL,
p_lin_pmt_rec =>p_line_Payment_rec,
p_lin_pmt_id => p_line_Payment_rec.payment_number,
x_index => l_index,
x_return_status => l_return_status); */
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_LINE_PAYMENT_UTIL.INSERT_ROW IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_LINE_PAYMENT_UTIL.INSERT_ROW' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_LINE_PAYMENT_UTIL.INSERT_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_LINE_PAYMENT_UTIL.INSERT_ROW' , 1 ) ;
oe_debug_pub.add( 'EXITING OE_LINE_PAYMENT_UTIL.INSERT_ROW' , 1 ) ;
, 'Insert_Row'
);
END Insert_Row;
PROCEDURE Delete_Row
( p_payment_number IN NUMBER :=
FND_API.G_MISS_NUM
, p_line_id 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_type_code,trxn_extension_id
FROM OE_PAYMENTS
WHERE LINE_ID = p_line_id
AND HEADER_ID = p_header_id;
oe_debug_pub.add( 'ENTERING OE_LINE_PAYMENT_UTIL.DELETE_ROW' , 1 ) ;
/* Set the operation on the record so that globals are updated as well */
l_new_line_Payment_rec.operation := OE_GLOBALS.G_OPR_DELETE;
--Calling delete API of Oracle Payments only if the payment
--type is credit card ach or direct debit.
IF l_payment_type_code in ('CREDIT_CARD','ACH','DIRECT_DEBIT') THEN
select invoice_to_org_id into l_invoice_to_org_id --Verify
from oe_order_lines_all where
header_id = p_header_id
and line_id = p_line_id;
OE_PAYMENT_TRXN_UTIL.Delete_Payment_Trxn
(p_header_id => p_header_id,
p_line_id => p_line_id,
p_payment_number=> l_pmt.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( 'Delete Payment at OE_LINE_Payment_UTIL.DELETE_ROW - UNEXPECTED ERROR' ) ;
oe_debug_pub.add( 'EXITING OE_LINE_Payment_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( 'Delete Payment at OE_LINE_Payment_UTIL.DELETE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_LINE_Payment_UTIL.DELETE_ROW' , 1 ) ;
/*OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_lin_pmt_rec =>l_new_line_Payment_rec,
p_lin_pmt_id => l_pmt.payment_number,
x_index => l_index,
x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FROM OE_LINE_PAYMENT_UTIL.DELETE_ROW IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_LINE_PAYMENT_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_LINE_PAYMENT_UTIL.DELETE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_LINE_PAYMENT_UTIL.DELETE_ROW' , 1 ) ;
OE_Delayed_Requests_Pvt.Delete_Reqs_for_deleted_entity(
p_entity_code => OE_GLOBALS.G_ENTITY_LINE_PAYMENT,
p_entity_id => p_line_id,
x_return_status => l_return_status
);
DELETE FROM OE_PAYMENTS
WHERE LINE_ID = p_line_id
AND HEADER_ID = p_header_id;
oe_debug_pub.add('1: after executing delete statement against oe_payments - before delete_payment_at_line');
Delete_Payment_at_line(p_line_id => p_line_id,
p_del_commitment => del_commitment,
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 Line - UNEXPECTED ERROR' ) ;
oe_debug_pub.add( 'EXITING OE_LINE_Payment_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( 'Delete Payment at Line - OE_LINE_Payment_UTIL.DELETE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_LINE_Payment_UTIL.DELETE_ROW' , 1 ) ;
/* Set the operation on the record so that globals are updated as well */
l_new_line_Payment_rec.operation := OE_GLOBALS.G_OPR_DELETE;
/* OE_ORDER_UTIL.Update_Global_Picture(p_Upd_New_Rec_If_Exists => True,
p_Lin_pmt_rec =>l_new_line_Payment_rec,
p_lin_pmt_id => p_payment_number,
x_index => l_index,
x_return_status => l_return_status); */
Delete_Line_PaymentType_Hold
(
p_header_id => p_header_id
, p_line_id => p_line_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_LINE_PAYMENT_UTIL.DELETE_ROW IS: ' || L_RETURN_STATUS ) ;
oe_debug_pub.add( 'EXITING OE_LINE_PAYMENT_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( 'UPDATE_GLOBAL_PICTURE ERROR IN OE_LINE_PAYMENT_UTIL.DELETE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_LINE_PAYMENT_UTIL.DELETE_ROW' , 1 ) ;
OE_Delayed_Requests_Pvt.Delete_Reqs_for_deleted_entity(
p_entity_code => OE_GLOBALS.G_ENTITY_LINE_Payment,
p_entity_id => p_line_id,
x_return_status => l_return_status
);
select payment_type_code,trxn_extension_id into p_payment_type_code,l_trxn_extension_id
from oe_payments
where payment_number = p_payment_number
and line_id = p_line_id
and header_id = p_header_id;
select payment_type_code,trxn_extension_id into p_payment_type_code,l_trxn_extension_id --R12 CC Encryption
from oe_payments
where line_id = p_line_id
and payment_number is null
and header_id = p_header_id;
oe_debug_pub.add('ULPMB.pls: delete_row - could not find the row');
oe_debug_pub.add('ULPMB.pls : delete row - too many rows - should not happen');
select invoice_to_org_id into l_invoice_to_org_id
from oe_order_lines_all where header_id = p_header_id
and line_id = p_line_id;
OE_PAYMENT_TRXN_UTIL.Delete_Payment_Trxn
(p_header_id => p_header_id,
p_line_id => p_line_id,
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 OE_LINE_Payment_UTIL.DELETE_ROW - UNEXPECTED ERROR' ) ;
oe_debug_pub.add( 'EXITING OE_LINE_Payment_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( '2:Delete Payment OE_LINE_Payment_UTIL.DELETE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_LINE_Payment_UTIL.DELETE_ROW' , 1 ) ;
DELETE FROM OE_PAYMENTS
WHERE PAYMENT_NUMBER = p_payment_number
AND LINE_ID = p_line_id
AND HEADER_ID = p_header_id
;
oe_debug_pub.add('2: after executing delete statement against oe_payments - before delete_payment_at_line');
Delete_Payment_at_line(p_line_id => p_line_id,
p_payment_number => p_payment_number,
p_payment_type_code => p_payment_type_code,
p_del_commitment => del_commitment,
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 Line - UNEXPECTED ERROR' ) ;
oe_debug_pub.add( 'EXITING OE_LINE_Payment_UTIL.DELETE_ROW' , 1 ) ;
oe_debug_pub.add( 'Delete Payment at Line - OE_LINE_Payment_UTIL.DELETE_ROW' ) ;
oe_debug_pub.add( 'EXITING OE_LINE_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_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 LINE_ID = p_line_id
AND HEADER_ID = p_header_id
);
x_Line_Payment_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
x_Line_Payment_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
x_Line_Payment_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
x_Line_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_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 LINE_ID = p_line_id
AND HEADER_ID = p_header_id
;
l_Line_Payment_rec.last_updated_by := l_implicit_rec.LAST_UPDATED_BY;
l_Line_Payment_rec.last_update_date := l_implicit_rec.LAST_UPDATE_DATE;
l_Line_Payment_rec.last_update_login := l_implicit_rec.LAST_UPDATE_LOGIN;
l_Line_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 line_id = l_line_id
AND header_id = l_header_id
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 line_id = p_line_id
AND header_id = p_header_id
FOR UPDATE NOWAIT;
SELECT payment_number
INTO l_payment_number
FROM OE_PAYMENTS
WHERE payment_number = p_payment_number
AND line_id = p_line_id
AND header_id = p_header_id
FOR UPDATE NOWAIT;
fnd_message.set_name('ONT','OE_LOCK_ROW_DELETED');
l_update_card_flag VARCHAR2(1) := 'N';
SELECT l.transaction_phase_code
INTO
l_transaction_phase_code
FROM oe_order_lines_all l
WHERE l.line_id = p_x_line_Payment_rec.line_id;
SELECT nvl(transactional_curr_code,'USD')
INTO l_currency_code from oe_order_headers
WHERE header_id=p_x_line_Payment_rec.header_id;
ELSIF p_x_line_Payment_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
l_new_commitment_bal := l_commitment_bal + p_old_line_Payment_rec.commitment_applied_amount - p_x_line_Payment_rec.commitment_applied_amount;
Select INVOICE_TO_ORG_ID into l_invoice_to_org_id
from oe_order_lines_all where header_id = p_x_line_Payment_rec.header_id
and line_id = p_x_line_payment_rec.line_id;
SELECT 'Y'
INTO l_is_split_payment
FROM oe_payments
WHERE header_id = p_x_line_Payment_rec.header_id
AND line_id = p_x_line_Payment_rec.line_id
AND payment_type_code IS NULL
AND credit_card_approval_code = 'CVV2_REQUIRED';
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_line_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;
Select SOURCE_DOCUMENT_TYPE_ID
Into l_order_source_doc_id
From oe_order_lines_all
Where line_id = p_x_line_payment_rec.line_id;
ELSIF p_x_line_payment_rec.operation = OE_GLOBALS.G_OPR_UPDATE THEN
IF p_x_line_payment_rec.trxn_extension_id IS NOT NULL
AND p_x_line_payment_rec.trxn_extension_id <> FND_API.G_MISS_NUM
AND ((p_x_line_Payment_rec.payment_type_code = 'CREDIT_CARD'
AND OE_GLOBALS.Is_Same_Credit_Card(p_old_line_Payment_rec.credit_card_number,
p_x_line_Payment_rec.credit_card_number,
p_old_line_Payment_rec.cc_instrument_id,
p_x_line_Payment_rec.cc_instrument_id)
AND p_x_line_Payment_rec.credit_card_holder_name = p_old_line_Payment_rec.credit_card_holder_name
AND p_x_line_Payment_rec.credit_card_expiration_date = p_old_line_Payment_rec.credit_card_expiration_date
AND p_x_line_Payment_rec.instrument_security_code = p_old_line_payment_rec.instrument_security_code)
OR (p_x_line_Payment_rec.payment_type_code IN ('ACH','DIRECT_DEBIT')
AND p_x_line_Payment_rec.payment_trx_id = p_old_line_Payment_rec.payment_trx_id)
OR (p_x_line_Payment_rec.payment_type_code IN ('CASH','WIRE_TRANSFER')))
THEN
--
IF l_debug_level > 0 THEN
oe_debug_pub.add('No change to payments attributes...');
--credit card payment type, the operation would be update but the
--transaction extension id would be null.
ELSIF p_x_line_payment_rec.trxn_extension_id IS NULL OR
p_x_line_payment_rec.trxn_extension_id = FND_API.G_MISS_NUM THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'Inside trxn extension id creation part....');
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';
oe_debug_pub.add('Update card flag..'||l_update_card_flag);
p_update_card_flag => l_update_card_flag,
p_instrument_id => p_x_line_payment_rec.cc_instrument_id,
p_instrument_assignment_id => p_x_line_payment_rec.cc_instrument_assignment_id,
p_x_trxn_extension_id => p_x_line_payment_rec.trxn_extension_id,
X_return_status => l_return_status,
X_msg_count => l_msg_count,
X_msg_data => l_msg_data);
--Trxn extension id exists and an update to some of the
--attributes of the payment transaction has occured. So
--need to call Update_Payment_Trxn API.
ELSE
BEGIN
-- validate cc security code
/*IF p_x_line_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;
oe_debug_pub.add('Verifying whether to call update card API...');
l_update_card_flag := 'Y';
l_update_card_flag := 'N';
l_update_card_flag := 'Y';
oe_debug_pub.add('Before calling update payment trxn...');
oe_debug_pub.add('Update card flag...'||l_update_card_flag);
--
OE_PAYMENT_TRXN_UTIL.Update_Payment_TRXN
(P_header_id => p_x_line_Payment_rec.header_id,
p_line_id => p_x_line_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_line_payment_rec.payment_trx_id,
p_payment_number => p_x_line_payment_rec.payment_number,
P_payment_type_code => p_x_line_payment_rec.payment_type_code,
p_card_number => p_x_line_payment_rec.credit_card_number,
p_card_code => p_x_line_payment_rec.credit_card_code,
p_card_holder_name => p_x_line_payment_rec.credit_card_holder_name,
p_exp_date => p_x_line_payment_rec.credit_card_expiration_date,
p_receipt_method_id => p_x_line_payment_rec.receipt_method_id,
p_instrument_security_code => p_x_line_payment_rec.instrument_security_code,
p_trxn_extension_id => p_x_line_payment_rec.trxn_extension_id,
p_update_card_flag => l_update_card_flag,
p_instrument_id => p_x_line_payment_rec.cc_instrument_id,
p_instrument_assignment_id => p_x_line_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_line_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');
l_update_card_flag := 'Y';
l_update_card_flag := 'N';
l_update_card_flag := 'Y';
oe_debug_pub.add('Update card flag..'||l_update_card_flag);
p_update_card_flag => l_update_card_flag,
p_instrument_id => p_x_line_payment_rec.cc_instrument_id,
p_instrument_assignment_id => p_x_line_payment_rec.cc_instrument_assignment_id,
p_x_trxn_extension_id => p_x_line_payment_rec.trxn_extension_id,
X_return_status => l_return_status,
X_msg_count => l_msg_count,
X_msg_data => l_msg_data);
p_x_line_payment_rec.operation = OE_GLOBALS.G_OPR_UPDATE
AND p_old_line_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_line_Payment_rec.trxn_extension_id);
OE_PAYMENT_TRXN_UTIL.Delete_Payment_Trxn
(p_header_id => p_x_line_payment_rec.header_id,
p_line_id => p_x_line_payment_rec.line_id,
p_payment_number => p_x_line_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_line_Payment_rec.trxn_extension_id,
P_site_use_id => l_invoice_to_org_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_line_payment_rec.trxn_extension_id := null;
Procedure Delete_Payment_at_line(p_header_id in number := null,
p_line_id in number,
p_payment_number in number := null,
p_payment_type_code in varchar2 := null,
p_del_commitment in number := 0,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2) is
p_count number := -1;
oe_debug_pub.add('update commitment and payment type ');
update oe_order_lines_all
set payment_type_code = null,
commitment_id = null
where line_id = p_line_id;
oe_debug_pub.add('update payment type ');
update oe_order_lines_all
set payment_type_code = null
where line_id = p_line_id;
oe_debug_pub.add('update commitment ');
update oe_order_lines_all
set commitment_id = null
where line_id = p_line_id;
, 'Delete_Payment_at_Line'
);
END Delete_Payment_at_line;