[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
SELECT 1
INTO l_prepaid
FROM oe_payments
WHERE header_id = l_header_id
AND payment_type_code = 'CREDIT_CARD';
SELECT 1
INTO l_prepaid
FROM oe_payments
WHERE header_id = l_header_id
AND payment_type_code = 'CREDIT_CARD';
OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
SELECT 1
INTO p_result
FROM oe_payments
WHERE header_id = l_header_id
AND payment_set_id is not null
AND rownum=1;
SELECT max(payment_set_id)
, sum(nvl(prepaid_amount, 0))
INTO x_payment_set_id
,x_prepaid_amount
FROM oe_payments
WHERE header_id = p_header_id
AND payment_set_id IS NOT NULL;
SELECT payment_set_id
,prepaid_amount
INTO x_payment_set_id
,x_prepaid_amount
FROM oe_payments
WHERE header_id = p_header_id
AND payment_type_code = 'CREDIT_CARD';
SELECT * MOAC_SQL_CHANGE * oh.header_id
,oh.orig_sys_document_ref
,oh.source_document_id
,oh.order_source_id
,oh.change_sequence
,oh.source_document_type_id
FROM oe_order_headers oh
,oe_order_holds_all hd --moac
,oe_hold_sources_all hs --moac
,iby_trxn_extensions_v ite -- ccencryption
,oe_payments op
WHERE oh.header_id= hd.header_id
AND hd.hold_source_id = hs.hold_source_id
AND hs.hold_id in (l_hold1, l_hold2, l_hold3, l_hold4)
* (SELECT hold_id -- replace the sql with hardcoded hold_id once it is seeded: Also based on what holds should be processed
FROM oe_hold_definitions
WHERE type_code='EPAYMENT') *
AND hs.hold_entity_code = 'O'
AND hs.released_flag = 'N'
AND oh.order_type_id = NVL(p_order_type_id, oh.order_type_id)
AND oh.org_id = hs.org_id --moac
AND hs.org_id = hd.org_id --moac
AND oh.sold_to_org_id IN (SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number BETWEEN NVL(p_customer_number_from, account_number)
AND NVL(p_customer_number_to, account_number)
AND nvl(CUSTOMER_CLASS_CODE, 'XXX') = NVL(p_customer_class_code, nvl(CUSTOMER_CLASS_CODE, 'XXX')))
AND oh.order_number BETWEEN NVL(p_order_number_from, oh.order_number)
AND NVL(p_order_number_to, oh.order_number)
AND oh.payment_type_code = 'CREDIT_CARD'
-- AND oh.credit_card_number = NVL(p_credit_card_number, oh.credit_card_number)
-- AND nvl(oh.credit_card_code, 'XXX') = NVL(p_credit_card_type, nvl(oh.credit_card_code, 'XXX'))
AND oh.header_id = ite.order_id
AND ite.cc_number_hash1 = DECODE(p_credit_card_number, null, ite.cc_number_hash1, iby_fndcpt_setup_pub.get_hash(p_Credit_Card_Number, 'F'))
AND ite.cc_number_hash2 = DECODE(p_credit_card_number, null, ite.cc_number_hash2, iby_fndcpt_setup_pub.get_hash(p_credit_card_number, 'T'))
AND nvl(ite.card_issuer_code, 'XXX') = nvl(p_credit_card_type, nvl(ite.card_issuer_code, 'XXX'))
AND oh.invoice_to_org_id = NVL(p_bill_to_org_id, oh.invoice_to_org_id)
AND oh.booked_date >= NVL(l_booked_date_since, oh.booked_date)
AND oh.order_category_code <> 'RETURN'
AND oh.header_id = op.header_id
AND op.trxn_extension_id = ite.trxn_extension_id
-- orders on header level holds for multiple payments
UNION
***/
(SELECT distinct /* MOAC_SQL_CHANGE */ oh.header_id
,oh.orig_sys_document_ref
,oh.source_document_id
,oh.order_source_id
,oh.change_sequence
,oh.source_document_type_id
,oh.org_id --bug4689411
FROM oe_order_headers oh
,oe_order_holds_all hd --moac
,oe_hold_sources_all hs --moac
,oe_payments op
-- iby_trxn_extensions_v ite -- ccencryption
,IBY_FNDCPT_TX_EXTENSIONS x
,IBY_PMT_INSTR_USES_ALL u
,IBY_CREDITCARD c
WHERE oh.header_id= hd.header_id
AND hd.hold_source_id = hs.hold_source_id
AND hs.hold_id in (l_hold1, l_hold2, l_hold3, l_hold4)
/* (SELECT hold_id -- replace the sql with hardcoded hold_id once it is seeded: Also based on what holds should be processed
FROM oe_hold_definitions
WHERE type_code='EPAYMENT') */
AND hs.hold_entity_code = 'O'
AND hs.released_flag = 'N'
AND oh.order_type_id = NVL(p_order_type_id, oh.order_type_id)
AND oh.org_id = hs.org_id -- moac
AND hs.org_id = hd.org_id --moac
AND oh.sold_to_org_id IN (SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number BETWEEN NVL(p_customer_number_from, account_number)
AND NVL(p_customer_number_to, account_number)
AND nvl(CUSTOMER_CLASS_CODE, 'XXX') = NVL(p_customer_class_code, nvl(CUSTOMER_CLASS_CODE, 'XXX')))
AND oh.order_number BETWEEN NVL(p_order_number_from, oh.order_number)
AND NVL(p_order_number_to, oh.order_number)
AND oh.header_id = op.header_id
AND to_char(op.header_id) = x.order_id --Bug#9696998
AND c.cc_number_hash1 = DECODE(p_credit_card_number, null, c.cc_number_hash1, iby_fndcpt_setup_pub.get_hash(p_Credit_Card_Number, 'F'))
AND c.cc_number_hash2 = DECODE(p_credit_card_number, null, c.cc_number_hash2, iby_fndcpt_setup_pub.get_hash(p_credit_card_number, 'T'))
AND nvl(c.card_issuer_code, 'XXX') = nvl(p_credit_card_type, nvl(c.card_issuer_code, 'XXX'))
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
AND op.payment_type_code = 'CREDIT_CARD'
AND oh.invoice_to_org_id = NVL(p_bill_to_org_id, oh.invoice_to_org_id)
AND oh.booked_date >= NVL(l_booked_date_since, oh.booked_date)
AND oh.order_category_code <> 'RETURN'
-- for CASH, CHECK payment types, no need to join with IBY table.
UNION
SELECT distinct /* MOAC_SQL_CHANGE */ oh.header_id
,oh.orig_sys_document_ref
,oh.source_document_id
,oh.order_source_id
,oh.change_sequence
,oh.source_document_type_id
,oh.org_id --bug4689411
FROM oe_order_headers oh
,oe_order_holds_all hd --moac
,oe_hold_sources_all hs --moac
,oe_payments op
WHERE oh.header_id= hd.header_id
AND hd.hold_source_id = hs.hold_source_id
AND hs.hold_id in (l_hold1, l_hold2, l_hold3)
/* (SELECT hold_id -- replace the sql with hardcoded hold_id once it is seeded: Also based on what holds should be processed
FROM oe_hold_definitions
WHERE type_code='EPAYMENT') */
AND hs.hold_entity_code = 'O'
AND hs.released_flag = 'N'
AND oh.order_type_id = NVL(p_order_type_id, oh.order_type_id)
AND oh.org_id = hs.org_id --moac
AND hs.org_id = hd.org_id --moac
AND oh.sold_to_org_id IN (SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number BETWEEN NVL(p_customer_number_from, account_number)
AND NVL(p_customer_number_to, account_number)
AND nvl(CUSTOMER_CLASS_CODE, 'XXX') = NVL(p_customer_class_code, nvl(CUSTOMER_CLASS_CODE, 'XXX')))
AND oh.order_number BETWEEN NVL(p_order_number_from, oh.order_number)
AND NVL(p_order_number_to, oh.order_number)
AND op.payment_type_code IN('CASH', 'CHECK')
AND p_credit_card_number IS NULL
AND oh.invoice_to_org_id = NVL(p_bill_to_org_id, oh.invoice_to_org_id)
AND oh.booked_date >= NVL(l_booked_date_since, oh.booked_date)
AND oh.order_category_code <> 'RETURN'
AND oh.header_id = op.header_id
-- get all orders that have at least one line being on line level
-- authorization holds for multiple payments
UNION
SELECT distinct /* MOAC_SQL_CHANGE */ oh.header_id
,oh.orig_sys_document_ref
,oh.source_document_id
,oh.order_source_id
,oh.change_sequence
,oh.source_document_type_id
,oh.org_id --bug4689411
FROM oe_order_lines_all ol --moac
,oe_order_headers oh
,oe_order_holds_all hd --moac
,oe_hold_sources_all hs --moac
,oe_payments op
-- ,iby_trxn_extensions_v ite -- ccencryption
,IBY_FNDCPT_TX_EXTENSIONS x
,IBY_PMT_INSTR_USES_ALL u
,IBY_CREDITCARD c
WHERE oh.header_id = ol.header_id
AND ol.line_id= hd.line_id
AND hd.hold_source_id = hs.hold_source_id
AND hs.hold_id in (l_hold1, l_hold2, l_hold3, l_hold4)
/* (SELECT hold_id -- replace the sql with hardcoded hold_id once it is seeded: Also based on what holds should be processed
FROM oe_hold_definitions
WHERE type_code='EPAYMENT') */
AND hs.hold_entity_code = 'O'
AND hs.released_flag = 'N'
AND oh.order_type_id = NVL(p_order_type_id, oh.order_type_id)
AND oh.org_id = hs.org_id --moac
AND hs.org_id = hd.org_id --moac
AND oh.sold_to_org_id IN (SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number BETWEEN NVL(p_customer_number_from, account_number)
AND NVL(p_customer_number_to, account_number)
AND nvl(CUSTOMER_CLASS_CODE, 'XXX') = NVL(p_customer_class_code, nvl(CUSTOMER_CLASS_CODE, 'XXX')))
AND oh.order_number BETWEEN NVL(p_order_number_from, oh.order_number)
AND NVL(p_order_number_to, oh.order_number)
AND ol.line_id = op.line_id
AND ol.header_id = op.header_id
AND op.payment_type_code = 'CREDIT_CARD'
AND to_char(ol.header_id) = x.order_id --Bug#9696998
AND to_char(ol.line_id) = x.trxn_ref_number1 --Bug#9696998 --order line_id
AND c.cc_number_hash1 = DECODE(p_credit_card_number, null, c.cc_number_hash1, iby_fndcpt_setup_pub.get_hash(p_Credit_Card_Number, 'F'))
AND c.cc_number_hash2 = DECODE(p_credit_card_number, null, c.cc_number_hash2, iby_fndcpt_setup_pub.get_hash(p_credit_card_number, 'T'))
AND nvl(c.card_issuer_code, 'XXX') = nvl(p_credit_card_type,
nvl(c.card_issuer_code, 'XXX'))
AND oh.invoice_to_org_id = NVL(p_bill_to_org_id, oh.invoice_to_org_id)
AND oh.booked_date >= NVL(l_booked_date_since, oh.booked_date)
AND oh.order_category_code <> 'RETURN'
AND op.trxn_extension_id = x.trxn_extension_id)
ORDER BY 7; --bug4689411 Using the column number to order by org_id. Please make sure that org_id is the 7th column when any changes are made to the select clause.
SELECT /*+ INDEX (a,OE_PROCESSING_MSGS_N2)
USE_NL (a b) */
a.header_id
, a.order_source_id
, a.original_sys_document_ref
, a.source_document_id
, a.change_sequence
, a.source_document_type_id
, b.message_text
FROM oe_processing_msgs a, oe_processing_msgs_tl b
WHERE a.request_id = l_request_id
AND a.transaction_id = b.transaction_id
AND b.language = oe_globals.g_lang
ORDER BY a.order_source_id, a.original_sys_document_ref, a.header_id;
fnd_file.put_line(FND_FILE.OUTPUT, 'PPP Hold Selected: '|| p_ppp_hold);
fnd_file.put_line(FND_FILE.OUTPUT, 'Epayment Failure Hold Selected: '|| p_epay_failure_hold);
fnd_file.put_line(FND_FILE.OUTPUT, 'Epayment Server Failure Hold Selected: '|| p_epay_server_failure_hold);
fnd_file.put_line(FND_FILE.OUTPUT, 'Payment Authorizatin Hold Selected: '|| p_payment_authorization_hold);
OE_MSG_PUB.update_msg_context(
p_entity_code => 'HEADER'
,p_entity_id => l_header_id
,p_header_id => l_header_id
,p_line_id => null
,p_orig_sys_document_ref => l_orig_sys_document_ref
,p_orig_sys_document_line_ref => null
,p_change_sequence => l_change_sequence
,p_source_document_id => l_source_document_id
,p_source_document_line_id => null
,p_order_source_id => l_order_source_id
,p_source_document_type_id => l_source_document_type_id
);
SELECT count(*)
INTO l_count
FROM oe_payments
WHERE header_id = l_header_id
AND payment_type_code <> 'COMMITMENT'
AND nvl(payment_collection_event, 'PREPAY') = 'PREPAY';
SELECT count(*)
INTO l_count
FROM oe_payments
WHERE header_id = p_header_id
AND payment_type_code <> 'COMMITMENT'
AND nvl(payment_collection_event, 'PREPAY') = 'PREPAY';
SELECT payment_type_code,payment_term_id
INTO l_payment_type_code, l_payment_term_id
FROM oe_order_headers
WHERE header_id = p_header_id;
SELECT LOOKUP_CODE into l_lookup_code
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'AR_PREPAY_VERSION';
SELECT count(*)
INTO l_payment_count
FROM oe_payments
WHERE payment_collection_event IS NULL
AND header_id = p_header_id;
SELECT count(*)
INTO l_multipay_count
FROM oe_payments
WHERE header_id = p_header_id;
l_installment_tbl.delete;
select payment_term_id, transactional_curr_code
into l_term_id, l_curr_code
from oe_order_headers_all
where header_id = p_header_id;
SELECT header_id
INTO l_header_id
FROM oe_order_lines_all
WHERE line_id = p_line_id;