[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT lengthb(p_credit_card_num)
INTO len_credit_card_num
FROM dual;
SELECT substrb(p_credit_card_num,i,1)
INTO l_cc_num_char(i)
FROM dual;
SELECT /*+ LEADING (OOH) */ ooh.header_id,
null line_id, -- line_id
OE_Default_Header_Payment.get_payment_number(ooh.header_id),
ita.tangibleid,
uba.instr_assignment_id,
uba.ext_party_id,
iby_fndcpt_tx_extensions_s.nextval -- the new transaction extension ID
FROM oe_order_headers_all ooh,
hz_cust_site_uses_all su,
hz_cust_acct_sites_all cas,
ap_bank_accounts_all ba,
ap_bank_account_uses_all bau,
iby_trans_all_v ita,
iby_upg_instruments uba
WHERE ooh.open_flag = 'N'
AND ooh.payment_type_code = 'CREDIT_CARD'
AND ooh.ordered_date >= l_cutoff_date
AND NOT EXISTS ( SELECT 'Y'
FROM oe_payments op
WHERE op.header_id = ooh.header_id )
AND ita.authcode(+) = ooh.credit_card_approval_code
AND ita.updatedate(+) = ooh.credit_card_approval_date
AND ita.reqtype(+) = 'ORAPMTREQ'
AND ita.status(+) = 0
AND ita.refinfo(+) = to_char(ooh.header_id) --6713227
AND su.site_use_id = ooh.invoice_to_org_id
AND su.site_use_code = 'BILL_TO'
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND su.org_id = cas.org_id
AND uba.cust_account_id = cas.cust_account_id
AND uba.acct_site_use_id = su.site_use_id
AND uba.payment_function = 'CUSTOMER_PAYMENT'
AND uba.instrument_type = 'CREDITCARD'
AND ba.bank_account_id = uba.bank_account_id
AND ba.bank_account_num = OE_Payment_Data_Migration_Util.Strip_Non_Numeric_Char(ooh.credit_card_number) --6757060
AND ba.bank_branch_id = 1
AND ba.account_type = 'EXTERNAL'
AND ba.bank_account_id = bau.external_bank_account_id
AND bau.customer_site_use_id = ooh.invoice_to_org_id
AND ooh.rowid BETWEEN p_start_rowid AND p_end_rowid ;
SELECT /*+ LEADING (OOH) */ ooh.header_id,
null line_id, -- line_id
OE_Default_Header_Payment.get_payment_number(ooh.header_id),
ita.tangibleid,
uba.instr_assignment_id,
uba.ext_party_id,
iby_fndcpt_tx_extensions_s.nextval -- the new transaction extension ID
FROM oe_order_headers_all ooh,
hz_cust_site_uses_all su,
hz_cust_acct_sites_all cas,
iby_trans_all_v ita,
iby_upg_instruments uba,
iby_security_segments seg,
iby_creditcard cc
WHERE ooh.open_flag = 'N'
AND ooh.payment_type_code = 'CREDIT_CARD'
AND ooh.ordered_date >= l_cutoff_date
AND NOT EXISTS ( SELECT 'Y'
FROM oe_payments op
WHERE op.header_id = ooh.header_id )
AND ita.authcode(+) = ooh.credit_card_approval_code
AND ita.updatedate(+) = ooh.credit_card_approval_date
AND ita.reqtype(+) = 'ORAPMTREQ'
AND ita.status(+) = 0
AND ita.refinfo(+) = to_char(ooh.header_id) --6713227
AND su.site_use_id = ooh.invoice_to_org_id
AND su.site_use_code = 'BILL_TO'
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND su.org_id = cas.org_id
AND uba.cust_account_id = cas.cust_account_id
AND uba.acct_site_use_id = su.site_use_id
AND uba.payment_function = 'CUSTOMER_PAYMENT'
AND uba.instrument_type = 'CREDITCARD'
AND IBY_CC_SECURITY_PUB.get_segment_id(OE_Payment_Data_Migration_Util.Strip_Non_Numeric_Char(ooh.credit_card_number))
= seg.sec_segment_id --6757060
AND (seg.cc_number_hash1 = cc.cc_number_hash1
AND seg.cc_number_hash2 = cc.cc_number_hash2)
AND cc.instrid = uba.instrument_id
AND ooh.rowid BETWEEN p_start_rowid AND p_end_rowid ;
SELECT /*+ LEADING (OP) */ op.header_id,
op.line_id,
op.payment_number,
ita.tangibleid,
uba.instr_assignment_id,
uba.ext_party_id,
iby_fndcpt_tx_extensions_s.nextval -- the new transaction extension ID
FROM oe_order_headers_all ooh,
hz_cust_site_uses_all su,
hz_cust_acct_sites_all cas,
ap_bank_accounts_all ba,
ap_bank_account_uses_all bau,
iby_trans_all_v ita,
iby_upg_instruments uba,
oe_payments op
WHERE ooh.open_flag = 'N'
AND ooh.ordered_date >= l_cutoff_date
AND ooh.header_id = op.header_id
AND op.trxn_extension_id is null
AND ita.authcode(+) = op.credit_card_approval_code
AND ita.updatedate(+) = op.credit_card_approval_date
AND ita.reqtype(+) = 'ORAPMTREQ'
AND ita.status(+) = 0
AND ita.refinfo(+) = to_char(op.header_id) --6713227
AND su.site_use_id = ooh.invoice_to_org_id
AND su.site_use_code = 'BILL_TO'
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND su.org_id = cas.org_id
AND uba.cust_account_id = cas.cust_account_id
AND uba.acct_site_use_id = su.site_use_id
AND uba.payment_function = 'CUSTOMER_PAYMENT'
AND uba.instrument_type = 'CREDITCARD'
AND op.payment_type_code = 'CREDIT_CARD'
AND op.credit_card_number IS NOT NULL
AND ba.bank_account_id = uba.bank_account_id
AND ba.bank_account_num = OE_Payment_Data_Migration_Util.Strip_Non_Numeric_Char(op.credit_card_number) --6757060
AND ba.bank_branch_id = 1
AND ba.account_type = 'EXTERNAL'
AND ba.bank_account_id = bau.external_bank_account_id
AND bau.customer_site_use_id = ooh.invoice_to_org_id
AND op.rowid BETWEEN p_start_rowid AND p_end_rowid ;
SELECT /*+ LEADING (OP) */ op.header_id,
op.line_id,
op.payment_number,
ita.tangibleid,
uba.instr_assignment_id,
uba.ext_party_id,
iby_fndcpt_tx_extensions_s.nextval -- the new transaction extension ID
FROM oe_order_headers_all ooh,
hz_cust_site_uses_all su,
hz_cust_acct_sites_all cas,
iby_trans_all_v ita,
iby_upg_instruments uba,
oe_payments op,
iby_security_segments seg,
iby_creditcard cc
WHERE ooh.open_flag = 'N'
AND ooh.ordered_date >= l_cutoff_date
AND ooh.header_id = op.header_id
AND op.trxn_extension_id is null
AND ita.authcode(+) = op.credit_card_approval_code
AND ita.updatedate(+) = op.credit_card_approval_date
AND ita.reqtype(+) = 'ORAPMTREQ'
AND ita.status(+) = 0
AND ita.refinfo(+) = to_char(op.header_id) --6713227
AND su.site_use_id = ooh.invoice_to_org_id
AND su.site_use_code = 'BILL_TO'
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND su.org_id = cas.org_id
AND uba.cust_account_id = cas.cust_account_id
AND uba.acct_site_use_id = su.site_use_id
AND uba.payment_function = 'CUSTOMER_PAYMENT'
AND uba.instrument_type = 'CREDITCARD'
AND op.payment_type_code = 'CREDIT_CARD'
AND op.credit_card_number IS NOT NULL
AND IBY_CC_SECURITY_PUB.get_segment_id(OE_Payment_Data_Migration_Util.Strip_Non_Numeric_Char(op.credit_card_number))
= seg.sec_segment_id --6757060
AND (seg.cc_number_hash1 = cc.cc_number_hash1
AND seg.cc_number_hash2 = cc.cc_number_hash2)
AND cc.instrid = uba.instrument_id
AND op.rowid BETWEEN p_start_rowid AND p_end_rowid ;
SELECT /*+ LEADING (OP) */ op.header_id,
op.line_id,
op.payment_number,
uba.instr_assignment_id,
uba.ext_party_id,
iby_fndcpt_tx_extensions_s.nextval -- the new transaction extension ID
FROM oe_order_headers_all ooh,
hz_cust_site_uses_all su,
hz_cust_acct_sites_all cas,
iby_upg_instruments uba,
oe_payments op
WHERE ooh.open_flag = 'N'
AND ooh.ordered_date >= l_cutoff_date
AND ooh.header_id = op.header_id
AND op.trxn_extension_id is null
AND op.payment_type_code IN ('DIRECT_DEBIT', 'ACH')
AND su.site_use_id = ooh.invoice_to_org_id
AND su.site_use_code = 'BILL_TO'
AND su.cust_acct_site_id = cas.cust_acct_site_id
AND su.org_id = cas.org_id
AND uba.cust_account_id = cas.cust_account_id
AND uba.acct_site_use_id = su.site_use_id
AND uba.payment_function = 'CUSTOMER_PAYMENT'
AND op.payment_trx_id = uba.bank_account_id
AND uba.instrument_type = 'BANKACCOUNT'
AND op.rowid BETWEEN p_start_rowid AND p_end_rowid ;
ad_parallel_updates_pkg.delete_update_information(
0,
l_table_owner,
l_table_name,
l_script_name ) ;
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
X_worker_id,
X_num_workers,
X_batch_size, 0) ;
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
X_batch_size,
TRUE) ;
header_id_tab.delete ;
line_id_tab.delete ;
payment_number_tab.delete ;
tangibleid_tab.delete ;
instr_assignment_id_tab.delete ;
ext_party_id_tab.delete ;
trxn_entity_id_tab.delete ;
INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
(trxn_extension_id,
payment_channel_code,
instr_assignment_id,
ext_payer_id,
order_id,
po_number,
trxn_ref_number1,
trxn_ref_number2,
additional_info,
tangibleid,
origin_application_id,
encrypted,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(trxn_entity_id_tab(k),
'CREDIT_CARD',
instr_assignment_id_tab(k),
ext_party_id_tab(k),
header_id_tab(k),
NULL,
line_id_tab(k),
payment_number_tab(k),
NULL,
tangibleid_tab(k),
660,
'N',
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1) ;
INSERT INTO OE_PAYMENTS
(trxn_extension_id,
payment_level_code,
payment_number,
header_id,
line_id,
payment_type_code,
payment_collection_event, --6700026
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES
(trxn_entity_id_tab(i),
'ORDER',
payment_number_tab(i),
header_id_tab(i),
line_id_tab(i),
'CREDIT_CARD',
'INVOICE', --6700026
sysdate,
1,
sysdate,
1) ;
ad_parallel_updates_pkg.processed_rowid_range
(l_rows_processed,
l_end_rowid) ;
ad_parallel_updates_pkg.get_rowid_range
(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
X_batch_size,
FALSE) ;
ad_parallel_updates_pkg.delete_update_information(
0,
l_table_owner,
l_table_name,
l_script_name ) ;
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
X_worker_id,
X_num_workers,
X_batch_size, 0) ;
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
X_batch_size,
TRUE) ;
header_id_tab.delete ;
line_id_tab.delete ;
payment_number_tab.delete ;
tangibleid_tab.delete ;
instr_assignment_id_tab.delete ;
ext_party_id_tab.delete ;
trxn_entity_id_tab.delete ;
INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
(trxn_extension_id,
payment_channel_code,
instr_assignment_id,
ext_payer_id,
order_id,
po_number,
trxn_ref_number1,
trxn_ref_number2,
additional_info,
tangibleid,
origin_application_id,
encrypted,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(trxn_entity_id_tab(k),
'CREDIT_CARD',
instr_assignment_id_tab(k),
ext_party_id_tab(k),
header_id_tab(k),
NULL,
line_id_tab(k),
payment_number_tab(k),
NULL,
tangibleid_tab(k),
660,
'N',
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1) ;
UPDATE OE_PAYMENTS
SET trxn_extension_id = trxn_entity_id_tab(i),
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_user_id
WHERE header_id = header_id_tab(i)
AND NVL(line_id,-99) = NVL(line_id_tab(i),-99)
AND payment_number = payment_number_tab(i) ;
ad_parallel_updates_pkg.processed_rowid_range
(l_rows_processed,
l_end_rowid) ;
ad_parallel_updates_pkg.get_rowid_range
(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
X_batch_size,
FALSE) ;
ad_parallel_updates_pkg.delete_update_information(
0,
l_table_owner,
l_table_name,
l_script_name ) ;
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
X_worker_id,
X_num_workers,
X_batch_size, 0) ;
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
X_batch_size,
TRUE) ;
header_id_tab.delete ;
line_id_tab.delete ;
payment_number_tab.delete ;
instr_assignment_id_tab.delete ;
ext_party_id_tab.delete ;
trxn_entity_id_tab.delete ;
INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
(trxn_extension_id,
payment_channel_code,
instr_assignment_id,
ext_payer_id,
order_id,
po_number,
trxn_ref_number1,
trxn_ref_number2,
additional_info,
origin_application_id,
encrypted,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
object_version_number)
VALUES
(trxn_entity_id_tab(k),
'BANK_ACCT_XFER',
instr_assignment_id_tab(k),
ext_party_id_tab(k),
header_id_tab(k),
NULL,
line_id_tab(k),
payment_number_tab(k),
NULL,
660,
'N',
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1) ;
UPDATE OE_PAYMENTS
SET trxn_extension_id = trxn_entity_id_tab(i),
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_user_id
WHERE header_id = header_id_tab(i)
AND NVL(line_id,-99) = NVL(line_id_tab(i),-99)
AND payment_number = payment_number_tab(i) ;
ad_parallel_updates_pkg.processed_rowid_range
(l_rows_processed,
l_end_rowid) ;
ad_parallel_updates_pkg.get_rowid_range
(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
X_batch_size,
FALSE) ;
SELECT /*+ LEADING (OP) */ op.header_id,
op.rowid
FROM oe_payments op
WHERE ((op.payment_type_code = 'CREDIT_CARD' AND op.credit_card_number IS NOT NULL)
OR (op.payment_type_code IN ('ACH', 'DIRECT_DEBIT') AND op.payment_trx_id IS NOT NULL))
AND op.trxn_extension_id IS NOT NULL
AND op.rowid BETWEEN p_start_rowid AND p_end_rowid ;
SELECT /*+ LEADING (OOH) */ ooh.header_id,
ooh.rowid
FROM oe_order_headers_all ooh
WHERE (ooh.payment_type_code = 'CREDIT_CARD' AND ooh.credit_card_number IS NOT NULL)
AND ooh.rowid BETWEEN p_start_rowid AND p_end_rowid ;
SELECT oohh.header_id, oohh.rowid
FROM oe_order_header_history oohh
WHERE oohh.payment_type_code = 'CREDIT_CARD'
AND oohh.credit_card_number is not null
AND oohh.credit_card_number <> '****'
AND oohh.instrument_id is not null
AND oohh.rowid BETWEEN p_start_rowid AND p_end_rowid;
ad_parallel_updates_pkg.delete_update_information(
0,
l_table_owner,
l_table_name,
l_script_name ) ;
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
X_worker_id,
X_num_workers,
X_batch_size, 0) ;
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
X_batch_size,
TRUE) ;
SELECT 'Y'
INTO l_exists_payment
FROM oe_payments
WHERE ((payment_type_code = 'CREDIT_CARD' AND credit_card_number IS NOT NULL)
OR (payment_type_code IN ('ACH', 'DIRECT_DEBIT') AND payment_trx_id IS NOT NULL))
AND trxn_extension_id IS NULL
AND ROWNUM = 1 ;
SELECT 'Y'
INTO l_exists_header
FROM oe_order_headers_all ooh,
oe_payments op
WHERE op.payment_type_code = 'CREDIT_CARD'
AND op.credit_card_number IS NOT NULL
AND ooh.header_id = op.header_id
AND op.trxn_extension_id IS NULL
AND ROWNUM = 1 ;
SELECT 'Y'
INTO l_exists_history
FROM oe_order_header_history
WHERE payment_type_code = 'CREDIT_CARD'
AND instrument_id IS NULL
AND ROWNUM = 1 ;
header_id_tab.delete ;
row_id_tab.delete ;
UPDATE oe_payments
SET credit_card_number = null,
credit_card_holder_name = null,
credit_card_expiration_date = null,
credit_card_code = null,
credit_card_approval_code = null,
credit_card_approval_date = null,
tangible_id = null,
payment_trx_id = null,
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_user_id
WHERE rowid = row_id_tab(i) ;
'Update failing at OE_PAYMENTS from OE_PAYMENTS_CUR for Header ID ' ||
header_id_tab(j));
ad_parallel_updates_pkg.processed_rowid_range
(l_rows_processed,
l_end_rowid) ;
ad_parallel_updates_pkg.get_rowid_range
(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
X_batch_size,
FALSE) ;
ad_parallel_updates_pkg.delete_update_information(
0,
l_table_owner,
l_table_name,
l_script_name ) ;
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
X_worker_id,
X_num_workers,
X_batch_size, 0) ;
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
X_batch_size,
TRUE) ;
header_id_tab.delete ;
row_id_tab.delete ;
UPDATE oe_order_headers_all
SET credit_card_number = null,
credit_card_holder_name = null,
credit_card_expiration_date = null,
credit_card_code = null,
credit_card_approval_code = null,
credit_card_approval_date = null,
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_user_id
WHERE rowid = row_id_tab(i) ;
'Update failing at OE_ORDER_HEADERS_ALL from HEADER_PAYMENTS_CUR ' ||
header_id_tab(j));
ad_parallel_updates_pkg.processed_rowid_range
(l_rows_processed,
l_end_rowid) ;
ad_parallel_updates_pkg.get_rowid_range
(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
X_batch_size,
FALSE) ;
ad_parallel_updates_pkg.delete_update_information(
0,
l_table_owner,
l_table_name,
l_script_name ) ;
ad_parallel_updates_pkg.initialize_rowid_range(
ad_parallel_updates_pkg.ROWID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
X_worker_id,
X_num_workers,
X_batch_size, 0) ;
ad_parallel_updates_pkg.get_rowid_range(
l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
X_batch_size,
TRUE) ;
header_id_tab.delete ;
row_id_tab.delete ;
UPDATE oe_order_header_history
SET credit_card_number = '****',
credit_card_holder_name = '****',
credit_card_expiration_date = sysdate,
credit_card_code = '****',
last_update_date = sysdate,
last_updated_by = l_user_id,
last_update_login = l_user_id
WHERE rowid = row_id_tab(i) ;
'Update failing at OE_ORDER_HEADER_HISTORY from HIST_PAYMENTS_CUR ' ||
header_id_tab(j));
ad_parallel_updates_pkg.processed_rowid_range
(l_rows_processed,
l_end_rowid) ;
ad_parallel_updates_pkg.get_rowid_range
(l_start_rowid,
l_end_rowid,
l_any_rows_to_process,
X_batch_size,
FALSE) ;