AR_XML_PAYMENT_TERMS_V

Details

• Schema: FUSION

• Object owner: AR

• Object type: VIEW

Columns

Name

TERM_NAME TERM_DESCRIPTION TERM_START_DATE_EFFECTIVE CUSTOMER_TRX_ID DUE_DATE DUE_AMOUNT CURRENCY_CODE DISCOUNT_DATE DISCOUNT_AMOUNT PAYMENT_USER_ONE PAYMENT_USER_TWO PAYMENT_USER_THREE PAYMENT_USER_FOUR PAYMENT_USER_FIVE PAYMENT_USER_SIX PAYMENT_USER_SEVEN PAYMENT_USER_EIGHT PAYMENT_USER_NINE PAYMENT_USER_TEN PAYMENT_USER_ELEVEN PAYMENT_USER_TWELVE PAYMENT_USER_THIRTEEN PAYMENT_USER_FOURTEEN PAYMENT_USER_FIFTEEN PAYMENT_USER_SIXTEEN PAYMENT_USER_SEVENTEEN PAYMENT_USER_EIGHTEEN PAYMENT_USER_NINETEEN PAYMENT_USER_TWENTY PAYMENT_USER_TWENTY_ONE PAYMENT_USER_TWENTY_TWO PAYMENT_USER_TWENTY_THREE PAYMENT_USER_TWENTY_FOUR PAYMENT_USER_TWENTY_FIVE PAYMENT_USER_TWENTY_SIX PAYMENT_USER_TWENTY_SEVEN PAYMENT_USER_TWENTY_EIGHT PAYMENT_USER_TWENTY_NINE PAYMENT_USER_THIRTY PAYMENT_SET_ID

Query

SQL_Statement

SELECT b.name TERM_NAME, b.description TERM_DESCRIPTION, b.start_date_active TERM_START_DATE_EFFECTIVE, ps.customer_trx_id CUSTOMER_TRX_ID, ps.due_date DUE_DATE, ps.amount_due_remaining DUE_AMOUNT, ps.INVOICE_CURRENCY_CODE CURRENCY_CODE, disc.discount_date DISCOUNT_DATE, ar_transfer_document.currRound( disc.discount_percent/100* ps.amount_due_remaining, disc.INVOICE_CURRENCY_CODE) DISCOUNT_AMOUNT, ar_xml_view_functions.pt_function1 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_One, ar_xml_view_functions.pt_function2 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Two, ar_xml_view_functions.pt_function3 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Three, ar_xml_view_functions.pt_function4 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Four, ar_xml_view_functions.pt_function5 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Five, ar_xml_view_functions.pt_function6 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Six, ar_xml_view_functions.pt_function7 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Seven, ar_xml_view_functions.pt_function8 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Eight, ar_xml_view_functions.pt_function9 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Nine , ar_xml_view_functions.pt_function10 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Ten, ar_xml_view_functions.pt_function11 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Eleven, ar_xml_view_functions.pt_function12 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Twelve, ar_xml_view_functions.pt_function13 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Thirteen, ar_xml_view_functions.pt_function14 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Fourteen, ar_xml_view_functions.pt_function15 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Fifteen, ar_xml_view_functions.pt_function16 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Sixteen, ar_xml_view_functions.pt_function17 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Seventeen, ar_xml_view_functions.pt_function18 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Eighteen, ar_xml_view_functions.pt_function19 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Nineteen, ar_xml_view_functions.pt_function20 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Twenty, ar_xml_view_functions.pt_function21 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Twenty_One, ar_xml_view_functions.pt_function22 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Twenty_Two, ar_xml_view_functions.pt_function23 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Twenty_Three, ar_xml_view_functions.pt_function24 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Twenty_Four, ar_xml_view_functions.pt_function25 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Twenty_Five, ar_xml_view_functions.pt_function26 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Twenty_Six, ar_xml_view_functions.pt_function27 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Twenty_Seven, ar_xml_view_functions.pt_function28 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Twenty_Eight, ar_xml_view_functions.pt_function29 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Twenty_Nine, ar_xml_view_functions.pt_function30 (ps.customer_trx_id, b.TERM_ID, ps.PAYMENT_SCHEDULE_ID) Payment_User_Thirty, (SELECT MIN(rline.payment_set_id) FROM ra_customer_trx_lines_all rline WHERE rline.LINE_TYPE= ' LINE' and rline.customer_trx_id=ps.customer_trx_id) PAYMENT_SET_ID from ar_payment_schedules_all ps, ra_terms b, (SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID , PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID , PS.TRX_NUMBER TRX_NUMBER , TLD.DISCOUNT_PERCENT DISCOUNT_PERCENT , trunc(DECODE(TLD.DISCOUNT_DAYS ,NULL ,NVL(TLD.DISCOUNT_DATE, DECODE ( LEAST(TO_NUMBER(TO_CHAR(PS.TRX_DATE,'DD')), NVL(T.DUE_CUTOFF_DAY,32)) ,T.DUE_CUTOFF_DAY,LAST_DAY( ADD_MONTHS(PS.TRX_DATE, TLD.DISCOUNT_MONTHS_FORWARD) ) + LEAST(TLD.DISCOUNT_DAY_OF_MONTH, TO_NUMBER(TO_CHAR(LAST_DAY(ADD_MONTHS(PS.TRX_DATE, TLD.DISCOUNT_MONTHS_FORWARD+1)),'DD'))) , LAST_DAY(ADD_MONTHS(PS.TRX_DATE,TLD.DISCOUNT_MONTHS_FORWARD-1)) + LEAST(TLD.DISCOUNT_DAY_OF_MONTH ,TO_NUMBER(TO_CHAR(LAST_DAY( ADD_MONTHS(PS.TRX_DATE,TLD.DISCOUNT_MONTHS_FORWARD)),'DD'))) ) ) , PS.TRX_DATE + TLD.DISCOUNT_DAYS)) DISCOUNT_DATE , T.CALC_DISCOUNT_ON_LINES_FLAG CALC_DISCOUNT_ON_LINES_FLAG , T.PARTIAL_DISCOUNT_FLAG PARTIAL_DISCOUNT_FLAG , PS.AMOUNT_DUE_REMAINING , PS.INVOICE_CURRENCY_CODE , PS.CUSTOMER_ID , PS.CUSTOMER_SITE_USE_ID FROM RA_TERMS_LINES_DISCOUNTS TLD , RA_TERMS T , AR_PAYMENT_SCHEDULES_ALL PS WHERE T.TERM_ID = TLD.TERM_ID AND PS.TERM_ID = T.TERM_ID AND PS.TERMS_SEQUENCE_NUMBER = TLD.SEQUENCE_NUM AND PS.CLASS <> 'PMT' AND PS.CLASS <> 'GUAR' ) disc WHERE b.term_id = ps.term_id and ps.payment_schedule_id = disc.payment_schedule_id(+)