eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AR_REVENUE_ASSIGNMENTS

Source

View Text - Preformatted


SELECT CTL.CUSTOMER_TRX_LINE_ID , /* 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(RRS.RULE_DATE, LEAST( (CTL.RULE_START_DATE - GPS1.START_DATE) + GPS2.START_DATE, GPS2.END_DATE)))) , /* GL DATE */ /* USE THE AR_CREDIT_MEMO_AMOUNT VALUE IF IT IS NOT NULL. OR USE THE LUMP SUM IF THIS IS A VARIABLE DURATION RULE WITH A LUMP SUM. */ TO_NUMBER(DECODE(CT.COMPLETE_FLAG, 'N', 0, NVL(LGD.AMOUNT, NVL(CMA.AMOUNT, DECODE(RRS_LUMP.PERCENT + DECODE(RRS.PERIOD_NUMBER, 1, 1, NULL), NULL, DECODE(RR.TYPE, 'A', CTL.REVENUE_AMOUNT * (RRS.PERCENT / 100), /* PRORATE THE AMOUNT EXCEPT OVER FIRST */ /* PERIOD IF THERE IS A LUMP SUM. */ ( CTL.REVENUE_AMOUNT - /* LUMP SUM CASE */ NVL( ( CTL.REVENUE_AMOUNT * (RRS_LUMP.PERCENT / 100) ), 0 ) ) / DECODE(RRS_LUMP.PERCENT, NULL, CTL.ACCOUNTING_RULE_DURATION, CTL.ACCOUNTING_RULE_DURATION -1) ), /* END NOT LUMP SUM CASE */ /* USE THE LUMP SUM IF IT IS NOT NULL AND THIS IS THE FIRST PERIOD. */ CTL.REVENUE_AMOUNT * (RRS_LUMP.PERCENT / 100) /* INSURE THAT THE SIGN OF THE ROW REFLECTS THE ACCOUNT TYPE AND INVOICING RULE. */ ) /* END CMA.AMOUNT NOT SPECIFIED CASE */ ) /* END CMA.AMOUNT NVL */ ) /* END LGD.AMOUNT NVL */ * DECODE(RAL.LOOKUP_CODE, 'REV', 1, - 1) ) ) , /* AMOUNT */ RAL.LOOKUP_CODE , /* ACCOUNT TYPE */ GPS1.PERIOD_SET_NAME /* PERIOD_SET NAME */ , rr.type rule_type /* RULE_TYPE */ FROM ra_customer_trx_lines ctl, gl_sets_of_books sob, ra_customer_trx ct, ra_rules rr, ra_rule_schedules rrs, ar_period_types gpt, ar_credit_memo_amounts cma, ra_rule_schedules rrs_lump, ra_cust_trx_line_gl_dist lgd, ar_periods gps1, ar_periods gps2, ar_lookups ral WHERE sob.set_of_books_id = ctl.set_of_books_id AND ct.customer_trx_id = ctl.customer_trx_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 /* Doubles the amount of rows returned for each GL date. */ AND ral.lookup_type = 'AUTOGL_TYPE' AND (ral.lookup_code = 'REV' OR ral.lookup_code = DECODE(ct.invoicing_rule_id, -2, 'UNEARN', -3, 'UNBILL')) AND DECODE(rr.type, 'A', rr.rule_id, -1) = rrs.rule_id /* Join to rrs_lump if the rule is variable duration with a lump sum */ AND rrs_lump.rule_id(+) = DECODE(rr.type, 'ACC_DUR', rr.rule_id, -10) AND rrs_lump.period_number(+) = 1 AND rr.rule_id = ctl.accounting_rule_id AND rrs.period_number <= DECODE(ctl.accounting_rule_duration , NULL, rr.occurrences, ctl.accounting_rule_duration) AND gps2.period_set_name = gps1.period_set_name AND gpt.period_type = gps1.period_type AND gpt.period_type = DECODE(rr.frequency, 'SPECIFIC', accounted_period_type, DECODE(ctl.previous_customer_trx_line_id, NULL, rr.frequency, accounted_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 + gpt.max_regular_period_length AND gps2.new_period_num = gps1.new_period_num + (rrs.period_number - 1) AND (rr.type in ('A', 'ACC_DUR') OR ctl.previous_customer_trx_line_id is NOT NULL) UNION ALL SELECT main_qry.customer_trx_line_id, main_qry.customer_trx_id, main_qry.previous_customer_trx_line_id, main_qry.request_id, main_qry.quantity, main_qry.gl_date, NVL(cma.amount, CASE main_qry.rule_type WHEN 'PP_DR_ALL' THEN main_qry.daily_rate * days_per_gl_period WHEN 'PP_DR_PP' THEN CASE full_or_partial WHEN 'F' THEN /** Full periods **/ (revenue_amount - ( days_in_partial_periods * main_qry.daily_rate )) / (cnt_of_full_periods) ELSE /** Partial Periods **/ main_qry.daily_rate * days_per_gl_period END ELSE (revenue_amount * period_factor) / (no_of_full_periods) END ) * DECODE(ral.lookup_code, 'REV', 1, -1) amount, ral.lookup_code account_class, period_set_name, main_qry.rule_type FROM ( SELECT /* */ctl.customer_trx_id, ctl.customer_trx_line_id, ctl.previous_customer_trx_line_id, ctl.request_id, ctl.rule_start_date, ctl.rule_end_date, ct.invoicing_rule_id, NVL(CTL.QUANTITY_CREDITED, CTL.QUANTITY_INVOICED) quantity, gps.start_date, gps.end_date, gps.period_type, gps.period_set_name, rr.type rule_type, /** GL Date **/ LEAST((ctl.rule_start_date - FIRST_VALUE(start_date) OVER (PARTITION BY gps.period_set_name, gps.period_type, ctl.customer_trx_line_id, ctl.CUSTOMER_TRX_ID ORDER BY gps.start_date ) ) + gps.start_date, gps.end_date, ctl.rule_end_date ) gl_date, /** Period Seq Number **/ ROW_NUMBER() OVER( PARTITION BY gps.period_set_name, gps.period_type, ctl.customer_trx_line_id,ctl.CUSTOMER_TRX_ID ORDER BY gps.start_date ) period_seq_num, /** Partial or Full ***/ DECODE(LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date) ), (gps.end_date - gps.start_date), 'F', 'P' ) full_or_partial, /** Total number of days in RevRec schedule **/ (ctl.rule_end_date - ctl.rule_start_date) + 1 total_days_in_schedule, /** Daily Rate **/ (ctl.revenue_amount) /((ctl.rule_end_date - ctl.rule_start_date) + 1) daily_rate, /** Days per given GL period **/ LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date) ) + 1 days_per_gl_period, /** Maximum Days per given GL period ***/ (gps.end_date - gps.start_date) + 1 max_days_per_gl_period, /** Period Factor **/ (LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date) ) + 1 ) / ((gps.end_date - gps.start_date) + 1) period_factor, /** total full periods (includes partial periods) ***/ SUM((LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date) ) + 1 ) / ((gps.end_date - gps.start_date) + 1) ) OVER (PARTITION BY gps.period_set_name, gps.period_type, ctl.customer_trx_line_id, ctl.CUSTOMER_TRX_ID ) no_of_full_periods, /** Count of only full periods ***/ SUM( CASE /** Count only full periods **/ WHEN LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date)) = (gps.end_date - gps.start_date) THEN 1 ELSE /** Do not count partial periods */ 0 END ) OVER (PARTITION BY gps.period_set_name, gps.period_type, ctl.customer_trx_line_id,ctl.CUSTOMER_TRX_ID ) cnt_of_full_periods, /**Days in partial periods ***/ SUM( CASE WHEN LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date)) = (gps.end_date - gps.start_date) THEN /** Full periods **/ 0 ELSE /** Partial periods */ LEAST((gps.end_date - gps.start_date), (gps.end_date - ctl.rule_start_date), (ctl.rule_end_date - gps.start_date)) + 1 END ) OVER (PARTITION BY gps.period_set_name, gps.period_type, ctl.customer_trx_line_id,ctl.CUSTOMER_TRX_ID ) days_in_partial_periods, /** Revenue Amount for line item ***/ ctl.revenue_amount FROM ar_periods gps, ar_system_parameters_all sys, gl_sets_of_books gls, ar_period_types gpt, ra_rules rr, ra_customer_trx_lines ctl, ra_customer_trx ct WHERE sys.set_of_books_id = gls.set_of_books_id AND sys.org_id = ctl.org_id AND gls.period_set_name = gps.period_set_name AND gps.period_type = gpt.period_type AND ctl.accounting_rule_id = rr.rule_id AND rr.type NOT IN ('A', 'ACC_DUR') AND ctl.previous_customer_trx_line_id IS NULL /* no regular credit memos */ AND rr.frequency = gpt.period_type AND ct.customer_trx_id = ctl.customer_trx_id AND (ctl.rule_start_date BETWEEN gps.start_date AND gps.end_date OR ctl.rule_end_date BETWEEN gps.start_date AND gps.end_date OR gps.start_date BETWEEN ctl.rule_start_date AND ctl.rule_end_date ) ) main_qry, ar_lookups ral, ar_credit_memo_amounts cma where ral.lookup_type = 'AUTOGL_TYPE' AND (ral.lookup_code = 'REV' OR ral.lookup_code = DECODE(main_qry.invoicing_rule_id, -2, 'UNEARN', -3, 'UNBILL')) AND main_qry.customer_trx_line_id = cma.customer_trx_line_id (+) AND main_qry.gl_date = cma.gl_date (+)

View Text - HTML Formatted

SELECT CTL.CUSTOMER_TRX_LINE_ID
, /* 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(RRS.RULE_DATE
, LEAST( (CTL.RULE_START_DATE - GPS1.START_DATE) + GPS2.START_DATE
, GPS2.END_DATE))))
, /* GL DATE */ /* USE THE AR_CREDIT_MEMO_AMOUNT VALUE IF IT IS NOT NULL. OR USE THE LUMP SUM IF THIS IS A VARIABLE DURATION RULE WITH A LUMP SUM. */ TO_NUMBER(DECODE(CT.COMPLETE_FLAG
, 'N'
, 0
, NVL(LGD.AMOUNT
, NVL(CMA.AMOUNT
, DECODE(RRS_LUMP.PERCENT + DECODE(RRS.PERIOD_NUMBER
, 1
, 1
, NULL)
, NULL
, DECODE(RR.TYPE
, 'A'
, CTL.REVENUE_AMOUNT * (RRS.PERCENT / 100)
, /* PRORATE THE AMOUNT EXCEPT OVER FIRST */ /* PERIOD IF THERE IS A LUMP SUM. */ ( CTL.REVENUE_AMOUNT - /* LUMP SUM CASE */ NVL( ( CTL.REVENUE_AMOUNT * (RRS_LUMP.PERCENT / 100) )
, 0 ) ) / DECODE(RRS_LUMP.PERCENT
, NULL
, CTL.ACCOUNTING_RULE_DURATION
, CTL.ACCOUNTING_RULE_DURATION -1) )
, /* END NOT LUMP SUM CASE */ /* USE THE LUMP SUM IF IT IS NOT NULL
AND THIS IS THE FIRST PERIOD. */ CTL.REVENUE_AMOUNT * (RRS_LUMP.PERCENT / 100) /* INSURE THAT THE SIGN OF THE ROW REFLECTS THE ACCOUNT TYPE
AND INVOICING RULE. */ ) /* END CMA.AMOUNT NOT SPECIFIED CASE */ ) /* END CMA.AMOUNT NVL */ ) /* END LGD.AMOUNT NVL */ * DECODE(RAL.LOOKUP_CODE
, 'REV'
, 1
, - 1) ) )
, /* AMOUNT */ RAL.LOOKUP_CODE
, /* ACCOUNT TYPE */ GPS1.PERIOD_SET_NAME /* PERIOD_SET NAME */
, RR.TYPE RULE_TYPE /* RULE_TYPE */
FROM RA_CUSTOMER_TRX_LINES CTL
, GL_SETS_OF_BOOKS SOB
, RA_CUSTOMER_TRX CT
, RA_RULES RR
, RA_RULE_SCHEDULES RRS
, AR_PERIOD_TYPES GPT
, AR_CREDIT_MEMO_AMOUNTS CMA
, RA_RULE_SCHEDULES RRS_LUMP
, RA_CUST_TRX_LINE_GL_DIST LGD
, AR_PERIODS GPS1
, AR_PERIODS GPS2
, AR_LOOKUPS RAL
WHERE SOB.SET_OF_BOOKS_ID = CTL.SET_OF_BOOKS_ID
AND CT.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_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 /* DOUBLES THE AMOUNT OF ROWS RETURNED FOR EACH GL DATE. */
AND RAL.LOOKUP_TYPE = 'AUTOGL_TYPE'
AND (RAL.LOOKUP_CODE = 'REV' OR RAL.LOOKUP_CODE = DECODE(CT.INVOICING_RULE_ID
, -2
, 'UNEARN'
, -3
, 'UNBILL'))
AND DECODE(RR.TYPE
, 'A'
, RR.RULE_ID
, -1) = RRS.RULE_ID /* JOIN TO RRS_LUMP IF THE RULE IS VARIABLE DURATION WITH A LUMP SUM */
AND RRS_LUMP.RULE_ID(+) = DECODE(RR.TYPE
, 'ACC_DUR'
, RR.RULE_ID
, -10)
AND RRS_LUMP.PERIOD_NUMBER(+) = 1
AND RR.RULE_ID = CTL.ACCOUNTING_RULE_ID
AND RRS.PERIOD_NUMBER <= DECODE(CTL.ACCOUNTING_RULE_DURATION
, NULL
, RR.OCCURRENCES
, CTL.ACCOUNTING_RULE_DURATION)
AND GPS2.PERIOD_SET_NAME = GPS1.PERIOD_SET_NAME
AND GPT.PERIOD_TYPE = GPS1.PERIOD_TYPE
AND GPT.PERIOD_TYPE = DECODE(RR.FREQUENCY
, 'SPECIFIC'
, ACCOUNTED_PERIOD_TYPE
, DECODE(CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID
, NULL
, RR.FREQUENCY
, ACCOUNTED_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 + GPT.MAX_REGULAR_PERIOD_LENGTH
AND GPS2.NEW_PERIOD_NUM = GPS1.NEW_PERIOD_NUM + (RRS.PERIOD_NUMBER - 1)
AND (RR.TYPE IN ('A'
, 'ACC_DUR') OR CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID IS NOT NULL) UNION ALL SELECT MAIN_QRY.CUSTOMER_TRX_LINE_ID
, MAIN_QRY.CUSTOMER_TRX_ID
, MAIN_QRY.PREVIOUS_CUSTOMER_TRX_LINE_ID
, MAIN_QRY.REQUEST_ID
, MAIN_QRY.QUANTITY
, MAIN_QRY.GL_DATE
, NVL(CMA.AMOUNT
, CASE MAIN_QRY.RULE_TYPE WHEN 'PP_DR_ALL' THEN MAIN_QRY.DAILY_RATE * DAYS_PER_GL_PERIOD WHEN 'PP_DR_PP' THEN CASE FULL_OR_PARTIAL WHEN 'F' THEN /** FULL PERIODS **/ (REVENUE_AMOUNT - ( DAYS_IN_PARTIAL_PERIODS * MAIN_QRY.DAILY_RATE )) / (CNT_OF_FULL_PERIODS) ELSE /** PARTIAL PERIODS **/ MAIN_QRY.DAILY_RATE * DAYS_PER_GL_PERIOD END ELSE (REVENUE_AMOUNT * PERIOD_FACTOR) / (NO_OF_FULL_PERIODS) END ) * DECODE(RAL.LOOKUP_CODE
, 'REV'
, 1
, -1) AMOUNT
, RAL.LOOKUP_CODE ACCOUNT_CLASS
, PERIOD_SET_NAME
, MAIN_QRY.RULE_TYPE
FROM ( SELECT /* */CTL.CUSTOMER_TRX_ID
, CTL.CUSTOMER_TRX_LINE_ID
, CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID
, CTL.REQUEST_ID
, CTL.RULE_START_DATE
, CTL.RULE_END_DATE
, CT.INVOICING_RULE_ID
, NVL(CTL.QUANTITY_CREDITED
, CTL.QUANTITY_INVOICED) QUANTITY
, GPS.START_DATE
, GPS.END_DATE
, GPS.PERIOD_TYPE
, GPS.PERIOD_SET_NAME
, RR.TYPE RULE_TYPE
, /** GL DATE **/ LEAST((CTL.RULE_START_DATE - FIRST_VALUE(START_DATE) OVER (PARTITION BY GPS.PERIOD_SET_NAME
, GPS.PERIOD_TYPE
, CTL.CUSTOMER_TRX_LINE_ID
, CTL.CUSTOMER_TRX_ID ORDER BY GPS.START_DATE ) ) + GPS.START_DATE
, GPS.END_DATE
, CTL.RULE_END_DATE ) GL_DATE
, /** PERIOD SEQ NUMBER **/ ROW_NUMBER() OVER( PARTITION BY GPS.PERIOD_SET_NAME
, GPS.PERIOD_TYPE
, CTL.CUSTOMER_TRX_LINE_ID
, CTL.CUSTOMER_TRX_ID ORDER BY GPS.START_DATE ) PERIOD_SEQ_NUM
, /** PARTIAL OR FULL ***/ DECODE(LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE) )
, (GPS.END_DATE - GPS.START_DATE)
, 'F'
, 'P' ) FULL_OR_PARTIAL
, /** TOTAL NUMBER OF DAYS IN REVREC SCHEDULE **/ (CTL.RULE_END_DATE - CTL.RULE_START_DATE) + 1 TOTAL_DAYS_IN_SCHEDULE
, /** DAILY RATE **/ (CTL.REVENUE_AMOUNT) /((CTL.RULE_END_DATE - CTL.RULE_START_DATE) + 1) DAILY_RATE
, /** DAYS PER GIVEN GL PERIOD **/ LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE) ) + 1 DAYS_PER_GL_PERIOD
, /** MAXIMUM DAYS PER GIVEN GL PERIOD ***/ (GPS.END_DATE - GPS.START_DATE) + 1 MAX_DAYS_PER_GL_PERIOD
, /** PERIOD FACTOR **/ (LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE) ) + 1 ) / ((GPS.END_DATE - GPS.START_DATE) + 1) PERIOD_FACTOR
, /** TOTAL FULL PERIODS (INCLUDES PARTIAL PERIODS) ***/ SUM((LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE) ) + 1 ) / ((GPS.END_DATE - GPS.START_DATE) + 1) ) OVER (PARTITION BY GPS.PERIOD_SET_NAME
, GPS.PERIOD_TYPE
, CTL.CUSTOMER_TRX_LINE_ID
, CTL.CUSTOMER_TRX_ID ) NO_OF_FULL_PERIODS
, /** COUNT OF ONLY FULL PERIODS ***/ SUM( CASE /** COUNT ONLY FULL PERIODS **/ WHEN LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE)) = (GPS.END_DATE - GPS.START_DATE) THEN 1 ELSE /** DO NOT COUNT PARTIAL PERIODS */ 0 END ) OVER (PARTITION BY GPS.PERIOD_SET_NAME
, GPS.PERIOD_TYPE
, CTL.CUSTOMER_TRX_LINE_ID
, CTL.CUSTOMER_TRX_ID ) CNT_OF_FULL_PERIODS
, /**DAYS IN PARTIAL PERIODS ***/ SUM( CASE WHEN LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE)) = (GPS.END_DATE - GPS.START_DATE) THEN /** FULL PERIODS **/ 0 ELSE /** PARTIAL PERIODS */ LEAST((GPS.END_DATE - GPS.START_DATE)
, (GPS.END_DATE - CTL.RULE_START_DATE)
, (CTL.RULE_END_DATE - GPS.START_DATE)) + 1 END ) OVER (PARTITION BY GPS.PERIOD_SET_NAME
, GPS.PERIOD_TYPE
, CTL.CUSTOMER_TRX_LINE_ID
, CTL.CUSTOMER_TRX_ID ) DAYS_IN_PARTIAL_PERIODS
, /** REVENUE AMOUNT FOR LINE ITEM ***/ CTL.REVENUE_AMOUNT
FROM AR_PERIODS GPS
, AR_SYSTEM_PARAMETERS_ALL SYS
, GL_SETS_OF_BOOKS GLS
, AR_PERIOD_TYPES GPT
, RA_RULES RR
, RA_CUSTOMER_TRX_LINES CTL
, RA_CUSTOMER_TRX CT
WHERE SYS.SET_OF_BOOKS_ID = GLS.SET_OF_BOOKS_ID
AND SYS.ORG_ID = CTL.ORG_ID
AND GLS.PERIOD_SET_NAME = GPS.PERIOD_SET_NAME
AND GPS.PERIOD_TYPE = GPT.PERIOD_TYPE
AND CTL.ACCOUNTING_RULE_ID = RR.RULE_ID
AND RR.TYPE NOT IN ('A'
, 'ACC_DUR')
AND CTL.PREVIOUS_CUSTOMER_TRX_LINE_ID IS NULL /* NO REGULAR CREDIT MEMOS */
AND RR.FREQUENCY = GPT.PERIOD_TYPE
AND CT.CUSTOMER_TRX_ID = CTL.CUSTOMER_TRX_ID
AND (CTL.RULE_START_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE OR CTL.RULE_END_DATE BETWEEN GPS.START_DATE
AND GPS.END_DATE OR GPS.START_DATE BETWEEN CTL.RULE_START_DATE
AND CTL.RULE_END_DATE ) ) MAIN_QRY
, AR_LOOKUPS RAL
, AR_CREDIT_MEMO_AMOUNTS CMA
WHERE RAL.LOOKUP_TYPE = 'AUTOGL_TYPE'
AND (RAL.LOOKUP_CODE = 'REV' OR RAL.LOOKUP_CODE = DECODE(MAIN_QRY.INVOICING_RULE_ID
, -2
, 'UNEARN'
, -3
, 'UNBILL'))
AND MAIN_QRY.CUSTOMER_TRX_LINE_ID = CMA.CUSTOMER_TRX_LINE_ID (+)
AND MAIN_QRY.GL_DATE = CMA.GL_DATE (+)