1. Tables and Views for Financials
  2. AR_REVENUE_ASSIGNMENTS_V

AR_REVENUE_ASSIGNMENTS_V

Details

  • Schema: FUSION

  • Object owner: AR

  • Object type: VIEW

Columns

Name

SESSION_ID

CUSTOMER_TRX_LINE_ID

CUSTOMER_TRX_ID

PREVIOUS_CUSTOMER_TRX_LINE_ID

REQUEST_ID

QUANTITY

GL_DATE

AMOUNT

ACCOUNT_CLASS

PERIOD_SET_NAME

RULE_TYPE

Query

SQL_Statement

SELECT

/*+ ORDERED USE_NL(ctl,ragt,lgd,gps1,gps2,cma) INDEX RAGT (ar_revenue_assignments_gt_n1) */

RAGT.SESSION_ID ,

CTL.CUSTOMER_TRX_LINE_ID ,

CTL.CUSTOMER_TRX_ID ,

CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID ,

CTL.REQUEST_ID ,

/* REQUEST_ID */

NVL(CTL.QUANTITY_CREDITED, CTL.QUANTITY_INVOICED) ,

NVL(LGD.ORIGINAL_GL_DATE, NVL(CMA.GL_DATE, NVL(RAGT.RULE_DATE, LEAST( (CTL.RULE_START_DATE - GPS1.START_DATE) + GPS2.START_DATE, GPS2.END_DATE , NVL(CTL.RULE_END_DATE, TO_DATE('31-12-4712','DD-MM-RRRR')) ) ) ) ),

/* GL DATE */

(CASE SUBSTR(RAGT.RULE_TYPE,1,1)

WHEN 'A'

THEN

/* normal fixed or variable duration rule */

TO_NUMBER(NVL(LGD.AMOUNT, NVL(CMA.AMOUNT, CTL.REVENUE_AMOUNT * (RAGT.PERCENT / 100))) * DECODE(RAGT.ACCOUNT_CLASS, 'REV',1 , 'UNEARN', decode (ar_revenue_management_pvt.is_inarrear_trx_cont_line(ctl.customer_trx_line_id),'Y',1,-1),- 1))

WHEN 'P'

THEN

/* PPRR rule */

RAGT.PPRR_AMOUNT

END) AMOUNT,

/* AMOUNT */

RAGT.ACCOUNT_CLASS,

/* ACCOUNT CLASS */

GPS1.PERIOD_SET_NAME,

/* PERIOD_SET NAME */

RAGT.RULE_TYPE

FROM ra_customer_trx_lines ctl,

ar_revenue_assignments_gt ragt,

ra_cust_trx_line_gl_dist lgd,

ar_periods gps1,

ar_periods gps2,

ar_credit_memo_amounts cma

WHERE ctl.customer_trx_line_id = ragt.customer_trx_line_id

AND ctl.customer_trx_line_id = cma.customer_trx_line_id(+)

AND DECODE(ctl.autorule_complete_flag, NULL, DECODE(ctl.autorule_duration_processed, -1, ctl.customer_trx_line_id, NULL), 'Y', DECODE(ctl.autorule_duration_processed, -1, ctl.customer_trx_line_id, NULL), NULL) = lgd.customer_trx_line_id(+)

AND 'N' = lgd.account_set_flag(+)

AND 'REV' = lgd.account_class(+)

AND DECODE(ctl.autorule_complete_flag, NULL, DECODE(ctl.autorule_duration_processed, -1, gps1.rowid, gps2.rowid), gps2.rowid) = gps2.rowid

AND DECODE(cma.gl_date, NULL, gps2.rowid, gps1.rowid) = gps2.rowid

AND gps2.period_set_name = gps1.period_set_name

AND ragt.period_type = gps1.period_type

AND gps2.period_type = gps1.period_type

AND ctl.rule_start_date BETWEEN gps1.start_date AND gps1.end_date

AND gps1.end_date BETWEEN ctl.rule_start_date AND ctl.rule_start_date + ragt.max_regular_period_length

AND gps2.new_period_num = gps1.new_period_num + (ragt.period_number - 1)