1 Reply Latest reply: Aug 7, 2012 5:50 PM by 870151 RSS

    ap_invoices divide by organizations

    870151
      Hi

      ESTA QUERY TRAE LOS DATOS DEL REPORTE "Listado de Saldos de Cuenta Abierta" de la E-business Suite 12 necesito dividirlo en organizaciones

      this query get data of report "Open Account Balances Listing" of EBS 12 and i need divide in organizations from inv


      I have a problem, I have the following query and brings me the intervals I need, however I need to divide them into organizations or regions and not how.

      here is my query of invoices

      SELECT mo.ACCOUNTS,
      mo.code_combination_id,
      mo.Invoice_num,
      NULL SALDO_CONTABLE,
      mo.monto_factura IMPORTE_ORIGINAL,
      NULL IMPORTE_RESTANTE
      FROM
      (SELECT gcc.segment1
      || '-'
      || gcc.segment2
      || '-'
      || gcc.segment3
      || '-'
      || gcc.segment4
      || '-'
      || gcc.segment5
      || '-'
      || gcc.segment6
      || '-'
      || gcc.segment7
      || '-'
      || gcc.segment8 ACCOUNTS,
      gcc.code_combination_id,
      (Api.Invoice_Amount) monto_factura,
      api.vendor_id,
      api.Invoice_num
      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'
      and to_date(tb.gl_date,'dd/mm/rrrr') between to_date('01/06/2012','dd/mm/rrrr') and TO_DATE ('30/06/2012','dd/mm/rrrr')
      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 = 131
      AND Api.Vendor_Id = Nvl('1041', Api.Vendor_Id)
      AND Pov.Vendor_Type_Lookup_Code = Nvl('MERCANCIA', Pov.Vendor_Type_Lookup_Code)) MO
      UNION
      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
      UNION
      SELECT
      c.ACCOUNTS,C.code_combination_id,NULL INVOICE_NUM,
      (SELECT
      DECODE (
      BA.currency_code,
      LED.currency_code,
      DECODE (
      BA.actual_flag,
      'A',
      ( (NVL (BA.begin_balance_dr_beq, 0)
      - NVL (BA.begin_balance_cr_beq, 0))
      + (NVL (BA.period_net_dr_beq, 0)
      - NVL (BA.period_net_cr_beq, 0))),
      ( (NVL (BA.begin_balance_dr, 0)
      - NVL (BA.begin_balance_cr, 0))
      + (NVL (BA.period_net_dr, 0)
      - NVL (BA.period_net_cr, 0)))),
      ( (NVL (BA.begin_balance_dr, 0)
      - NVL (BA.begin_balance_cr, 0))
      + (NVL (BA.period_net_dr, 0) - NVL (BA.period_net_cr, 0)))) monto
      FROM GL_BALANCES BA,
      GL_LEDGERS LED ,
      GL_PERIOD_STATUSES PS
      WHERE BA.period_name = 'JUN-12'
      AND BA.code_combination_id = c.code_combination_id
      AND BA.currency_code = 'MXN'
      AND LED.ledger_id = PS.ledger_id
      AND PS.application_id = 101
      AND BA.ledger_id = PS.ledger_id
      AND BA.period_name = PS.period_name) SALDO_CONTABLE,
      NULL IMPORTE_ORIGINAL,
      NULL IMPORTE_RESTANTE
      FROM
      (SELECT a.accts_pay_code_combination_id code_combination_id,
      gcc.segment1
      || '-'
      || gcc.segment2
      || '-'
      || gcc.segment3
      || '-'
      || gcc.segment4
      || '-'
      || gcc.segment5
      || '-'
      || gcc.segment6
      || '-'
      || gcc.segment7
      || '-'
      || gcc.segment8 ACCOUNTS
      FROM
      (select distinct Api.accts_pay_code_combination_id
      from Ap_Invoices_All api,
      Ap_Suppliers Pov
      where Api.vendor_id =Pov.vendor_id
      and api.gl_date between '01/06/2012' and '30/06/2012'
      and Pov.vendor_name like 'ASTRA%') a,
      gl_code_combinations gcc
      WHERE 1=1 and
      a.accts_pay_code_combination_id = gcc.code_combination_id ) C





      here is my query of organizations



      select par.organization_code,
      org.organization_id,
      org.name,
      org.attribute1,
      org.attribute2
      from hr_all_organization_units org, mtl_parameters par
      where 1=1
      and par.organization_id = org.organization_id
      and org.attribute2 is not null




      I must divide the data in a query 1
      in the organizations of the query 2

      help me please.

      Edited by: 867148 on 06-ago-2012 11:11
        • 1. Re: ap_invoices divide by organizations
          870151
          RESOLVED PROMBLEMA!!!

          Hello

          And found the answer to divide ap_invoinces organization_id organizations or the query is as follows.

          select POS.SHIP_TO_LOCATION_ID, LA.INVENTORY_ORGANIZATION_ID, LA.LOCATION_CODE
          from hr_locations_all LA,
          Ap_Supplier_Sites_All Pos
          where LA.SHIP_TO_LOCATION_ID = POS.SHIP_TO_LOCATION_ID
          and POS.SHIP_TO_LOCATION_ID = 49060

          AP_INVOICES joins Ap_Supplier_Sites_All and to the same with hr_locations_all


          here have remaining amount divide by origanization_id
          in the end my query is like this:

          SELECT RA. ACCOUNTS,
          RA.CODE_COMBINATION_ID,
          RA.VENDOR_NAME,
          RA.INVOICE_ID,
          RA.INVOICE_NUM,
          RA.INVOICE_AMOUNT IMPORTE_RESTANTE,
          LA.INVENTORY_ORGANIZATION_ID ORGANIZATION_ID,
          LA.DESCRIPTION DESCRIPCION_ORGANIZATION
          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,
          AI.INVOICE_ID,
          POS.SHIP_TO_LOCATION_ID,
          POV.VENDOR_NAME
          FROM
          AP_INVOICES_ALL AI,
          AP_BATCHES_ALL AB,
          AP_TERMS AT,
          gl_code_combinations gcc,
          Ap_Suppliers Pov,
          Ap_Supplier_Sites_All Pos
          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_Site_Id = Pos.Vendor_Site_Id
          AND AI.Vendor_Id = Pov.Vendor_Id
          --AND AI.vendor_id = '1041'
          AND invoice_date between '01/06/2012' and '30/06/2012') RA,
          hr_locations_all LA
          WHERE (1=1)
          AND LA.SHIP_TO_LOCATION_ID = RA.SHIP_TO_LOCATION_ID
          AND LA.INVENTORY_ORGANIZATION_ID = 2210



          RESOLVED PROMBLEMA!!!