[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT pmt.payment_id,
translate(pmt.payment_ref_number,'0: -_', '0') payment_ref_number,
pmt.credit_card_code,
pmt.credit_card_holder_name,
pmt.credit_card_expiration_date,
to_char(pmt.payment_id) ||'-'|| hdr.quote_number, --order_id
to_char(hdr.quote_header_id) trxn_ref_number1,
nvl(hdr.invoice_to_cust_party_id, hdr.cust_party_id), --party_id
IBY_FNDCPT_TX_EXTENSIONS_S.nextval, --trxn_extension_id
IBY_INSTR_S.nextval,
DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval, PAYER.EXT_PAYER_ID),
DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we should create new external payer
IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
sec.cc_number_hash1,
sec.cc_number_hash2,
sec.cc_issuer_range_id,
sec.sec_segment_id,
sec.cc_number_length,
sec.cc_unmask_digits,
lpad(sec.cc_unmask_digits, nvl(range.card_number_length, length(pmt.payment_ref_number)), 'X') masked_cc_number
FROM aso_payments pmt,
iby_external_payers_all payer,
aso_quote_headers_all hdr,
iby_security_segments sec,
iby_cc_issuer_ranges range
WHERE pmt.quote_header_id = hdr.quote_header_id
and pmt.payment_type_code = 'CREDIT_CARD'
and pmt.payment_ref_number is not null
and nvl(hdr.invoice_to_cust_party_id, hdr.cust_party_id) = payer.party_id (+)
and payer.cust_account_id is null
and payer.acct_site_use_id is null
and payer.org_id is null
--and 'OPERATING_UNIT' = payer.org_type(+)
and 'CUSTOMER_PAYMENT' = payer.payment_function(+)
and pmt.rowid between p_start_rowid and p_end_rowid
and pmt.trxn_extension_id is null
and sec.sec_segment_id = IBY_CC_SECURITY_PUB.get_segment_id(pmt.payment_ref_number)
and sec.cc_issuer_range_id = range.cc_issuer_range_id (+);
SELECT pmt.payment_id,
pmt.payment_ref_number,
pmt.credit_card_code,
pmt.credit_card_holder_name,
pmt.credit_card_expiration_date,
to_char(pmt.payment_id) ||'-'|| hdr.quote_number, --order_id
to_char(hdr.quote_header_id) trxn_ref_number1,
nvl(hdr.invoice_to_cust_party_id, hdr.cust_party_id), --party_id
iby_fndcpt_tx_extensions_s.nextval, --trxn_extension_id
iby_instr_s.nextval,
decode(payer.ext_payer_id, null, iby_external_payers_all_s.nextval, payer.ext_payer_id),
decode(payer.ext_payer_id, null,'Y', 'N'), -- this flag determines whether we should create new external payer
iby_pmt_instr_uses_all_s.nextval, -- the new instrument use id
iby_fndcpt_setup_pub.get_hash(pmt.payment_ref_number, fnd_api.g_false) cc_number_hash1,
iby_fndcpt_setup_pub.get_hash(pmt.payment_ref_number, fnd_api.g_true) cc_number_hash2,
iby_cc_validate.get_cc_issuer_range(pmt.payment_ref_number) cc_issuer_range_id,
--null sec_segment_id,
decode(iby_cc_validate.get_cc_issuer_range(pmt.payment_ref_number), null,length(pmt.payment_ref_number), null) cc_number_length,
substr(pmt.payment_ref_number,greatest(-4,-length(pmt.payment_ref_number))) cc_unmask_digits,
lpad(substr(pmt.payment_ref_number, greatest(-4,-length(pmt.payment_ref_number))), length(pmt.payment_ref_number), 'X' ) masked_cc_number
FROM aso_payments pmt,
iby_external_payers_all payer,
aso_quote_headers_all hdr
WHERE pmt.quote_header_id = hdr.quote_header_id
and pmt.payment_type_code = 'CREDIT_CARD'
and pmt.payment_ref_number is not null
and nvl(hdr.invoice_to_cust_party_id, hdr.cust_party_id) = payer.party_id (+)
and payer.cust_account_id is null
and payer.acct_site_use_id is null
and payer.org_id is null
--and 'OPERATING_UNIT' = payer.org_type(+)
and 'CUSTOMER_PAYMENT' = payer.payment_function(+)
and pmt.rowid between p_start_rowid and p_end_rowid
and pmt.trxn_extension_id is null;
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
);
INSERT INTO IBY_CREDITCARD( CARD_OWNER_ID,
INSTRUMENT_TYPE,
PURCHASECARD_FLAG,
CARD_ISSUER_CODE,
ACTIVE_FLAG,
SINGLE_USE_FLAG,
EXPIRYDATE,
CHNAME,
CCNUMBER,
INSTRID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ENCRYPTED,
CC_NUMBER_HASH1,
CC_NUMBER_HASH2,
CC_ISSUER_RANGE_ID,
--SEC_SEGMENT_ID_TAB, --getting expression is of wrong type error, so commenting out, but needs to verify it latter on
CARD_MASK_SETTING,
CARD_UNMASK_LENGTH,
CC_NUMBER_LENGTH,
MASKED_CC_NUMBER,
--SEC_SUBKEY_ID,
OBJECT_VERSION_NUMBER
)
VALUES ( party_id_tab(i),
'CREDITCARD',
'N',
credit_card_code_tab(i),
'Y',
'Y',
cc_expiration_date_tab(i),
cc_holder_name_tab(i),
decode(sec_segment_id_tab(i), null,payment_ref_number_tab(i), cc_unmask_digits_tab(i)),
instrument_id_tab(i),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
decode(SEC_SEGMENT_ID_TAB(i),null,'N','Y'),
cc_number_hash1_tab(i),
cc_number_hash2_tab(i),
cc_issuer_range_id_tab(i),
--sec_segment_id_tab(i),--getting expression is of wrong type error, so commenting out, but needs to verify it latter on
'DISPLAY_LAST',
4,
cc_number_length_tab(i),
masked_cc_number_tab(i),
1
);
fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_CREDITCARD for Payment ID ' || payment_id_tab(j));
INSERT INTO IBY_PMT_INSTR_USES_ALL( INSTRUMENT_PAYMENT_USE_ID,
EXT_PMT_PARTY_ID,
INSTRUMENT_TYPE,
INSTRUMENT_ID,
PAYMENT_FUNCTION,
ORDER_OF_PREFERENCE,
START_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
payment_flow
)
SELECT instr_assignment_id_tab(i),
ext_payer_id_tab(i),
'CREDITCARD',
instrument_id_tab(i),
'CUSTOMER_PAYMENT',
1,
sysdate,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1,
'FUNDS_CAPTURE'
FROM IBY_EXTERNAL_PAYERS_ALL
-- Note: For products that do not use all the party context columns, it is mandatory to
-- add NOT NULL clause for those columns.
WHERE payment_function = 'CUSTOMER_PAYMENT'
and party_id = party_id_tab(i)
and org_type is null
and org_id is null
and cust_account_id is null
and acct_site_use_id is null
and rownum = 1;
fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_PMT_INSTR_USES_ALL for Payment Id: ' || payment_id_tab(j));
fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_PMT_INSTR_USES_ALL for Instrument Assignment Id: ' || instr_assignment_id_tab(j));
INSERT INTO IBY_FNDCPT_TX_EXTENSIONS( TRXN_EXTENSION_ID,
PAYMENT_CHANNEL_CODE,
INSTR_ASSIGNMENT_ID,
ORDER_ID,
PO_NUMBER,
TRXN_REF_NUMBER1,
TRXN_REF_NUMBER2,
ADDITIONAL_INFO,
TANGIBLEID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
encrypted,
origin_application_id
)
VALUES( trxn_extension_id_tab(i),
'CREDIT_CARD',
instr_assignment_id_tab(i),
order_id_tab(i),
null,
trxn_ref_number1_tab(i),
null,
null,
null,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1,
'Y',
679
);
fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_PMT_INSTR_USES_ALL for trxn_ref_number1_tab(j): ' || trxn_ref_number1_tab(j));
INSERT INTO IBY_CREDITCARD( CARD_OWNER_ID,
INSTRUMENT_TYPE,
PURCHASECARD_FLAG,
CARD_ISSUER_CODE,
ACTIVE_FLAG,
SINGLE_USE_FLAG,
EXPIRYDATE,
CHNAME,
CCNUMBER,
INSTRID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ENCRYPTED,
CC_NUMBER_HASH1,
CC_NUMBER_HASH2,
CC_ISSUER_RANGE_ID,
--SEC_SEGMENT_ID_TAB,
CARD_MASK_SETTING,
CARD_UNMASK_LENGTH,
CC_NUMBER_LENGTH,
MASKED_CC_NUMBER,
--SEC_SUBKEY_ID,
OBJECT_VERSION_NUMBER
)
VALUES ( party_id_tab(i),
'CREDITCARD',
'N',
credit_card_code_tab(i),
'Y',
'Y',
cc_expiration_date_tab(i),
cc_holder_name_tab(i),
--decode(sec_segment_id_tab(i), null,payment_ref_number_tab(i), cc_unmask_digits_tab(i)),
payment_ref_number_tab(i),
instrument_id_tab(i),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
--decode(SEC_SEGMENT_ID_TAB(i), NULL,'N','Y'),
'N',
cc_number_hash1_tab(i),
cc_number_hash2_tab(i),
cc_issuer_range_id_tab(i),
--sec_segment_id_tab(i),
'DISPLAY_LAST',
4,
cc_number_length_tab(i),
masked_cc_number_tab(i),
1
);
fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_CREDITCARD for Payment ID ' || payment_id_tab(j));
INSERT INTO IBY_PMT_INSTR_USES_ALL( INSTRUMENT_PAYMENT_USE_ID,
EXT_PMT_PARTY_ID,
INSTRUMENT_TYPE,
INSTRUMENT_ID,
PAYMENT_FUNCTION,
ORDER_OF_PREFERENCE,
START_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
payment_flow
)
SELECT instr_assignment_id_tab(i),
ext_payer_id_tab(i),
'CREDITCARD',
instrument_id_tab(i),
'CUSTOMER_PAYMENT',
1,
sysdate,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1,
'FUNDS_CAPTURE'
FROM IBY_EXTERNAL_PAYERS_ALL
-- Note: For products that do not use all the party context columns, it is mandatory to
-- add NOT NULL clause for those columns.
WHERE payment_function = 'CUSTOMER_PAYMENT'
and party_id = party_id_tab(i)
and org_type is null
and org_id is null
and cust_account_id is null
and acct_site_use_id is null
and rownum = 1;
fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_PMT_INSTR_USES_ALL for Payment Id: ' || payment_id_tab(j));
fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_PMT_INSTR_USES_ALL for Instrument Assignment Id: ' || instr_assignment_id_tab(j));
INSERT INTO IBY_FNDCPT_TX_EXTENSIONS( TRXN_EXTENSION_ID,
PAYMENT_CHANNEL_CODE,
INSTR_ASSIGNMENT_ID,
ORDER_ID,
PO_NUMBER,
TRXN_REF_NUMBER1,
TRXN_REF_NUMBER2,
ADDITIONAL_INFO,
TANGIBLEID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER,
encrypted,
origin_application_id
)
VALUES( trxn_extension_id_tab(i),
'CREDIT_CARD',
instr_assignment_id_tab(i),
order_id_tab(i),
null,
trxn_ref_number1_tab(i),
null,
null,
null,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1,
'Y',
679
);
fnd_file.put_line(FND_FILE.OUTPUT, 'Insert failing at IBY_PMT_INSTR_USES_ALL for trxn_ref_number1_tab(j): ' || trxn_ref_number1_tab(j));
UPDATE aso_payments a
SET a.TRXN_EXTENSION_ID = TRXN_EXTENSION_ID_TAB(i),
a.CREDIT_CARD_APPROVAL_CODE = NULL,
a.CREDIT_CARD_APPROVAL_DATE = NULL,
a.CREDIT_CARD_CODE = NULL,
a.CREDIT_CARD_EXPIRATION_DATE = NULL,
a.CREDIT_CARD_HOLDER_NAME = NULL,
a.PAYMENT_REF_NUMBER = NULL
WHERE a.payment_id = payment_id_tab(i);
fnd_file.put_line(FND_FILE.OUTPUT,'No payment tbl records to update');
fnd_file.put_line(FND_FILE.OUTPUT,'Did not update aso_payments table because of errors');
fnd_file.put_line(FND_FILE.OUTPUT, 'Update failing at asopayments for payment_id_tab(j): ' || payment_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
);