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.

#oracleebs #oracler12 #receivables #autoaccounting #flexfieldsegments #receivablessetup #query #dataextraction #sqlquery