Document Sequence Voucher Count - Financial Year Wise

--Document Sequence ID Identification Query

SELECT DISTINCT
FDS.NAME,
FDS.DOC_SEQUENCE_ID
FROM
FND_DOCUMENT_SEQUENCES       FDS,
FND_DOC_SEQUENCE_ASSIGNMENTS FDSA
WHERE
FDS.DOC_SEQUENCE_ID = FDSA.DOC_SEQUENCE_ID
AND FDSA.START_DATE >= '01-APR-24';

--GL Query for Document Value Count

SELECT
COUNT(*)
FROM
GL_JE_HEADERS GLH
WHERE
DEFAULT_EFFECTIVE_DATE BETWEEN '01-APR-2024' AND '31-MAR-2025'
AND DOC_SEQUENCE_ID = 4098
AND LEDGER_ID = 2021;
--ORDER BY
--GLH.DOC_SEQUENCE_VALUE DESC;

--AP Invoice Query for Document Value Count

SELECT
COUNT(*)
FROM
AP_INVOICES_ALL API
WHERE
SET_OF_BOOKS_ID = 2262
AND INVOICE_DATE BETWEEN '01-APR-2024' AND '31-MAR-2025'
AND DOC_SEQUENCE_ID = 4114;
--ORDER BY
--API.doc_sequence_value DESC;

--AP Payment Query for Document Value Count

SELECT
COUNT(*)
FROM
AP_CHECKS_ALL      APC,
HR_OPERATING_UNITS HOU
WHERE
HOU.ORGANIZATION_ID = APC.ORG_ID
AND HOU.SET_OF_BOOKS_ID =2262
AND CHECK_DATE BETWEEN '01-APR-2024' AND '31-MAR-2025'
AND DOC_SEQUENCE_ID = 4108;
--ORDER BY
--APC.DOC_SEQUENCE_VALUE DESC;

--AR Query for Document Value Count

SELECT
COUNT(*)
FROM
RA_CUSTOMER_TRX_ALL RACT
WHERE
SET_OF_BOOKS_ID = 2262
AND TRX_DATE BETWEEN '01-APR-2024' and '31-MAR-2025'
AND DOC_SEQUENCE_ID = 4106;
--ORDER BY
--RACT.DOC_SEQUENCE_VALUE DESC;

SELECT
COUNT(*)
FROM
AR_ADJUSTMENTS_ALL ARAA
WHERE
SET_OF_BOOKS_ID = 2262
AND APPLY_DATE BETWEEN '01-APR-2024' AND '31-MAR-2025'
AND DOC_SEQUENCE_ID = 4103;
--ORDER BY
--ARAA.DOC_SEQUENCE_VALUE DESC;

SELECT
COUNT(*)
FROM
AR_CASH_RECEIPTS_ALL A
WHERE
SET_OF_BOOKS_ID = 2262
AND RECEIPT_DATE BETWEEN '01-APR-2024' AND '31-MAR-2025'
AND DOC_SEQUENCE_ID = 4103;
--ORDER BY
--A.DOC_SEQUENCE_VALUE DESC;

--CM Query for Document Value Count

SELECT
COUNT(*)
FROM
CE_STATEMENT_HEADERS CES,
HR_OPERATING_UNITS   HOU
WHERE
HOU.ORGANIZATION_ID = CES.ORG_ID
AND HOU.SET_OF_BOOKS_ID IN ( 2325, 2327 )
AND CES.STATEMENT_DATE BETWEEN '01-APR-2024' AND '31-MAR-2025'
 AND CES.DOC_SEQUENCE_ID = 4078;
--ORDER BY
--CES.DOC_SEQUENCE_VALUE DESC;

-----------------------------------------------------------------------------------------------------------------------------

#oracleebs, #ebsr12, #sql, #documentsequence, #vouchercount, #fycount, #sysadmin 

Sub-Ledger Document Sequence Assignment Count and End Date - Ledger Wise

SELECT count(*)
FROM FND_DOC_SEQUENCE_ASSIGNMENTS
WHERE     
            SET_OF_BOOKS_ID = 2021 -- (Enter Ledger ID)
AND   APPLICATION_ID IN (200,222,660,101,260)
AND   START_DATE >= '01-APR-2024'
AND   END_DATE IS NULL;

UPDATE
FND_DOC_SEQUENCE_ASSIGNMENTS
SET     
END_DATE = '31-MAR-2025'
WHERE     
            SET_OF_BOOKS_ID = 2021 -- (Enter Ledger ID)
AND   APPLICATION_ID IN (200,222,660,101,260)
AND   START_DATE >= '01-APR-2024'
AND   END_DATE IS NULL;

--------------------------------------------------------------------------------------------------------------------------------#Oracleebs #documentsequence #documentassignments #ledger #applicationid #yearendactivity #r12
--------------------------------------------------------------------------------------------------------------------------------