[Home] [Help]
421:
422:
423: -- Cursor that queries all transactions needed to be migrated with encryption enabled
424:
425: -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
426: -- the party contexts that are not in the external payer table can be identified.
427: -- 2. The credit card number needs to be numeric.
428: cursor oksline_cur_sec is
429: select
439: IV.bill_to_site_use_id,
440: IV.authoring_org_id,
441: iby_fndcpt_tx_extensions_s.nextval, -- the new transaction extension ID
442: iby_instr_s.nextval, -- the new credit card id
443: DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
444: PAYER.EXT_PAYER_ID),
445: DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
446: -- should create new external payer
447: IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
458: from okc_k_lines_b line, okc_k_headers_all_b hdr, hz_cust_accounts_all hz
459: where line.dnz_chr_id = hdr.id
460: and line.lse_id in (1,12,19,46)
461: and hz.cust_account_id = line.cust_acct_id) IV,
462: IBY_EXTERNAL_PAYERS_ALL payer,
463: IBY_SECURITY_SEGMENTS sec,
464: IBY_CC_ISSUER_RANGES rangE
465: where IV.line_id = oksline.cle_id
466: and oksline.cc_no is not null
478: and oksline.id BETWEEN p_id_low AND p_id_high;
479:
480: -- Cursor that queries all transactions needed to be migrated with encryption disable
481:
482: -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
483: -- the party contexts that are not in the external payer table can be identified.
484: -- 2. The credit card number needs to be numeric.
485: cursor oksline_cur is
486: select
496: IV.bill_to_site_use_id,
497: IV.authoring_org_id,
498: IBY_FNDCPT_TX_EXTENSIONS_S.nextval, -- the new transaction extension ID
499: IBY_INSTR_S.nextval, -- the new credit card id
500: DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
501: PAYER.EXT_PAYER_ID),
502: DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
503: -- should create new external payer
504: IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
519: from okc_k_lines_b line, okc_k_headers_all_b hdr, hz_cust_accounts_all hz
520: where line.dnz_chr_id = hdr.id
521: and line.lse_id in (1,12,19,46)
522: and hz.cust_account_id = line.cust_acct_id) IV,
523: IBY_EXTERNAL_PAYERS_ALL payer
524: where IV.line_id = oksline.cle_id
525: and oksline.cc_no is not null
526: and oksline.payment_type = 'CCR'
527: and oksline.cc_bank_acct_id is null
671: l_user_id,
672: sysdate,
673: l_user_id,
674: 1
675: FROM IBY_EXTERNAL_PAYERS_ALL payer
676: WHERE payer.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
677: AND payer.PARTY_ID = c_customer_id(i)
678: AND payer.ORG_TYPE = 'OPERATING_UNIT'
679: AND payer.ORG_ID = c_org_id(i)
812:
813:
814: -- Cursor that queries all transactions needed to be migrated with encryption enabled
815:
816: -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
817: -- the party contexts that are not in the external payer table can be identified.
818: -- 2. The credit card number needs to be numeric.
819: cursor oksline_cur_sec is
820: select
829: IV.bill_to_site_use_id,
830: IV.authoring_org_id,
831: iby_fndcpt_tx_extensions_s.nextval, -- the new transaction extension ID
832: iby_instr_s.nextval, -- the new credit card id
833: DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
834: PAYER.EXT_PAYER_ID),
835: DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
836: -- should create new external payer
837: IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
848: from okc_k_lines_b line, okc_k_headers_all_b hdr, hz_cust_accounts_all hz
849: where line.dnz_chr_id = hdr.id
850: and line.lse_id in (1,12,19,46)
851: and hz.cust_account_id = line.cust_acct_id) IV,
852: IBY_EXTERNAL_PAYERS_ALL payer,
853: IBY_SECURITY_SEGMENTS sec,
854: IBY_CC_ISSUER_RANGES rangE
855: where IV.line_id = oksline.cle_id
856: and oksline.cc_no is not null
868: and oksline.id BETWEEN p_id_low AND p_id_high;
869:
870: -- Cursor that queries all transactions needed to be migrated with encryption disable
871:
872: -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
873: -- the party contexts that are not in the external payer table can be identified.
874: -- 2. The credit card number needs to be numeric.
875: cursor oksline_cur is
876: select
885: IV.bill_to_site_use_id,
886: IV.authoring_org_id,
887: IBY_FNDCPT_TX_EXTENSIONS_S.nextval, -- the new transaction extension ID
888: IBY_INSTR_S.nextval, -- the new credit card id
889: DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
890: PAYER.EXT_PAYER_ID),
891: DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
892: -- should create new external payer
893: IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
908: from okc_k_lines_b line, okc_k_headers_all_b hdr, hz_cust_accounts_all hz
909: where line.dnz_chr_id = hdr.id
910: and line.lse_id in (1,12,19,46)
911: and hz.cust_account_id = line.cust_acct_id) IV,
912: IBY_EXTERNAL_PAYERS_ALL payer
913: where IV.line_id = oksline.cle_id
914: and oksline.cc_no is not null
915: and oksline.payment_type = 'CCR'
916: and oksline.cc_bank_acct_id is null
1060: l_user_id,
1061: sysdate,
1062: l_user_id,
1063: 1
1064: FROM IBY_EXTERNAL_PAYERS_ALL payer
1065: WHERE payer.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
1066: AND payer.PARTY_ID = c_customer_id(i)
1067: AND payer.ORG_TYPE = 'OPERATING_UNIT'
1068: AND payer.ORG_ID = c_org_id(i)
1201:
1202:
1203: -- Cursor that queries all transactions needed to be migrated with encryption enabled
1204:
1205: -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
1206: -- the party contexts that are not in the external payer table can be identified.
1207: -- 2. The credit card number needs to be numeric.
1208:
1209: cursor okshdr_cur_sec is
1219: IV.bill_to_site_use_id,
1220: IV.authoring_org_id,
1221: iby_fndcpt_tx_extensions_s.nextval, -- the new transaction extension ID
1222: iby_instr_s.nextval, -- the new credit card id
1223: DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
1224: PAYER.EXT_PAYER_ID),
1225: DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
1226: -- should create new external payer
1227: IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
1240: where hdr.bill_to_site_use_id = site.site_use_id
1241: and site.cust_acct_site_id = acct.cust_acct_site_id
1242: and acct.cust_account_id = hz.cust_account_id
1243: and site.site_use_code = 'BILL_TO' ) IV,
1244: IBY_EXTERNAL_PAYERS_ALL payer,
1245: IBY_SECURITY_SEGMENTS sec,
1246: IBY_CC_ISSUER_RANGES rangE
1247: where IV.hdr_id = okshdr.chr_id
1248: and okshdr.cc_no is not null
1260: and okshdr.id BETWEEN p_id_low AND p_id_high;
1261:
1262: -- Cursor that queries all transactions needed to be migrated with encryption disable
1263:
1264: -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
1265: -- the party contexts that are not in the external payer table can be identified.
1266: -- 2. The credit card number needs to be numeric.
1267: cursor okshdr_cur is
1268: select
1277: IV.bill_to_site_use_id,
1278: IV.authoring_org_id,
1279: IBY_FNDCPT_TX_EXTENSIONS_S.nextval, -- the new transaction extension ID
1280: IBY_INSTR_S.nextval, -- the new credit card id
1281: DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
1282: PAYER.EXT_PAYER_ID),
1283: DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
1284: -- should create new external payer
1285: IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
1302: where hdr.bill_to_site_use_id = site.site_use_id
1303: and site.cust_acct_site_id = acct.cust_acct_site_id
1304: and acct.cust_account_id = hz.cust_account_id
1305: and site.site_use_code = 'BILL_TO') IV,
1306: IBY_EXTERNAL_PAYERS_ALL payer
1307: where IV.hdr_id = okshdr.chr_id
1308: and okshdr.cc_no is not null
1309: ---and okshdr.id = 317191029854960778512632995409857241499
1310: and okshdr.payment_type = 'CCR'
1455: l_user_id,
1456: sysdate,
1457: l_user_id,
1458: 1
1459: FROM IBY_EXTERNAL_PAYERS_ALL payer
1460: WHERE payer.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
1461: AND payer.PARTY_ID = c_customer_id(i)
1462: AND payer.ORG_TYPE = 'OPERATING_UNIT'
1463: AND payer.ORG_ID = c_org_id(i)
1596:
1597:
1598: -- Cursor that queries all transactions needed to be migrated with encryption enabled
1599:
1600: -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
1601: -- the party contexts that are not in the external payer table can be identified.
1602: -- 2. The credit card number needs to be numeric.
1603:
1604: cursor okshdr_cur_sec is
1613: IV.bill_to_site_use_id,
1614: IV.authoring_org_id,
1615: iby_fndcpt_tx_extensions_s.nextval, -- the new transaction extension ID
1616: iby_instr_s.nextval, -- the new credit card id
1617: DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
1618: PAYER.EXT_PAYER_ID),
1619: DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
1620: -- should create new external payer
1621: IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
1634: where hdr.bill_to_site_use_id = site.site_use_id
1635: and site.cust_acct_site_id = acct.cust_acct_site_id
1636: and acct.cust_account_id = hz.cust_account_id
1637: and site.site_use_code = 'BILL_TO') IV,
1638: IBY_EXTERNAL_PAYERS_ALL payer,
1639: IBY_SECURITY_SEGMENTS sec,
1640: IBY_CC_ISSUER_RANGES rangE
1641: where IV.hdr_id = okshdr.chr_id
1642: and okshdr.cc_no is not null
1654: and okshdr.id BETWEEN p_id_low AND p_id_high;
1655:
1656: -- Cursor that queries all transactions needed to be migrated with encryption disable
1657:
1658: -- Note: 1. This table is out-joined with the table IBY_EXTERNAL_PAYERS_ALL so that
1659: -- the party contexts that are not in the external payer table can be identified.
1660: -- 2. The credit card number needs to be numeric.
1661: cursor okshdr_cur is
1662: select
1670: IV.bill_to_site_use_id,
1671: IV.authoring_org_id,
1672: IBY_FNDCPT_TX_EXTENSIONS_S.nextval, -- the new transaction extension ID
1673: IBY_INSTR_S.nextval, -- the new credit card id
1674: DECODE(PAYER.EXT_PAYER_ID, null, IBY_EXTERNAL_PAYERS_ALL_S.nextval,
1675: PAYER.EXT_PAYER_ID),
1676: DECODE(PAYER.EXT_PAYER_ID, null,'Y', 'N'), -- this flag determines whether we
1677: -- should create new external payer
1678: IBY_PMT_INSTR_USES_ALL_S.nextval, -- the new instrument use id
1695: where hdr.bill_to_site_use_id = site.site_use_id
1696: and site.cust_acct_site_id = acct.cust_acct_site_id
1697: and acct.cust_account_id = hz.cust_account_id
1698: and site.site_use_code = 'BILL_TO') IV,
1699: IBY_EXTERNAL_PAYERS_ALL payer
1700: where IV.hdr_id = okshdr.chr_id
1701: and okshdr.cc_no is not null
1702: ---and okshdr.id = 317191029854960778512632995409857241499
1703: and okshdr.payment_type = 'CCR'
1847: l_user_id,
1848: sysdate,
1849: l_user_id,
1850: 1
1851: FROM IBY_EXTERNAL_PAYERS_ALL payer
1852: WHERE payer.PAYMENT_FUNCTION = 'CUSTOMER_PAYMENT'
1853: AND payer.PARTY_ID = c_customer_id(i)
1854: AND payer.ORG_TYPE = 'OPERATING_UNIT'
1855: AND payer.ORG_ID = c_org_id(i)