Any query for BIP with General ledger + budget + PO detail

Question

Zeneyda HM · Answer

I have these queries

this for accounting detail

SELECT GJB.NAME                     AS LOTE_DE_ASIENTO,       GJH.NAME                     AS ASIENTO,       GJB.DEFAULT_PERIOD_NAME      AS PERIODO_CONTABLE,       GLE.NAME                     AS LIBRO_MAYOR,       XEP.NAME                     AS ENTIDAD_JURIDICA,       TO_CHAR(GJH.DEFAULT_EFFECTIVE_DATE-6/24,'DD-MM-YYYY hh24:mi:ss')    AS FECHA_CONTABLE,       --GJC.DESCRIPTION              AS CATEGORIA,

GJH.POSTING_ACCT_SEQ_VALUE   AS NUMERO_ASIENTO,       GJB.JE_SOURCE                AS ORIGEN,       GJH.JE_CATEGORY              AS CATEGORIA,       B.DESCRIPTION AS NOMBRE_CUENTA_CONTABLE,       GJL.JE_LINE_NUM              AS LINEA,

GCC.SEGMENT1 || '-' ||        GCC.SEGMENT2 || '-' ||       GCC.SEGMENT3 || '-' ||       GCC.SEGMENT4 || '-' ||       GCC.SEGMENT5 || '-' ||       GCC.SEGMENT6 || '-' ||       GCC.SEGMENT7 || '-' ||       GCC.SEGMENT8                 AS CUENTA,

GJL.CURRENCY_CODE            AS DIVISA,              GJL.ENTERED_DR               AS ENTRO_DEBITO,       GJL.ENTERED_CR               AS ENTRO_CREDITO,

GJL.ACCOUNTED_DR             AS CONTABILIZADO_DEBITO,       GJL.ACCOUNTED_CR             AS CONTABILIZADO_CREDITO,       GJL.DESCRIPTION              AS DESCRIPCIONFROM GL_JE_BATCHES GJBINNER JOIN GL_JE_HEADERS GJH ON GJB.JE_BATCH_ID = GJH.JE_BATCH_IDINNER JOIN GL_JE_LINES GJL ON GJH.JE_HEADER_ID = GJL.JE_HEADER_IDINNER JOIN GL_LEDGERS GLE ON GJL.LEDGER_ID = GLE.LEDGER_IDINNER JOIN XLE_ENTITY_PROFILES XEP ON GJH.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_IDINNER JOIN GL_CODE_COMBINATIONS GCC ON GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_IDLEFT JOIN FND_FLEX_VALUES_VL B ON GCC.SEGMENT2 = B.Flex_value

this with the purchase detail

SELECT prh.requisition_number,prl.line_number,prl.line_status,prl.req_po_instance_id,prl.reqs_in_pool_flag,ph.segment1 AS po_number,pl.line_num,--prh.emergency_po_number,prl.reqtopo_automation_failed,prl.reqtopo_auto_failed_reason,--Decode(Nvl2(prh.emergency_po_number, 1, -1), 1, prh.soldto_le_id,--1, NULL) AS SOLDTO_LE_ID,--prh.pcard_id,prl.line_type_id,prl.pcard_flag,prl.requisition_header_id,prl.requisition_line_id,prl.prc_bu_id AS req_line_prc_bu,prh.prc_bu_id ASreq_header_prc_bu,prl.req_bu_id,prl.assigned_buyer_id,-- prl.suggested_buyer_id,-- prl.negotiated_by_preparer_flag,-- prl.negotiation_required_flag,-- prl.source_document_type,-- prl.source_doc_header_id,prl.vendor_id,prl.vendor_site_id,prl.line_location_id,prl.item_id,prl.category_id,prl.item_revision,prl.amount,prl.quantity,prl.unit_price,prl.purchase_basis,prl.matching_basis,--,prl.created_by--,prl.creation_date--,prl.last_UPDATED_by--,prl.last_UPDATE_dateph.document_creation_method,ph.prc_bu_id AS po_prc_bu,ph.document_status,ph.po_header_id,--,ph.created_by--,ph.last_updated_by--,ph.creation_date--,ph.last_update_dateprl.tax_classification_code,prl.item_source-- prl.punchout_catalog_id,-- prl.smart_form_idFROM po_headers_all ph,po_lines_draft_all pl,por_requisition_lines_all prl,por_requisition_headers_all prhWHERE ph.po_header_id(+) = pl.po_header_idAND prl.po_line_id = pl.po_line_id(+)AND prl.requisition_header_id = prh.requisition_header_idAND prh.creation_date > SYSDATE - 200ORDER BY prl.requisition_header_id,prl.line_number

i would like to have both results in 1 query, how can i join both queries?

MandeepGupta · Answer

https://community.oracle.com/products/oracleanalytics/discussion/26200/any-query-for-bip-with-general-ledger-budget-po-detail

Welcome to Oracle analytics query.

It will be helpful for community members if you can share some more details on what exact attributes you are looking for. You just have mentioned the module names which have too many tables/objects/columns.

Thanks.