[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
SUM(ROUND(nvl(ool.tax_value,0), OE_ORDER_UTIL.G_Precision))
, SUM(ROUND(nvl(ool.Ordered_Quantity,0)
*(ool.unit_selling_price), OE_ORDER_UTIL.G_Precision))
INTO
l_tax_total
, l_order_total
FROM oe_order_lines_all ool
WHERE ool.header_id = p_header_id
AND ool.open_flag = 'N'
AND ool.cancelled_flag = 'N'
AND ool.line_category_code <> 'RETURN'
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM mtl_system_items mti
WHERE mti.inventory_item_id = ool.inventory_item_id
AND mti.organization_id = nvl(ool.ship_from_org_id,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID'))
AND (mti.invoiceable_item_flag = 'N'
OR mti.invoice_enabled_flag = 'N'));
SELECT SUM(ROUND(nvl(op.commitment_applied_amount,0), OE_ORDER_UTIL.G_Precision))
INTO l_commitment_total
FROM oe_payments op
WHERE op.header_id = p_header_id
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM mtl_system_items mti, oe_order_lines_all ool
WHERE ool.line_id = op.line_id
AND mti.inventory_item_id = ool.inventory_item_id
AND mti.organization_id = nvl(ool.ship_from_org_id,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID'))
AND (mti.invoiceable_item_flag = 'N'
OR mti.invoice_enabled_flag = 'N'));
SELECT SUM(ROUND(nvl(ool.Ordered_Quantity,0) *(ool.unit_selling_price), OE_ORDER_UTIL.G_Precision))
INTO l_commitment_total
FROM oe_order_lines_all ool
WHERE ool.header_id = p_header_id
AND ool.commitment_id is not null
AND ool.open_flag = 'N'
AND ool.cancelled_flag = 'N'
AND ool.line_category_code <> 'RETURN'
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM mtl_system_items mti
WHERE mti.inventory_item_id = ool.inventory_item_id
AND mti.organization_id = nvl(ool.ship_from_org_id,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID'))
AND (mti.invoiceable_item_flag = 'N'
OR mti.invoice_enabled_flag = 'N'));
SELECT SUM(
ROUND(
DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',-P.OPERAND,P.OPERAND), OE_ORDER_UTIL.G_Precision
)
)
INTO l_chgs_wo_line_id
FROM OE_PRICE_ADJUSTMENTS P
WHERE P.HEADER_ID = p_header_id
AND P.LINE_ID IS NULL
AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND P.APPLIED_FLAG = 'Y'
AND NVL(P.INVOICED_FLAG, 'N') = 'N';
SELECT SUM(
ROUND(
DECODE(P.CREDIT_OR_CHARGE_FLAG,'C',
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
-P.OPERAND,
(-L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT)),
DECODE(P.ARITHMETIC_OPERATOR, 'LUMPSUM',
P.OPERAND,
(L.ORDERED_QUANTITY*P.ADJUSTED_AMOUNT))
)
,OE_ORDER_UTIL.G_Precision
)
)
INTO l_chgs_w_line_id
FROM OE_PRICE_ADJUSTMENTS P,
OE_ORDER_LINES_ALL L
WHERE P.HEADER_ID = p_header_id
AND P.LINE_ID = L.LINE_ID
AND P.LIST_LINE_TYPE_CODE = 'FREIGHT_CHARGE'
AND P.APPLIED_FLAG = 'Y'
AND L.header_id = p_header_id
AND L.open_flag = 'N'
AND L.cancelled_flag = 'N'
AND L.line_category_code <> 'RETURN'
AND NOT EXISTS
(SELECT 'Non Invoiceable Item Line'
FROM MTL_SYSTEM_ITEMS MTI
WHERE MTI.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
AND MTI.ORGANIZATION_ID = NVL(L.SHIP_FROM_ORG_ID,
oe_sys_parameters.value('MASTER_ORGANIZATION_ID'))
AND (MTI.INVOICEABLE_ITEM_FLAG = 'N'
OR MTI.INVOICE_ENABLED_FLAG = 'N'));
SELECT nvl(prepaid_amount, 0)
INTO l_prepaid_amount
FROM oe_payments
WHERE header_id= p_header_rec.header_id
AND payment_type_code = 'CREDIT_CARD';
SELECT ba.bank_account_id
INTO l_remittance_bank_account_id
FROM ar_receipt_methods rm,
ap_bank_accounts ba,
ar_receipt_method_accounts rma ,
ar_receipt_classes rc
WHERE rm.receipt_method_id = p_receipt_method_id
and rm.receipt_method_id = rma.receipt_method_id
and rc.receipt_class_id = rm.receipt_class_id
and rc.creation_method_code = 'AUTOMATIC'
and rma.remit_bank_acct_use_id = ba.bank_account_id
and ba.account_type = 'INTERNAL'
and ba.currency_code = decode(ba.receipt_multi_currency_flag, 'Y'
,ba.currency_code
,p_header_rec.transactional_curr_code)
and rma.primary_flag = 'Y';
SELECT line_id
FROM oe_order_lines
WHERE NVL(INVOICE_INTERFACE_STATUS_CODE, 'N') <> 'PREPAID'
AND header_id = p_header_id;
if calling action is UPDATE (this is coming from delayed request),
or p_delayed_request is true, then
- if delta <> 0, apply PPP hold.(at this point order has been booked)
- else return.
if calling action is BOOKING, then
- if there is no ppp hold on the order(first time booking),
and profile option is set to be immediate at booking, then
process payment.
- else (there is ppp hold on the order, this comes from commit changes)
return.
else for other calling action, just return.
Error handler:
if AR API returns with error, apply credit card failure hold.
if AR API returns with success, release PPP hold and credit card failure hl.
*******************************************************************/
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'OEXVPPYB: CALLING ACTION IS: '||L_CALLING_ACTION , 3 ) ;
IF l_calling_action = 'UPDATE' THEN
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'IN OEXVPPYB.PLS: THIS IS COMING FROM DELAYED REQUEST.' , 1 ) ;
SELECT trxn_extension_id into l_trxn_extension_id
FROM OE_PAYMENTS where header_id = p_header_rec.header_id;
SELECT payment_set_id, check_number -- bug 4724845
INTO l_payment_set_id, l_receipt_number
FROM oe_payments
WHERE header_id = l_header_rec.header_id
AND payment_type_code = 'CREDIT_CARD';
SELECT 'Y'
INTO l_exists_prepay
FROM oe_payments
WHERE header_id = l_header_rec.header_id
AND payment_type_code = 'CREDIT_CARD';
UPDATE oe_payments
SET prepaid_amount = nvl(prepaid_amount,0) + l_pending_amount
WHERE header_id = l_header_rec.header_id
AND payment_type_code = 'CREDIT_CARD';
l_payment_types_rec.last_update_date := SYSDATE;
l_payment_types_rec.last_updated_by := FND_GLOBAL.USER_ID;
oe_payments_util.insert_row(p_payment_types_rec => l_payment_types_rec);
UPDATE oe_order_lines
SET INVOICE_INTERFACE_STATUS_CODE = 'PREPAID'
WHERE line_id = l_line_id;
SELECT payment_set_id
INTO l_payment_set_id
FROM oe_payments
WHERE header_id = l_header_rec.header_id
AND payment_type_code = 'CREDIT_CARD';
UPDATE oe_payments
SET prepaid_amount = nvl(prepaid_amount,0) + l_pending_amount
WHERE header_id = p_header_rec.header_id
AND payment_type_code = 'CREDIT_CARD';
UPDATE oe_order_lines
SET INVOICE_INTERFACE_STATUS_CODE = 'PREPAID'
WHERE line_id = l_line_id;
l_insert VARCHAR2(1) := 'N';
SELECT /*MOAC_SQL_CHANGE*/ Opt.credit_check_flag,
Op.receipt_method_id,
Op.payment_type_code,
op.defer_payment_processing_flag,
Op.payment_set_id,
Op.payment_trx_id,
Op.payment_collection_event,
Op.prepaid_amount,
Op.credit_card_code,
Op.credit_card_approval_code,
Op.check_number,
Op.payment_number,
Op.payment_amount,
Op.trxn_extension_id --R12 CC Encryption
FROM oe_payment_types_all opt,
Oe_payments op
WHERE opt.payment_type_code = op.payment_type_code
AND op.payment_collection_event = 'PREPAY'
AND op.payment_type_code <> 'COMMITMENT'
AND op.line_id is null
AND op.header_id =p_header_id
And opt.org_id=l_org_id; --moac
select count(payment_type_code)
from oe_payments
where header_id = p_header_id
and line_id is null;
SELECT 'Y'
INTO l_payment_exists
FROM oe_payments
WHERE header_id = p_header_id
AND rownum = 1;
update_hdr_payment(p_header_id => p_header_id
,p_action => 'ACTIONS_PAYMENTS'
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
);
update_payment_numbers(p_header_id => p_header_id
,p_line_id => p_line_id
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
);
update_payment_numbers(p_header_id => p_header_id
,p_line_id => p_line_id
,x_return_status => l_return_status
,x_msg_data => l_msg_data
,x_msg_count => l_msg_count
);
SELECT 'Y'
INTO l_exists_prepay
FROM oe_payments
WHERE payment_collection_event = 'PREPAY'
AND header_id = p_header_id
AND rownum=1;
SELECT payment_set_id
INTO l_payment_set_id
FROM oe_payments
WHERE header_id = p_header_id
AND payment_set_id IS NOT NULL
AND rownum = 1;
OR (l_calling_action='UPDATE' AND p_delayed_request=FND_API.G_TRUE))
AND l_pending_amount <> 0 THEN
-- if calling action is null, this is invoked from action, we
-- need to process payments regardless of the defer flag.
-- Apply PPP prepayment hold (hold id 13) on the order;
Begin -- receipt_method_id selection
select receipt_method_id into l_receipt_method_id
from oe_payment_types_vl
where payment_type_code = c_payment_rec.payment_type_code
and rownum = 1;
end; -- receipt_method_id selection
oe_debug_pub.add( 'OEXVPPYB: update oe_payments for payment_set_id: '||l_payment_set_id, 3) ;
UPDATE oe_payments
SET payment_set_id = l_payment_set_id,
prepaid_amount = l_pending_amount + l_prepaid_amount,
credit_card_approval_code = l_approval_code
WHERE header_id = p_header_id
and nvl(payment_number, -1) = nvl(c_payment_rec.payment_number, -1);
SELECT 'Y'
INTO l_exists_prepay_lines
FROM oe_payments
WHERE payment_collection_event = 'PREPAY'
AND payment_set_id is NULL
AND header_id = p_header_id
AND payment_type_code <> 'COMMITMENT'
AND line_id is null
AND rownum=1;
UPDATE oe_payments
SET prepaid_amount = nvl(prepaid_amount,0) + l_pending_amount
WHERE header_id = p_header_id
AND nvl(payment_number, -1) = nvl(c_payment_rec.payment_number, -1);
-- Update INVOICE_INTERFACE_STATUS_CODE of the line to 'PREPAID';
SELECT 'Y'
INTO l_do_cc_authorization
FROM oe_payments
WHERE payment_type_code = 'CREDIT_CARD'
AND payment_collection_event = 'INVOICE'
AND header_id = p_header_id
AND rownum = 1;
SELECT org_id, invoice_to_org_id
INTO l_org_id, l_site_use_id
FROM oe_order_lines_all
WHERE line_id = p_line_id;
UPDATE oe_payments
SET credit_card_approval_code = 'CVV2_REQUIRED'
WHERE line_id = l_line_payment_tbl(i).line_id
AND header_id = l_line_payment_tbl(i).header_id;
SELECT rc.creation_status creation_status,
nvl(rm.payment_type_code, 'CHECK') payment_type_code
FROM ar_cash_receipts cr,
ar_receipt_classes rc,
ar_receipt_methods rm,
ar_receivable_applications ar
WHERE rm.receipt_class_id = rc.receipt_class_id
AND cr.receipt_method_id = rm.receipt_method_id
AND cr.cash_receipt_id = ar.cash_receipt_id
AND ar.display = 'Y'
AND ar.applied_payment_schedule_id = -7
AND ar.payment_set_id = p_payment_set_id;
SELECT header_id
INTO l_header_id
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT sum(nvl(payment_amount, 0))
INTO l_prepaid_total
FROM oe_payments op
WHERE op.payment_collection_event = 'PREPAY'
AND op.header_id = l_header_id;
SELECT payment_type_code, trxn_extension_id
INTO l_payment_type_code, l_trxn_extension_id
FROM oe_payments
WHERE line_id = p_line_id
AND header_id = l_header_id --To avoid FTS on oe_payments table (SQL ID 14882779)
AND nvl(payment_type_code, 'COMMITMENT') <> 'COMMITMENT';
SELECT payment_type_code, trxn_extension_id
INTO l_payment_type_code, l_trxn_extension_id
FROM oe_payments
WHERE header_id = l_header_id
AND line_id IS NULL
AND nvl(payment_collection_event, 'PREPAY') = 'INVOICE'
AND nvl(payment_type_code, 'COMMITMENT') <> 'COMMITMENT';
SELECT 'Y'
INTO l_exists_auth
FROM IBY_TRXN_EXT_AUTHS_V
WHERE trxn_extension_id = l_trxn_extension_id
AND authorization_status=0;
SELECT 'Y'
INTO l_exists_auth
FROM IBY_FNDCPT_TX_OPERATIONS o,
IBY_TRXN_SUMMARIES_ALL a
WHERE o.trxn_extension_id = l_trxn_extension_id
AND o.transactionid = a.transactionid
AND a.status=0;
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => p_line_id
,p_flow_status_code => 'PAYMENT_ASSURANCE_COMPLETE'
,x_return_status => l_return_status);
oe_debug_pub.add('Return status 1 from update_flow_status_code for pay now instrument check: '||l_return_status , 3 ) ;
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => p_line_id
,p_flow_status_code => 'PAY_NOW_PAYMENT_NOT_ASSURED'
,x_return_status => l_return_status);
oe_debug_pub.add('Return status 2 from update_flow_status_code for pay now instrument check: '||l_return_status , 3 ) ;
SELECT payment_set_id
INTO l_payment_set_id
FROM oe_payments op
,oe_order_lines_all ool
WHERE op.header_id = ool.header_id
AND ool.line_id = p_line_id
AND rownum = 1;
SELECT ''Y''
FROM dual
WHERE
(
EXISTS
(
SELECT distinct ra.cash_receipt_id, crh.status
FROM AR_CASH_RECEIPT_HISTORY crh , ar_cash_receipts cr, ar_receivable_applications ra
WHERE crh.cash_receipt_id = ra.cash_receipt_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND ra.payment_set_id = :payment_set_id
AND ((cr.payment_trxn_extension_id IS NOT NULL AND
EXISTS (SELECT ''1''
FROM IBY_PMT_INSTR_USES_ALL ipiu,
IBY_FNDCPT_TX_EXTENSIONS ifte
WHERE ipiu.instrument_payment_use_id = ifte.instr_assignment_id
AND ifte.trxn_extension_id = cr.payment_trxn_extension_id AND
((ipiu.instrument_type = ''CREDITCARD'' AND
crh.status NOT IN (''REMITTED'', ''CLEARED'')) OR
(ipiu.instrument_type <> ''CREDITCARD'' AND
crh.status NOT IN (''CLEARED''))))) OR
(cr.payment_trxn_extension_id IS NULL AND
crh.status <> ''CLEARED''))
AND nvl(current_record_flag, ''N'') = ''Y''
)
)';
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => p_line_id,
p_flow_status_code => 'PAYMENT_ASSURANCE_COMPLETE',
x_return_status => l_return_status);
oe_debug_pub.add('Return status 1 from update_flow_status_code: '||l_return_status , 3 ) ;
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => p_line_id,
p_flow_status_code => 'PAYMENT_ASSURANCE_NOT_ASSURED',
x_return_status => l_return_status);
oe_debug_pub.add('Return status 2 from update_flow_status_code: '||l_return_status , 3 ) ;
select order_source_id, orig_sys_document_ref, change_sequence
,source_document_type_id, source_document_id
into l_order_source_id, l_orig_sys_document_ref, l_change_sequence,
l_source_document_type_id, l_source_document_id
from oe_order_headers where header_id = p_header_id;
select to_number(oe_sys_parameters.value ('SET_OF_BOOKS_ID')) into l_sob_id from dual;
SELECT org_id
INTO l_organization_id
FROM oe_order_headers_all
WHERE header_id = p_header_id;
Procedure Delete_Payments
( p_line_id IN NUMBER
, p_header_id IN NUMBER
, p_invoice_to_org_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
) IS
l_trxn_Extension_id NUMBER;
SELECT payment_number, payment_type_code,
trxn_extension_id --R12 CC Encryption
FROM OE_PAYMENTS
WHERE
(
HEADER_ID = p_header_id
AND line_id is NULL
AND payment_type_code IN ('ACH','DIRECT_DEBIT')
) OR
(
HEADER_ID = p_header_id AND line_id is null
and payment_type_code = 'CREDIT_CARD'
AND payment_collection_event = 'PREPAY'
);
SELECT payment_number,payment_type_code,
trxn_extension_id
FROM oe_payments
where header_id = p_header_id
and line_id = p_line_id
AND payment_type_code IN ('CREDIT_CARD','ACH','DIRECT_DEBIT');
oe_debug_pub.add('Entering OEXVPPYB Delete_Payments');
oe_debug_pub.add('Header_id in delete payments'||p_header_id);
OE_PAYMENT_TRXN_UTIL.Delete_Payment_Trxn
(p_header_id => p_header_id,
p_line_id => p_line_id,
p_payment_number => line_payments_rec.payment_number,
P_site_use_id => p_invoice_to_org_id,
p_trxn_extension_id => line_payments_rec.trxn_extension_id,
x_return_status =>l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
DELETE FROM OE_PAYMENTS
WHERE HEADER_ID = p_header_id
AND LINE_ID = p_line_id
AND payment_number = line_payments_rec.payment_number;
oe_debug_pub.add('Header_id in delete payments inside header payments cursor'||p_header_id);
OE_PAYMENT_TRXN_UTIL.Delete_Payment_Trxn
(p_header_id => p_header_id,
p_line_id => null,
p_payment_number => header_payments_rec.payment_number,
P_site_use_id => p_invoice_to_org_id,
p_trxn_extension_id => header_payments_rec.trxn_extension_id,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
DELETE FROM OE_PAYMENTS
WHERE HEADER_ID = p_header_id
AND payment_number = header_payments_rec.payment_number
AND line_id is null;
--Need to update oe_order headers table with
--null payment type code as it has been deleted from
--oe_payments table.
Update oe_order_headers_all set
payment_type_code = null where
header_id = p_header_id;
oe_debug_pub.add( 'Unexpected error in Delete_Payments: ' || SQLERRM , 3 ) ;
, 'Delete_Payments'
);
oe_debug_pub.add( 'Oracle error in others in delete_payments: '||SQLERRM , 3 ) ;
END Delete_Payments;
Procedure Update_Hdr_Payment(p_header_id in number,
p_action in varchar2,
p_line_id in number,
x_return_status out nocopy varchar2,
x_msg_count out nocopy number,
x_msg_data out nocopy varchar2) is
l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
l_del_payment number := 0; -- 0 means do not delete, 1 means delete.
select count(payment_type_code)
from oe_payments
where header_id = p_header_id
and line_id is null;
select payment_type_code,
payment_amount,
check_number
/*credit_card_code,
credit_card_holder_name,
credit_card_number,
credit_card_expiration_date,
credit_card_approval_code*/ --R12 CC Encryption
from oe_payments
where header_id = p_header_id
and payment_collection_event = 'INVOICE'
and line_id is null;
select payment_type_code,
payment_amount,
check_number
/*credit_card_code,
credit_card_holder_name,
credit_card_number,
credit_card_expiration_date,
credit_card_approval_code*/ --R12 CC Encryption
from oe_payments
where header_id = p_header_id
and ( payment_collection_event = 'PREPAY'
or prepaid_amount is not null )
and line_id is null;
select count(payment_type_code)
from oe_payments
where header_id = p_header_id
and (payment_collection_event = 'PREPAY'
or prepaid_amount is not null )
and line_id is null;
and exists ( select null
from ra_terms rat, oe_order_headers_all oeh
where oeh.header_id = p_header_id
and oeh.payment_term_id = rat.term_id
and rat.prepayment_flag = 'Y' );
select payment_type_code,
payment_amount,
check_number,
/*credit_card_code,
credit_card_holder_name,
credit_card_number,
credit_card_expiration_date,
credit_card_approval_code,
credit_card_approval_date, --bug3906851 */ --R12 CC Encryption
payment_term_id,
transactional_curr_code
from oe_order_headers_all
where header_id = p_header_id;
select payment_type_code, payment_trx_id
from oe_payments
where line_id = l_lock_line_id
and header_id = p_header_id;
select 'Y',payment_number --bug3781675
from oe_payments
where header_id = p_header_id
and line_id is null
and nvl(payment_collection_event,'PREPAY') = 'INVOICE';
oe_debug_pub.add('entering update_hdr_payment ');
if p_action = 'UPDATE_LINE' then
if p_line_id is null then
x_return_status := FND_API.G_RET_STS_ERROR;
Savepoint update_line;
select line_id, payment_type_code, commitment_id
into l_lock_line_id, line_payment_type, line_commitment_id
from oe_order_lines_all
where line_id = p_line_id
for update nowait;
update oe_order_lines_all
set payment_type_code = line_payment_type,
commitment_id = line_commitment_id
where line_id = l_lock_line_id;
oe_debug_pub.add('failed to update line payment type: ' || substr(sqlerrm,1,300));
rollback to savepoint update_line;
rollback to savepoint update_line;
end if; -- if p_action = 'UPDATE_LINE'
SELECT nvl(MAX(payment_number),0) INTO l_max_payment_number
FROM oe_payments
WHERE header_id = p_header_id
AND line_id IS NULL;
IF p_action = 'UPDATE_HEADER' THEN
--bug3733877 commenting the following condition and checking if invoice payment exists.
--if (l_prepay_count = 1 and p_count = 1) then
if nvl(l_invoice_payment_exists,'N') = 'N' then
l_payment_type_code := null;
l_del_payment := 1; --delete payment info at the header
update oe_order_headers_all
set payment_type_code = l_payment_type_code,
-- payment_amount = l_payment_amount, --bug 5185139
check_number = l_check_number, --R12 CC Encryption
/*credit_card_code = l_credit_card_code,
credit_card_holder_name = l_credit_card_holder_name,
credit_card_number = l_credit_card_number,
credit_card_expiration_date = l_credit_card_expiration_date,
credit_card_approval_code = l_credit_card_approval_code,*/ --R12 CC Encryption
lock_control = lock_control + 1 --bug3781675
where header_id = p_header_id;
OE_ORDER_UTIL.Update_Global_Picture
(p_Upd_New_Rec_If_Exists =>FALSE
, p_header_rec => l_header_rec
, p_old_header_rec => l_old_header_rec
, p_header_id => l_header_rec.header_id
, x_index => l_index
, x_return_status => l_return_status);
oe_debug_pub.add( 'UPDATE_GLOBAL RETURN STATUS FOR HDR IS: ' || L_RETURN_STATUS ) ;
elsif p_action in ('UPDATE_PAYMENT', 'ACTIONS_LINE_PAYMENTS', 'ACTIONS_PAYMENTS') then
if p_action = 'ACTIONS_LINE_PAYMENTS' then
if l_prepay_count > 0 then
oe_debug_pub.add('cannot do this as there exists prepayments ');
then -- insert payment record into oe_payments
if nvl(l_prepayment_flag, 'N') = 'Y' and
--bug3733877 adding the following condition so that prepayment record gets insterted only when there are no payment record in oe_payments.
p_count = 0 then
OE_OE_TOTALS_SUMMARY.Order_Totals
(
p_header_id=>p_header_id,
p_subtotal =>l_subtotal,
p_discount =>l_discount,
p_charges =>l_charges,
p_tax =>l_tax
);
l_x_Header_Payment_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
--bug3733877 Adding the following condition so that the prepayment record is inserted only when p_count = 0
p_count = 0 THEN
l_x_Header_Payment_tbl(2):=OE_ORDER_PUB.G_MISS_HEADER_PAYMENT_REC;
DELETE FROM oe_payments
WHERE header_id = p_header_id
AND line_id IS NULL
AND nvl(payment_collection_event,'PREPAY') = 'INVOICE';
END IF; -- if p_action in ('UPDATE_HEADER', 'DELETE_PAYMENT')
END Update_Hdr_Payment;
UPDATE oe_payments
SET payment_amount = 0, prepaid_amount = 0
WHERE payment_collection_event = 'PREPAY'
AND header_id = p_header_rec.header_id;
PROCEDURE Update_Payment_Numbers(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
CURSOR lock_lin_Payments(p_header_id in number,
p_line_id in NUMBER) IS
SELECT payment_type_code
FROM oe_payments
WHERE header_id = p_header_id
AND line_id = p_line_id
AND payment_number is null
FOR UPDATE NOWAIT;
SELECT payment_type_code
FROM oe_payments
WHERE header_id = p_header_id
AND payment_number is null
FOR UPDATE NOWAIT;
update oe_payments
set payment_number = 1
where header_id = p_header_id
and line_id = p_line_id
and payment_number is null
and payment_type_code = l_payment_type;
update oe_payments
set payment_number = 1
where header_id = p_header_id
and payment_number is null;
END Update_Payment_Numbers;
PROCEDURE Delete_Payment_Hold
(p_line_id IN NUMBER
,p_header_id IN NUMBER
,p_hold_type IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
l_prepay_exists VARCHAR2(1) := 'N';
oe_debug_pub.add( 'OEXVPPYB: Entering OE_PREPAYMENT_PVT.Delete_Payment_Hold.' , 3 ) ;
, 'Delete_Prepayment_Hold'
);
END Delete_Payment_Hold;
SELECT first_installment_code , relative_amount/base_amount
FROM ra_terms t,
ra_terms_lines tl
WHERE t.term_id = tl.term_id
AND t.term_id = p_term_id
AND sequence_num = 1;
SELECT line_id
,payment_term_id
FROM oe_order_lines_all
WHERE header_id = p_header_id;
SELECT line_id
,payment_term_id
FROM oe_order_lines_all
WHERE header_id = p_header_id
AND nvl(invoice_interface_status_code,'NO') <> 'YES';
SELECT line_id
,header_id
,payment_term_id
INTO l_line_tbl(1).line_id
,l_line_tbl(1).header_id
,l_line_tbl(1).payment_term_id
FROM oe_order_lines_all
WHERE line_id=p_line_id;