2021年2月26日 星期五

Oracle EBS: 處理未產生分錄的交易(Return to supplier)

因倉庫重複作業, 造成return兩次, 第二次分錄未產生, 且rcv_transactions已有交易, mtl_material_transactions無交易(因第二次已無庫存可退, 導致卡帳)


詢問oracle support後, 有提供datafix補rcv_transactions的分錄(df_missing_rrsl_r12_Org.sql).


1.需確認以下兩個table無對應資料(其中一個有值則無法重新產生分錄):


select *

from RCV_RECEIVING_SUB_LEDGER

where RCV_TRANSACTION_ID= 93197645  --Return to Supplier


select * 

 from rcv_accounting_events

 where rcv_transaction_id = 93197645  --Return to Supplier


2.刪除rcv_transactions_interface return卡帳資料

3.待倉庫部門退料後,重吃MTL_MATERIAL_TRANSACTIONS_TEMP卡帳資料

4.執行datafix產生return分錄


-- df_missing_rrsl_r12_Org.sql

set serveroutput on size 1000000

set linesize 1500

set pagesize 10000

set verify off

SET TRIMSPOOL ON

SET TRIMOUT   ON

spool cr_receipt_accounting_log.lst

DECLARE

   L_RETURN_STATUS    VARCHAR2(1);

   L_MSG_DATA         VARCHAR2(2000);

   L_MSG_COUNT        NUMBER;

   L_PARENT_TRX_ID    NUMBER;

   l_stmt_num         NUMBER;

   L_PO_HEADER_ID     NUMBER;

   L_CONSIGNED_FLAG   VARCHAR2(2);


CURSOR candidate_txns IS

SELECT T1.*

  FROM RCV_TRANSACTIONS T1,

       MTL_PARAMETERS mp

 WHERE t1.organization_id = mp.ORGANIZATION_ID

  AND  NVL(mp.process_enabled_flag, 'N') <> 'Y'

  AND t1.transaction_type in ('RECEIVE','RETURN TO VENDOR','CORRECT')

  AND NOT (t1.transaction_type = 'CORRECT' and t1.destination_type_code = 'INVENTORY')

  AND NOT (t1.transaction_type = 'CORRECT' and t1.destination_type_code = 'EXPENSE')

  AND NOT (t1.transaction_type = 'CORRECT' and t1.destination_type_code = 'SHOP FLOOR')

  AND EXISTS

        (SELECT 1

           FROM PO_LINE_LOCATIONS_ALL poll

          WHERE ACCRUE_ON_RECEIPT_FLAG='Y'

            AND POLL.LINE_LOCATION_ID =T1.PO_LINE_LOCATION_ID)

   AND NVL(COMMENTS,'!') NOT IN ('OPM RECEIPT','OPM Receipt Correction')

   AND NOT EXISTS

           (SELECT 1

              FROM RCV_RECEIVING_SUB_LEDGER T2

             WHERE T2.RCV_TRANSACTION_ID=T1.TRANSACTION_ID)

   AND EXISTS ( SELECT 'GL and PO has OPEN PERIOD for the transaction_date'

           FROM gl_period_statuses GL_PER,

        gl_period_statuses PO_PER,

financials_system_params_all FSP

  WHERE    GL_PER.application_id = 101

    AND    PO_PER.application_id = 201

            AND    PO_PER.set_of_books_id    = FSP.set_of_books_id

            AND    trunc(PO_PER.start_date)       <=  t1.transaction_date

            AND    trunc(PO_PER.end_date)+0.99999 >= t1.transaction_date

            AND    PO_PER.closing_status     = 'O'

            AND    PO_PER.period_name        = GL_PER.period_name

            AND    GL_PER.set_of_books_id    = FSP.set_of_books_id

            AND    trunc(GL_PER.start_date)       <=  t1.transaction_date

            AND    trunc(GL_PER.end_date)+0.99999 >=  t1.transaction_date

            AND    GL_PER.closing_status in ('O', 'F')

             )

UNION ALL

SELECT T1.*

  FROM RCV_TRANSACTIONS T1,

       po_distributions_all POD,

       MTL_PARAMETERS mp

 WHERE t1.organization_id = mp.ORGANIZATION_ID

  AND  NVL(mp.process_enabled_flag, 'N') <> 'Y'

  AND T1.transaction_type in ('DELIVER','RETURN TO RECEIVING','CORRECT')

   AND NOT (T1.transaction_type = 'CORRECT' AND T1.destination_type_code = 'RECEIVING')

   AND T1.po_distribution_id = POD.po_distribution_id

   AND pod.accrue_on_receipt_flag = 'Y'

   AND pod.destination_type_code = 'EXPENSE'

   AND EXISTS

        (SELECT 1

           FROM PO_LINE_LOCATIONS_ALL poll

          WHERE ACCRUE_ON_RECEIPT_FLAG='Y'

            AND POLL.LINE_LOCATION_ID =T1.PO_LINE_LOCATION_ID)

   AND NVL(COMMENTS,'!') NOT IN ('OPM RECEIPT','OPM Receipt Correction')

   AND NOT EXISTS

           (SELECT 1

              FROM RCV_RECEIVING_SUB_LEDGER T2

             WHERE T2.RCV_TRANSACTION_ID=T1.TRANSACTION_ID)

   AND EXISTS ( SELECT 'GL and PO has OPEN PERIOD for the transaction_date'

           FROM gl_period_statuses GL_PER,

            gl_period_statuses PO_PER,

        financials_system_params_all FSP

      WHERE    GL_PER.application_id = 101

        AND    PO_PER.application_id = 201

            AND    PO_PER.set_of_books_id    = FSP.set_of_books_id

            AND    trunc(PO_PER.start_date)       <=  t1.transaction_date

            AND    trunc(PO_PER.end_date)+0.99999 >= t1.transaction_date

            AND    PO_PER.closing_status     = 'O'

            AND    PO_PER.period_name        = GL_PER.period_name

            AND    GL_PER.set_of_books_id    = FSP.set_of_books_id

            AND    trunc(GL_PER.start_date)       <=  t1.transaction_date

            AND    trunc(GL_PER.end_date)+0.99999 >=  t1.transaction_date

            AND    GL_PER.closing_status in ('O', 'F')

            );


BEGIN


    mo_global.init('PO');

    mo_global.set_policy_context('S',&operating_unit_id);

    dbms_output.put_line('mo global set successfully');


l_stmt_num := 10;

   FOR c_rec IN candidate_txns LOOP

    l_stmt_num := 20;

      dbms_output.put_line('Receipt_TXN  : '||c_rec.transaction_id);

      dbms_output.put_line('PO Header ID : '||c_rec.po_header_id);


      SELECT NVL(consigned_consumption_flag, 'N')

      INTO   l_consigned_flag

      FROM   PO_HEADERS_all

      WHERE  po_header_id = c_rec.po_header_id;

        l_stmt_num := 30;

      /*dbms_output.put_line('Consigned PO : '||l_consigned_flag);*/


      IF (l_consigned_flag = 'N') THEN

      l_stmt_num := 40;

    RCV_AccrualAccounting_GRP.Create_AccountingEvents(

  p_api_version        => 1.0,

  p_source_type        => 'RECEIVING',

  p_rcv_transaction_id => c_rec.transaction_id,

  p_direct_delivery_flag=> null,

  p_gl_group_id      => null,

  x_return_status      => l_return_status,

  x_msg_count          => l_msg_count,

  x_msg_data           => l_msg_data

  );

               l_stmt_num := 50;

      IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN

        l_stmt_num := 60;

          dbms_output.put_line('Error ocurred while creating Accounting for TXN: '|| TO_CHAR(c_rec.transaction_id));

                  RAISE FND_API.g_exc_unexpected_error ;

      ELSE

          l_stmt_num := 70;

          dbms_output.put_line('Accounting created sucessfully for Txn : '||TO_CHAR(c_rec.transaction_id));

              END IF;

      END IF; /* consigned */

    END LOOP;

l_stmt_num := 80;

COMMIT;

EXCEPTION

 WHEN FND_API.g_exc_unexpected_error THEN

   dbms_output.put_line('l_msg_count : '||l_msg_count);

   dbms_output.put_line('l_msg_data : '||l_msg_data);

   Rollback;

 WHEN OTHERS THEN

   dbms_output.put_line ( 'some error has occurred at stmt no '||l_stmt_num||'  '||SQLCODE||'  '||SQLERRM);

   Rollback;

END;

/

spool off




沒有留言:

張貼留言

注意:只有此網誌的成員可以留言。