EBS opm流程制造业务模块分录查询

在路上~~~~

已于 2024-10-31 16:19:21 修改

阅读量377

点赞数 2

文章标签：制造

于 2024-10-31 15:58:31 首次发布

本文链接：https://blog.csdn.net/weixin_70597203/article/details/143403784

版权

--OPM WIP分录

--OPM WIP分录
SELECT gxv.organization_id,
       gxv.inventory_item_id,
       xah.event_type_code,
       gcc.concatenated_segments,
       SUM(nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0)) xla_net_amt,
       decode(xah.event_type_code, 'RELE', 'WIP_ISSUE', 'CERT', 'WIP_COMP') source_type
FROM   xla.xla_ae_headers PARTITION(gmf) xah,
       xla.xla_ae_lines PARTITION(gmf) xal,
       xla.xla_transaction_entities PARTITION(gmf) xte,
       (SELECT *
        FROM   gmf_xla_extract_headers eh
        WHERE  ((eh.transaction_source_type_id = 5 AND
               eh.transaction_action_id IN (1, 32, 27, 31) AND
               eh.event_class_code = 'BATCH_MATERIAL') OR
               (eh.transaction_source_type_id = 0 AND
               eh.transaction_action_id = 0 AND
               eh.event_class_code = 'BATCH_CLOSE'))
        AND    eh.ledger_id = 2122
        AND    eh.transaction_date >= DATE
         '2024-05-01'
        AND    eh.source_document_id IN
               (SELECT hh.batch_id
                 FROM   gme_batch_header hh
                 WHERE  hh.batch_no = 'GQ0024050801')
        AND    eh.organization_id = 421) gxv,
       gl_code_combinations_kfv gcc,
       gl_ledgers gl
WHERE  xah.ae_header_id = xal.ae_header_id
AND    xah.entity_id = xte.entity_id
AND    xah.application_id = xte.application_id
AND    nvl(xte.source_id_int_1, -99) = gxv.transaction_id
      --AND    (xah.event_type_code = 'RELE' OR xah.event_type_code = 'CERT')
      --AND    xah.period_name = '2023-11'
AND    xah.ledger_id = 2122
AND    xah.ledger_id = gxv.ledger_id
AND    gcc.chart_of_accounts_id = gl.chart_of_accounts_id
AND    gl.ledger_id = xah.ledger_id
AND    gcc.code_combination_id = xal.code_combination_id
      /*AND    (gcc.segment3 LIKE '1403%' OR gcc.segment3 LIKE '1406%')
      AND    xal.accounting_class_code = 'INVENTORY_VALUATION'
      AND    xte.application_id = 555*/
AND    gxv.organization_id = 421
GROUP  BY gxv.organization_id,
          gcc.concatenated_segments,
          gxv.inventory_item_id,
          xah.event_type_code;

--opm 库存分录

SELECT g_request_id,
       m.tran_id,
       mmt.organization_id,
       mmt.inventory_item_id,
       mtt.transaction_type_name,
       m.xla_net_amt,
       m.tran_net_amt
FROM   (SELECT tran_id,
               SUM(xla_net_amt) xla_net_amt,
               SUM(tran_nete_amt) tran_net_amt
        FROM   (SELECT xte.source_id_int_1 tran_id,
                       nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0) xla_net_amt,
                       0 tran_nete_amt
                FROM   xla.xla_ae_headers           PARTITION(cst) xah,
                       xla.xla_ae_lines             PARTITION(cst) xal,
                       xla.xla_transaction_entities PARTITION(cst) xte
                WHERE  xah.ae_header_id = xal.ae_header_id
                AND    xah.entity_id = xte.entity_id
                AND    xah.application_id = xte.application_id
                AND    xah.period_name = '2023-11'
                AND    xah.ledger_id = 2122
                AND    xal.accounting_class_code = 'INVENTORY_VALUATION'
                AND    xte.application_id = 707
                UNION ALL
                SELECT r.trx_hdr_id,
                       0 xla_net_amt,
                       nvl(r.accounted_dr, 0) - nvl(r.accounted_cr, 0) net_amt
                FROM   apps.xla_inv_ael_sl_pac_v r
                WHERE  1 = 1
                AND    r.set_of_books_id = p_ledger_id
                AND    r.acct_line_type_name = '库存估价'
                AND    r.inventory_organization_id = p_organization_id
                AND    r.accounting_date >= to_date(p_period_name, 'yyyy-mm')
                AND    r.accounting_date <
                       add_months(to_date(p_period_name, 'yyyy-mm'), 1))
        GROUP  BY tran_id) m,
       apps.cux_1_inv_transaction mmt,
       apps.mtl_transaction_types mtt
WHERE  1 = 1
AND    mmt.transaction_date >= p_period_start_date
AND    mmt.transaction_date <= p_period_end_date
AND    mmt.organization_id = p_organization_id
AND    xla_net_amt <> tran_net_amt
AND    m.tran_id = mmt.transaction_id
AND    mmt.transaction_type_id = mtt.transaction_type_id;

--opm SO发料

SELECT so.organization_id,
       so.inventory_item_id,
       SUM(nvl(xal.accounted_cr, 0) - nvl(xal.accounted_dr, 0)) * -1 net_amount,
       'SHIP'
FROM   xla.xla_transaction_entities PARTITION(gmf) xte,
       xla.xla_events PARTITION(gmf) xe,
       xla.xla_ae_headers PARTITION(gmf) xah,
       xla_ae_lines PARTITION(gmf) xal,
       gl_code_combinations gcc,
       (SELECT *
        FROM   gmf.gmf_xla_extract_headers eh
        WHERE  (eh.transaction_source_type_id IN (2, 8) /* 2-SO 8-Internal Order */
               AND eh.transaction_action_id IN (1, 21) /* 1-Issue from stores,21-Intransit Shipment */
               )
        OR     (eh.transaction_source_type_id = 12 AND
              eh.transaction_action_id IN (1, 26, 27) /* 1-Issue from stores, 26-Logical Receipt,27-RMA receipt */
              )
        OR     (eh.transaction_source_type_id = 2 /* Sales Order */
              AND eh.transaction_action_id = 36 /* COGS Recognition */
              )
        OR     (eh.transaction_source_type_id IN (2, 8) /* Sales Order and Internal Orders */
              AND eh.transaction_action_id IN (1, 7) /* 1-Issue from stores 7-Logical Issue */
              )
        OR     (eh.transaction_source_type_id = 8 /* Internal Orders */
              AND eh.transaction_action_id = 21 /* Intransit shipment */
              )
        OR     (eh.transaction_source_type_id = 8 /* Internal Orders */
              AND eh.transaction_action_id = 3 /* Direct Xfer */
              )
        OR     (eh.transaction_source_type_id = 7 /* Internal requisition */
              AND eh.transaction_action_id = 15 /* Logical Intransit Receipt */
              )
        OR     (eh.transaction_source_type_id = 13 /* Inventory */
              AND eh.transaction_action_id IN (3, 21, 15) /* 3-Direct organization transfer, 21-Intransit shipment,15-Logical Intransit Receipt */
              )
        AND    eh.ledger_id = 2122
        AND    eh.organization_id = 421) so 
WHERE  xte.ledger_id = 2122
AND    xte.entity_code = 'ORDERMANAGEMENT'
AND    xte.application_id = xe.application_id
AND    xte.entity_id = xe.entity_id
AND    xe.application_id = xah.application_id
AND    xe.event_id = xah.event_id
AND    xah.application_id = xal.application_id
AND    xah.ae_header_id = xal.ae_header_id
AND    gcc.code_combination_id = xal.code_combination_id
AND    xah.period_name = '2023-11'
AND    (gcc.segment3 LIKE '1403%' OR gcc.segment3 LIKE '1406%')
      -- AND    (gcc.segment3 = '6401010101' OR gcc.segment3 = '6401010201')
AND    (xe.event_type_code = 'SO_ISSUE' --销货订单发放：对应销售订单的出库
      --xe.event_type_code = 'COGS_RECOGNITION' --销货成本确认：对应销售订单的出库确认
      OR xe.event_type_code = 'RMA_RECEIPT' --RMA 接收:对应销售订单的退货
      )
AND    xte.source_id_int_1 = so.transaction_id
AND    xte.ledger_id = so.ledger_id
AND    so.organization_id = 421
GROUP  BY so.inventory_item_id,
          so.organization_id;

---opm po采购

SELECT 
       organization_id,
       inventory_item_id,event_type_code,
       xla_net_amt/*,
       source_type*/
FROM   (SELECT gxv.organization_id,
               gxv.inventory_item_id,
               xah.event_type_code,
               SUM(nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0)) xla_net_amt/*,
               decode(xah.event_type_code,
                      'RELE',
                      'WIP_ISSUE',
                      'CERT',
                      'WIP_COMP') source_type*/
        FROM   xla.xla_ae_headers PARTITION(gmf) xah,
               xla.xla_ae_lines PARTITION(gmf) xal,
               xla.xla_transaction_entities PARTITION(gmf) xte,
               (SELECT hh.*
                FROM   gmf_xla_extract_headers hh
                WHERE  hh.entity_code = 'PURCHASING'
                AND    hh.ledger_id = 2122
                AND    hh.organization_id = 421
                AND    hh.transaction_date >= DATE
                 '2023-11-01'
                AND    hh.transaction_date < DATE
                 '2023-12-01'
                AND    hh.transaction_type_id IN (-99, 18, 36)) gxv,
               gl_code_combinations gcc,
               gl_ledgers gl
        WHERE  xah.ae_header_id = xal.ae_header_id
        AND    xah.entity_id = xte.entity_id
        AND    xah.application_id = xte.application_id
        AND    xte.source_id_int_1 = gxv.transaction_id 
        AND    xah.period_name = '2023-11'
        AND    xah.ledger_id = 2122
        AND    xah.ledger_id = gxv.ledger_id
        AND    gcc.chart_of_accounts_id = gl.chart_of_accounts_id
        AND    gl.ledger_id = xah.ledger_id
        AND    gcc.code_combination_id = xal.code_combination_id
        AND    (gcc.segment3 LIKE '1403%' OR gcc.segment3 LIKE '1406%')
        AND    xal.accounting_class_code = 'INVENTORY_VALUATION'
        AND    xte.application_id = 555 
        AND    gxv.organization_id = 421
        GROUP  BY gxv.organization_id,
                  gxv.inventory_item_id,
                  xah.event_type_code);