Oracle Developer's Share

在跑Periodic Cost Distributions Processor的時候發生了以下錯誤

100CSTPAPBR.interorg_cost_txn12ORA-01403: no data found: CSTPALPC.create_dist_entry : 40

透過以下SQL找出了logfile(l9990000.req) 9990000=request_id

SELECT fcr.request_id,
       fcpt.user_concurrent_program_name,
       fcp.concurrent_program_name,
       fcr.phase_code,
       fcr.status_code,
       fcr.actual_start_date,
       fcr.actual_completion_date,
       fcr.completion_text,
       fcr.logfile_name,
       fcr.outfile_name
FROM fnd_concurrent_programs_tl  fcpt,
     fnd_concurrent_programs     fcp,
     fnd_concurrent_requests     fcr
WHERE 1 = 1
      AND fcpt.application_id = fcp.application_id
      AND fcpt.concurrent_program_id = fcp.concurrent_program_id
      AND fcpt.language = USERENV('LANG')
      AND fcpt.application_id = fcr.program_application_id
      AND fcpt.concurrent_program_id = fcr.concurrent_program_id
--      and fcr.request_id = 8599280
      AND fcp.concurrent_program_name = 'CSTPDPPC'

      
最後的訊息如下
Same Cost Group xfer
Direct xfer
No accounting for Expense to Expense or Asset to Asset
Balance_account << 
Balance_account >>
Create_inv_ae_lines >>
Create_acct_lines >>
Transaction: 69865685
Transaction Type: 
Calling Package CSTPAPBR.create_acct_lines ...
CSTPAPBR.Create_acct_lines <<
Event type: 3-3-13
Create_inv_ae_lines <<
Interorg_cost_txn <<
Same Cost Group xfer
Direct xfer
No accounting for Expense to Expense or Asset to Asset
Balance_account << 
Balance_account >>
Create_inv_ae_lines >>
Create_acct_lines >>
Transaction: 69865813
Transaction Type: 
Calling Package CSTPAPBR.create_acct_lines ...
CSTPAPBR.Create_acct_lines <<
Event type: 3-3-13
Create_inv_ae_lines <<
Interorg_cost_txn <<
100CSTPAPBR.interorg_cost_txn12ORA-01403: no data found
CSTPALPC.dyn_proc_call : Error Calling Package
100CSTPAPBR.interorg_cost_txn12ORA-01403: no data found: CSTPALPC.create_dist_entry : 40
Create_Acct_Entry >>> 
CSTPDPPC.dyn_proc_call : Error Calling Package
100CSTPAPBR.interorg_cost_txn12ORA-01403: no data found: CSTPALPC.create_dist_entry : 40
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+

+---------------------------------------------------------------------------+
Executing request completion options...

Output is not being printed because:
The print option has been disabled for this report.

Finished executing request completion options.

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 05-AUG-2019 15:40:15

+---------------------------------------------------------------------------+

在依照Error_msg所指的位置(l_stmt_num = 12)

            o_ae_err_rec.l_err_msg    := 'CSTPAPBR.interorg_cost_txn' || TO_CHAR(l_stmt_num) || SUBSTR(SQLERRM, 1, 180);  

            
發現錯誤位置在第1245行

        l_stmt_num             := 12;                                           /* Bug 2643329 propogation of 2506505 */

        SELECT NVL(expense_account, -1)
        INTO l_exp_account
        FROM mtl_fiscal_cat_accounts
        WHERE legal_entity_id = i_ae_txn_rec.legal_entity_id
              AND cost_type_id = i_ae_txn_rec.cost_type_id
              AND cost_group_id = i_ae_txn_rec.cost_group_id
              AND category_id = i_ae_txn_rec.category_id;

              
在依照上面訊息所對應的transaction_id得出category_id跟其他提交請求時的參數，發現No Data Found

SELECT                                                                                                --i_event_type_id,
      mmt.transaction_action_id,
       mmt.transaction_source_type_id,
       NVL(mmt.transaction_source_id, -1),
       mmt.transaction_type_id,
       NULL,
       mmt.transaction_id,
       mmt.inventory_item_id,
       --                   i_legal_entity,
       --                   i_cost_type_id,
       --                   i_cost_group_id,
       NVL(mmt.periodic_primary_quantity, mmt.primary_quantity),
       mmt.subinventory_code,
       mmt.transfer_organization_id,
       mmt.transfer_subinventory,
       mmt.transfer_transaction_id,
       NVL(mmt.distribution_account_id, -1),
       mmt.currency_code,
       mmt.currency_conversion_type,
       NVL(mmt.currency_conversion_date, transaction_date),
       NVL(mmt.currency_conversion_rate, -1),
       'MTL',
       --                   i_period_id,
       mmt.transaction_date,
       mmt.organization_id,
       NVL(mmt.material_account, -1),
       NVL(mmt.material_overhead_account, -1),
       NVL(mmt.resource_account, -1),
       NVL(mmt.outside_processing_account, -1),
       NVL(mmt.overhead_account, -1),
       DECODE(mmt.flow_schedule, 'Y', 1, 0),
       DECODE(msi.inventory_asset_flag, 'Y', 0, 1),
       mmt.repetitive_line_id,
       NVL(mmt.encumbrance_account, -1),
       NVL(mmt.encumbrance_amount, 0)
FROM mtl_material_transactions  mmt,
     mtl_system_items           msi
WHERE mmt.inventory_item_id = msi.inventory_item_id
      AND mmt.organization_id = msi.organization_id
      AND mmt.transaction_id = 69865813;                                                               --i_transaction_id

SELECT category_id
--            INTO l_ae_txn_rec.category_id
FROM mtl_item_categories mic
WHERE mic.inventory_item_id = 376139                                                    --l_ae_txn_rec.inventory_item_id
      AND mic.organization_id = 106                                                       --l_ae_txn_rec.organization_id
      AND mic.category_set_id = (SELECT category_set_id
                                 FROM mtl_default_category_sets
                                 WHERE functional_area_id = 5);

推斷是某一特定料號的對應Category的費用科目未設定

進系統>> Cost Management >> Periodic Costing >> Setup >> Periodic Account Assignment
新增該料號的Category所指定的科目