1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

AR Invoice Status

Discussion in 'Oracle Apps Technical' started by jagadekara, Nov 21, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I need to display AR Invoice Status(Paid, Fully Paid, UN Paid) through SQL.

    Any ideas?
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    Is there any standard function for this?
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi,

    Haven't seen any standard function for this specific requirement like you have in the following packaged functions:
    - ap_invoices_pkg.get_approval_status
    - ap_invoices_pkg.get_posting_status

    Most probably you'll have to write some custom function based on at least (among other tables) AP_PAYMENT_SCHEDULES_ALL, AP_INVOICES_ALL ...
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Rajen,

    Some where I got this..

    SELECT DECODE(SUM(ARR.AMOUNT_DUE_REMAINING),
    0,
    'Paid',
    NULL,
    'Unpaid',
    'Partially Paid') PAY_STATUS
    FROM RA_CUSTOMER_TRX_PARTIAL_V RCTP, AR_RECEIVABLE_APPLICATIONS_V ARR
    WHERE RCTP.CUSTOMER_TRX_ID = ARR.CUSTOMER_TRX_ID(+)
    AND RCTP.BATCH_SOURCE_ID = 20
    AND RCTP.TRX_NUMBER = 'XXX'
    ;

    But this is not correct.
     
  5. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Thanks Jagadekara !
    Oops ... I again mixed up AP and AR (may be because I've been recently very much engrossed in AP queries :confused:) ... my reply was linked to AP, I'm sure you noticed that.
     
  6. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Yup!..

    No Problem..