CA_BI_INTFC ABENDED at Step CA_BI_INTFC.ALLNotes.LoadNote (Action SQL) -- RC = 805 (Doc ID 2270378.1)

Last updated on MAY 31, 2017

Applies to:

PeopleSoft Enterprise FIN Contracts - Version 9.2 to 9.2 [Release 9]
Information in this document applies to any platform.

Symptoms

On : 9.2 version, Limit Processing

CA_BI_INTFC failed on Unique Constraint

ABENDED at Step CA_BI_INTFC.ALLNotes.LoadNote (Action SQL) -- RC = 805
ROLLBACK
/
-- 12:43:34.299 SQL Error: ORA-00001: unique constraint (SYSADM.PS_INTFC_BI) violated

ERROR
-----------------------

-- 18:40:55.905 .........(CA_BI_INTFC.ALLNotes.LoadNote) (SQL)
INSERT INTO PS_INTFC_BI (INTFC_ID, INTFC_LINE_NUM, TRANS_TYPE_BI,
TRANS_TYPE_BI_SEQ, HDR_FIELDS_KEY, HDR_FIELDS_BILL_BY, ADJ_TRANS_TYPE,
CREATE_NEW_BILL, TMP_BILL_FLG, ENTRY_TYPE, ENTRY_REASON, ENTRY_EVENT,
LOAD_STATUS_BI, ERROR_STATUS_BI, BUSINESS_UNIT, BUSINESS_UNIT_GL,
BILL_TO_CUST_ID, ADDRESS_SEQ_NUM, BILL_TO_COPIES, CNTCT_SEQ_NUM, NAME1,
INTERUNIT_FLG, BUSINESS_UNIT_TO, DIRECT_INVOICING, RANGE_SELECTION_ID,
BILL_SOURCE_ID, BILL_TYPE_ID, BILL_CYCLE_ID, BILL_BY_ID, PAYMENT_METHOD,
PYMNT_TERMS_CD, BANK_CD, BANK_ACCT_KEY, BI_CURRENCY_CD, BASE_CURRENCY,
CUR_RT_TYPE, RATE_MULT, RATE_DIV, CUR_RT_SOURCE, INVOICE_DT, ACCOUNTING_DT,
ACCRUE_UNBILLED, TARGET_INVOICE, INVOICE, DOC_TYPE, CONSOL_SETID,
CONSOL_CUST_ID, CONSOL_KEY, INVOICE_TO_ADJ, ADJ_DELTA_ACTION,
LINE_SEQ_TO_ADJ, LINE_SEQ_NUM, LINE_DST_SEQ_NUM, LINE_DFR_SEQ_NUM,
LINE_UAR_SEQ_NUM, LAST_NOTE_SEQ_NUM, NOTES_SEQ_NUM, LINE_TYPE, IDENTIFIER,
DESCR, UNIT_OF_MEASURE, QTY, ORIG_QTY, UNIT_AMT, LIST_PRICE, PPRC_PROMO_CD,
MERCH_TYPE, TAX_CD, TAX_EXEMPT_CERT, TAX_EXEMPT_FLG, TAX_EXEMPT_RC,
TAX_JOB_NUM, BI_TAX_TIMING, CUSTOMER_GROUP, VAT_TXN_TYPE_CD, TAX_CD_VAT,
VAT_APPLICABILITY, VAT_PRODUCT_GROUP, PROD_GRP_SETID, IST_TXN_FLG,
IDENTIFIER_TBL, SHIP_FROM_LOC, ORD_ACCEPT_LOC, ORD_ORIGIN_LOC, STORE_LOC,
TITLE_PASSAGE, TAX_GROUP, TAX_USER_AREA, TAX_TRANS_TYPE, TAX_TRANS_SUB_TYPE,
NET_EXTENDED_AMT, GROSS_EXTENDED_AMT, REV_RECOG_BASIS, PROJECT_ID,
BUSINESS_UNIT_OM, ORDER_NO, ORDER_INT_LINE_NO, SCHED_LINE_NBR, DEMAND_SOURCE,
DEMAND_LINE_NO, BUSINESS_UNIT_RMA, RMA_ID, RMA_LINE_NBR, PRODUCT_ID,
ORDER_DATE, PO_REF, PO_LINE, CONTRACT_NUM, CONTRACT_DT, CONTRACT_TYPE,
CONTRACT_LINE_NUM, FREIGHT_TERMS, BILL_OF_LADING, COUNTRY_SHIP_FROM,
COUNTRY_SHIP_TO, SHIP_TO_CUST_ID, SHIP_TO_ADDR_NUM, SHIP_ID, SHIP_TYPE_ID,
SHIP_FROM_BU, SHIP_DATE, SHIP_TIME, PACKSLIP_NO, LC_ID, LOC_DOC_ID,
SEQUENCE_NBR, SOLD_TO_CUST_ID, SOLD_TO_ADDR_NUM, BUSINESS_UNIT_PC,
BUSINESS_UNIT_CA, RT_EFFDT, BILL_PLAN_ID, PC_DISTRIB_STATUS, BPLAN_LN_NBR,
EVENT_OCCURRENCE, XREF_SEQ_NUM, CONTRACT_PPD_SEQ, ANALYSIS_TYPE, RESOURCE_ID,
RESOURCE_TYPE, RESOURCE_CATEGORY, RESOURCE_SUB_CAT, ACTIVITY_ID,
ACTIVITY_TYPE, DIST_CFG_FLAG, PRODUCT_KIT_ID, SYSTEM_SOURCE, EMPLID,
EMPL_RCD, START_DT, END_DT, FROM_DT, TO_DT, SERVICE_CUST_ID,
SERVICE_ADDR_NUM, NOTE_TYPE, STD_NOTE_FLAG, INTERNAL_FLAG, HDR_OR_LINE_NOTE,
AR_LVL, AR_DST_OPT, GL_LVL, ENABLE_DFR_REV_FLG, BILLING_SPECIALIST,
BILLING_AUTHORITY, BILLING_FREQUENCY, BILL_INQUIRY_PHONE, SALES_PERSON,
COLLECTOR, CR_ANALYST, INVOICE_FORM_ID, STD_NOTE_CD, TOT_LINE_DST_AMT,
TOT_LINE_DST_PCT, TOT_LINE_DFR_PCT, TOT_LINE_DFR_AMT, TOT_LINE_UAR_AMT,
TOT_LINE_UAR_PCT, SSN, CHARGE_FROM_DT, CHARGE_TO_DT, SUBCUST_QUAL1,
SUBCUST_QUAL2, REIMB_AGREEMENT, TOT_DISCOUNT_AMT, TOT_SURCHARGE_AMT,
ACCUMULATE, VAT_TREATMENT_GRP, COUNTRY_VAT_BILLFR, COUNTRY_VAT_BILLTO,
VAT_TREATMENT, PHYSICAL_NATURE, COUNTRY_LOC_BUYER, STATE_LOC_BUYER,
COUNTRY_LOC_SELLER, STATE_LOC_SELLER, VAT_SVC_SUPPLY_FLG, VAT_SERVICE_TYPE,
COUNTRY_VAT_PERFRM, STATE_VAT_PERFRM, COUNTRY_VAT_SUPPLY, STATE_VAT_SUPPLY,
STATE_SHIP_FROM, STATE_SHIP_TO, MAST_CONTR_ID, TAX_CUST_ID, BUSINESS_UNIT_AM,
BUSINESS_UNIT_AMTO, ASSET_ID, PROFILE_ID, COST_TYPE, STATE_VAT_DEFAULT,
SO_ID, BUSINESS_UNIT_RF, SOURCE_REF_TYPE, SOURCE_REF_NO, SOURCE_REF_KEY,
ACCEPTGIRO_IND, CA_PGP_SEQ, SUM_TEMPLATE_ID, SUM_GROUP_TYPE, SUM_GROUP_ID,
CUST_DEPOSIT_ID, TAX_DATE, GROUP_CODE, SRV_PERFORM_LOC, TAX_DIVISION,
TAX_DEPARTMENT, PT_SALE_FLG, CUST_PICKUP, CUSTOMER_PO_LINE,
CUSTOMER_PO_SCHED, PUBLIC_VOUCHER_NBR, PVN_GEN_LVL, USER_AMT1, USER_AMT2,
USER_DT1, USER_DT2, USER1, USER2, USER3, USER4, USER5, USER6, USER7, USER8,
USER9, SETID, PROCESS_INSTANCE, ADD_DTTM, LAST_UPDATE_DTTM,
VAT_RVRSE_CHG_GDS, TAX_CD_VAT_RVC, BI_AP_LVL) SELECT 168, TA1.INTFC_LINE_NUM,
'NOTE', TA1.TRANS_TYPE_BI_SEQ, DRV.HDR_FIELDS_KEY, DRV.HDR_FIELDS_BILL_BY, '
', DRV.CREATE_NEW_BILL, DRV.TMP_BILL_FLG, DRV.ENTRY_TYPE, DRV.ENTRY_REASON,
DRV.ENTRY_EVENT, DRV.LOAD_STATUS_BI, DRV.ERROR_STATUS_BI, DRV.BUSINESS_UNIT,
DRV.BUSINESS_UNIT_GL, DRV.BILL_TO_CUST_ID, DRV.ADDRESS_SEQ_NUM,
DRV.BILL_TO_COPIES, DRV.CNTCT_SEQ_NUM, DRV.NAME1, DRV.INTERUNIT_FLG,
DRV.BUSINESS_UNIT_TO, DRV.DIRECT_INVOICING, DRV.RANGE_SELECTION_ID,
DRV.BILL_SOURCE_ID, DRV.BILL_TYPE_ID, DRV.BILL_CYCLE_ID, DRV.BILL_BY_ID,
DRV.PAYMENT_METHOD, DRV.PYMNT_TERMS_CD, DRV.BANK_CD, DRV.BANK_ACCT_KEY,
DRV.BI_CURRENCY_CD, DRV.BASE_CURRENCY, DRV.CUR_RT_TYPE, DRV.RATE_MULT,
DRV.RATE_DIV, DRV.CUR_RT_SOURCE, DRV.INVOICE_DT, DRV.ACCOUNTING_DT,
DRV.ACCRUE_UNBILLED, DRV.TARGET_INVOICE, DRV.INVOICE, DRV.DOC_TYPE,
DRV.CONSOL_SETID, DRV.CONSOL_CUST_ID, DRV.CONSOL_KEY, DRV.INVOICE_TO_ADJ,
DRV.ADJ_DELTA_ACTION, DRV.LINE_SEQ_TO_ADJ, DRV.LINE_SEQ_NUM,
DRV.LINE_DST_SEQ_NUM, DRV.LINE_DFR_SEQ_NUM, DRV.LINE_UAR_SEQ_NUM,
DRV.LAST_NOTE_SEQ_NUM, TA1.TRANS_TYPE_BI_SEQ, DRV.LINE_TYPE, DRV.IDENTIFIER,
DRV.DESCR, DRV.UNIT_OF_MEASURE, DRV.QTY, DRV.ORIG_QTY, DRV.UNIT_AMT,
DRV.LIST_PRICE, DRV.PPRC_PROMO_CD, DRV.MERCH_TYPE, DRV.TAX_CD,
DRV.TAX_EXEMPT_CERT, DRV.TAX_EXEMPT_FLG, DRV.TAX_EXEMPT_RC, DRV.TAX_JOB_NUM,
DRV.BI_TAX_TIMING, DRV.CUSTOMER_GROUP, DRV.VAT_TXN_TYPE_CD, DRV.TAX_CD_VAT,
DRV.VAT_APPLICABILITY, DRV.VAT_PRODUCT_GROUP, DRV.PROD_GRP_SETID,
DRV.IST_TXN_FLG, DRV.IDENTIFIER_TBL, DRV.SHIP_FROM_LOC, DRV.ORD_ACCEPT_LOC,
DRV.ORD_ORIGIN_LOC, DRV.STORE_LOC, DRV.TITLE_PASSAGE, DRV.TAX_GROUP,
DRV.TAX_USER_AREA, DRV.TAX_TRANS_TYPE, DRV.TAX_TRANS_SUB_TYPE,
DRV.NET_EXTENDED_AMT, DRV.GROSS_EXTENDED_AMT, DRV.REV_RECOG_BASIS,
DRV.PROJECT_ID, DRV.BUSINESS_UNIT_OM, DRV.ORDER_NO, DRV.ORDER_INT_LINE_NO,
DRV.SCHED_LINE_NBR, DRV.DEMAND_SOURCE, DRV.DEMAND_LINE_NO,
DRV.BUSINESS_UNIT_RMA, DRV.RMA_ID, DRV.RMA_LINE_NBR, DRV.PRODUCT_ID,
DRV.ORDER_DATE, DRV.PO_REF, DRV.PO_LINE, TA1.CONTRACT_NUM, DRV.CONTRACT_DT,
DRV.CONTRACT_TYPE, DRV.CONTRACT_LINE_NUM, DRV.FREIGHT_TERMS,
DRV.BILL_OF_LADING, DRV.COUNTRY_SHIP_FROM, DRV.COUNTRY_SHIP_TO,
DRV.SHIP_TO_CUST_ID, DRV.SHIP_TO_ADDR_NUM, DRV.SHIP_ID, DRV.SHIP_TYPE_ID,
DRV.SHIP_FROM_BU, DRV.SHIP_DATE, DRV.SHIP_TIME, DRV.PACKSLIP_NO, DRV.LC_ID,
DRV.LOC_DOC_ID, DRV.SEQUENCE_NBR, DRV.SOLD_TO_CUST_ID, DRV.SOLD_TO_ADDR_NUM,
DRV.BUSINESS_UNIT_PC, DRV.BUSINESS_UNIT_CA, DRV.RT_EFFDT, TA1.BILL_PLAN_ID,
DRV.PC_DISTRIB_STATUS, TA1.BPLAN_LN_NBR, TA1.EVENT_OCCURRENCE,
DRV.XREF_SEQ_NUM, DRV.CONTRACT_PPD_SEQ, DRV.ANALYSIS_TYPE, DRV.RESOURCE_ID,
DRV.RESOURCE_TYPE, DRV.RESOURCE_CATEGORY, DRV.RESOURCE_SUB_CAT,
DRV.ACTIVITY_ID, DRV.ACTIVITY_TYPE, DRV.DIST_CFG_FLAG, DRV.PRODUCT_KIT_ID,
DRV.SYSTEM_SOURCE, DRV.EMPLID, DRV.EMPL_RCD, DRV.START_DT, DRV.END_DT,
DRV.FROM_DT, DRV.TO_DT, DRV.SERVICE_CUST_ID, DRV.SERVICE_ADDR_NUM,
TA1.NOTE_TYPE, 'N', 'N', TA1.HDR_OR_LINE_NOTE, DRV.AR_LVL, DRV.AR_DST_OPT,
DRV.GL_LVL, DRV.ENABLE_DFR_REV_FLG, DRV.BILLING_SPECIALIST,
DRV.BILLING_AUTHORITY, DRV.BILLING_FREQUENCY, DRV.BILL_INQUIRY_PHONE,
DRV.SALES_PERSON, DRV.COLLECTOR, DRV.CR_ANALYST, DRV.INVOICE_FORM_ID, ' ',
DRV.TOT_LINE_DST_AMT, DRV.TOT_LINE_DST_PCT, DRV.TOT_LINE_DFR_PCT,
DRV.TOT_LINE_DFR_AMT, DRV.TOT_LINE_UAR_AMT, DRV.TOT_LINE_UAR_PCT, DRV.SSN,
DRV.CHARGE_FROM_DT, DRV.CHARGE_TO_DT, DRV.SUBCUST_QUAL1, DRV.SUBCUST_QUAL2,
DRV.REIMB_AGREEMENT, DRV.TOT_DISCOUNT_AMT, DRV.TOT_SURCHARGE_AMT,
DRV.ACCUMULATE, DRV.VAT_TREATMENT_GRP, DRV.COUNTRY_VAT_BILLFR,
DRV.COUNTRY_VAT_BILLTO, DRV.VAT_TREATMENT, DRV.PHYSICAL_NATURE,
DRV.COUNTRY_LOC_BUYER, DRV.STATE_LOC_BUYER, DRV.COUNTRY_LOC_SELLER,
DRV.STATE_LOC_SELLER, DRV.VAT_SVC_SUPPLY_FLG, DRV.VAT_SERVICE_TYPE,
DRV.COUNTRY_VAT_PERFRM, DRV.STATE_VAT_PERFRM, DRV.COUNTRY_VAT_SUPPLY,
DRV.STATE_VAT_SUPPLY, DRV.STATE_SHIP_FROM, DRV.STATE_SHIP_TO,
DRV.MAST_CONTR_ID, DRV.TAX_CUST_ID, DRV.BUSINESS_UNIT_AM,
DRV.BUSINESS_UNIT_AMTO, DRV.ASSET_ID, DRV.PROFILE_ID, DRV.COST_TYPE,
DRV.STATE_VAT_DEFAULT, DRV.SO_ID, DRV.BUSINESS_UNIT_RF, DRV.SOURCE_REF_TYPE,
DRV.SOURCE_REF_NO, DRV.SOURCE_REF_KEY, ' ', DRV.CA_PGP_SEQ,
DRV.SUM_TEMPLATE_ID, DRV.SUM_GROUP_TYPE, DRV.SUM_GROUP_ID, ' ', NULL, 0, ' ',
' ', ' ', ' ', ' ', ' ', ' ', 0, ' ', DRV.USER_AMT1, DRV.USER_AMT2,
DRV.USER_DT1, DRV.USER_DT2, DRV.USER1, DRV.USER2, DRV.USER3, DRV.USER4,
DRV.USER5, DRV.USER6, DRV.USER7, DRV.USER8, DRV.USER9, DRV.SETID,
TA1.PROCESS_INSTANCE, DRV.ADD_DTTM, DRV.LAST_UPDATE_DTTM, ' ', ' ', ' ' FROM
PS_CA_BINOTE_TA15 TA1, PS_CA_BI_DRIVER5 DRV WHERE TA1.PROCESS_INSTANCE =
DRV.PROCESS_INSTANCE AND TA1.CONTRACT_NUM = DRV.CONTRACT_NUM AND
TA1.BILL_PLAN_ID = DRV.BILL_PLAN_ID AND TA1.BPLAN_LN_NBR = DRV.BPLAN_LN_NBR
AND TA1.EVENT_OCCURRENCE = DRV.EVENT_OCCURRENCE AND TA1.INTFC_LINE_NUM =
DRV.INTFC_LINE_NUM AND TA1.PROCESS_INSTANCE = 119365
/
-- Row(s) affected: 0
-- 18:40:56.140 Process 119365 ABENDED at Step CA_BI_INTFC.ALLNotes.LoadNote (Action SQL) -- RC = 805
ROLLBACK
/
-- 18:40:56.173 SQL Error: ORA-00001: unique constraint (EMDBO.PS_INTFC_BI) violated


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Create a government contract with rate based contract line.
2. Associate Rates and Project/Activity. Set limit of $5000
3. Assign Bill Plan. Add a Bill Plan Header Note.
4. Create transactions in PROJ_RESOURCE for this Project/Activity exceeding $5000. You will see OLT rows in PROJ_RESOURCE.
5. Notice that CA_PR_SUMM also gets a OLT summarized row.
6. Run CA_BI_INTFC and notice the above error

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms