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 */ 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)