Wednesday, 26 June 2013

Oracle note to Inventory-SLA-GL Reconcilation

Details
This white paper aims at providing troubleshooting guide for the Inventory period closing activities and Gl reconciliation issues which is faced after the Inventory period is closed.
Intention of the white paper is to provide the effective guidelines and some handful scripts which can be used in checking various aspects of period closing as well as Reconciliation between sub-ledger and General Ledger (GL).
Following Pre-checks are important before attempting to reconcile the value at Sub-ledger for a given account code combination with the value of that account code combination reflecting in GL.

The scripts mentioned are not applicable for Periodic Costing System (PAC).
This is applicable for R12 using Standard and/or Average Costing.
Suitable for Oracle seeded Account Derivation Rules (ADR).

NOTE : The script contained in the attachment section is being updated periodically. Please download it from Metalink every time you need to run it in order to have the latest version.
Summary
 1. All Material/WIP transactions should be costed.
This is the primary step in the process of Inventory Period close and ultimately in Reconciliation. If there are any errors in the respective tables or Cost worker errors those need to be resolved. To check for these errors, diagnostics scripts are available,
Please refer to the  Note 603657.1 -Error/Uncosted/Pending Material Transactions
2. Create Accounting-Cost Management request.
After checking all the transactions for the concerned Inventory period are costed, Create Accounting-Cost Management concurrent request should be run to transfer all the transactions to GL.
This concurrent request should get successfully completed.
For request parameters and the explanations for each of the parameters and the associated script :
Please refer to the Note 755943.1 -Transfer Inventory Transaction Into General Ledger through Subledger Accounting SLA Flow
 3. Inventory Period should be closed.
Once Create Accounting-Cost Management concurrent request is completed successfully, Inventory period should be closed. While closing the Inventory Period, a report will get triggered called as "Period Close Reconciliation Report".
This concurrent program and report is used to create summarized transaction records. It displays the differences between accounted value and inventory in the Discrepancy column.
Following are key columns in Period Close Reconciliation Report:
Accounted Value: The valuation when calculated using previous adjusted summarization data + distribution information from MTA (Mtl_transaction_accounts).
On-hand Value: The valuation when calculated using current values from MOQ - quantities and costs from MMT. (Mtl_material_transactions).
Discrepancy: The difference between Account Value and On-hand Value.
Once the Period Close Reconciliation report completes normal, please check the report values and especially Discrepancy column. If there is a difference, this is the first indicator of mismatch in the Sub-ledger and Gl. In R12, you can run the Period Close reconciliation report for the closed period as well.
For more discussions on the Period Close Reconciliation Report,
Please refer to the  Note 295182.1 -Period Close Reconciliation Report (CSTRPCRE)
 4.BackdatedTransactions.
Timely closing of Inventory period will prevent the backdated transactions which will have its impact on Inventory valuation at the sub-ledger level as well as in the Gl. This is the primary factor which creates a difference in Sub-ledger and Gl.
Please refer to the Note 361597.1<strong-How Backdated Transactions Impact on Inventory Reports and GL Value
 5. Mismatch like MMT-MOQD and MMT-CQL.
There is mismatch in quantities in different sub-ledger tables like Mtl_material_transactions (MMT) and Mtl_on_hand_quantities (MOQ). This can also lead to sub-ledger level difference and discrepancy in Period close reconciliation report.
For MMT-MOQ mismatch and the resolution thereof
Please refer to the Note  279205.1 -Find Mismatch Between MTL_MATERIAL_TRANSACTIONS (MMT) and MTL_ONHAND_QUANTITIES_DETAIL
 In average costing Environment, there can be MMT-CQL mismatch,  
Please refer to the Note  378348.1 which provides txn.sql to identify MMT-CQL mismatch.
 6. No negative ledger Id's exists at the legal entity level.
This is the next level check in the SLA. If there are negative ledger id’s for the gl batches then these needs to be rectified and resolved. There is a diagnostics and Data fix which is available to resolve.
Please refer to the Note 883557.1 -How To Avoid and Fix Corruption in Data Transfer from SLA - Negative Ledger_ID, Not Reached GL, Duplicate in GL
 7. Sub-ledger Period Close Exception Report.
From Cost Management SLA Responsibility, Sub-ledger period close exception report states the entities/events which are errored while transferring to Gl. This report is very handy so to know the first level check whether there are any transactions not transferred to Gl.
8. From General Ledger(GL) responsibility, Account Analysis Sub-Ledger report 180 Characters report will be useful to get the data for the account code combination.
9. Recon_diag.sql
After completing the above pre-checks, If there is difference in the GL and Inventory, final step is to start the reconciliation process.
Recon_diag.sql is attatched.
In Recon_diag.sql there are 5 input values which need to be provided.
1)     Org code= The inventory organization for which reconciliation activity is going on.
2)      Reference account = the account code combination in question for which Gl and sub-ledger values are not matching.
3)      from date = The inventory period, or date in which value mismatch is there.
4)     to date= The inventory period, or date in which value mismatch is there.
5)     Ledger_id = Enter the Ledger_id of the default ledger.  This can be obtained from the gl_ledgers  table.

(For 3 & 4 , pl run the script on monthly basis. Ideally if you are reconciling the April-2012 period, then condition would be
from date=01-apr-2012 to date=30-apr-2012)

After running the Recon_diag.sql two text files will be created in users default locations.
File names will be SLA_GL_RECON.TXT and INV_SLA_RECON.TXT
 The output will gather data from the Sub-ledger tables like Mtl_transaction_accounts and Wip_transaction_accounts in INV_SLA_RECON.TXT.

Data from SLA and GL tables like Xla_transaction_entities, Xla_Ae_Headers and Xla_ae_lines   and Gl_je_lines, GL_Je_batches, Gl_import_references is gathered in SLA_GL_RECON.TXT.

Financials and India Tax accounting Entries with General Ledger

Step 1

Import all the records pending in interface table and ensure that there are no pending transactions in the interface. Compare the
GL account balances with the India Localization sub-ledger balances to verify whether the balances are tallying. The following are the India
Localization reports that could be used for this purpose: 
1. India Receiving account distrubution report.
2. India Receiving accounts report.
3. Accrual re-build reconcillation report.
4. India cenvat monthly return inputs and capital goods.
5. India cenvat monthly abstract report.
 Step 2 
If any differences are observed in these balances, following queries should be run to find out the exceptions.
The list of queries are given below:

Tax accounting entries generated at the time of Receipt:

1.Query to identify receipts for which multiple entries are generated with Transaction Type = 'RECEIVE'

select receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr, count(*) cnt,
sum(nvl(entered_cr,0)) - avg(nvl(entered_cr,0)) "Excess Credit",
sum(nvl(entered_dr,0))-avg(nvl(entered_dr,0)) "Excess Debit" ,
gcc.concatenated_segments
from JAI_RCV_JOURNAL_ENTRIES a,gl_code_combinations_kfv gcc
where 0 < (
select count(*)
from JAI_RCV_JOURNAL_ENTRIES b
where a.transaction_id=b.transaction_id
and a.code_combination_id=b.code_combination_id
and a.acct_nature=b.acct_nature
and a.acct_type=b.acct_type
and nvl(a.entered_cr,0) = nvl(b.entered_cr,0)
and nvl(a.entered_dr,0) = nvl(b.entered_dr,0)
and a.rowid <> b.rowid
)
and a.period_name='&period_name'
and a.organization_code='&organization_code'
and a.transaction_type ='RECEIVE'
and a.code_combination_id=&CCID and gcc.code_combination_id = a.code_combination_id
group by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr,gcc.concatenated_segments
order by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr

2.Query to identify total tax amount generated at the time of Receiving

SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RECEIVE'
AND TRUNC(E.CREATION_DATE)
BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'

3.Query to identify total tax amount generated at the time of delivery

SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='DELIVER'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'

4.Query to identify total modvat amount generated for CENVAT entries


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.ACCT_NATURE ='MODVAT'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'

5.Query to identify multiple entries populated into mtl_transaction_accounts (Standard Costing Organization) when material is delivered


Select
b.transaction_id,b.reference_account,b.base_transaction_value,count(*),
nvl(sum(base_transaction_value),0) - nvl(avg(base_transaction_value),0) "Excess Amount Passed"
from mtl_transaction_accounts b, org_acct_periods c, mtl_material_transactions a
where 0 < (select count(1)
from mtl_transaction_accounts d
where b.transaction_id = d.transaction_id
and b.reference_account = d.reference_account
and b.organization_id = d.organization_id
and b.request_id is null
)
and a.transaction_id = b.transaction_id
and a.organization_id = b.organization_id
and b.reference_account = &code_id
and b.request_id is null
and a.acct_period_id = c.acct_period_id
and a.organization_id = c.organization_id
and c.period_name = '&PERIOD_NAME'
and c.organization_id = &org_id
group by b.transaction_id,b.reference_account,b.base_transaction_value
having nvl(sum(base_transaction_value),0) - nvl(avg(base_transaction_value),0) <> 0
order by b.transaction_id
  
6.Return to Receiving Entries: Query to identify multiple entries generated for Return to Receiving entries
select receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr, count(*) cnt,
sum(nvl(entered_cr,0)) - avg(nvl(entered_cr,0)) "Excess Credit",
sum(nvl(entered_dr,0))-avg(nvl(entered_dr,0)) "Excess Debit" ,
gcc.concatenated_segments
from JAI_RCV_JOURNAL_ENTRIES a,gl_code_combinations_kfv gcc
where 0 < (
select count(*)
from JAI_RCV_JOURNAL_ENTRIES b
where a.transaction_id=b.transaction_id
and a.code_combination_id=b.code_combination_id
and a.acct_nature=b.acct_nature
and a.acct_type=b.acct_type
and nvl(a.entered_cr,0) = nvl(b.entered_cr,0)
and nvl(a.entered_dr,0) = nvl(b.entered_dr,0)
and a.rowid <> b.rowid
)
and a.organization_code='&organization_code'
and gcc.code_combination_id = a.code_combination_id
and a.TRANSACTION_TYPE ='RETURN TO RECEIVING'
and trunc(a.creation_date) between '&from_date' and '&to_date'
group by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr,gcc.concatenated_segments
order by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr

7.Query to identify total tax amount generated at the time of Return to Receipt

SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RETURN TO RECEIVING'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'

8.Return to Vendor Entries: Query to identify multiple entries generated for Return to vendor entries
  
select receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr, count(*) cnt,
sum(nvl(entered_cr,0)) - avg(nvl(entered_cr,0)) "Excess Credit",
sum(nvl(entered_dr,0))-avg(nvl(entered_dr,0)) "Excess Debit" ,
gcc.concatenated_segments
from JAI_RCV_JOURNAL_ENTRIES a,gl_code_combinations_kfv gcc
where 0 < (
select count(*)
from JAI_RCV_JOURNAL_ENTRIES b
where a.transaction_id=b.transaction_id
and a.code_combination_id=b.code_combination_id
and a.acct_nature=b.acct_nature
and a.acct_type=b.acct_type
and nvl(a.entered_cr,0) = nvl(b.entered_cr,0)
and nvl(a.entered_dr,0) = nvl(b.entered_dr,0)
and a.rowid <> b.rowid
)
and a.period_name='&period_name'
and a.organization_code='&organization_code'
and gcc.code_combination_id = a.code_combination_id
and a.transaction_type ='RETURN TO VENDOR'
and a.code_combination_id=&CCID
group by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr,gcc.concatenated_segments
order by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr

9.Query to identify total tax amount generated at the time of Return to Vendor
SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RETURN TO VENDOR'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'

10.Query to find the Modvat entries which hit PLA for RTV when the PLA pref =1

select receive.organization_id "Organization",
recLine.inventory_item_id "Item Id",
itemMaster.item_class "Item Class",
receive.transaction_id "Receive Transaction id",
rtv.transaction_id "Return Transaction id",
receive.quantity "Receive Qty",
rtv.quantity "Returned Quantity",
pla.dr_basic_ed "PLA amount"
from
rcv_transactions receive,
rcv_transactions rtv,
JAI_RCV_LINES recLine,
JAI_INV_ITM_SETUPS itemMaster,
JAI_CMN_RG_PLA_TRXS pla
where
receive.transaction_type = 'RECEIVE'
and rtv.transaction_type = 'RETURN TO VENDOR'
and receive.shipment_line_id = recLine.shipment_line_id
and rtv.shipment_line_id = recLine.shipment_line_id
and receive.shipment_line_id = rtv.shipment_line_id
and receive.organization_id = rtv.organization_id
and rtv.organization_id = &organization_id
and recLine.inventory_item_id = itemMaster.inventory_item_id
and recLine.organization_id = itemMaster.organization_id
and rtv.transaction_date between '&date_start' and '&date_end'
and pla.ref_document_id = rtv.transaction_id
and pla.transaction_id = 19
order by 1, 2

11.Query to identify multiple entries generated at time of Receipt for AP Accrual account

select receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr, count(*) cnt,
sum(nvl(entered_cr,0)) - avg(nvl(entered_cr,0)) "Excess Credit",
sum(nvl(entered_dr,0))-avg(nvl(entered_dr,0)) "Excess Debit" ,
gcc.concatenated_segments
from JAI_RCV_JOURNAL_ENTRIES a,gl_code_combinations_kfv gcc
where 0 < (
select count(*)
from JAI_RCV_JOURNAL_ENTRIES b
where a.transaction_id=b.transaction_id
and a.code_combination_id=b.code_combination_id
and a.acct_nature=b.acct_nature
and a.acct_type=b.acct_type
and nvl(a.entered_cr,0) = nvl(b.entered_cr,0)
and nvl(a.entered_dr,0) = nvl(b.entered_dr,0)
and a.rowid <> b.rowid
)
and a.period_name='&period_name'
and a.organization_code='&organization_code'
and gcc.code_combination_id = a.code_combination_id
and a.TRANSACTION_TYPE ='RECEIVE'
and a.code_combination_id = &CCID and trunc(a.creation_date) between '&p_date_from' and '&p_date_to'
group by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr,gcc.concatenated_segments
order by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr

12.Query to identify total tax amount genearted for AP Accrual account at the time of Receipt
SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RECEIVE'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'
AND E.CODE_COMBINATION_ID =&CCID

13.Query to identify total tax amount generated for AP Accrual at the time of Return to Vendor

SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RETURN TO VENDOR'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'
AND E.CODE_COMBINATION_ID =&CCID
  
14.Sum of Credit not equal to Sum of Debit for Return To Vendor transactions

SELECT NVL(SUM(ENTERED_CR),0), NVL(SUM(ENTERED_DR),0),RECEIPT_NUM
FROM JAI_RCV_JOURNAL_ENTRIES
WHERE TRANSACTION_TYPE='RETURN TO VENDOR'
GROUP BY RECEIPT_NUM
HAVING NVL(SUM(ENTERED_CR),0) <> NVL(SUM(ENTERED_CR),0)
ORDER BY RECEIPT_NUM

15.Query to give total tax amount for AP Accrual where the tax entries are not generated

select rct.organization_id, jrl.receipt_num, jrtl.currency,
sum(jrtl.tax_amount),rcv.code_combination_id
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct,
RCV_RECEIVING_SUB_LEDGER rcv
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and acct_nature = 'Receiving'
)
and rct.transaction_id=rcv.rcv_transaction_id
and NVL(rcv.entered_cr,0) > 0
and rcv.code_combination_id=&CCID
group by rct.organization_id, jrl.receipt_num, jrtl.currency, rcv.code_combination_id
order by rct.organization_id,jrl.receipt_num,jrtl.currency, rcv.code_combination_id


16.Query to identify cases of discrepancy in invoice distribution and the corresponding tax amount in Receipt / PO

select f.segment1 "PO Number", g.receipt_num "Receipt Number",
b.transaction_id "Receipt tran id", a.po_distribution_id "PO Dist.Id",
e.invoice_num "Invoice Number ", a.invoice_id "Invoice_id",
a.distribution_line_number "Distribution Line No", a.posted_flag "Posted",
c.tax_amount "Receipt Tax Amount", a.amount "Invoice Distribution Amt.", (c.tax_amount - a.amount ) "Differential Amt"
from ap_invoice_distributions_all a,
rcv_transactions b,
JAI_RCV_LINE_TAXES c,
JAI_AP_MATCH_INV_TAXES d,
ap_invoices_all e,
po_headers_all f,
rcv_shipment_headers g
where a.rcv_transaction_id = b.transaction_id
and b.shipment_line_id = c.shipment_line_id
and a.invoice_id = d.invoice_id
and a.distribution_line_number = d.distribution_line_number
and c.tax_id = d.tax_id
and a.invoice_id = e.invoice_id
and b.po_header_id = f.po_header_id
and b.shipment_header_id = g.shipment_header_id
and b.transaction_type= 'RECEIVE'
and e.source = 'ERS'
and abs(c.tax_amount - a.amount ) >= 1
and b.transaction_date between '&from_date' and '&to_date'

17.Query to give total sum of the taxes based on transaction type from JAI_RCV_JOURNAL_ENTRIES (IL Subledger)

SELECT
NVL(SUM(B.TAX_AMOUNT),0)
FROM
JAI_RCV_LINES A,
JAI_RCV_LINE_TAXES B,
MTL_SYSTEM_ITEMS C,
RCV_SHIPMENT_LINES D,
JAI_RCV_JOURNAL_ENTRIES E
WHERE
A.SHIPMENT_HEADER_ID = B.SHIPMENT_HEADER_ID
AND A.SHIPMENT_LINE_ID = B.SHIPMENT_LINE_ID
AND A.SHIPMENT_LINE_ID = D.SHIPMENT_LINE_ID
AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
AND C.INVENTORY_ITEM_ID = D.ITEM_ID
AND A.SHIPMENT_LINE_ID = E.SHIPMENT_LINE_ID
AND E.TRANSACTION_TYPE ='RECEIVE'
AND D.TO_ORGANIZATION_ID = C.ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) > '&parameter_date'
AND D.TO_ORGANIZATION_ID = &P_ORGANIZATION_ID
ORDER BY D.TO_ORGANIZATION_ID,TO_NUMBER(A.RECEIPT_NUM)




18.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise


SELECT
NVL(SUM(B.TAX_AMOUNT),0)
FROM
JAI_RCV_LINES A,
JAI_RCV_LINE_TAXES B,
MTL_SYSTEM_ITEMS C,
RCV_SHIPMENT_LINES D,
JAI_RCV_JOURNAL_ENTRIES E
WHERE
A.SHIPMENT_HEADER_ID = B.SHIPMENT_HEADER_ID
AND A.SHIPMENT_LINE_ID = B.SHIPMENT_LINE_ID
AND A.SHIPMENT_LINE_ID = D.SHIPMENT_LINE_ID
AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
AND C.INVENTORY_ITEM_ID = D.ITEM_ID
AND A.SHIPMENT_LINE_ID = E.SHIPMENT_LINE_ID
AND E.TRANSACTION_TYPE ='DELIVER'
AND D.TO_ORGANIZATION_ID = C.ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) > '&parameter_date'
AND D.TO_ORGANIZATION_ID = &P_ORGANIZATION_ID
ORDER BY D.TO_ORGANIZATION_ID,TO_NUMBER(A.RECEIPT_NUM)




19.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise


SELECT
NVL(SUM(B.TAX_AMOUNT),0)
FROM
JAI_RCV_LINES A,
JAI_RCV_LINE_TAXES B,
MTL_SYSTEM_ITEMS C,
RCV_SHIPMENT_LINES D,
JAI_RCV_JOURNAL_ENTRIES E
WHERE
A.SHIPMENT_HEADER_ID = B.SHIPMENT_HEADER_ID
AND A.SHIPMENT_LINE_ID = B.SHIPMENT_LINE_ID
AND A.SHIPMENT_LINE_ID = D.SHIPMENT_LINE_ID
AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
AND C.INVENTORY_ITEM_ID = D.ITEM_ID
AND A.SHIPMENT_LINE_ID = E.SHIPMENT_LINE_ID
AND E.TRANSACTION_TYPE ='RETURN TO RECEIVING'
AND D.TO_ORGANIZATION_ID = C.ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) > '&parameter_date'
AND D.TO_ORGANIZATION_ID = &P_ORGANIZATION_ID
ORDER BY D.TO_ORGANIZATION_ID,TO_NUMBER(A.RECEIPT_NUM)




20.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise


SELECT
NVL(SUM(B.TAX_AMOUNT),0)
FROM
JAI_RCV_LINES A,
JAI_RCV_LINE_TAXES B,
MTL_SYSTEM_ITEMS C,
RCV_SHIPMENT_LINES D,
JAI_RCV_JOURNAL_ENTRIES E
WHERE
A.SHIPMENT_HEADER_ID = B.SHIPMENT_HEADER_ID
AND A.SHIPMENT_LINE_ID = B.SHIPMENT_LINE_ID
AND A.SHIPMENT_LINE_ID = D.SHIPMENT_LINE_ID
AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
AND C.INVENTORY_ITEM_ID = D.ITEM_ID
AND A.SHIPMENT_LINE_ID = E.SHIPMENT_LINE_ID
AND E.TRANSACTION_TYPE ='RETURN TO VENDOR'
AND D.TO_ORGANIZATION_ID = C.ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) > '&parameter_date'
AND D.TO_ORGANIZATION_ID = &P_ORGANIZATION_ID
ORDER BY D.TO_ORGANIZATION_ID,TO_NUMBER(A.RECEIPT_NUM)




21.Query to Find the Sum of Tax Amounts from JAI_RCV_JOURNAL_ENTRIES WHERE TRANSACTION_TYPE='RETURN TO VENDOR'


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RETURN TO VENDOR'
AND TRUNC(E.CREATION_DATE) > '&parameter_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'




22.Query to identify Receipts for which accounting entries are not generated


select substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35),sum(jrtl.tax_amount),rcv.code_combination_id
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct,
RCV_RECEIVING_SUB_LEDGER rcv,
gl_code_combinations_kfv glc
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and transaction_type = 'RECEIVE'
)
and jrtl.tax_amount != 0
and rct.transaction_id=rcv.rcv_transaction_id
and rcv.code_combination_id = glc.code_combination_id
and NVL(rcv.entered_cr,0) > 0
and JRL.CREATION_DATE BETWEEN '&P_DATE_FROM' AND '&P_DATE_TO'
and rct.organization_id=&p_organization_id
group by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35), rcv.code_combination_id
order by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
rcv.code_combination_id



23.Query to identify receipts where tax entries have been updated in IL subledger but missing in General Ledger


select substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35),sum(jrtl.tax_amount),rcv.code_combination_id
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct,
RCV_RECEIVING_SUB_LEDGER rcv,
gl_code_combinations_kfv glc
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and not exists (select '1'
from gl_je_lines
where reference_5 = to_char(jrtl.transaction_id)
and description like 'India%'
)
and jrtl.tax_amount != 0
and rct.transaction_id=rcv.rcv_transaction_id
and rcv.code_combination_id = glc.code_combination_id
--and NVL(rcv.entered_Dr,0) > 0
and JRL.CREATION_DATE BETWEEN '&P_DATE_FROM' AND '&P_DATE_TO'
and rct.organization_id=&p_organization_id
group by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35), rcv.code_combination_id
order by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
rcv.code_combination_id



24.Query to identify Receipt where accounting entries not generated for Deliver transactions


select rct.organization_id, jrl.receipt_num, jrtl.currency,
sum(jrtl.tax_amount)
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'DELIVER'
and nvl(jrtl.modvat_flag, 'N') = 'N'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and transaction_type = 'DELIVER'
)
and jrl.creation_date between '&from_date' and '&to_date'
group by rct.organization_id, jrl.receipt_num, jrtl.currency
order by rct.organization_id, jrl.receipt_num, jrtl.currency



25.Query to find Receipts for which RTR entries are not generated


select rct.organization_id, jrl.receipt_num, jrtl.currency,
sum(jrtl.tax_amount)
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RETURN TO RECEIVING'
and nvl(jrtl.modvat_flag, 'N') = 'N'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and transaction_type = 'RETURN TO RECEIVING'
)
and jrl.creation_date between '&from_date' and '&to_date'
group by rct.organization_id, jrl.receipt_num, jrtl.currency
order by rct.organization_id, jrl.receipt_num, jrtl.currency



26.Query to Find (RTV) Returns for which accounting is not generated


select substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35),sum(jrtl.tax_amount),rcv.code_combination_id
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct,
RCV_RECEIVING_SUB_LEDGER rcv,
gl_code_combinations_kfv glc
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RETURN TO VENDOR'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and transaction_type = 'RETURN TO VENDOR'
)
and jrtl.tax_amount != 0
and rct.transaction_id=rcv.rcv_transaction_id
and rcv.code_combination_id = glc.code_combination_id
and NVL(rcv.entered_cr,0) > 0
and JRL.CREATION_DATE BETWEEN '&P_DATE_FROM' AND '&P_DATE_TO'
and rct.organization_id=&p_organization_id
group by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35), rcv.code_combination_id
order by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
rcv.code_combination_id



27.Query to identify Receipts not yet claimed


select rct.organization_id, jrl.receipt_num, jrtl.currency,
sum(jrtl.tax_amount)
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
rcv_transactions rct
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and nvl(jrtl.modvat_flag, 'N') = 'Y'
and exists (select '1'
from JAI_RCV_CENVAT_CLAIMS
where shipment_line_id = jrtl.shipment_line_id
and cenvat_claimed_ptg <> 100
)
and jrl.creation_date between '&from_date' and '&to_date'
group by rct.organization_id, jrl.receipt_num, jrtl.currency
order by rct.organization_id, jrl.receipt_num, jrtl.currency