Oracle eBusiness Suite Technical Reference

Release 11i - Copyright (c) 2001 - 2005 Oracle

This Technical Reference Manual (TRM) in any form, software or printed matter, contains proprietary information of Oracle ; it is provided under an Oracle agreement containing restrictions on use and disclosure and is also protected by copyright, patent, and other intellectual property law. Restrictions applicable to this TRM include, but are not limited to: (a) exercising either the same degree of care to safeguard the confidentiality of this TRM as you exercise to safeguard the confidentiality of your own most important Confidential Information or a reasonable degree of care, whichever is greater; (b) maintaining agreements with your employees and agents that protect the Confidential Information of third parties such as Oracle and instructing such employees and agents of these requirements for this TRM; (c) restricting disclosure of this TRM to those of your employees who have a 'need to know' consistent with the purposes for which this TRM was disclosed to you; (d) maintaining this TRM at all times on your premises; (e) not removing or destroying any proprietary or confidential legends or markings placed upon this TRM in whatever form, software or printed matter; and (f) not reproducing or transmitting this TRM in any form or by any means, electronic or mechanical, for any purpose, without the express written permission of Oracle. You should not use this TRM in any form, software or printed matter, to create software that performs the same or similar functions as any Oracle products.

The information in this TRM is subject to change without notice. If you find any problems in the TRM in any form, software or printed matter, please report them to us in writing. Oracle does not warrant that this TRM is error free. This TRM is provided to customer 'as is' with no warranty of any kind. This TRM does not constitute Documentation as that term is defined in Oracle's agreements. Reverse engineering of the Programs (software and documentation) associated with this TRM are prohibited. The Programs associated with this TRM are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently dangerous applications. It shall be licensee's responsibility to take all appropriate fail safe, backup, redundancy and other measures to ensure the safe use of such applications if the Programs are used for such purposes, and Oracle disclaims liability for any damages caused by such use of the Programs. Program Documentation is licensed for use solely to support the deployment of the Programs and not for any other any other purpose. Restricted Rights Legend

This TRM and the Programs associated with this TRM delivered subject to the DOD FAR Supplement are 'commercial computer software' and use, duplication and disclosure of the TRM and the Programs associated with this TRM shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise, this TRM and the Programs associated with this TRM delivered subject to the Federal Acquisition Regulations are 'restricted computer software' and use, duplication and disclosure of the TRM and the Programs associated with this TRM shall be subject to the restrictions in FAR 52.227-14, Rights in Data General, including Alternate III (June 1987). Oracle, 500 Oracle Parkway, Redwood City, CA 94065.

Oracle is a registered trademark. AIM Advantage™, Big Business. Small Business. All Business.™, CDM Advantage™, .comference™, ConText®, Des40®, Easy*Query™, Easy*SQL®, Education Objects™, EMM Advantage™, Enabling the Information Age®, ExpertDetectSM, Express®, FastForwardSM, GEMMS®, Gist™, Hyper*SQL™, instantOracle™, "Internet Business Intelligence - Insight for Everyone"™, iSQL*Plus™, JInitiator™, NLS*Workbench®, Object Marketplace™, ODP Pulse™, ODP Techwire™, OLA™, On Oracle™, Oracle®, Oracle Book®, Oracle Bookbatch®, Oracle BookBuilder®, OracleBRONZE®, Oracle Business OnLine®, OracleDevConnectSM, Oracle® Discoverer™, Oracle® Expert™, Oracle Financials®, OracleGOLDSM, OracleIncidentSM, OracleJSP™, OracleLifeCycleSM, Oracle Media Objects®, OracleMERCURYSM, OracleMetaLinkSM, OraclemetalsSM, OracleMobile™, Oracle Names®, Oracle NetSolutions®, Oracle® One™, Oracle Open Gateways®, Oracle OpenWorld®, OraclePLATINUMSM, Oracle Power Objects®, Oracle Press®, Oracle Procedural Gateway®, Oracle Quality®, Oracle Secure Network Services®, OracleSILVERSM, Oracle® SQL/Services™, Oracle® SQL*TutorSM, OracleSTERLINGSM, Oracle Store®, Oracle Transparent Gateway®, OracleWare®, Oracle8i™, Oracle9i™, PC Express®, Personal Express®, PJM Advantage™, PL/SQL™, Pro*Ada®, Pro*C™, Pro*C/C++™, Pro*COBOL®, Pro*FORTRAN®, Pro*Pascal®, Pro*PL/1®, Pro*REXX®, Profit™, ProREXX™, Report Card™, Secure Access 2.0™, Secure Network Services®, Security Without Compromise™, SellingPoint®, SQL*Plus®, Tutor™ are trademarks or registered trademarks of Oracle. Other names may be trademarks of their respective owners. The absence of a product or service name, slogan, or logo from this list does not constitute a waiver of Oracle's trademark or other intellectual property rights concerning that name, slogan, or logo.

eTRM - BIC Tables and Views

TABLE - BIC.BIC_CUSTOMER_SUMMARY_ALL
TABLE - BIC.BIC_DEBUG
TABLE - BIC.BIC_LC_SETUP_ALL
TABLE - BIC.BIC_MEASURES_ALL
TABLE - BIC.BIC_MEASURE_ATTRIBS
TABLE - BIC.BIC_MEASURE_BUCKETS
TABLE - BIC.BIC_MEASURE_HIERARCHY
TABLE - BIC.BIC_PARTY_STATUS_SUMM
TABLE - BIC.BIC_PARTY_SUMM
TABLE - BIC.BIC_PARTY_SUMMARY
TABLE - BIC.BIC_PERIODS
TABLE - BIC.BIC_PROFILE_VALUES_ALL
TABLE - BIC.BIC_RELATIONSHIP_TYPES
TABLE - BIC.BIC_STANDARD_VALUES
VIEW - APPS.BIC_DIMV_OPERATING_UNITS

Table: BIC_CUSTOMER_SUMMARY_ALL

Description: Customer Intelligence Summary Information - for Org striped measures only
Primary Key: BIC_CUSTOMER_SUMMARY_ALL_PK
  1. PERIOD_START_DATE
  2. CUSTOMER_ID
  3. MEASURE_ID
Comments

Customer Intelligence Summary information. This gets populated by the concurrent program 'Extract Customer Summary' - for Org striped measures only

Indexes
Index Type Tablespace Column
BIC_CUSTOMER_SUMMARY_U1 UNIQUE APPS_TS_SUMMARY PERIOD_START_DATE
CUSTOMER_ID
MEASURE_ID
BIC_CUSTOMER_SUMMARY_N1 NONUNIQUE APPS_TS_SUMMARY MEASURE_CODE
Columns
Name Datatype Length Mandatory Comments
MEASURE_ID NUMBER
Y Id for measures like Satisfaction, Loyalty etc.,
PERIOD_START_DATE DATE
Y Period Start Date
CUSTOMER_ID NUMBER
Y FK to Hz_Parties
BUCKET_ID NUMBER

Bucket Id
VALUE NUMBER

Value for that bucket
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATION_DATE DATE
Y Standard who column - date when this row was created.
CREATED_BY NUMBER
Y Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
ORG_ID NUMBER

Organization identifier
LAST_UPDATE_LOGIN NUMBER

Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).
REQUEST_ID NUMBER

Concurrent Program who column - concurrent request id of the program that last updated this row (foreign key to FND_CONCURRENT_REQUESTS.REQUEST_ID).
PROGRAM_APPLICATION_ID NUMBER

Concurrent Program who column - application id of the program that last updated this row (foreign key to FND_APPLICATION.APPLICATION_ID).
PROGRAM_ID NUMBER

Concurrent Program who column - program id of the program that last updated this row (foreign key to FND_CONCURRENT_PROGRAM.CONCURRENT_PROGRAM_ID).
PROGRAM_UPDATE_DATE DATE

Concurrent Program who column - date when a program last updated this row).
SCORE NUMBER

Score defined
SECURITY_GROUP_ID NUMBER (15)
Security Group Id
MEASURE_CODE VARCHAR2 (30)
Code name for measures like Satisfaction, Loyalty etc.,

[top of page]

BIC.BIC_CUSTOMER_SUMMARY_ALL does not reference any database object

BIC.BIC_CUSTOMER_SUMMARY_ALL is referenced by following:

BIC
VIEW - BIC_CUSTOMER_SUMMARY_ALL#

[top of page]

Table: BIC_DEBUG

Description: This table stores the debug messages.
Comments
***** Warning: Oracle Internal Use Only *****

Oracle Corporation does not support access to Oracle applications data using this object, except from standard Oracle Applications programs.

Indexes
Index Type Tablespace Column
SYS_IL0000081831C00004$$ UNIQUE APPS_TS_NOLOGGING
Columns
Name Datatype Length Mandatory Comments
CREATION_DATE DATE

Standard who column - date when this row was created.
REPORT_ID VARCHAR2 (30)
Report Identifier
SECURITY_GROUP_ID NUMBER (15)
MESSAGE CLOB (4000)
Message text

[top of page]

BIC.BIC_DEBUG does not reference any database object

BIC.BIC_DEBUG is referenced by following:

BIC
VIEW - BIC_DEBUG#

[top of page]

Table: BIC_LC_SETUP_ALL

Description: Lifecycle related Setup information
Comments

Lifecycle related setup information

Indexes
Index Type Tablespace Column
BIC_LC_SETUP_U1 UNIQUE APPS_TS_SEED STAGE_ID
ZD_EDITION_NAME
Columns
Name Datatype Length Mandatory Comments
STAGE_ID NUMBER
Y Stage Id
ORG_ID NUMBER

Organization identifier
STAGE_CODE VARCHAR2 (15) Y Stage Code
STAGE_NAME VARCHAR2 (100)
Stage Name
MEASURE_VALUE1 NUMBER

Measure Value 1
MEASURE_VALUE2 NUMBER

Measure Value 2
MEASURE_VALUE1_OP VARCHAR2 (2)
Measure Value 1 Operator
MEASURE_VALUE2_OP VARCHAR2 (2)
Measure Value 2 Operator
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATION_DATE DATE
Y Standard who column - date when this row was created.
CREATED_BY NUMBER
Y Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
LAST_UPDATE_LOGIN NUMBER

Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).
SECURITY_GROUP_ID NUMBER

Security Group Id
ZD_EDITION_NAME VARCHAR2 (30) Y

[top of page]

BIC.BIC_LC_SETUP_ALL does not reference any database object

BIC.BIC_LC_SETUP_ALL is referenced by following:

BIC
VIEW - BIC_LC_SETUP_ALL#

[top of page]

Table: BIC_MEASURES_ALL

Description: The list of measures will be seeded
Primary Key: BIC_MEASURES_ALL_PK
  1. MEASURE_ID
Foreign Keys from this table:
Foreign Key Column Foreign Table
BIC_MEASURES_ALL.MEASURE_CODE BIC_MEASURE_ATTRIBS
BIC_MEASURES_ALL.MEASURE_CODE BIC_MEASURE_ATTRIBS
BIC_MEASURES_ALL.MEASURE_CODE BIC_MEASURE_ATTRIBS
Foreign Keys to this table:
Foreign Key Column Foreign Table
BIC_MEASURE_BUCKETS.MEASURE_ID BIC_MEASURES_ALL
Comments

The list of measures will be seeded. The user cannot add to or delete from this list. The measures will be seeded for each operating unit

Indexes
Index Type Tablespace Column
BIC_MEASURES_U1 UNIQUE APPS_TS_SEED MEASURE_ID
ZD_EDITION_NAME
BIC_MEASURES_U2 UNIQUE APPS_TS_SEED MEASURE_CODE
ORG_ID
ZD_EDITION_NAME
Columns
Name Datatype Length Mandatory Comments
MEASURE_ID NUMBER
Y Measure Identifier
MEASURE_CODE VARCHAR2 (30) Y Internal code
NAME VARCHAR2 (60) Y Measure name
ORG_ID NUMBER

Organization identifier
ENABLED_FLAG VARCHAR2 (1) Y If the user does not want to use this measure, the enable_flag can be set to 'N'
DESCRIPTION VARCHAR2 (240)
Description of Measure
WEIGHT NUMBER

The user can specify the weight for each measure
TIME_UNIT_CODE VARCHAR2 (30)
The time unit code indicates the period (month, quarter etc ) for which the value for this measure is stored in the customer summary table. This will be the same unit for the value range in the measure_buckets table. This is from cs_lookups for lookup_type='BIC_TIME_CODE'
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATION_DATE DATE
Y Standard who column - date when this row was created.
CREATED_BY NUMBER
Y Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
LAST_UPDATE_LOGIN NUMBER

Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).
SECURITY_GROUP_ID NUMBER (15)
Security Group Id
ZD_EDITION_NAME VARCHAR2 (30) Y

[top of page]

BIC.BIC_MEASURES_ALL does not reference any database object

BIC.BIC_MEASURES_ALL is referenced by following:

BIC
VIEW - BIC_MEASURES_ALL#

[top of page]

Table: BIC_MEASURE_ATTRIBS

Description: This table has the internal attributes for each measure.
Primary Key: BIC_MEASURE_ATTRIBS_PK
  1. MEASURE_CODE
Foreign Keys to this table:
Foreign Key Column Foreign Table
BIC_MEASURES_ALL.MEASURE_CODE BIC_MEASURE_ATTRIBS
BIC_MEASURES_ALL.MEASURE_CODE BIC_MEASURE_ATTRIBS
BIC_MEASURES_ALL.MEASURE_CODE BIC_MEASURE_ATTRIBS
BIC_MEASURE_HIERARCHY.MEASURE_CODE BIC_MEASURE_ATTRIBS
BIC_MEASURE_HIERARCHY.PARENT_MEASURE_CODE BIC_MEASURE_ATTRIBS
BIC_STANDARD_VALUES.MEASURE_CODE BIC_MEASURE_ATTRIBS
Comments

This table has the internal attributes for each measure. The data in this table is seeded and is not visible to the user. It is used by the concurrent program and reports

Indexes
Index Type Tablespace Column
BIC_MEASURE_ATTRIBS_U1 UNIQUE APPS_TS_SEED MEASURE_CODE
ZD_EDITION_NAME
Columns
Name Datatype Length Mandatory Comments
MEASURE_CODE VARCHAR2 (30) Y Internal code
LOV_FLAG VARCHAR2 (1) Y 'Y' indicates if the measure uses a lov, 'N' indicates that it uses a range
AGGREGATION_METHOD VARCHAR2 (15)
'AVG', 'SUM','COUNT'
SQL_STATEMENT VARCHAR2 (2000)
the select statement used to populate the customer summary table.*
OPERATION_TYPE VARCHAR2 (3)
Can be a) SQL if the measure has sql txt assigned to it to find value b) FML if the measure value will be calculated using formula involving one or more than one measurec) FMD if the measure value will be calculated using formula involving one or more than one measures and the value will be divided by no of daysd) SIX Sub Indexe) MIX Main Index
MULT_FACTOR NUMBER

value computed using formula will be multiplied by mult_factor
TIME_UNIT_CODE VARCHAR2 (30)
Internal code
SECURITY_GROUP_ID NUMBER (15)
Security Group Id
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATION_DATE DATE

Standard who column - date when this row was created.
CREATED_BY NUMBER

Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
LAST_UPDATE_LOGIN NUMBER

Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).
DISABLE_FLAG VARCHAR2 (1)
Disable Y or N
ZD_EDITION_NAME VARCHAR2 (30) Y

[top of page]

BIC.BIC_MEASURE_ATTRIBS does not reference any database object

BIC.BIC_MEASURE_ATTRIBS is referenced by following:

BIC
VIEW - BIC_MEASURE_ATTRIBS#

[top of page]

Table: BIC_MEASURE_BUCKETS

Description: This table will store groups for Loyalty, Satisfaction and their child measures
Primary Key: BIC_MEASURE_BUCKETS_PK
  1. BUCKET_ID
Foreign Keys from this table:
Foreign Key Column Foreign Table
BIC_MEASURE_BUCKETS.MEASURE_ID BIC_MEASURES_ALL
Foreign Keys to this table:
Foreign Key Column Foreign Table
BIC_BUCKET_VALUES.BUCKET_ID BIC_MEASURE_BUCKETS
BIC_BUCKET_VALUES.BUCKET_ID BIC_MEASURE_BUCKETS
BIC_BUCKET_VALUES.BUCKET_ID BIC_MEASURE_BUCKETS
BIC_BUCKET_VALUES.BUCKET_ID BIC_MEASURE_BUCKETS
Comments

This table will store groups for Loyalty, Satisfaction and their child measures. For each measure, 5 groups will be seeded in this table. The user can add or delete groups. The time unit for the group range (low_value & high_value) will be the time_unit_code for the parent measure (Satisfaction or Loyalty) specified in bic_measures

Indexes
Index Type Tablespace Column
BIC_MEASURE_BUCKETS_U1 UNIQUE APPS_TS_SEED BUCKET_ID
BIC_MEASURE_BUCKETS_N1 NONUNIQUE APPS_TS_SEED MEASURE_ID
Columns
Name Datatype Length Mandatory Comments
BUCKET_ID NUMBER
Y Bucket Identifier
MEASURE_ID NUMBER
Y Measure Identifier
BUCKET_NAME VARCHAR2 (60)
Bucket name
BUCKET_POINTS NUMBER
Y The points awarded if the customer falls in this bucket
LOW_VALUE NUMBER

The low value of the range specified for measures with lov_flag as 'N'. The time unit will be the same as for the parent measure (Satisfaction or Loyalty) specified in bic_measures
HIGH_VALUE NUMBER

The high value of the range specified for measures with lov_flag as 'N'. The time unit will be the same as for the parent measure (Satisfaction or Loyalty) specified in bic_measures
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATION_DATE DATE
Y Standard who column - date when this row was created.
CREATED_BY NUMBER
Y Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
LAST_UPDATE_LOGIN NUMBER

Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).
OPERATOR VARCHAR2 (30)
specified as 'AND' or 'OR' for measures which use a lov
SECURITY_GROUP_ID NUMBER (15)
Security Group Id

[top of page]

BIC.BIC_MEASURE_BUCKETS does not reference any database object

BIC.BIC_MEASURE_BUCKETS is referenced by following:

BIC
VIEW - BIC_MEASURE_BUCKETS#

[top of page]

Table: BIC_MEASURE_HIERARCHY

Description: Hierarchy of various measures, relating sub-measures to main measures
Primary Key: BIC_MEASURE_HIERARCHY_PK
  1. PARENT_MEASURE_CODE
  2. MEASURE_CODE
Foreign Keys from this table:
Foreign Key Column Foreign Table
BIC_MEASURE_HIERARCHY.MEASURE_CODE BIC_MEASURE_ATTRIBS
BIC_MEASURE_HIERARCHY.PARENT_MEASURE_CODE BIC_MEASURE_ATTRIBS
Comments

Hierarchy of various measures, relating sub-measures to main measures

Indexes
Index Type Tablespace Column
BIC_MEASURE_HIERARCHY_U1 UNIQUE APPS_TS_SEED MEASURE_CODE
PARENT_MEASURE_CODE
ZD_EDITION_NAME
Columns
Name Datatype Length Mandatory Comments
MEASURE_CODE VARCHAR2 (30) Y Measure Code
PARENT_MEASURE_CODE VARCHAR2 (30) Y Parent Measure Code
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATION_DATE DATE
Y Standard who column - date when this row was created.
CREATED_BY NUMBER
Y Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
LAST_UPDATE_LOGIN NUMBER

Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).
SEQUENCE_NUMBER NUMBER

Sequence Number
OPERATION_CODE VARCHAR2 (1)
Operation Code
SECURITY_GROUP_ID NUMBER (15)
Security Group Id
ZD_EDITION_NAME VARCHAR2 (30) Y

[top of page]

BIC.BIC_MEASURE_HIERARCHY does not reference any database object

BIC.BIC_MEASURE_HIERARCHY is referenced by following:

BIC
VIEW - BIC_MEASURE_HIERARCHY#

[top of page]

Table: BIC_PARTY_STATUS_SUMM

Description: From Party Summary table, the rows are transposed as columns for matrix reporting. i.e., Flattened table for non-org striped measures.
Foreign Keys from this table:
Foreign Key Column Foreign Table
BIC_PARTY_STATUS_SUMM.PARTY_ID HZ_PARTIES
Comments

From Party Summary table, the rows are transposed as columns for matrix reporting. i.e., Flattened table for non-org striped measures.

Indexes
Index Type Tablespace Column
BIC_PARTY_STATUS_SUMM_U1 NONUNIQUE APPS_TS_SUMMARY PERIOD_START_DATE
PARTY_ID
Columns
Name Datatype Length Mandatory Comments
PERIOD_START_DATE DATE
Y Period Start Date
PARTY_ID NUMBER
Y FK to Hz_Parties
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
CREATION_DATE DATE
Y Standard who column - date when this row was created.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATED_BY NUMBER
Y Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
ACQUISITION NUMBER

Acquisition
ACTIVATION NUMBER

Activation
RETENTION NUMBER

Retention
MARKET_SEGMENT_ID NUMBER

Id of Marget Segment
LIFE_CYCLE NUMBER

Life Cycle
LAST_UPDATE_LOGIN NUMBER

Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).
REQUEST_ID NUMBER

Concurrent Program who column - concurrent request id of the program that last updated this row (foreign key to FND_CONCURRENT_REQUESTS.REQUEST_ID).
PROGRAM_APPLICATION_ID NUMBER

Concurrent Program who column - application id of the program that last updated this row (foreign key to FND_APPLICATION.APPLICATION_ID).
PROGRAM_ID NUMBER

Concurrent Program who column - program id of the program that last updated this row (foreign key to FND_CONCURRENT_PROGRAM.CONCURRENT_PROGRAM_ID).
PROGRAM_UPDATE_DATE DATE

Concurrent Program who column - date when a program last updated this row).
SECURITY_GROUP_ID NUMBER (15)
Security Group Id

[top of page]

BIC.BIC_PARTY_STATUS_SUMM does not reference any database object

BIC.BIC_PARTY_STATUS_SUMM is referenced by following:

BIC
VIEW - BIC_PARTY_STATUS_SUMM#

[top of page]

Table: BIC_PARTY_SUMM

Description: From Customer Summary table, the rows are transposed as columns for matrix reporting. i.e., Flattened table for org striped measures.
Foreign Keys from this table:
Foreign Key Column Foreign Table
BIC_PARTY_SUMM.PARTY_ID HZ_PARTIES
Comments

From Customer Summary table, the rows are transposed as columns for matrix reporting. i.e., Flattened table for org striped measures.

Indexes
Index Type Tablespace Column
BIC_PARTY_SUMM_U1 UNIQUE APPS_TS_SUMMARY PERIOD_START_DATE
PARTY_ID
ORG_ID
Columns
Name Datatype Length Mandatory Comments
PERIOD_START_DATE DATE
Y Period Start Date
PARTY_ID NUMBER
Y FK to Hz_Parties
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
CREATION_DATE DATE
Y Standard who column - date when this row was created.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATED_BY NUMBER
Y Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
ORG_ID NUMBER

Organization identifier
LOYALTY NUMBER

Loyalty
SATISFACTION NUMBER

Satisfaction
SF_BILLING NUMBER

Satisfaction's sub-measure : Billing
SF_CONTRACT NUMBER

Satisfaction's sub-measure : Contract
SF_QUALITY NUMBER

Satisfaction's sub-measure : Quality
SF_SERVICE NUMBER

Satisfaction's sub-measure : Service
SF_SHIPMENT NUMBER

Satisfaction's sub-measure : Shipment
ORDER_AMT NUMBER

Order Amount
ORDER_LINES_DELIVERED NUMBER

Order Lines Delivered
ORDER_LINES_ONTIME NUMBER

Order Lines Ontime
ORDER_NUM NUMBER

Order Number
ORDER_QTY NUMBER

Order Quantity
ORDER_QTY_CUML NUMBER

Order Quantity Cumulative
ORDER_RECENCY NUMBER

How recent is this order
PAYMENTS NUMBER

Payments
RETURNS NUMBER

Returns
RETURN_BY_VALUE NUMBER

Return by Value
RETURN_BY_VALUE_PCT NUMBER

Return by Value percent
RETURN_QTY NUMBER

Return Quantity
PCT_RETURN_QTY NUMBER

Pct Return Quantity
OL_DEL_VALUE NUMBER

Del Value
OL_ONTIME_VALUE NUMBER

On time value
ONTIME_PAYMENTS NUMBER

On time payments
ONTIME_SHIP_PCT NUMBER

On time shipment percent
ONTIME_VALUE_PCT NUMBER

on time value percent
ON_TIME_PAYMENT_RATE NUMBER

on time payment rate
ASSOCIATION_YRS NUMBER

Association years
CLOSED_SRS NUMBER

Closed Service Requests
COGS NUMBER

Cost of Goods
CONTRACTS_CUML NUMBER

Contracts
CONTRACT_AMT NUMBER

Contract Amount
CONTRACT_DURATION NUMBER

Contract Duration
INACTIVE_CONTRACTS NUMBER

Inactive Contracts
OPEN_CONTRACTS NUMBER

Open Contracts
NEW_CONTRACTS NUMBER

New Contracts
RENEWED_CONTRACTS NUMBER

Renewed Contracts
ESC_SRS NUMBER

Escalated Service Requests
FIRST_CALL_CL_RATE NUMBER

First Call Rate
INTERACTIONS NUMBER

Interactions
INTERAC_CUML NUMBER

Total Interactions
NO_OF_COMPLAINTS NUMBER

Number of Complaints
NO_OF_INTERACTIONS NUMBER

Number of Interactions
NO_OF_TRANSFERS NUMBER

Number of Transfers
OPEN_SRS NUMBER

Open Service Requests
PCT_ESC_SRS NUMBER

Escalated Service Requests
PCT_REOPENED_SRS NUMBER

Reopened Service Requests
PERCT_CALL_REWORK NUMBER

Call rework
PRODUCTS NUMBER

Products
REFERALS NUMBER

Referals
REOPENED_SRS NUMBER

Reopened Service Requests
SALES NUMBER

Sales
SRS_LOGGED NUMBER

Service Requests Logged
SR_CLOSED_INT NUMBER

Closed Service Requests
TOTAL_HOLD_TIME NUMBER

Total hold time
TOTAL_LEN_OF_EMP NUMBER

Total len of Employee
TOTAL_SR_RESL_TIME NUMBER

Total Service Request Resolution time
TOTAL_SR_RESPONSE_TIME NUMBER

Total Service Request Response time
AVG_CLOSED_SRS NUMBER

Average Closed Service Requests
AVG_COMPLAINTS NUMBER

Average Complaints
AVG_HOLD_TIME NUMBER

Average Hold time
AVG_INTERACTIONS NUMBER

Average Interactions
AVG_INTERACTIONS_PER_SR NUMBER

Average Interactions per Service request
AVG_LEN_OF_EMP NUMBER

Average Len of Employee
AVG_OUTSTANDING_SRS NUMBER

Average Outstanding Service Requests
AVG_PERIOD_FOR_ACTIVE_CONT NUMBER

Average Period for Active Contracts
AVG_SRS_LOGGED NUMBER

Average Service Requests Logged
AVG_SR_RESL_TIME NUMBER

Average Service Request resolution time
AVG_SR_RESPONSE_TIME NUMBER

Average Service request Response time
AVG_TALK_TIME NUMBER

Average talk time
AVG_TRANSFERS_BEF_RESL NUMBER

Average transfers before resolution
AVG_TRANSFERS_PER_SR NUMBER

Average transfers per Service Request
AVG_WORKLOAD NUMBER

Average workload
CALLS NUMBER

Calls
CALL_LENGTH NUMBER

Call Length
CALL_TYPE_INTERACTIONS NUMBER

Call type Interactions
MARKET_SEGMENT_ID NUMBER

Id of Marget Segment
PROFITABILITY NUMBER

Profitability
LAST_UPDATE_LOGIN NUMBER

Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).
REQUEST_ID NUMBER

Concurrent Program who column - concurrent request id of the program that last updated this row (foreign key to FND_CONCURRENT_REQUESTS.REQUEST_ID).
PROGRAM_APPLICATION_ID NUMBER

Concurrent Program who column - application id of the program that last updated this row (foreign key to FND_APPLICATION.APPLICATION_ID).
PROGRAM_ID NUMBER

Concurrent Program who column - program id of the program that last updated this row (foreign key to FND_CONCURRENT_PROGRAM.CONCURRENT_PROGRAM_ID).
PROGRAM_UPDATE_DATE DATE

Concurrent Program who column - date when a program last updated this row).
SECURITY_GROUP_ID NUMBER (15)
Security Group id

[top of page]

BIC.BIC_PARTY_SUMM does not reference any database object

BIC.BIC_PARTY_SUMM is referenced by following:

BIC
VIEW - BIC_PARTY_SUMM#

[top of page]

Table: BIC_PARTY_SUMMARY

Description: Customer Intelligence Summary information. This gets populated by the concurrent program 'Extract Customer Summary' - for non-Org striped measures only
Foreign Keys from this table:
Foreign Key Column Foreign Table
BIC_PARTY_SUMMARY.PARTY_ID HZ_PARTIES
Comments

Customer Intelligence Summary information. This gets populated by the concurrent program 'Extract Customer Summary' - for non-Org striped measures only

Indexes
Index Type Tablespace Column
BIC_PARTY_SUMMARY_U1 UNIQUE APPS_TS_SUMMARY PERIOD_START_DATE
PARTY_ID
MEASURE_ID
BIC_PARTY_SUMMARY_N1 NONUNIQUE APPS_TS_SUMMARY MEASURE_CODE
Columns
Name Datatype Length Mandatory Comments
MEASURE_ID NUMBER
Y Measure id
PERIOD_START_DATE DATE
Y Period Start Date
PARTY_ID NUMBER
Y FK to Hz_Parties
BUCKET_ID NUMBER

Bucket Id
VALUE NUMBER

Value
SECURITY_GROUP_ID NUMBER (15)
Security Group Id
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATION_DATE DATE
Y Standard who column - date when this row was created.
CREATED_BY NUMBER
Y Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
LAST_UPDATE_LOGIN NUMBER

Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).
REQUEST_ID NUMBER

Concurrent Program who column - concurrent request id of the program that last updated this row (foreign key to FND_CONCURRENT_REQUESTS.REQUEST_ID).
PROGRAM_APPLICATION_ID NUMBER

Concurrent Program who column - application id of the program that last updated this row (foreign key to FND_APPLICATION.APPLICATION_ID).
PROGRAM_ID NUMBER

Concurrent Program who column - program id of the program that last updated this row (foreign key to FND_CONCURRENT_PROGRAM.CONCURRENT_PROGRAM_ID).
PROGRAM_UPDATE_DATE DATE

Concurrent Program who column - date when a program last updated this row).
SCORE NUMBER

Score
MEASURE_CODE VARCHAR2 (30)
Internal code for measures like Satisfaction, Loyalty etc.,
BIC_PARTY_SUMMARY_MEASURE_ID NUMBER

[top of page]

BIC.BIC_PARTY_SUMMARY does not reference any database object

BIC.BIC_PARTY_SUMMARY is referenced by following:

BIC
VIEW - BIC_PARTY_SUMMARY#

[top of page]

Table: BIC_PERIODS

Description: GL Periods information, populated by 'Extract Calendar' concurrent program
Comments

GL Periods information, populated by 'Extract Calendar' concurrent program

Columns
Name Datatype Length Mandatory Comments
ACT_PERIOD_NAME VARCHAR2 (15) Y Period Name
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
CREATION_DATE DATE
Y Standard who column - date when this row was created.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATED_BY NUMBER
Y Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
START_DATE DATE
Y Start Date
ACT_PERIOD_START_DATE DATE
Y Period Start Date
ACT_PERIOD_END_DATE DATE
Y Period End Date
ACT_YEAR NUMBER (15)
Year
ACT_PERIOD_NUM NUMBER (15)
Period Number
ACT_QUARTER NUMBER (15)
Quarter
ACT_YEAR_START_DATE DATE

Year Start Date
ACT_QUARTER_START_DATE DATE

Quarter Start Date
ACT_HALF_YEAR NUMBER

Half Year
SECURITY_GROUP_ID NUMBER (15)
Security Group Id

[top of page]

BIC.BIC_PERIODS does not reference any database object

BIC.BIC_PERIODS is referenced by following:

BIC
VIEW - BIC_PERIODS#

[top of page]

Table: BIC_PROFILE_VALUES_ALL

Description: Activation and Retention related setup information
Comments

Activation and Retention related setup information

Indexes
Index Type Tablespace Column
BIC_PROFILE_VALUES_U1 UNIQUE APPS_TS_SEED PROFILE_ID
ZD_EDITION_NAME
Columns
Name Datatype Length Mandatory Comments
PROFILE_ID NUMBER
Y Profile Id
ORG_ID NUMBER

Organization identifier
ACTIVATION_PERIOD NUMBER

Activation Period
ATTRITION_PERIOD NUMBER

Attrition Period
FIRST_RETENTION_PERIOD DATE

First Retention Period
LC_COMPARISON_TYPE VARCHAR2 (15)
Life cycle - Comparison Type
LC_GRANULARITY_LEVEL VARCHAR2 (15)
Life cycle - Granularity Level
LC_MEASURE_ID NUMBER

Life cycle - measure id
LC_STARTING_PERIOD DATE

Life cycle - Start Period
LC_NEW_CUST_PERIOD NUMBER

Life cycle - New Customer Period
LC_INSIG_LEVEL NUMBER

Life cycle - Insignificant Level
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATION_DATE DATE
Y Standard who column - date when this row was created.
CREATED_BY NUMBER
Y Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
LAST_UPDATE_LOGIN NUMBER

Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).
LC_MEASURE_CODE VARCHAR2 (30)
Life Cycle measure code
SECURITY_GROUP_ID NUMBER

Security Group Id
ZD_EDITION_NAME VARCHAR2 (30) Y

[top of page]

BIC.BIC_PROFILE_VALUES_ALL does not reference any database object

BIC.BIC_PROFILE_VALUES_ALL is referenced by following:

BIC
VIEW - BIC_PROFILE_VALUES_ALL#

[top of page]

Table: BIC_RELATIONSHIP_TYPES

Description:
Comments
***** Warning: Oracle Internal Use Only *****

Oracle Corporation does not support access to Oracle applications data using this object, except from standard Oracle Applications programs.

Indexes
Index Type Tablespace Column
BIC_RELATIONSHIP_TYPES_U1 UNIQUE APPS_TS_ARCHIVE PARTY_RELATIONSHIP_TYPE
Columns
Name Datatype Length Mandatory Comments
PARTY_RELATIONSHIP_TYPE VARCHAR2 (30) Y
DISPLAY_FLAG VARCHAR2 (1) Y
SECURITY_GROUP_ID NUMBER (15)
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATION_DATE DATE
Y Standard who column - date when this row was created.
CREATED_BY NUMBER
Y Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
LAST_UPDATE_LOGIN NUMBER

Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).

[top of page]

BIC.BIC_RELATIONSHIP_TYPES does not reference any database object

BIC.BIC_RELATIONSHIP_TYPES is referenced by following:

BIC
VIEW - BIC_RELATIONSHIP_TYPES#

[top of page]

Table: BIC_STANDARD_VALUES

Description: This table stores industry standard values for satisfaction
Primary Key: BIC_STANDARD_VALUES_PK
  1. MEASURE_CODE
  2. PERIOD_START_DATE
Foreign Keys from this table:
Foreign Key Column Foreign Table
BIC_STANDARD_VALUES.MEASURE_CODE BIC_MEASURE_ATTRIBS
Comments

This table stores industry standard values for satisfaction. The data in this table is entered using a setup form for this purpose. The time period will be the same as the time_unit_code for the measure SATISFACTION in bic_measures table

Indexes
Index Type Tablespace Column
BIC_STANDARD_VALUES_U1 UNIQUE APPS_TS_SEED MEASURE_CODE
PERIOD_START_DATE
Columns
Name Datatype Length Mandatory Comments
MEASURE_CODE VARCHAR2 (30) Y values could be :'SATISFACTION', 'SF_SALES', 'SF_PRD_QUALITY' ,'SF_PRD_SHIPMENT','SF_BILLING','SF_SERVICE','SF_CONTRACT'
PERIOD_START_DATE DATE
Y The first date of the period
STD_VALUE NUMBER

The industry standard value
LAST_UPDATE_DATE DATE
Y Standard Who column - date when a user last updated this row.
LAST_UPDATED_BY NUMBER
Y Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATION_DATE DATE
Y Standard who column - date when this row was created.
CREATED_BY NUMBER
Y Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
LAST_UPDATE_LOGIN NUMBER

Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).
REQUEST_ID NUMBER

Concurrent Program who column - concurrent request id of the program that last updated this row (foreign key to FND_CONCURRENT_REQUESTS.REQUEST_ID).
PROGRAM_APPLICATION_ID NUMBER

Concurrent Program who column - application id of the program that last updated this row (foreign key to FND_APPLICATION.APPLICATION_ID).
PROGRAM_ID NUMBER

Concurrent Program who column - program id of the program that last updated this row (foreign key to FND_CONCURRENT_PROGRAM.CONCURRENT_PROGRAM_ID).
PROGRAM_UPDATE_DATE DATE

Concurrent Program who column - date when a program last updated this row).
SECURITY_GROUP_ID NUMBER (15)
Security Group Id

[top of page]

BIC.BIC_STANDARD_VALUES does not reference any database object

BIC.BIC_STANDARD_VALUES is referenced by following:

BIC
VIEW - BIC_STANDARD_VALUES#

[top of page]

View: BIC_DIMV_OPERATING_UNITS

Comments
View Type

A Business Intelligence System view

Columns
Name Datatype Length Mandatory Comments
ALL_FK CHAR (3)
ORG_ID NUMBER (15) Y Organization identifier
ORG_NAME VARCHAR2 (240) Y

[top of page]

APPS.BIC_DIMV_OPERATING_UNITS references the following:

- HR_OPERATING_UNITS

APPS.BIC_DIMV_OPERATING_UNITS is not referenced by any database object

[top of page]
Copyright (c) 2001 - 2005, Oracle - Use Restricted by Contract