[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ parallel(WBR) */
WB_Low
,WB_High,rownum num
FROM
(SELECT /*+ no_merge parallel(WB) */ MIN(ID) WB_Low, MAX(ID) WB_High
FROM
(SELECT /*+ no_merge parallel(khdr) */ ID, FLOOR((ROWNUM-1)/l_bucket_size) Worker_Bucket
FROM
( SELECT id
FROM oks_k_headers_b okshdr
WHERE okshdr.cc_no IS NOT NULL
AND okshdr.payment_type = 'CCR'
AND okshdr.trxn_extension_id is null
order by id) KHDR) WB GROUP BY Worker_Bucket) WBR;
SELECT /*+ parallel(WBR) */
WB_Low
,WB_High,rownum num
FROM
(SELECT /*+ no_merge parallel(WB) */ MIN(ID) WB_Low, MAX(ID) WB_High
FROM
(SELECT /*+ no_merge parallel(kln) */ ID, FLOOR((ROWNUM-1)/l_bucket_size) Worker_Bucket
FROM
( SELECT oksline.id
FROM oks_k_lines_b oksline, okc_k_lines_b okcline
WHERE oksline.cle_id=okcline.id
and oksline.cc_no IS NOT NULL
AND oksline.payment_type = 'CCR'
AND oksline.trxn_extension_id is null
and okcline.lse_id in (1,12,19,46)
order by id) KLN) WB GROUP BY Worker_Bucket) WBR;
Select /*+ PARALLEL(okshdr) */
min(okshdr.id) minid,
max(okshdr.id) maxid,
avg(okshdr.id) avgid,
stddev(okshdr.id) stdid,
count(*) total
From OKS_K_HEADERS_B okshdr ;
Select /*+ PARALLEL(okshdrh) */
min(okshdrh.id) minid,
max(okshdrh.id) maxid,
avg(okshdrh.id) avgid,
stddev(okshdrh.id) stdid,
count(*) total
From OKS_K_HEADERS_BH okshdrh ;
Select /*+ PARALLEL(oksline) */
min(oksline.id) minid,
max(oksline.id) maxid,
avg(oksline.id) avgid,
stddev(oksline.id) stdid,
count(*) total
From OKS_K_LINES_B oksline ;
Select /*+ PARALLEL(okslineh) */
min(okslineh.id) minid,
max(okslineh.id) maxid,
avg(okslineh.id) avgid,
stddev(okslineh.id) stdid,
count(*) total
From OKS_K_LINES_BH okslineh ;
select
oksline.id oksline_id,
oksline.major_version oksline_major_version,
TRANSLATE(oksline.cc_no,'0: -_', '0'),
'UNKNOWN',
oksline.cc_expiry_date,
IV.hdr_id okchdr_id,
IV.line_id okcline_id,
IV.party_id,
IV.cust_acct_id,
IV.bill_to_site_use_id,
IV.authoring_org_id,
iby_fndcpt_tx_extensions_s.nextval, -- the new transaction extension ID
iby_instr_s.nextval, -- the new credit card id
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(oksline.cc_no)),'X')
from OKS_K_LINES_BH oksline,
(select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, line.cust_acct_id,
line.bill_to_site_use_id, line.id line_id
from okc_k_lines_b line, okc_k_headers_all_b hdr, hz_cust_accounts_all hz
where line.dnz_chr_id = hdr.id
and line.lse_id in (1,12,19,46)
and hz.cust_account_id = line.cust_acct_id) IV,
IBY_EXTERNAL_PAYERS_ALL payer,
IBY_SECURITY_SEGMENTS sec,
IBY_CC_ISSUER_RANGES rangE
where IV.line_id = oksline.cle_id
and oksline.cc_no is not null
and oksline.payment_type = 'CCR'
and oksline.cc_bank_acct_id is null
and oksline.trxn_extension_id is null
---and IV.party_id = PAYER.PARTY_ID (+)
and IV.cust_acct_id = PAYER.CUST_ACCOUNT_ID(+)
and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
---and IV.authoring_org_id = PAYER.ORG_ID(+)
and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
and sec.sec_segment_id = IBY_CC_SECURITY_PUB.get_segment_id(oksline.cc_no)
and sec.CC_ISSUER_RANGE_ID = RANGE.CC_ISSUER_RANGE_ID (+)
and oksline.id BETWEEN p_id_low AND p_id_high;
select
oksline.id oksline_id,
oksline.major_version oksline_major_version,
TRANSLATE(oksline.cc_no,'0: -_', '0'),
'UNKNOWN',
oksline.cc_expiry_date,
IV.hdr_id okchdr_id,
IV.line_id okcline_id,
IV.party_id,
IV.cust_acct_id,
IV.bill_to_site_use_id,
IV.authoring_org_id,
IBY_FNDCPT_TX_EXTENSIONS_S.nextval, -- the new transaction extension ID
IBY_INSTR_S.nextval, -- the new credit card id
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(oksline.cc_no, FND_API.G_FALSE) cc_number_hash1,
----iby_fndcpt_setup_pub.get_hash(oksline.cc_no, FND_API.G_TRUE) cc_number_hash2,
iby_fndcpt_setup_pub.get_hash(oksline.cc_no, 'F') cc_number_hash1,
iby_fndcpt_setup_pub.get_hash(oksline.cc_no, 'T') cc_number_hash2,
IBY_CC_VALIDATE.Get_CC_Issuer_Range(oksline.cc_no) cc_issuer_range_id,
Null sec_segment_id,
DECODE(IBY_CC_VALIDATE.Get_CC_Issuer_Range(oksline.cc_no), NULL,LENGTH(oksline.cc_no), NULL) cc_number_length,
SUBSTR(oksline.cc_no,GREATEST(-4,-LENGTH(oksline.cc_no))) cc_unmask_digits,
LPAD(SUBSTR(oksline.cc_no, GREATEST(-4,-LENGTH(oksline.cc_no))),
LENGTH(oksline.cc_no),
'X' ) masked_cc_number
from oks_k_lines_bh oksline,
(select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, line.cust_acct_id,
line.bill_to_site_use_id, line.id line_id
from okc_k_lines_b line, okc_k_headers_all_b hdr, hz_cust_accounts_all hz
where line.dnz_chr_id = hdr.id
and line.lse_id in (1,12,19,46)
and hz.cust_account_id = line.cust_acct_id) IV,
IBY_EXTERNAL_PAYERS_ALL payer
where IV.line_id = oksline.cle_id
and oksline.cc_no is not null
and oksline.payment_type = 'CCR'
and oksline.cc_bank_acct_id is null
and oksline.trxn_extension_id is null
---and IV.party_id = PAYER.PARTY_ID (+)
and IV.cust_acct_id = PAYER.CUST_ACCOUNT_ID(+)
and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
---and IV.authoring_org_id = PAYER.ORG_ID(+)
and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
and oksline.id BETWEEN p_id_low AND p_id_high;
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,
CC_NUM_SEC_SEGMENT_ID,
CARD_MASK_SETTING,
CARD_UNMASK_LENGTH,
CC_NUMBER_LENGTH,
MASKED_CC_NUMBER,
OBJECT_VERSION_NUMBER
)
VALUES(
c_customer_id(i),
'CREDITCARD',
'N',
c_cc_code(i),
'Y',
'Y',
c_cc_exp_date(i),
null,
DECODE(c_sec_segment_id(i), NULL,c_cc_number(i), c_cc_unmask_digits(i)),
c_cc_id(i),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
DECODE(c_sec_segment_id(i), NULL,'N','Y'),
c_hash1(i),
c_hash2(i),
c_card_range_id(i),
c_sec_segment_id(i),
'DISPLAY_LAST',
4,
c_cc_num_length(i),
c_cc_masked_num(i),
1
);
INSERT INTO IBY_PMT_INSTR_USES_ALL
(INSTRUMENT_PAYMENT_USE_ID,
EXT_PMT_PARTY_ID,
INSTRUMENT_TYPE,
INSTRUMENT_ID,
PAYMENT_FUNCTION,
PAYMENT_FLOW,
ORDER_OF_PREFERENCE,
START_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
object_version_number)
SELECT
c_instr_assignment_id(i),
EXT_PAYER_ID,
'CREDITCARD',
c_cc_id(i),
'CUSTOMER_PAYMENT',
'FUNDS_CAPTURE',
1,
sysdate,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1
FROM IBY_EXTERNAL_PAYERS_ALL payer
WHERE payer.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
AND payer.PARTY_ID = c_customer_id(i)
AND payer.ORG_TYPE = 'OPERATING_UNIT'
AND payer.ORG_ID = c_org_id(i)
AND payer.CUST_ACCOUNT_ID = c_cust_account_id(i)
AND payer.ACCT_SITE_USE_ID = c_cust_site_use_id(i)
AND ROWNUM = 1;
INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
(TRXN_EXTENSION_ID,
PAYMENT_CHANNEL_CODE,
INSTR_ASSIGNMENT_ID,
ENCRYPTED,
ORIGIN_APPLICATION_ID,
ORDER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES
(c_trxn_entity_id(i),
'CREDIT_CARD',
c_instr_assignment_id(i),
'N',
515,
oksline_id(i),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1);
UPDATE oks_k_lines_bh
SET TRXN_EXTENSION_ID = c_trxn_entity_id(i)
WHERE id = oksline_id(i)
AND major_version = oksline_major_version(i);
insert into oks_rule_error( chr_id,
cle_id , attribute_name, attribute_value,
major_version, rule_information_category )
values (okchdr_id(i), okcline_id(i), 'CC_NO', c_cc_number(i),
oksline_major_version(i), 'R12CC');
oksline_id.delete;
oksline_major_version.delete;
c_cc_number.delete;
c_cc_code.delete;
c_cc_exp_date.delete;
okcline_id.delete;
okchdr_id.delete;
c_customer_id.delete;
c_cust_account_id.delete;
c_cust_site_use_id.delete;
c_org_id.delete;
c_trxn_entity_id.delete;
c_cc_id.delete;
c_ext_payer_id.delete;
c_create_payer_flag.delete;
c_instr_assignment_id.delete;
c_hash1.delete;
c_hash2.delete;
c_card_range_id.delete;
c_sec_segment_id.delete;
c_cc_num_length.delete;
c_cc_unmask_digits.delete;
c_cc_masked_num.delete;
select
oksline.id oksline_id,
TRANSLATE(oksline.cc_no,'0: -_', '0'),
'UNKNOWN',
oksline.cc_expiry_date,
IV.hdr_id okchdr_id,
IV.line_id okcline_id,
IV.party_id,
IV.cust_acct_id,
IV.bill_to_site_use_id,
IV.authoring_org_id,
iby_fndcpt_tx_extensions_s.nextval, -- the new transaction extension ID
iby_instr_s.nextval, -- the new credit card id
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(oksline.cc_no)),'X')
from OKS_K_LINES_B oksline,
(select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, line.cust_acct_id,
line.bill_to_site_use_id, line.id line_id
from okc_k_lines_b line, okc_k_headers_all_b hdr, hz_cust_accounts_all hz
where line.dnz_chr_id = hdr.id
and line.lse_id in (1,12,19,46)
and hz.cust_account_id = line.cust_acct_id) IV,
IBY_EXTERNAL_PAYERS_ALL payer,
IBY_SECURITY_SEGMENTS sec,
IBY_CC_ISSUER_RANGES rangE
where IV.line_id = oksline.cle_id
and oksline.cc_no is not null
and oksline.payment_type = 'CCR'
and oksline.cc_bank_acct_id is null
and oksline.trxn_extension_id is null
---and IV.party_id = PAYER.PARTY_ID (+)
and IV.cust_acct_id = PAYER.CUST_ACCOUNT_ID(+)
and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
---and IV.authoring_org_id = PAYER.ORG_ID(+)
and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
and sec.sec_segment_id = IBY_CC_SECURITY_PUB.get_segment_id(oksline.cc_no)
and sec.CC_ISSUER_RANGE_ID = RANGE.CC_ISSUER_RANGE_ID (+)
and oksline.id BETWEEN p_id_low AND p_id_high;
select
oksline.id oksline_id,
TRANSLATE(oksline.cc_no,'0: -_', '0'),
'UNKNOWN',
oksline.cc_expiry_date,
IV.hdr_id okchdr_id,
IV.line_id okcline_id,
IV.party_id,
IV.cust_acct_id,
IV.bill_to_site_use_id,
IV.authoring_org_id,
IBY_FNDCPT_TX_EXTENSIONS_S.nextval, -- the new transaction extension ID
IBY_INSTR_S.nextval, -- the new credit card id
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(oksline.cc_no, FND_API.G_FALSE) cc_number_hash1,
----iby_fndcpt_setup_pub.get_hash(oksline.cc_no, FND_API.G_TRUE) cc_number_hash2,
iby_fndcpt_setup_pub.get_hash(oksline.cc_no, 'F') cc_number_hash1,
iby_fndcpt_setup_pub.get_hash(oksline.cc_no, 'T') cc_number_hash2,
IBY_CC_VALIDATE.Get_CC_Issuer_Range(oksline.cc_no) cc_issuer_range_id,
Null sec_segment_id,
DECODE(IBY_CC_VALIDATE.Get_CC_Issuer_Range(oksline.cc_no), NULL,LENGTH(oksline.cc_no), NULL) cc_number_length,
SUBSTR(oksline.cc_no,GREATEST(-4,-LENGTH(oksline.cc_no))) cc_unmask_digits,
LPAD(SUBSTR(oksline.cc_no, GREATEST(-4,-LENGTH(oksline.cc_no))),
LENGTH(oksline.cc_no),
'X' ) masked_cc_number
from oks_k_lines_b oksline,
(select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, line.cust_acct_id,
line.bill_to_site_use_id, line.id line_id
from okc_k_lines_b line, okc_k_headers_all_b hdr, hz_cust_accounts_all hz
where line.dnz_chr_id = hdr.id
and line.lse_id in (1,12,19,46)
and hz.cust_account_id = line.cust_acct_id) IV,
IBY_EXTERNAL_PAYERS_ALL payer
where IV.line_id = oksline.cle_id
and oksline.cc_no is not null
and oksline.payment_type = 'CCR'
and oksline.cc_bank_acct_id is null
and oksline.trxn_extension_id is null
---and IV.party_id = PAYER.PARTY_ID (+)
and IV.cust_acct_id = PAYER.CUST_ACCOUNT_ID(+)
and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
---and IV.authoring_org_id = PAYER.ORG_ID(+)
and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
and oksline.id BETWEEN p_id_low AND p_id_high;
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,
CC_NUM_SEC_SEGMENT_ID,
CARD_MASK_SETTING,
CARD_UNMASK_LENGTH,
CC_NUMBER_LENGTH,
MASKED_CC_NUMBER,
OBJECT_VERSION_NUMBER
)
VALUES(
c_customer_id(i),
'CREDITCARD',
'N',
c_cc_code(i),
'Y',
'Y',
c_cc_exp_date(i),
null,
DECODE(c_sec_segment_id(i), NULL,c_cc_number(i), c_cc_unmask_digits(i)),
c_cc_id(i),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
DECODE(c_sec_segment_id(i), NULL,'N','Y'),
c_hash1(i),
c_hash2(i),
c_card_range_id(i),
c_sec_segment_id(i),
'DISPLAY_LAST',
4,
c_cc_num_length(i),
c_cc_masked_num(i),
1
);
INSERT INTO IBY_PMT_INSTR_USES_ALL
(INSTRUMENT_PAYMENT_USE_ID,
EXT_PMT_PARTY_ID,
INSTRUMENT_TYPE,
INSTRUMENT_ID,
PAYMENT_FUNCTION,
PAYMENT_FLOW,
ORDER_OF_PREFERENCE,
START_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
object_version_number)
SELECT
c_instr_assignment_id(i),
EXT_PAYER_ID,
'CREDITCARD',
c_cc_id(i),
'CUSTOMER_PAYMENT',
'FUNDS_CAPTURE',
1,
sysdate,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1
FROM IBY_EXTERNAL_PAYERS_ALL payer
WHERE payer.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
AND payer.PARTY_ID = c_customer_id(i)
AND payer.ORG_TYPE = 'OPERATING_UNIT'
AND payer.ORG_ID = c_org_id(i)
AND payer.CUST_ACCOUNT_ID = c_cust_account_id(i)
AND payer.ACCT_SITE_USE_ID = c_cust_site_use_id(i)
AND ROWNUM = 1;
INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
(TRXN_EXTENSION_ID,
PAYMENT_CHANNEL_CODE,
INSTR_ASSIGNMENT_ID,
ENCRYPTED,
ORIGIN_APPLICATION_ID,
ORDER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES
(c_trxn_entity_id(i),
'CREDIT_CARD',
c_instr_assignment_id(i),
'N',
515,
oksline_id(i),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1);
UPDATE oks_k_lines_b
SET TRXN_EXTENSION_ID = c_trxn_entity_id(i)
WHERE id = oksline_id(i);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'error in insert lines '||sqlerrm );
insert into oks_rule_error( chr_id,
cle_id , attribute_name, attribute_value,
major_version, rule_information_category )
values (okchdr_id(i), okcline_id(i), 'CC_NO', c_cc_number(i),
NULL, 'R12CC');
oksline_id.delete;
c_cc_number.delete;
c_cc_code.delete;
c_cc_exp_date.delete;
okcline_id.delete;
okchdr_id.delete;
c_customer_id.delete;
c_cust_account_id.delete;
c_cust_site_use_id.delete;
c_org_id.delete;
c_trxn_entity_id.delete;
c_cc_id.delete;
c_ext_payer_id.delete;
c_create_payer_flag.delete;
c_instr_assignment_id.delete;
c_hash1.delete;
c_hash2.delete;
c_card_range_id.delete;
c_sec_segment_id.delete;
c_cc_num_length.delete;
c_cc_unmask_digits.delete;
c_cc_masked_num.delete;
select
okshdr.id okshdr_id,
okshdr.major_version okshdr_major_version,
TRANSLATE(okshdr.cc_no,'0: -_', '0'),
'UNKNOWN',
okshdr.cc_expiry_date,
IV.hdr_id okchdr_id,
IV.party_id,
IV.cust_account_id,
IV.bill_to_site_use_id,
IV.authoring_org_id,
iby_fndcpt_tx_extensions_s.nextval, -- the new transaction extension ID
iby_instr_s.nextval, -- the new credit card id
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(okshdr.cc_no)),'X')
from OKS_K_HEADERS_BH okshdr,
(select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, hz.cust_account_id,
hdr.bill_to_site_use_id
from okc_k_headers_all_b hdr, hz_cust_accounts_all hz,
hz_cust_site_uses_all site, hz_cust_acct_sites_all acct
where hdr.bill_to_site_use_id = site.site_use_id
and site.cust_acct_site_id = acct.cust_acct_site_id
and acct.cust_account_id = hz.cust_account_id
and site.site_use_code = 'BILL_TO' ) IV,
IBY_EXTERNAL_PAYERS_ALL payer,
IBY_SECURITY_SEGMENTS sec,
IBY_CC_ISSUER_RANGES rangE
where IV.hdr_id = okshdr.chr_id
and okshdr.cc_no is not null
and okshdr.payment_type = 'CCR'
and okshdr.cc_bank_acct_id is null
and okshdr.trxn_extension_id is null
---and IV.party_id = PAYER.PARTY_ID (+)
and IV.cust_account_id = PAYER.CUST_ACCOUNT_ID(+)
and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
---and IV.authoring_org_id = PAYER.ORG_ID(+)
and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
and sec.sec_segment_id = IBY_CC_SECURITY_PUB.get_segment_id(okshdr.cc_no)
and sec.CC_ISSUER_RANGE_ID = RANGE.CC_ISSUER_RANGE_ID (+)
and okshdr.id BETWEEN p_id_low AND p_id_high;
select
okshdr.id okshdr_id,
okshdr.major_version okshdr_major_version,
TRANSLATE(okshdr.cc_no,'0: -_', '0'),
'UNKNOWN',
okshdr.cc_expiry_date,
IV.hdr_id okchdr_id,
IV.party_id,
IV.cust_account_id,
IV.bill_to_site_use_id,
IV.authoring_org_id,
IBY_FNDCPT_TX_EXTENSIONS_S.nextval, -- the new transaction extension ID
IBY_INSTR_S.nextval, -- the new credit card id
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(okshdr.cc_no, FND_API.G_FALSE) cc_number_hash1,
----iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, FND_API.G_TRUE) cc_number_hash2,
iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, 'F') cc_number_hash1,
iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, 'T') cc_number_hash2,
IBY_CC_VALIDATE.Get_CC_Issuer_Range(okshdr.cc_no) cc_issuer_range_id,
Null sec_segment_id,
DECODE(IBY_CC_VALIDATE.Get_CC_Issuer_Range(okshdr.cc_no), NULL,LENGTH(okshdr.cc_no), NULL) cc_number_length,
SUBSTR(okshdr.cc_no,GREATEST(-4,-LENGTH(okshdr.cc_no))) cc_unmask_digits,
LPAD(SUBSTR(okshdr.cc_no, GREATEST(-4,-LENGTH(okshdr.cc_no))),
LENGTH(okshdr.cc_no),
'X' ) masked_cc_number
from oks_k_headers_bh okshdr,
(select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, hz.cust_account_id,
hdr.bill_to_site_use_id
from okc_k_headers_all_b hdr, hz_cust_accounts_all hz,
hz_cust_site_uses_all site, hz_cust_acct_sites_all acct
where hdr.bill_to_site_use_id = site.site_use_id
and site.cust_acct_site_id = acct.cust_acct_site_id
and acct.cust_account_id = hz.cust_account_id
and site.site_use_code = 'BILL_TO') IV,
IBY_EXTERNAL_PAYERS_ALL payer
where IV.hdr_id = okshdr.chr_id
and okshdr.cc_no is not null
---and okshdr.id = 317191029854960778512632995409857241499
and okshdr.payment_type = 'CCR'
and okshdr.cc_bank_acct_id is null
and okshdr.trxn_extension_id is null
---and IV.party_id = PAYER.PARTY_ID (+)
and IV.cust_account_id = PAYER.CUST_ACCOUNT_ID(+)
and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
---and IV.authoring_org_id = PAYER.ORG_ID(+)
and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
and okshdr.id BETWEEN p_id_low AND p_id_high;
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,
CC_NUM_SEC_SEGMENT_ID,
CARD_MASK_SETTING,
CARD_UNMASK_LENGTH,
CC_NUMBER_LENGTH,
MASKED_CC_NUMBER,
OBJECT_VERSION_NUMBER
)
VALUES(
c_customer_id(i),
'CREDITCARD',
'N',
c_cc_code(i),
'Y',
'Y',
c_cc_exp_date(i),
null,
DECODE(c_sec_segment_id(i), NULL,c_cc_number(i), c_cc_unmask_digits(i)),
c_cc_id(i),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
DECODE(c_sec_segment_id(i), NULL,'N','Y'),
c_hash1(i),
c_hash2(i),
c_card_range_id(i),
c_sec_segment_id(i),
'DISPLAY_LAST',
4,
c_cc_num_length(i),
c_cc_masked_num(i),
1
);
INSERT INTO IBY_PMT_INSTR_USES_ALL
(INSTRUMENT_PAYMENT_USE_ID,
EXT_PMT_PARTY_ID,
INSTRUMENT_TYPE,
INSTRUMENT_ID,
PAYMENT_FUNCTION,
PAYMENT_FLOW,
ORDER_OF_PREFERENCE,
START_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
object_version_number)
SELECT
c_instr_assignment_id(i),
EXT_PAYER_ID,
'CREDITCARD',
c_cc_id(i),
'CUSTOMER_PAYMENT',
'FUNDS_CAPTURE',
1,
sysdate,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1
FROM IBY_EXTERNAL_PAYERS_ALL payer
WHERE payer.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
AND payer.PARTY_ID = c_customer_id(i)
AND payer.ORG_TYPE = 'OPERATING_UNIT'
AND payer.ORG_ID = c_org_id(i)
AND payer.CUST_ACCOUNT_ID = c_cust_account_id(i)
AND payer.ACCT_SITE_USE_ID = c_cust_site_use_id(i)
AND ROWNUM = 1;
INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
(TRXN_EXTENSION_ID,
PAYMENT_CHANNEL_CODE,
INSTR_ASSIGNMENT_ID,
ENCRYPTED,
ORIGIN_APPLICATION_ID,
ORDER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES
(c_trxn_entity_id(i),
'CREDIT_CARD',
c_instr_assignment_id(i),
'N',
515,
okshdr_id(i),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1);
UPDATE oks_k_headers_bh
SET TRXN_EXTENSION_ID = c_trxn_entity_id(i)
WHERE id = okshdr_id(i)
AND major_version = oksline_major_version(i);
insert into oks_rule_error( chr_id,
cle_id , attribute_name, attribute_value,
major_version, rule_information_category )
values (okchdr_id(i), NULL, 'CC_NO', c_cc_number(i),
oksline_major_version(i), 'R12CC');
okshdr_id.delete;
oksline_major_version.delete;
c_cc_number.delete;
c_cc_code.delete;
c_cc_exp_date.delete;
okcline_id.delete;
okchdr_id.delete;
c_customer_id.delete;
c_cust_account_id.delete;
c_cust_site_use_id.delete;
c_org_id.delete;
c_trxn_entity_id.delete;
c_cc_id.delete;
c_ext_payer_id.delete;
c_create_payer_flag.delete;
c_instr_assignment_id.delete;
c_hash1.delete;
c_hash2.delete;
c_card_range_id.delete;
c_sec_segment_id.delete;
c_cc_num_length.delete;
c_cc_unmask_digits.delete;
c_cc_masked_num.delete;
select
okshdr.id okshdr_id,
TRANSLATE(okshdr.cc_no,'0: -_', '0'),
'UNKNOWN',
okshdr.cc_expiry_date,
IV.hdr_id okchdr_id,
IV.party_id,
IV.cust_account_id,
IV.bill_to_site_use_id,
IV.authoring_org_id,
iby_fndcpt_tx_extensions_s.nextval, -- the new transaction extension ID
iby_instr_s.nextval, -- the new credit card id
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(okshdr.cc_no)),'X')
from OKS_K_HEADERS_B okshdr,
(select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, hz.cust_account_id,
hdr.bill_to_site_use_id
from okc_k_headers_all_b hdr, hz_cust_accounts_all hz,
hz_cust_site_uses_all site, hz_cust_acct_sites_all acct
where hdr.bill_to_site_use_id = site.site_use_id
and site.cust_acct_site_id = acct.cust_acct_site_id
and acct.cust_account_id = hz.cust_account_id
and site.site_use_code = 'BILL_TO') IV,
IBY_EXTERNAL_PAYERS_ALL payer,
IBY_SECURITY_SEGMENTS sec,
IBY_CC_ISSUER_RANGES rangE
where IV.hdr_id = okshdr.chr_id
and okshdr.cc_no is not null
and okshdr.payment_type = 'CCR'
and okshdr.cc_bank_acct_id is null
and okshdr.trxn_extension_id is null
---and IV.party_id = PAYER.PARTY_ID (+)
and IV.cust_account_id = PAYER.CUST_ACCOUNT_ID(+)
and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
---and IV.authoring_org_id = PAYER.ORG_ID(+)
and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
and sec.sec_segment_id = IBY_CC_SECURITY_PUB.get_segment_id(okshdr.cc_no)
and sec.CC_ISSUER_RANGE_ID = RANGE.CC_ISSUER_RANGE_ID (+)
and okshdr.id BETWEEN p_id_low AND p_id_high;
select
okshdr.id okshdr_id,
TRANSLATE(okshdr.cc_no,'0: -_', '0'),
'UNKNOWN',
okshdr.cc_expiry_date,
IV.hdr_id okchdr_id,
IV.party_id,
IV.cust_account_id,
IV.bill_to_site_use_id,
IV.authoring_org_id,
IBY_FNDCPT_TX_EXTENSIONS_S.nextval, -- the new transaction extension ID
IBY_INSTR_S.nextval, -- the new credit card id
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(okshdr.cc_no, FND_API.G_FALSE) cc_number_hash1,
----iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, FND_API.G_TRUE) cc_number_hash2,
iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, 'F') cc_number_hash1,
iby_fndcpt_setup_pub.get_hash(okshdr.cc_no, 'T') cc_number_hash2,
IBY_CC_VALIDATE.Get_CC_Issuer_Range(okshdr.cc_no) cc_issuer_range_id,
Null sec_segment_id,
DECODE(IBY_CC_VALIDATE.Get_CC_Issuer_Range(okshdr.cc_no), NULL,LENGTH(okshdr.cc_no), NULL) cc_number_length,
SUBSTR(okshdr.cc_no,GREATEST(-4,-LENGTH(okshdr.cc_no))) cc_unmask_digits,
LPAD(SUBSTR(okshdr.cc_no, GREATEST(-4,-LENGTH(okshdr.cc_no))),
LENGTH(okshdr.cc_no),
'X' ) masked_cc_number
from oks_k_headers_b okshdr,
(select hz.party_id, hdr.authoring_org_id, hdr.id hdr_id, hz.cust_account_id,
hdr.bill_to_site_use_id
from okc_k_headers_all_b hdr, hz_cust_accounts_all hz,
hz_cust_site_uses_all site, hz_cust_acct_sites_all acct
where hdr.bill_to_site_use_id = site.site_use_id
and site.cust_acct_site_id = acct.cust_acct_site_id
and acct.cust_account_id = hz.cust_account_id
and site.site_use_code = 'BILL_TO') IV,
IBY_EXTERNAL_PAYERS_ALL payer
where IV.hdr_id = okshdr.chr_id
and okshdr.cc_no is not null
---and okshdr.id = 317191029854960778512632995409857241499
and okshdr.payment_type = 'CCR'
and okshdr.cc_bank_acct_id is null
and okshdr.trxn_extension_id is null
---and IV.party_id = PAYER.PARTY_ID (+)
and IV.cust_account_id = PAYER.CUST_ACCOUNT_ID(+)
and IV.bill_to_site_use_id = PAYER.ACCT_SITE_USE_ID(+)
---and IV.authoring_org_id = PAYER.ORG_ID(+)
and 'OPERATING_UNIT' = PAYER.ORG_TYPE(+)
and 'CUSTOMER_PAYMENT' = PAYER.PAYMENT_FUNCTION(+)
and okshdr.id BETWEEN p_id_low AND p_id_high;
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,
CC_NUM_SEC_SEGMENT_ID,
CARD_MASK_SETTING,
CARD_UNMASK_LENGTH,
CC_NUMBER_LENGTH,
MASKED_CC_NUMBER,
OBJECT_VERSION_NUMBER
)
VALUES(
c_customer_id(i),
'CREDITCARD',
'N',
c_cc_code(i),
'Y',
'Y',
c_cc_exp_date(i),
null,
DECODE(c_sec_segment_id(i), NULL,c_cc_number(i), c_cc_unmask_digits(i)),
c_cc_id(i),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
DECODE(c_sec_segment_id(i), NULL,'N','Y'),
c_hash1(i),
c_hash2(i),
c_card_range_id(i),
c_sec_segment_id(i),
'DISPLAY_LAST',
4,
c_cc_num_length(i),
c_cc_masked_num(i),
1
);
INSERT INTO IBY_PMT_INSTR_USES_ALL
(INSTRUMENT_PAYMENT_USE_ID,
EXT_PMT_PARTY_ID,
INSTRUMENT_TYPE,
INSTRUMENT_ID,
PAYMENT_FUNCTION,
PAYMENT_FLOW,
ORDER_OF_PREFERENCE,
START_DATE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
object_version_number)
SELECT
c_instr_assignment_id(i),
EXT_PAYER_ID,
'CREDITCARD',
c_cc_id(i),
'CUSTOMER_PAYMENT',
'FUNDS_CAPTURE',
1,
sysdate,
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1
FROM IBY_EXTERNAL_PAYERS_ALL payer
WHERE payer.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
AND payer.PARTY_ID = c_customer_id(i)
AND payer.ORG_TYPE = 'OPERATING_UNIT'
AND payer.ORG_ID = c_org_id(i)
AND payer.CUST_ACCOUNT_ID = c_cust_account_id(i)
AND payer.ACCT_SITE_USE_ID = c_cust_site_use_id(i)
AND ROWNUM = 1;
INSERT INTO IBY_FNDCPT_TX_EXTENSIONS
(TRXN_EXTENSION_ID,
PAYMENT_CHANNEL_CODE,
INSTR_ASSIGNMENT_ID,
ENCRYPTED,
ORIGIN_APPLICATION_ID,
ORDER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
OBJECT_VERSION_NUMBER)
VALUES
(c_trxn_entity_id(i),
'CREDIT_CARD',
c_instr_assignment_id(i),
'N',
515,
okshdr_id(i),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
1);
UPDATE oks_k_lines_b
SET TRXN_EXTENSION_ID = c_trxn_entity_id(i)
WHERE id = okshdr_id(i);
FND_FILE.PUT_LINE (FND_FILE.LOG, 'error in insert hdr '||sqlerrm );
insert into oks_rule_error( chr_id,
cle_id , attribute_name, attribute_value,
major_version, rule_information_category )
values (okchdr_id(i), NULL, 'CC_NO', c_cc_number(i),
NULL, 'R12CC');
okshdr_id.delete;
c_cc_number.delete;
c_cc_code.delete;
c_cc_exp_date.delete;
okcline_id.delete;
okchdr_id.delete;
c_customer_id.delete;
c_cust_account_id.delete;
c_cust_site_use_id.delete;
c_org_id.delete;
c_trxn_entity_id.delete;
c_cc_id.delete;
c_ext_payer_id.delete;
c_create_payer_flag.delete;
c_instr_assignment_id.delete;
c_hash1.delete;
c_hash2.delete;
c_card_range_id.delete;
c_sec_segment_id.delete;
c_cc_num_length.delete;
c_cc_unmask_digits.delete;
c_cc_masked_num.delete;
SELECT okh.chr_id,
kh.contract_number ||' ' ||kh.contract_number_modifier
contract_number,
kh.start_date start_date,
kh.end_date end_date,
kh.date_terminated date_terminated,
st.meaning status,
ore.attribute_value cc_no,
ore.cc_expiry_date cc_exp_date,
(SELECT party_name
FROM hz_parties a,
hz_cust_accounts b
WHERE a.party_id = b.party_id
AND b.cust_account_id = cust_acct_id) party_name
FROM oks_rule_error ore,
oks_k_headers_b okh,
okc_k_headers_all_b kh,
okc_statuses_v st
WHERE ore.RULE_INFORMATION_CATEGORY = 'R12CC'
AND ore.chr_id = kh.ID
AND ore.cle_id IS NULL
AND kh.ID = okh.chr_id
AND st.code = kh.sts_code;
SELECT kh.ID,
kh.contract_number||' '||kh.contract_number_modifier contract_number,
kh.major_version,
kh.start_date start_date,
kh.end_date end_date,
kh.date_terminated date_terminated,
st.meaning status,
ore.attribute_value cc_no,
ore.cc_expiry_date cc_exp_date,
(SELECT party_name
FROM hz_parties a,
hz_cust_accounts b
WHERE a.party_id = b.party_id
AND b.cust_account_id = cust_acct_id) party_name
FROM oks_rule_error ore,
okc_k_headers_all_bh kh,
okc_statuses_v st
WHERE ore.RULE_INFORMATION_CATEGORY = 'R12CC'
AND ore.chr_id = kh.ID
AND ore.cle_id IS NULL
AND ore.major_version IS NOT NULL
AND st.code = kh.STS_CODE;
SELECT ore.cle_id,
kh.contract_number ||' ' ||kh.contract_number_modifier
contract_number,
lc.line_number,
lc.start_date start_date,
lc.end_date end_date,
lc.date_terminated date_terminated,
st.meaning status,
ore.attribute_value cc_no,
ore.cc_expiry_date cc_exp_date,
(SELECT party_name
FROM hz_parties a,
hz_cust_accounts b
WHERE a.party_id = b.party_id
AND b.cust_account_id = lc.cust_acct_id) party_name,
(SELECT decode(fnd_profile.value('OKS_ITEM_DISPLAY_PREFERENCE'),'DISPLAY_DESC',
B.CONCATENATED_SEGMENTS ,T.DESCRIPTION )
FROM MTL_SYSTEM_ITEMS_B_KFV B,
MTL_SYSTEM_ITEMS_TL T
WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
AND T.LANGUAGE = userenv('LANG')
AND B.INVENTORY_ITEM_ID = object1_id1
AND ROWNUM < 2) service_name
FROM oks_rule_error ore,
okc_k_headers_all_b kh,
okc_statuses_v st,
oks_k_lines_b ls,
okc_k_lines_b lc,
okc_k_items it
WHERE ore.RULE_INFORMATION_CATEGORY = 'R12CC'
AND ore.cle_id = ls.cle_ID
AND ore.CHR_id = ls.DNZ_CHR_ID
AND ls.cle_id = lc.ID
AND ls.dnz_chr_id = kh.id
AND lc.STS_CODE = st.code
AND it.cle_id = lc.ID
AND it.jtot_object1_code = 'OKX_SERVICE';
SELECT ore.cle_id,
kh.contract_number ||' ' ||kh.contract_number_modifier
contract_number,
lc.major_version,
lc.line_number,
lc.start_date start_date,
lc.end_date end_date,
lc.date_terminated date_terminated,
st.meaning status,
ore.attribute_value cc_no,
ore.cc_expiry_date cc_exp_date,
(SELECT party_name
FROM hz_parties a,
hz_cust_accounts b
WHERE a.party_id = b.party_id
AND b.cust_account_id = lc.cust_acct_id) party_name,
(SELECT decode(fnd_profile.value('OKS_ITEM_DISPLAY_PREFERENCE'),'DISPLAY_DESC',
B.CONCATENATED_SEGMENTS ,T.DESCRIPTION )
FROM MTL_SYSTEM_ITEMS_B_KFV B,
MTL_SYSTEM_ITEMS_TL T
WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
AND T.LANGUAGE = userenv('LANG')
AND B.INVENTORY_ITEM_ID = object1_id1
AND ROWNUM < 2) service_name
FROM oks_rule_error ore,
okc_k_headers_all_b kh,
okc_statuses_v st,
okc_k_lines_bh lc,
okc_k_items it
WHERE ore.RULE_INFORMATION_CATEGORY = 'R12CC'
AND ore.cle_id = lc.id
AND kh.id = lc.dnz_chr_id
AND lc.STS_CODE = st.code
AND it.cle_id = lc.ID
AND it.jtot_object1_code = 'OKX_SERVICE';
line_id.DELETE;
contract_number.DELETE;
line_number.DELETE;
line_sdate.DELETE;
line_edate.DELETE;
date_terminated.DELETE;
line_status.DELETE;
cc_number.DELETE;
cc_exp_date.DELETE;
party_name.DELETE;
service_name.DELETE;
line_id.DELETE;
contract_number.DELETE;
line_number.DELETE;
line_sdate.DELETE;
line_edate.DELETE;
date_terminated.DELETE;
line_status.DELETE;
cc_number.DELETE;
cc_exp_date.DELETE;
party_name.DELETE;
service_name.DELETE;
SELECT /*+ parallel(WBR) */
WB_Low
,WB_High,rownum num
FROM
(SELECT /*+ no_merge parallel(WB) */ MIN(ID) WB_Low, MAX(ID) WB_High
FROM
(SELECT /*+ no_merge parallel(khdr) */ ID, FLOOR((ROWNUM-1)/l_bucket_size) Worker_Bucket
FROM
( SELECT id
FROM oks_k_headers_b okshdr
WHERE ((okshdr.cc_no IS NOT NULL
AND okshdr.payment_type = 'CCR'
AND okshdr.trxn_extension_id is not null)
OR
(okshdr.trxn_extension_id is null
AND okshdr.chr_id in (select chr_id from oks_rule_error))
)
order by id) KHDR) WB GROUP BY Worker_Bucket) WBR;
SELECT /*+ parallel(WBR) */
WB_Low
,WB_High,rownum num
FROM
(SELECT /*+ no_merge parallel(WB) */ MIN(ID) WB_Low, MAX(ID) WB_High
FROM
(SELECT /*+ no_merge parallel(khdr) */ ID, FLOOR((ROWNUM-1)/l_bucket_size) Worker_Bucket
FROM
( Select rl.Id
From okc_rules_b rl
, okc_rule_groups_b rg
, Oks_k_headers_b Kh
Where rl.rgp_id = rg.id
And rl.rule_information_category = 'CCR'
And rl.rule_information1 is not null
And ((rg.chr_id = Kh.chr_id
And Kh.trxn_extension_id is not null)
Or
(rg.chr_id in (select chr_id from oks_rule_error))
)
order by id) KHDR) WB GROUP BY Worker_Bucket) WBR;
SELECT /*+ parallel(WBR) */
WB_Low
,WB_High,rownum num
FROM
(SELECT /*+ no_merge parallel(WB) */ MIN(ID) WB_Low, MAX(ID) WB_High
FROM
(SELECT /*+ no_merge parallel(kln) */ ID, FLOOR((ROWNUM-1)/l_bucket_size) Worker_Bucket
FROM
( SELECT oksline.id
FROM oks_k_lines_b oksline, okc_k_lines_b okcline
WHERE oksline.cle_id=okcline.id
and okcline.lse_id in (1,12,19,46)
and ((oksline.cc_no IS NOT NULL
AND oksline.payment_type = 'CCR'
AND oksline.trxn_extension_id is not null)
OR
(oksline.trxn_extension_id is null
AND oksline.cle_id in (select cle_id from oks_rule_error))
)
order by id) KLN) WB GROUP BY Worker_Bucket) WBR;
Select /*+ PARALLEL(okshdrh) */
min(okshdrh.id) minid,
max(okshdrh.id) maxid,
avg(okshdrh.id) avgid,
stddev(okshdrh.id) stdid,
count(*) total
From OKS_K_HEADERS_BH okshdrh ;
Select /*+ PARALLEL(okslineh) */
min(okslineh.id) minid,
max(okslineh.id) maxid,
avg(okslineh.id) avgid,
stddev(okslineh.id) stdid,
count(*) total
From OKS_K_LINES_BH okslineh ;
PROCEDURE UPDATE_CC_LINEH(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
p_id_low IN NUMBER,
p_id_high IN NUMBER,
p_batchsize IN NUMBER ) IS
Cursor l_line_hist_csr Is
Select Ks.id
From oks_k_lines_bh ks
where Ks.cc_no is not null
And Ks.trxn_extension_id is not null
And Ks.id between p_id_low and p_id_high;
Update Oks_k_Lines_bh
Set cc_no = null,
cc_bank_acct_id = null,
cc_expiry_date = null,
cc_auth_code = null
Where id = l_line_id(i);
End UPDATE_CC_LINEH;
PROCEDURE UPDATE_CC_HEADERH(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
p_id_low IN NUMBER,
p_id_high IN NUMBER,
p_batchsize IN NUMBER ) IS
Cursor l_hdr_hist_csr Is
Select id, chr_id
From oks_k_headers_bh
Where cc_no is not null
And trxn_extension_id is not null
And id between p_id_low and p_id_high;
Update Oks_k_headers_bh
Set cc_no = null,
cc_bank_acct_id = null,
cc_expiry_date = null,
cc_auth_code = null
Where id = l_hdr_id(i);
Update okc_rules_bh
Set rule_information1 = null,
rule_information2 = null,
rule_information3 = null,
rule_information4 = null
Where dnz_chr_id = l_chr_id(i)
and rule_information_category = 'CCR';
End UPDATE_CC_HEADERH;
PROCEDURE UPDATE_CC_HEADER(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
p_id_low IN NUMBER,
p_id_high IN NUMBER,
p_batchsize IN NUMBER ) IS
Cursor l_hdr_csr Is
Select id, chr_id
From oks_k_headers_b
Where cc_no is not null
And trxn_extension_id is not null
And id between p_id_low and p_id_high;
Update Oks_k_headers_b
Set cc_no = null,
cc_bank_acct_id = null,
cc_expiry_date = null,
cc_auth_code = null
Where id = l_hdr_id(i);
Update okc_rules_b
Set rule_information1 = null,
rule_information2 = null,
rule_information3 = null,
rule_information4 = null
Where dnz_chr_id = l_chr_id(i)
and rule_information_category = 'CCR';
End UPDATE_CC_HEADER;
PROCEDURE UPDATE_CC_HEADER_RULE(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
p_id_low IN NUMBER,
p_id_high IN NUMBER,
p_batchsize IN NUMBER ) IS
Cursor l_hdr_rule_csr Is
Select rl.Id
From okc_rules_b rl
, okc_rule_groups_b rg
, Oks_k_headers_b Kh
Where rl.rgp_id = rg.id
And rl.rule_information_category = 'CCR'
And rl.rule_information1 is not null
And ((rg.chr_id = Kh.chr_id
And Kh.trxn_extension_id is not null)
Or
(rg.chr_id in (select chr_id from oks_rule_error))
)
And rl.id between p_id_low and p_id_high;
Update okc_rules_b
Set rule_information1 = null
Where id = l_hdr_id(i);
End UPDATE_CC_HEADER_RULE;
PROCEDURE UPDATE_CC_LINE(
ERRBUF OUT NOCOPY VARCHAR2,
RETCODE OUT NOCOPY NUMBER,
p_id_low IN NUMBER,
p_id_high IN NUMBER,
p_batchsize IN NUMBER ) IS
Cursor l_line_csr Is
Select Ks.id
From oks_k_lines_b ks
Where Ks.cc_no is not null
And Ks.trxn_extension_id is not null
And Ks.id between p_id_low and p_id_high;
Update Oks_k_Lines_b
Set cc_no = null,
cc_bank_acct_id = null,
cc_expiry_date = null,
cc_auth_code = null
Where id = l_line_id(i);
End UPDATE_CC_LINE;