发票收款应用界面脚本代码,OracleEBS代码库,德仔网

代码语言
代码分类
【OracleEBS】发票收款应用界面脚本
作者:专注Oracle EBS / 发布于2012/11/29/ 991
--发票收款应用界面脚本

SELECT APP.CASH_RECEIPT_ID CASH_RECEIPT_ID
      ,PS_INV.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
      ,TO_NUMBER('') CM_CUSTOMER_TRX_ID
      ,APP.LAST_UPDATE_DATE LAST_UPDATE_DATE
      ,APP.LAST_UPDATED_BY LAST_UPDATED_BY
      ,APP.CREATION_DATE CREATION_DATE
      ,APP.CREATED_BY CREATED_BY
      ,APP.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
      ,APP.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
      ,APP.PROGRAM_ID PROGRAM_ID
      ,APP.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
      ,APP.REQUEST_ID REQUEST_ID
      ,CR.RECEIPT_NUMBER RECEIPT_NUMBER
      ,'Y' APPLIED_FLAG
      ,PS_INV.CUSTOMER_ID CUSTOMER_ID
      ,SUBSTRB(PARTY.PARTY_NAME
              ,1
              ,50) CUSTOMER_NAME
      ,CUST.ACCOUNT_NUMBER CUSTOMER_NUMBER
      ,PS_INV.TRX_NUMBER TRX_NUMBER
      ,TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
                       ,-1
                       ,NULL
                       ,PS_INV.TERMS_SEQUENCE_NUMBER)) INSTALLMENT
      ,APP.AMOUNT_APPLIED AMOUNT_APPLIED
      ,NVL(APP.AMOUNT_APPLIED_FROM
          ,APP.AMOUNT_APPLIED) AMOUNT_APPLIED_FROM
      ,APP.TRANS_TO_RECEIPT_RATE TRANS_TO_RECEIPT_RATE
      ,TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
                       ,-1
                       ,NULL
                       ,NVL(APP.EARNED_DISCOUNT_TAKEN
                           ,0) + NVL(APP.UNEARNED_DISCOUNT_TAKEN
                                    ,0))) DISCOUNT
      ,APP.EARNED_DISCOUNT_TAKEN DISCOUNTS_EARNED
      ,APP.UNEARNED_DISCOUNT_TAKEN DISCOUNTS_UNEARNED
      ,PS_INV.DISCOUNT_TAKEN_EARNED DISCOUNT_TAKEN_EARNED
      ,PS_INV.DISCOUNT_TAKEN_UNEARNED DISCOUNT_TAKEN_UNEARNED
      ,TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
                       ,-1
                       ,NULL
                       ,PS_INV.AMOUNT_DUE_REMAINING)) AMOUNT_DUE_REMAINING
      ,DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
             ,-1
             ,TO_DATE(NULL)
             ,PS_INV.DUE_DATE) DUE_DATE
      ,PS_INV.STATUS STATUS
      ,PS_INV.TERM_ID TERM_ID
      ,DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
             ,-1
             ,NULL
             ,L_CLASS.MEANING) TRX_CLASS_NAME
      ,PS_INV.CLASS TRX_CLASS_CODE
      ,CTT.NAME TRX_TYPE_NAME
      ,CTT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
      ,CT.TRX_DATE TRX_DATE
      ,SU.LOCATION LOCATION_NAME
      ,CT.BILL_TO_SITE_USE_ID BILL_TO_SITE_USE_ID
      ,TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
                       ,-1
                       ,NULL
                       ,TRUNC(APP.APPLY_DATE) - TRUNC(PS_INV.DUE_DATE))) DAYS_LATE
      ,CTL.LINE_NUMBER LINE_NUMBER
      ,CTL.CUSTOMER_TRX_LINE_ID CUSTOMER_TRX_LINE_ID
      ,APP.APPLY_DATE APPLY_DATE
      ,APP.GL_DATE GL_DATE
      ,APP.GL_POSTED_DATE GL_POSTED_DATE
      ,APP.REVERSAL_GL_DATE REVERSAL_GL_DATE
      ,PS_INV.EXCHANGE_RATE EXCHANGE_RATE
      ,DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
             ,-1
             ,CR.CURRENCY_CODE
             ,PS_INV.INVOICE_CURRENCY_CODE) INVOICE_CURRENCY_CODE
      ,PS_INV.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
      ,PS_INV.AMOUNT_IN_DISPUTE AMOUNT_IN_DISPUTE
      ,PS_INV.AMOUNT_LINE_ITEMS_ORIGINAL AMOUNT_LINE_ITEMS_ORIGINAL
      ,TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
                       ,-1
                       ,NULL
                       ,PS_INV.ACCTD_AMOUNT_DUE_REMAINING)) ACCTD_AMOUNT_DUE_REMAINING
      ,APP.ACCTD_AMOUNT_APPLIED_TO ACCTD_AMOUNT_APPLIED_TO
      ,APP.ACCTD_AMOUNT_APPLIED_FROM ACCTD_AMOUNT_APPLIED_FROM
      ,TO_NUMBER(DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
                       ,-1
                       ,NULL
                       ,APP.ACCTD_AMOUNT_APPLIED_FROM -
                        NVL(APP.ACCTD_AMOUNT_APPLIED_TO
                           ,APP.ACCTD_AMOUNT_APPLIED_FROM))) EXCHANGE_GAIN_LOSS
      ,PS_INV.DISCOUNT_REMAINING DISCOUNT_REMAINING
      ,ARPT_SQL_FUNC_UTIL.GET_TERM_DETAILS(PS_INV.TERM_ID
                                          ,'CALC_DISCOUNT_ON_LINES_FLAG') CALC_DISCOUNT_ON_LINES_FLAG
      ,ARPT_SQL_FUNC_UTIL.GET_TERM_DETAILS(PS_INV.TERM_ID
                                          ,'PARTIAL_DISCOUNT_FLAG') PARTIAL_DISCOUNT_FLAG
      ,CTT.ALLOW_OVERAPPLICATION_FLAG ALLOW_OVERAPPLICATION_FLAG
      ,CTT.NATURAL_APPLICATION_ONLY_FLAG NATURAL_APPLICATION_ONLY_FLAG
      ,CTT.CREATION_SIGN CREATION_SIGN
      ,PS_INV.PAYMENT_SCHEDULE_ID APPLIED_PAYMENT_SCHEDULE_ID
      ,APP.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE
      ,APP.USSGL_TRANSACTION_CODE_CONTEXT USSGL_TRANSACTION_CODE_CONTEXT
      ,CT.PURCHASE_ORDER PURCHASE_ORDER
      ,CT.DOC_SEQUENCE_ID TRX_DOC_SEQUENCE_ID
      ,CT.DOC_SEQUENCE_VALUE TRX_DOC_SEQUENCE_VALUE
      ,BS.NAME TRX_BATCH_SOURCE_NAME
      ,PS_INV.AMOUNT_ADJUSTED AMOUNT_ADJUSTED
      ,PS_INV.AMOUNT_ADJUSTED_PENDING AMOUNT_ADJUSTED_PENDING
      ,PS_INV.AMOUNT_LINE_ITEMS_REMAINING AMOUNT_LINE_ITEMS_REMAINING
      ,PS_INV.FREIGHT_ORIGINAL FREIGHT_ORIGINAL
      ,PS_INV.FREIGHT_REMAINING FREIGHT_REMAINING
      ,PS_INV.RECEIVABLES_CHARGES_REMAINING RECEIVABLES_CHARGES_REMAINING
      ,PS_INV.TAX_ORIGINAL TAX_ORIGINAL
      ,PS_INV.TAX_REMAINING TAX_REMAINING
      ,PS_INV.SELECTED_FOR_RECEIPT_BATCH_ID SELECTED_FOR_RECEIPT_BATCH_ID
      ,APP.RECEIVABLE_APPLICATION_ID RECEIVABLE_APPLICATION_ID
      ,APP.SECONDARY_APPLICATION_REF_ID SECONDARY_APPLICATION_REF_ID
      ,APP.COMMENTS
      ,CTT.ATTRIBUTE10 TRANSACTION_CATEGORY
      ,APP.RECEIVABLES_TRX_ID RECEIVABLES_TRX_ID
      ,DECODE(PS_INV.CLASS
             ,'PMT'
             ,ART.NAME
             ,DECODE(SIGN(APP.APPLIED_PAYMENT_SCHEDULE_ID)
                    ,-1
                    ,ART.NAME
                    ,NULL)) REC_ACTIVITY_NAME
      ,APP.APPLICATION_REF_TYPE
      ,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING(DECODE(APP.APPLIED_PAYMENT_SCHEDULE_ID
                                                   ,-7
                                                   ,'AR_PREPAYMENT_TYPE'
                                                   ,'APPLICATION_REF_TYPE')
                                            ,APP.APPLICATION_REF_TYPE) APPLICATION_REF_TYPE_MEANING
      ,APP.APPLICATION_REF_ID
      ,APP.APPLICATION_REF_NUM
      ,APP.PAYMENT_SET_ID
      ,APP.APPLICATION_REF_REASON
      ,DECODE(APP.SECONDARY_APPLICATION_REF_ID
             ,NULL
             ,DECODE(PS_INV.CLASS
                    ,'CM'
                    ,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CREDIT_MEMO_REASON'
                                                          ,CT.REASON_CODE)
                    ,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INVOICING_REASON'
                                                          ,CT.REASON_CODE))
             ,ARP_DEDUCTION.GET_TM_ORACLE_REASON(APP.SECONDARY_APPLICATION_REF_ID)) APPLICATION_REF_REASON_MEANING
      ,APP.CUSTOMER_REFERENCE
      ,APP.CUSTOMER_REASON
      ,APP.APPLIED_REC_APP_ID
      ,APP.SECONDARY_APPLICATION_REF_TYPE SECONDARY_APPLICATION_REF_TYPE
      ,APP.SECONDARY_APPLICATION_REF_NUM SECONDARY_APPLICATION_REF_NUM
  FROM AR_RECEIVABLE_APPLICATIONS_ALL APP
      ,AR_CASH_RECEIPTS_ALL           CR
      ,AR_PAYMENT_SCHEDULES_ALL       PS_INV
      ,HZ_CUST_ACCOUNTS               CUST
      ,HZ_PARTIES                     PARTY
      ,RA_CUSTOMER_TRX_ALL            CT
      ,RA_CUST_TRX_TYPES_ALL          CTT
      ,RA_CUSTOMER_TRX_LINES_ALL      CTL
      ,RA_BATCH_SOURCES_ALL           BS
      ,HZ_CUST_SITE_USES_ALL          SU
      ,AR_CONS_INV_ALL                CI
      ,AR_LOOKUPS                     L_CLASS
      ,AR_RECEIVABLES_TRX_ALL         ART
 WHERE APP.DISPLAY = 'Y'
   AND APP.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID
   AND CT.CUSTOMER_TRX_ID(+) = PS_INV.CUSTOMER_TRX_ID
   AND BS.BATCH_SOURCE_ID(+) = CT.BATCH_SOURCE_ID
   AND CTT.CUST_TRX_TYPE_ID(+) = PS_INV.CUST_TRX_TYPE_ID
   AND CUST.CUST_ACCOUNT_ID(+) = PS_INV.CUSTOMER_ID
   AND CUST.PARTY_ID = PARTY.PARTY_ID(+)
   AND SU.SITE_USE_ID(+) = PS_INV.CUSTOMER_SITE_USE_ID
   AND CTL.CUSTOMER_TRX_LINE_ID(+) = APP.APPLIED_CUSTOMER_TRX_LINE_ID
   AND PS_INV.CLASS = L_CLASS.LOOKUP_CODE
   AND L_CLASS.LOOKUP_TYPE = 'INV/CM'
   AND CI.CONS_INV_ID(+) = PS_INV.CONS_INV_ID
   AND APP.APPLIED_PAYMENT_SCHEDULE_ID = PS_INV.PAYMENT_SCHEDULE_ID
   AND ART.RECEIVABLES_TRX_ID(+) = APP.RECEIVABLES_TRX_ID
   AND CR.CASH_RECEIPT_ID = 294508;
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
　发票收款应用界面脚本　
同语言下
可能有用的
专注Oracle EBS贡献的其它代码(66)