This discussion is archived
9 Replies Latest reply: Aug 6, 2012 11:01 AM by 870151 RSS

payables report QUERY

870151 Newbie
Currently Being Moderated
HI

I NEED TO GET THE QUERY THE QUERY OF REPORT LIST OF OPEN ACCOUNTS BALANCE,
I searched the forums but have not found anything,
IS A REPORT OF ACCOUNTS PAYABLE AND MUST Bring me 8 SEGMENTS OF THE ACCOUNTS OF GL_CODE_COMBINATIONS TABLE WITH BALANCE ACCOUNTING, ORIGINAL AMOUNT AND rEMAINING AMOUNT

FOR EXAMPLE, This is the report generated and which need to get the query that I bring these values


Definición de Informe: GFE_BALANCE
Origen de Asiento: Cuentas a Pagar
Fecha Inicial: 1/06/2012
A Partir de: 30/06/2012

Cuenta Saldo Contable Importe Original Importe Restante
(account) (balancing) (original amount) (remaining)
16-042-2001-2601-0000-00-0000-000 34058564.10 3494865.77 -2949034.67
16-050-2001-2601-0000-00-0000-000 37814992.82 0.00 -2392616.50


I apologize for my English as I can, I hope I helped.
  • 1. Re: payables report QUERY
    Octopus Rex Expert
    Currently Being Moderated
    Hello.

    Which is the version of your APPS?
    Which is your exact problem? Are you running a standard report and cannot see all segments? Which is the report?

    Octavio
  • 2. Re: payables report QUERY
    870151 Newbie
    Currently Being Moderated
    I need to get the query to the information of the report "Open Account Balances Listing" module AP
    I use version 12 of the eBusinees Suite

    Right now I found the query to get the original amount my but I do not get Remaining Amount

    my query is

    SELECT gcc.segment1
    || '-'
    || gcc.segment2
    || '-'
    || gcc.segment3
    || '-'
    || gcc.segment4
    || '-'
    || gcc.segment5
    || '-'
    || gcc.segment6
    || '-'
    || gcc.segment7
    || '-'
    || gcc.segment8 ACCOUNT,
    Pov.Vendor_Name,
    Pos.Vendor_Site_Code,
    Api.Invoice_Num,
    Api.Terms_Date,
    Apt.Name,
    Aps.Due_Date,
    Aps.Amount_Remaining "cantidad restante",
    Api.Invoice_Amount monto_factura,
    Api.amount_paid monto_pagado,
    Api.discount_amount_taken
    FROM
    Ap_Invoices_All Api,
    Ap_Payment_Schedules_All Aps,
    Ap_Suppliers Pov,
    Ap_Supplier_Sites_All Pos,
    Ap_Terms Apt,
    xla.xla_transaction_entities xte,
    gl_code_combinations gcc,
    (SELECT tb.code_combination_id,
    NVL (tb.applied_to_entity_id, tb.source_entity_id) entity_id,
    SUM (NVL (tb.acctd_rounded_cr, 0)),
    SUM (NVL (tb.acctd_rounded_dr, 0)),
    SUM (NVL (tb.acctd_rounded_cr, 0))
    - SUM (NVL (tb.acctd_rounded_dr, 0)) diff,
    party_id
    FROM xla_trial_balances tb
    WHERE tb.definition_code ='GFE_BALANCE'
    GROUP BY tb.code_combination_id,
    NVL (tb.applied_to_entity_id, tb.source_entity_id),
    tb.party_id
    HAVING SUM (NVL (tb.acctd_rounded_cr, 0)) <>
    SUM (NVL (tb.acctd_rounded_dr, 0))) tb
    Where tb.entity_id = xte.entity_id
    AND xte.source_id_int_1 = Api.invoice_id
    And Api.Invoice_Id = Aps.Invoice_Id
    And Api.Vendor_Id = Pov.Vendor_Id
    And Api.Vendor_Site_Id = Pos.Vendor_Site_Id
    And Api.Terms_Id = Apt.Term_Id
    AND tb.code_combination_id = gcc.code_combination_id
    And Nvl(Aps.Amount_Remaining, 0) != 0
    And Api.Org_Id = :p_org_id--130
    And Api.INVOICE_DATE Between '01/06/2012' And '30/06/2012'
    And Api.Vendor_Id = Nvl('p_id_proveedor', Api.Vendor_Id)
    And Pov.Vendor_Type_Lookup_Code = Nvl('MERCANCIA', Pov.Vendor_Type_Lookup_Code)


    If anyone knows how to get the Remaining Amount would greatly appreciate it
  • 3. Re: payables report QUERY
    Octopus Rex Expert
    Currently Being Moderated
    Hi.

    The amount due remaining should correspond to (from your query):

    SUM (NVL (tb.acctd_rounded_cr, 0)) - SUM (NVL (tb.acctd_rounded_dr, 0)) diff,

    Octavio
  • 4. Re: payables report QUERY
    870151 Newbie
    Currently Being Moderated
    am, and try that, but the value of DIFF is equal to Amount_Remaining AND Invoice_Amount, i don´t know why they are the same values, but still can not find the remaining amount
  • 5. Re: payables report QUERY
    Octopus Rex Expert
    Currently Being Moderated
    Hello.

    The amount of DIFF will be equal to INVOICE_AMOUNT when no payments have been made.

    Octavio
  • 6. Re: payables report QUERY
    870151 Newbie
    Currently Being Moderated
    Yes, you're right, but when I generate the report I get remainig amount, and do not know where you get it, that's the problem : S
  • 7. Re: payables report QUERY
    870151 Newbie
    Currently Being Moderated
    in fact if the ebs shows me that unpaid bills, however, the report reflects a Remaining amount, I thought it is because they have been partially paid, so maybe this taking this Remaining amount of GL, but I do not know.

    the report that generated is "Open Account Balances Listing"
    EBS verison 12
  • 8. Re: payables report QUERY
    Octopus Rex Expert
    Currently Being Moderated
    Hi.

    If the report shows a Remaining Amount diffrent from the Invoice Amount it is because partial payments have been made.
    And yes, in that case, the remaining amount reflects to GL, on the corresponding account defined as Liability Account.

    Octavio
  • 9. Re: payables report QUERY
    870151 Newbie
    Currently Being Moderated
    It was not that,
    I have sought and found how to get the Remaining amount and this is the query:

    (No no era eso, ya encontre la query que trae el remaining amount, y es la siguiente:)

    SELECT RA. ACCOUNTS,
    RA.CODE_COMBINATION_ID,
    RA.Invoice_num,
    NULL SALDO_CONTABLE,
    NULL IMPORTE_ORIGINAL,
    RA.INVOICE_AMOUNT IMPORTE_RESTANTE
    FROM
    (SELECT gcc.segment1
    || '-'
    || gcc.segment2
    || '-'
    || gcc.segment3
    || '-'
    || gcc.segment4
    || '-'
    || gcc.segment5
    || '-'
    || gcc.segment6
    || '-'
    || gcc.segment7
    || '-'
    || gcc.segment8 ACCOUNTS,
    (AI.INVOICE_AMOUNT) INVOICE_AMOUNT, AI.ACCTS_PAY_CODE_COMBINATION_ID CODE_COMBINATION_ID, AI.INVOICE_NUM
    FROM
    AP_INVOICES_ALL AI,
    AP_BATCHES_ALL AB,
    AP_TERMS AT,
    gl_code_combinations gcc
    WHERE (1=1)
    AND AI.BATCH_ID = AB.BATCH_ID(+)
    AND AI.TERMS_ID = AT.TERM_ID(+)
    AND AI.ACCTS_PAY_CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
    AND AI.vendor_id = '1041'
    AND invoice_date between '01/06/2012' and '30/06/2012') RA

    Con esto se puede obtener el ramaining amount mismo que se puede verificar dentro de la EBS 12 en la ruta

    - Estar en la responsabilidad de AP- SUPERVISOR

    Facturas > Consulta > Facturas

    - Ingresar los datos del proveedor que se quiera consutar Y DAR CLIC EN EL BOTON ""CALCULAR SALDO ADEUDADO""

    Automaticamente aparecera otra ventana, seleccionar UNIDAD OPERATIVA y presionar ""CALCULAR""

    ahi se mostrara ell total del monto adeudado si se desean ver el detalle presionar en FACTURAS

    Y LISTO


    ¡¡¡¡¡¡¡¡¡¡¡¡¡PROBLEMA RESUELTO!!!!!!!!!!!!!

    PROBLEM SOLVED!!!!!!!!!!!!!!!!!!!!!!!!!


    ahi esta la query y la forma de corroborarlo para en la EBS, esto para el reporte de "LISTADO DE SALDOS DE CUENTA ABIERTA"

    there is the query and the form of corroboration for the EBS, report of "Open Account Balances Listing"

    Edited by: 867148 on 06-ago-2012 11:00

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points