Skip to main content

Project Accounting Mismatch SQL Queries

1.) AP-NOPROJ-ENTRIES-JAN TO MAR

SELECT   d.ACCOUNTING_DATE, i.invoice_num, i.invoice_date, i.invoice_amount, d.amount,
         d.accounting_date, g.segment4, g.segment6, d.project_id , g.concatenated_segments, d.invoice_distribution_id,
         i.creation_date, d.status, d.posted_status_disp
    FROM ap_invoices_v i,
         ap_invoice_lines_all l,
         ap_invoice_distributions_v d,
         gl_code_combinations_kfv g
   WHERE i.invoice_id = l.invoice_id
     AND i.invoice_id = d.invoice_id
     AND l.line_number = d.invoice_line_number
     AND d.dist_code_combination_id = g.code_combination_id
     AND d.project_id IS NULL
     AND i.vendor_id != 313296
     AND g.segment6 != '0000'
     AND g.segment4 LIKE '13%'
     AND d.ACCOUNTING_DATE BETWEEN '01-Jan-2015' AND '31-MAR-2015'
ORDER BY d.status

2.) INV-NOPROJ-ENTRIES- JAN TO MAR

SELECT  MMT.ORGANIZATION_ID, MP.ORGANIZATION_CODE, MTA.TRANSACTION_SOURCE_TYPE_ID, MTT.TRANSACTION_TYPE_NAME,MMT.TRANSACTION_DATE,
        MTA.BASE_TRANSACTION_VALUE, MTA.REFERENCE_ACCOUNT, GLCC.CONCATENATED_SEGMENTS, GLCC.SEGMENT4, GLCC.SEGMENT6
FROM MTL_TRANSACTION_ACCOUNTS MTA,
GL_CODE_COMBINATIONS_KFV GLCC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_PARAMETERS MP
WHERE MTA.REFERENCE_ACCOUNT = GLCC.CODE_COMBINATION_ID
  AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
  AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
  AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
  AND TRUNC(MTA.TRANSACTION_DATE) BETWEEN '01-JAN-2015' AND '31-MAR-2015'
  AND GLCC.SEGMENT6 != '0000'
  AND MMT.SOURCE_PROJECT_ID IS NULL


3.) INV-PROJ-MM-COA-ENTRIES- JAN TO MAR

select TRANSACTION_SOURCE,  glcc.segment6, a.project_number, SUM(A.BURDENED_COST)
from PA_EXPEND_ITEMS_ADJUST2_V a,
PA_COST_DISTRIBUTION_LINES_ALL pd,
GL_CODE_COMBINATIONS_KFV glcc
where a.expenditure_item_id = pd.expenditure_item_id
  and pd.dr_code_combination_id = glcc.code_combination_id
and a.expenditure_category = 'Material'
and a.project_number != glcc.segment6
and pd.gl_date between '01-JAN-2015' AND '31-MAR-2015'
group by TRANSACTION_SOURCE,  glcc.segment6, a.project_number
ORDER BY 3 ASC NULLS LAST

4.) INV-MISC-PROJ ERROR- JAN TO MAR

select O.ORGANIZATION_ID, o.NAME,(mmt.ACTUAL_COST *mmt.TRANSACTION_QUANTITY) Amount, g.CONCATENATED_SEGMENTS Account ,mmt.ERROR_CODE, mmt.ERROR_EXPLANATION,mmt.PROJECT_ID, mmt.*
from MTL_MATERIAL_TRANSACTIONS MMT, HR_ORGANIZATION_UNITS O, gl_code_combinations_kfv g
where 
mmt.ORGANIZATION_ID = o.ORGANIZATION_ID
and mmt.DISTRIBUTION_ACCOUNT_ID = g.CODE_COMBINATION_ID
and mmt.ERROR_CODE is not null
and mmt.TRANSACTION_DATE between '01-JAN-2015' AND '31-MAR-2015'

5.) PO-NOPROJ-ENTRIES – JAN TO MAR

SELECT
B.TRANSACTION_ID, B.TRANSACTION_TYPE,  rcv_sub.CODE_COMBINATION_ID RCV_CCID, C.CONCATENATED_SEGMENTS RCV_ACCTS, C.SEGMENT4, C.SEGMENT6,
B.PROJECT_ID, B.TASK_ID, B.TRANSACTION_DATE,
po_dist.CODE_COMBINATION_ID POD_CCID, C1.CONCATENATED_SEGMENTS PO_ACCTS,
 po_dist.project_ID PO_PROJECT_ID, po_dist.accrue_on_receipt_flag, po_dist.expenditure_type,
D.SEGMENT1 PO_NUMBER, E.ORGANIZATION_CODE INV_ORG, F.RECEIPT_NUM, 
rcv_sub.PA_ADDITION_FLAG, rcv_sub.ACTUAL_FLAG, rcv_sub.JE_SOURCE_NAME, rcv_sub.JE_CATEGORY_NAME,
 (SELECT 'X' FROM pa_expenditure_items_all ei WHERE  ei.document_distribution_id = rcv_sub.rcv_transaction_id) RCV_SUB,
 (SELECT 'EXISTS' FROM PA_EXPEND_ITEMS_ADJUST2_V D WHERE  D.ORIG_USER_EXP_TXN_REFERENCE = TO_CHAR(B.TRANSACTION_ID) ) PA_TRNS,
(NVL(rcv_sub.ENTERED_DR,0)-NVL(rcv_sub.ENTERED_CR,0)) VALUE
FROM RCV_TRANSACTIONS B,
     rcv_receiving_sub_ledger rcv_sub,
     po_distributions_all po_dist,
     GL_CODE_COMBINATIONS_KFV C,
     GL_CODE_COMBINATIONS_KFV C1,
     PO_HEADERS_ALL D,
     MTL_PARAMETERS E,
     RCV_SHIPMENT_HEADERS F
WHERE B.TRANSACTION_ID = rcv_sub.RCV_TRANSACTION_ID
  AND rcv_sub.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID
  AND po_dist.CODE_COMBINATION_ID = C1.CODE_COMBINATION_ID
  AND B.PO_HEADER_ID = D.PO_HEADER_ID
  AND B.ORGANIZATION_ID = E.ORGANIZATION_ID
  AND B.SHIPMENT_HEADER_ID = F.SHIPMENT_HEADER_ID
  AND B.DESTINATION_TYPE_CODE = 'EXPENSE'
  AND B.PO_DISTRIBUTION_ID = po_dist.PO_DISTRIBUTION_ID
  AND B.PROJECT_ID IS NULL
  AND C.SEGMENT4 != '13310'  -- Exclude the Contra of Receiving Accounting
  AND (C.SEGMENT6 != '0000' OR C1.SEGMENT6 != '0000') -- Exclude where accounting done for common Project
AND TRUNC(B.TRANSACTION_DATE) BETWEEN '01-JAN-2015' AND '31-MAR-2015'
AND NOT EXISTS (SELECT 1 FROM PA_EXPEND_ITEMS_ADJUST2_V D WHERE  D.ORIG_USER_EXP_TXN_REFERENCE = TO_CHAR(B.TRANSACTION_ID))
ORDER BY F.RECEIPT_NUM, B.TRANSACTION_DATE

6.) Pur-Missing Receipt Accr-ProjID-JAN TO MAR

SELECT
B.TRANSACTION_ID, B.TRANSACTION_TYPE,  rcv_sub.CODE_COMBINATION_ID RCV_CCID, C.CONCATENATED_SEGMENTS RCV_ACCTS, C.SEGMENT4, C.SEGMENT6,
B.PROJECT_ID, B.TASK_ID, B.TRANSACTION_DATE,
po_dist.CODE_COMBINATION_ID POD_CCID, C1.CONCATENATED_SEGMENTS PO_ACCTS,
 po_dist.project_ID PO_PROJECT_ID, po_dist.accrue_on_receipt_flag, po_dist.expenditure_type,
D.SEGMENT1 PO_NUMBER, E.ORGANIZATION_CODE INV_ORG, F.RECEIPT_NUM, 
rcv_sub.PA_ADDITION_FLAG, rcv_sub.ACTUAL_FLAG, rcv_sub.JE_SOURCE_NAME, rcv_sub.JE_CATEGORY_NAME,
 (SELECT 'X' FROM pa_expenditure_items_all ei WHERE  ei.document_distribution_id = rcv_sub.rcv_transaction_id) RCV_SUB,
 (SELECT 'EXISTS' FROM PA_EXPEND_ITEMS_ADJUST2_V D WHERE  D.ORIG_USER_EXP_TXN_REFERENCE = TO_CHAR(B.TRANSACTION_ID) ) PA_TRNS,
(NVL(rcv_sub.ENTERED_DR,0)-NVL(rcv_sub.ENTERED_CR,0)) VALUE
FROM RCV_TRANSACTIONS B,
     rcv_receiving_sub_ledger rcv_sub,
     po_distributions_all po_dist,
     GL_CODE_COMBINATIONS_KFV C,
     GL_CODE_COMBINATIONS_KFV C1,
     PO_HEADERS_ALL D,
     MTL_PARAMETERS E,
     RCV_SHIPMENT_HEADERS F
WHERE B.TRANSACTION_ID = rcv_sub.RCV_TRANSACTION_ID
  AND rcv_sub.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID
  AND po_dist.CODE_COMBINATION_ID = C1.CODE_COMBINATION_ID
  AND B.PO_HEADER_ID = D.PO_HEADER_ID
  AND B.ORGANIZATION_ID = E.ORGANIZATION_ID
  AND B.SHIPMENT_HEADER_ID = F.SHIPMENT_HEADER_ID
  AND B.DESTINATION_TYPE_CODE = 'EXPENSE'
  AND B.PO_DISTRIBUTION_ID = po_dist.PO_DISTRIBUTION_ID
  AND B.PROJECT_ID IS NOT NULL
  AND C.SEGMENT4 != '13310'  -- Exclude the Contra of Receiving Accounting
  AND (C.SEGMENT6 != '0000' OR C1.SEGMENT6 != '0000') -- Exclude where accounting done for common Project
AND TRUNC(B.TRANSACTION_DATE) BETWEEN '01-JAN-2015' AND '31-MAR-2015'
AND NOT EXISTS (SELECT 1 FROM PA_EXPEND_ITEMS_ADJUST2_V D WHERE  D.ORIG_USER_EXP_TXN_REFERENCE = TO_CHAR(B.TRANSACTION_ID))
ORDER BY F.RECEIPT_NUM, B.TRANSACTION_DATE


7.) AP Item date & GL date mismatch – JAN TO MAR

select p.SEGMENT1 Prj_no,g.segment4 Acct, d.AMOUNT,d.BASE_AMOUNT Func_amt, i.INVOICE_NUM,i.INVOICE_CURRENCY_CODE CUR, i.INVOICE_AMOUNT,d.ACCOUNTING_DATE, d.EXPENDITURE_ITEM_DATE, g.CONCATENATED_SEGMENTS Combination
from ap_invoice_distributions d, ap_invoices i,  pa_projects p, gl_code_combinations_kfv g
where d.INVOICE_ID = i.INVOICE_ID
and d.PROJECT_ID = p.PROJECT_ID
and d.DIST_CODE_COMBINATION_ID = g.CODE_COMBINATION_ID
and g.segment4 NOT in (25470,25480)
and d.ACCOUNTING_DATE between '01-JAN-2015' AND '31-MAR-2015'
and d.EXPENDITURE_ITEM_DATE >'31-MAR-2015'
UNION
select p.SEGMENT1 Prj_no,g.segment4 Acct,d.AMOUNT, d.BASE_AMOUNT Func_amt, i.INVOICE_NUM,i.INVOICE_CURRENCY_CODE CUR,i.INVOICE_AMOUNT,d.ACCOUNTING_DATE, d.EXPENDITURE_ITEM_DATE, g.CONCATENATED_SEGMENTS Combination
from ap_invoice_distributions d, ap_invoices i,  pa_projects p, gl_code_combinations_kfv g
where d.INVOICE_ID = i.INVOICE_ID
and d.PROJECT_ID = p.PROJECT_ID
and d.DIST_CODE_COMBINATION_ID = g.CODE_COMBINATION_ID
and g.segment4 NOT in (25470,25480)
and d.EXPENDITURE_ITEM_DATE between '01-JAN-2015' AND '31-MAR-2015'
and d.ACCOUNTING_DATE >'31-MAR-2015'

Comments

Popular posts from this blog

R12 Oracle E-Business Tax Configuration Guide

Applies to: Oracle E-Business Tax - Version: 12.0 to 12.2 Information in this document applies to any platform. Abstract R12 Oracle E-Business Tax Configuration Guide Document History R12 Oracle E-Business Tax Configuration 1. Tax Basic Tax Configuration Tax Definition: comprises the tax data that you set up for each tax regime and tax that your company or institution is subject to. The Tax Authority designates the regulations and rates that apply to the tax regime. Required Task List a) External Dependencies Create First Party: Legal Entity and Establishments Create Reporting and Collecting Tax Authorities b) Tax Configuration Create Tax Authorities Party Tax Profiles Create Tax Regimes Create First Party Legal Entity Party Tax Profile Create Tax Create Tax Status Create Tax Jurisdictions Tax Rate Responsibility: Tax Manager Path: Tax Managers -> Tax Managers -> Home Complete the following setup tasks: -Legal Entities and Establishments -Reporting and Collecting Tax Authorities ...

Create Accounting for a Payment generates errors 95333 and 95359

Create Accounting for a Payment generates errors 95333 and 95359 Error: 95333: A conversion rate does not exist to convert USD to AUD for the conversion type Corporate and conversion date 20-MAR-09 for line -25. Please use the Daily Rates form in General Ledger to enter a conversion rate for these currencies, conversion date and conversion type. 95359: There is no accounted amount for the subledger journal entry line. Please inform your system administrator or support representative that: The source assigned to the accounting attribute Accounted Amount has no value for extract line number 88547. Please make sure the source assigned to the accounting attribute Accounted Amount has a valid value, or assign a different source to this accounting attribute. Solution: 1. Specify a conversion rate for the currencies and conversion date mentioned in the error message 95333 Navigation under the General Ledger responsibility: Setup > Currencies > Currency Rates Manager > Daily Rates ...

R12: Payables Holds Explained – Why They are Applied and How to Remove

Invoice Validation checks the matching, tax, period status, exchange rate, and distribution information for invoices you enter. If an invoice has a hold, you can release the hold by correcting the exception that caused Invoice Validation to apply the hold by updating the invoice or the purchase order, or changing the invoice tolerances. Then resubmit Invoice Validation to release the hold. In the Invoice Holds tab, you can manually release certain invoice holds, even if you have not resolved the matching error condition. Here are some of the regular holds and explanation Account Holds Payables uses holds with this type to identify invoices with invalid or incorrect accounts. An invalid account prevents payment of an invoice and prevents Payables from creating accounting entries for an invoice or payment. Dist Acct Invalid:  Invoice distribution distributed to invalid account. To release the holds you must correct account and resubmit invoice validation. ERV Acct Invalid: ...