因倉庫重複作業, 造成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
