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
--------------------------------------------------------------------------------------------------------------------------------

How to change Date Format in Oracle EBS R12?

 

We can change the Date Format as per the business needs, which can be achieved by just changing a profile option.

Profile Option: ICX: Date format mask 

Cash Forecasting - Cash Management

 

Cash forecasting is a planning tool that helps you anticipate the cash flow in and out of your business, allowing you to project your cash needs and evaluate your company's liquidity position.

In Oracle R12 you can forecast your cash position on the basis of transactions from subledgers. These transactions can be PO, PR, AP Invoice, AP Payment (Future Dated), AR Invoice, AR Receipt (Future Dated), and many more.

Process for Cash Forecasting in Oracle.

Step 1: Go to Cash Management Responsibility and Click on Cash Forecasting.

Step 2: Go to Forecast Template and click on Create Template

Enter all the details and click on the next

Here add the Source Type as per business requirements

Now as per the above steps add all the other sources as per business requirements.

Click on Next.

Now add the buckets which will tell you the inflow and outflow of cash as per the sources added above.

Click on Finish and then re-query your template.

Step 3: Click on Run Forecast as shown in the above image.

Now add the mandatory details and click on Submit.

 

After submitting, system will trigger 3 concurrent programs.

After completion of these 3 programs click on view forecast.

Now click on the Forecast Name Hyperlink to see the Cash Forecasting.

Here you can see the details of the Inflow and  Outflow of Cash as per sources selected in the forecast template.

Maximize Credits - PPR (Payment Process Request)

The concept of Maximize Credits in PPR is used when a payment is having more Negative Value then Positive Value, which means more credit memos then standard invoice.

Understand this with below examples.

Example 1: Maximize Credit Disabled

Standard Invoice 1: 300
Standard Invoice 2: 200
Credit Memo 1: -350

In this case total payment is positive which is Rs 150, so system will pick the Invoice and Credit Memo and will pay the amount.

Example2. Maximize Credit Disabled

Standard Invoice 1: 300
Standard Invoice 2: 200
Credit Memo 1: -350
Credit Memo 2: -250
Credit Memo 3: -100

In this case PPR will not select any Invoice and Credit Memo due to Credit is Low, which means that total amount to be paid is Rs -200 which is not allowed as per oracle standards.

Example3. Maximize Credit Enabled

Standard Invoice 1: 300
Standard Invoice 2: 200
Credit Memo 1: -350
Credit Memo 2: -250
Credit Memo 3: -100

 In this case PPR will select the data in the below logic.

Standard Invoice 1: 300
Standard Invoice 2: 200
Credit Memo 1: -150
Credit Memo 2: -250
Credit Memo 3: -100

Here PPR automatically deducts the amount of Credit Memo and make it equal to the total of Standard Invoice and then will Pay Zero Payment. Which is the actual concept of Maximize Credit.

To know more about the concept refer the below Oracle Doc. ID.

How does a Payment Process Request (PPR) handle Credit Memos in R12? (Doc ID 1279569.1)