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

User Password Reset SQL Query

/************************************************************
*PURPOSE: To change/reset password of a user from backend   *
*************************************************************/
SET serveroutput ON;
DECLARE
  v_user_name    VARCHAR2(30):= upper('JAIN ROHAN');
  v_new_password VARCHAR2(30):= 'Rohan777';
  v_status       BOOLEAN;
BEGIN
  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name, 
                                                                                        newpassword => v_new_password 
                                                                                      );
  IF v_status  THEN
    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
    COMMIT;
  ELSE
    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
  END IF;
END;

Query for GST Document Sequence Form Data Extraction

 

SELECT
JTDS.DOC_SEQ_NAME SEQUENCE_NAME,
JTDS.START_NUMBER,
JTDS.CURRENT_NUMBER,
JTDS.PREFIX,
JTDS.END_NUMBER LAST_NUMBER,
TO_CHAR(JTDS.EFFECTIVE_FROM, 'DD-MON-YYYY') START_DATE,
TO_CHAR(JTDS.EFFECTIVE_TO, 'DD-MON-YYYY') END_DATE,
JTDA.REGIME_NAME,
JTDA.REGISTRATION_NUMBER,
JTDA.ORGANIZATION_TYPE_NAME,
JTDA.ORGANIZATION_NAME,
JTDA.LOCATION_NAME,
JTDA.DOCUMENT_TYPE_CODE,
JTDA.DOCUMENT_CATEGORY_NAME,
JTDA.INTENDED_USE_NAME,
JTDA.RCM_SUPPLY_DAYS,
TO_CHAR(JTDA.EFFECTIVE_FROM, 'DD-MON-YYYY') START_DATE,
TO_CHAR(JTDA.EFFECTIVE_TO, 'DD-MON-YYYY') END_DATE
FROM
APPS.JAI_TAX_DOC_SEQS JTDS,
APPS.JAI_TAX_DOC_SEQ_ASGMT_V JTDA
WHERE
JTDS.DOC_SEQ_ID=JTDA.DOC_SEQ_ID
AND
JTDS.EFFECTIVE_FROM>='01-APR-22'

How to find Concurrent Program registered under which responsibility

SELECT
DISTINCT FNRTL.RESPONSIBILITY_NAME
FROM
APPS.FND_REQUEST_GROUPS FRG,
APPS.FND_APPLICATION_TL FAPP,
APPS.FND_REQUEST_GROUP_UNITS FRGU,
APPS.FND_CONCURRENT_PROGRAMS FCP,
APPS.FND_CONCURRENT_PROGRAMS_TL FCPL,
APPS.FND_RESPONSIBILITY FNR,
APPS.FND_RESPONSIBILITY_TL FNRTL
WHERE 1=1
AND FRG.APPLICATION_ID=FAPP.APPLICATION_ID
AND FRG.APPLICATION_ID=FRGU.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID=FRGU.REQUEST_GROUP_ID
AND FRG.REQUEST_GROUP_ID=FNR.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID=FNR.APPLICATION_ID
AND FNR.RESPONSIBILITY_ID=FNRTL.RESPONSIBILITY_ID
AND FRGU.REQUEST_UNIT_ID=FCP.CONCURRENT_PROGRAM_ID
AND FRGU.UNIT_APPLICATION_ID=FCP.APPLICATION_ID
AND FCP.CONCURRENT_PROGRAM_ID=FCPL.CONCURRENT_PROGRAM_ID
AND FCPL.USER_CONCURRENT_PROGRAM_NAME='Trial Balance - Detail'  ---(Change Program Name as per need)
AND FNRTL.LANGUAGE='US'
AND FAPP.LANGUAGE='US'

Query for Auto-Accounting Form Data Extraction - Operating Unit Wise

SELECT
HOU.NAME OPERATING_UNIT,
DECODE(RAD.TYPE, 'REC', 'RECEIVABLE', 'BR_REC', 'BILLS RECEIVABLE', 'TAX', 'TAX', 'BR_REMITTANCE', 'REMITTED BILLS RECEIVABLE', 'UNBILL', 'UNBILLED RECEIVABLE', 'BR_FACTOR', 'FACTORED BILLS RECEIVABLE', 'BR_UNPAID_REC', 'UNPAID BILLS RECEIVABLE', 'FREIGHT', 'FREIGHT', 'REV', 'REVENUE', 'UNEARN', 'UNEARNED REVENUE', 'SUSPENSE', 'AUTOINVOICE CLEARING', NULL) TYPE,
RADS.SEGMENT SEGMENT_NUMBER,
DECODE(RADS.SEGMENT, 'SEGMENT1' , 'Business Unit', 'SEGMENT2' , 'Account', 'SEGMENT3' , 'Cost Center', 'SEGMENT4' , 'Product Group', 'SEGMENT5' , 'Intercompany', 'SEGMENT6' , 'Future1', 'SEGMENT7' , 'Future2', NULL) SEGMENT,
DECODE(RADS.TABLE_NAME, 'RA_STD_TRX_LINES', 'STANDARD LINES', 'RA_CUST_TRX_TYPES', 'TRANSACTION TYPES', 'RA_TAXES', 'TAXES', 'RA_SITE_USES', 'SITES', 'RA_SALESREPS', 'SALESPERSON', NULL) TABLE_NAME,
RADS.CONSTANT
FROM
HR_OPERATING_UNITS HOU,
APPS.RA_ACCOUNT_DEFAULTS_ALL RAD,
APPS.RA_ACCOUNT_DEFAULT_SEGMENTS RADS
WHERE
HOU.ORGANIZATION_ID=RAD.ORG_ID
AND
RAD.GL_DEFAULT_ID=RADS.GL_DEFAULT_ID
AND
RAD.ORG_ID=2610                -----(PASS YOUR ORG_ID)
ORDER BY TYPE, RADS.SEGMENT;

Note: Change values in "DECODE - RADS.SEGMENT"  as per your business structure as highlighted in the above query.

Query for Receipt Class and Receipt Method Form Data Extraction

SELECT 

ARC.NAME RECEIPT_CLASSES,

ARM.NAME ,

ARM.PRINTED_NAME,

ARM.START_DATE "EFFECTIVE DATES FROM",

HOA.NAME "OPERATING UNIT",

CEA.BANK_ACCOUNT_NAME,

CEA.BANK_ACCOUNT_NUM,

CEA.BANK_ACCOUNT_ID,

CEA.IBAN_NUMBER,

CEA.EFT_USER_NUM,

CBBV.BANK_NAME,

CBBV.BANK_BRANCH_NAME,

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID=ARMAA.CASH_CCID)"CASH ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID=ARMAA.ON_ACCOUNT_CCID)"ON ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID=ARMAA.UNAPPLIED_CCID)"UNAPPLIED ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID=ARMAA.UNEARNED_CCID) "UNEARNED ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID=ARMAA.UNIDENTIFIED_CCID)"UNIDENTIFIED ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID=ARMAA.FACTOR_CCID)"FACTORING",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID=ARMAA.BANK_CHARGES_CCID) "BANK CHARGES ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID=ARMAA.RECEIPT_CLEARING_CCID) "RECEIPT CLEARING ACCOUNT"

FROM

AR_RECEIPT_CLASSES ARC,

AR_RECEIPT_METHODS ARM,

AR_RECEIPT_METHOD_ACCOUNTS_ALL ARMAA,

HR_ORGANIZATION_UNITS HOA,

CE_BANK_ACCT_USES_ALL CBAUA,

CE_BANK_ACCOUNTS CEA,

CE_BANK_BRANCHES_V CBBV

WHERE

1 = 1

AND 

ARC.RECEIPT_CLASS_ID = ARM.RECEIPT_CLASS_ID

AND 

ARM.RECEIPT_METHOD_ID = ARMAA.RECEIPT_METHOD_ID

AND 

ARM.END_DATE IS NULL

AND 

ARMAA.ORG_ID = HOA.ORGANIZATION_ID

AND 

ARMAA.REMIT_BANK_ACCT_USE_ID = CBAUA.BANK_ACCT_USE_ID

AND 

CBAUA.BANK_ACCOUNT_ID = CEA.BANK_ACCOUNT_ID

AND 

CEA.BANK_BRANCH_ID = CBBV.BRANCH_PARTY_ID

ORDER BY RECEIPT_CLASSES;

Query for Tax Type Form Data Extraction

SELECT

JR.REGIME_CODE "TAX REGIME CODE",

JR.REGIME_NAME "TAX REGIME NAME",

JTT.TAX_TYPE_CODE "TAX TYPE CODE",

JTT.TAX_TYPE_NAME "TAX TYPE NAME",

TO_CHAR(JTT.EFFECTIVE_FROM, 'DD-MON-RRRR') "START DATE",

TO_CHAR(JTT.EFFECTIVE_TO, 'DD-MON-RRRR') "END DATE",

JTT.RECOVERABLE_FLAG "RECOVERABLE",

JTT.ALLOW_ABATEMENT "ALLOW ABATEMENT",

JTT.OFFSET_FLAG "APPLICABLE FOR OFSET",

JTT.SELF_ASSESSED_FLAG "SELF ASSESSED / REVERSE CHARGE",

JTT.REPORTING_ONLY_FLAG "SET TAX TYPE FOR REPORTING PURPOSE ONLY",

JTT.UPDATE_VENDOR_ON_TRANSACTION "UPDATE VENDOR ON TRANSACTION",

JTT.WTHLD_TRX_APPLICABLE_FLAG "WITHHOLDING TAX APPLICABLE",

JTT.TAX_POINT_BASIS "TAX POINT BASIS",

GLL.NAME "LEDGER",

HOU.NAME "OPERATING UNIT",

HOU1.NAME "INVENTORY ORGANIZATION",

HRL.LOCATION_CODE "LOCATION",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.INTERIM_RECOVERY_CCID) "INTERIM RECOVERY ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.RECOVERY_CCID) "RECOVERY ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.INTERIM_LIABILITY_CCID) "INTERIM LIABILITY ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.LIABILITY_CCID) "LIABILITY ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.EXPENSE_CCID) "EXPENSE ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.ROUNDING_CCID) "ROUNDING ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.SUSPENSE_CCID) "SUSPENSE ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.ADVRCPT_SUSPENSE_CCID) "ADVANCE SUSPENSE ACCOUNT",

(SELECT CONCATENATED_SEGMENTS FROM GL_CODE_COMBINATIONS_KFV WHERE CODE_COMBINATION_ID = JTA.ISO_SUSPENSE_CCID) "ISO SUSPENSE ACCOUNT"

FROM

APPS.JAI_TAX_TYPES JTT,

APPS.JAI_REGIMES JR,

APPS.JAI_TAX_ACCOUNTS JTA,

HR_ALL_ORGANIZATION_UNITS HOU,

HR_ALL_ORGANIZATION_UNITS HOU1,

HR_LOCATIONS HRL,

GL_LEDGERS GLL

WHERE

JR.REGIME_ID=JTT.REGIME_ID

AND

JTT.TAX_TYPE_ID=JTA.TAX_ACCOUNT_ENTITY_ID

AND

JTA.ORG_ID=HOU.ORGANIZATION_ID(+)

AND

JTA.ORGANIZATION_ID=HOU1.ORGANIZATION_ID(+)

AND

JTA.LOCATION_ID=HRL.LOCATION_ID(+)

AND

JTA.LEDGER_ID=GLL.LEDGER_ID(+)

AND

JTA.LEDGER_ID=114                           -------(Pass Ledger ID)

ORDER BY JR.REGIME_CODE, JTT.TAX_TYPE_CODE;

Transaction Date Cannot be Future Date (Stuck in Transaction Open Interface)

Step 1: Query the record which needs to be updated

SELECT 
TRANSACTION_DATE,  MTI.*  

FROM  MTL_TRANSACTIONS_INTERFACE MTI
WHERE 
TRANSACTION_HEADER_ID='';     ---(From Transaction Open Interface)

Request ID Stuck in AR Interface Lines

SYMPTOM:- When importing sales order from Order Management to Receivable, 'Auto-invoice Import Program' goes in error. Resulting in request id of 'Auto-invoice Import Program' got stored in receivable interface lines and unable to import order in receivables.

Analysis: The standard error due to which this issue occurs is that at time of 'Third Party Registration' of customer where registration number is entered incorrect, which leads in error-ed out of 'Autoinvoice Import Program'.

SOLUTION:-

Alternative 1 :-  

1. Do RMA for that particular sales order which will bring back the quantity of the item into the inventory. And delete both record of sales order and RMA order from receivable interface lines 
2. Correct the registration number at 'Third Party Registration Level'.
3. Create the sales order again and import it in receivables. 

Alternative 2 :-

create table RA_INT_LIN_BKP

as

select *

from RA_INTERFACE_LINES_ALL

where INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'

and INTERFACE_LINE_ATTRIBUTE1 = ''    ------(with Order Number)

and INTERFACE_STATUS is null;

-- 1 row should be backed up

create table RA_INT_SCR_BKP

as

select *

from RA_INTERFACE_SALESCREDITS_ALL

where INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'

and INTERFACE_LINE_ATTRIBUTE1 = ''    ------(with Order Number)

and INTERFACE_STATUS is null;

-- 1 row should be backed up

update RA_INTERFACE_LINES_ALL

set REQUEST_ID = null

where INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'

and INTERFACE_LINE_ATTRIBUTE1 = ''         ------(with Order Number)

and INTERFACE_STATUS is null;

-- 1 row should be updated

update RA_INTERFACE_SALESCREDITS_ALL

set REQUEST_ID = null

where INTERFACE_LINE_CONTEXT = 'ORDER ENTRY'

and INTERFACE_LINE_ATTRIBUTE1 = ''         ------(with Order Number)

and INTERFACE_STATUS is null;

-- 1 row should be updated

commit;