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'