This AR Transaction Types Listing query retrieves customer transaction type details including bTransaction Name, description, class, Payment Terms, printing options for Transactions, freight and tax settings, printing option etc. The transaction type report, includes invoice and credit memo types in addition to the various GL accounts associated with them.
#sqlquery
SELECT
--SQL4FUSION (AN ORBIT ANALYTICS PROJECT)
--This query retrieves customer transaction types with financial flags, terms, and classifications.
RCTTA.NAME TRANSACTION_NAME,
RCTTA.DESCRIPTION TRANSACTION_DESCRIPTION,
ARL2.MEANING TRANSACTION_CLASS,
RCTTA.ACCOUNTING_AFFECT_FLAG OPEN_RECEIVABLE,
RCTTA.POST_TO_GL,
RT.NAME TERM_NAME,
RT.DESCRIPTION TERM_DESCRIPTION,
ARL3.MEANING PRINTING_OPTION,
ARL4.MEANING TRANSACTION_STATUS,
RCTTA.ALLOW_FREIGHT_FLAG ALLOW_FREIGHT,
RCTTA.TAX_CALCULATION_FLAG TAX_CALCULATION,
(
SELECT
DECODE(RCTTA.SUBSEQUENT_TRX_TYPE_ID,
NULL,
'',
SUBSTR(NAME, 1, 21))
FROM
RA_CUST_TRX_TYPES_ALL
WHERE
CUST_TRX_TYPE_ID = RCTTA.SUBSEQUENT_TRX_TYPE_ID
OR ( RCTTA.SUBSEQUENT_TRX_TYPE_ID IS NULL
AND ROWNUM < 2 )
) INVOICE_TYPE,
DECODE(RCTTA.CREDIT_MEMO_TYPE_ID,
NULL,
'',
SUBSTR(RCTTA.NAME, 1, 27)) CREDIT_MEMO_TYPE,
ARL1.MEANING CREATION_SIGN,
RCTTA.NATURAL_APPLICATION_ONLY_FLAG NATURAL_APPLICATION_ONLY,
RCTTA.ALLOW_OVERAPPLICATION_FLAG ALLOW_OVERAPPLICATION,
RCTTA.START_DATE,
RCTTA.END_DATE,
RCTTA.SUBSEQUENT_TRX_TYPE_ID INVOICE_TYPE_TYPE_ID,
RCTTA.CREDIT_MEMO_TYPE_ID,
RCTTA.DEFAULT_TERM TERM_ID,
RCTTA.ORG_ID ORGANIZATION_ID,
RT.FIRST_INSTALLMENT_CODE,
RT.CALC_DISCOUNT_ON_LINES_FLAG,
GCC1.SEGMENT1
|| '-'
|| GCC1.SEGMENT2
|| '-'
|| GCC1.SEGMENT3
|| '-'
|| GCC1.SEGMENT4
|| '-'
|| GCC1.SEGMENT5 REVENUE_ACCOUNT,
GCC2.SEGMENT1
|| '-'
|| GCC2.SEGMENT2
|| '-'
|| GCC2.SEGMENT3
|| '-'
|| GCC2.SEGMENT4
|| '-'
|| GCC2.SEGMENT5 FREIGHT_ACCOUNT,
GCC3.SEGMENT1
|| '-'
|| GCC3.SEGMENT2
|| '-'
|| GCC3.SEGMENT3
|| '-'
|| GCC3.SEGMENT4
|| '-'
|| GCC3.SEGMENT5 RECEIVABLE_ACCOUNT,
GCC4.SEGMENT1
|| '-'
|| GCC4.SEGMENT2
|| '-'
|| GCC4.SEGMENT3
|| '-'
|| GCC4.SEGMENT4
|| '-'
|| GCC4.SEGMENT5 CLEARING_ACCOUNT,
GCC5.SEGMENT1
|| '-'
|| GCC5.SEGMENT2
|| '-'
|| GCC5.SEGMENT3
|| '-'
|| GCC5.SEGMENT4
|| '-'
|| GCC5.SEGMENT5 UNBILLED_ACCOUNT,
GCC6.SEGMENT1
|| '-'
|| GCC6.SEGMENT2
|| '-'
|| GCC6.SEGMENT3
|| '-'
|| GCC6.SEGMENT4
|| '-'
|| GCC6.SEGMENT5 UNEARNED_ACCOUNT,
GCC7.SEGMENT1
|| '-'
|| GCC7.SEGMENT2
|| '-'
|| GCC7.SEGMENT3
|| '-'
|| GCC7.SEGMENT4
|| '-'
|| GCC7.SEGMENT5 TAX_ACCOUNT
FROM
RA_CUST_TRX_TYPES_ALL RCTTA,
AR_LOOKUPS ARL1,
AR_LOOKUPS ARL2,
AR_LOOKUPS ARL3,
AR_LOOKUPS ARL4,
RA_TERMS RT,
AR_REF_ACCOUNTS_ALL ARAA,
GL_CODE_COMBINATIONS GCC1,
GL_CODE_COMBINATIONS GCC2,
GL_CODE_COMBINATIONS GCC3,
GL_CODE_COMBINATIONS GCC4,
GL_CODE_COMBINATIONS GCC5,
GL_CODE_COMBINATIONS GCC6,
GL_CODE_COMBINATIONS GCC7
WHERE
1 = 1
AND RCTTA.CREATION_SIGN = ARL1.LOOKUP_CODE
AND ARL1.LOOKUP_TYPE = 'SIGN'
AND RCTTA.TYPE = ARL2.LOOKUP_CODE (+)
AND ARL2.LOOKUP_TYPE (+) = 'INV/CM'
AND RCTTA.DEFAULT_PRINTING_OPTION = ARL3.LOOKUP_CODE
AND ARL3.LOOKUP_TYPE = 'INVOICE_PRINT_OPTIONS'
AND RCTTA.DEFAULT_STATUS = ARL4.LOOKUP_CODE
AND ARL4.LOOKUP_TYPE = 'INVOICE_TRX_STATUS'
AND RT.TERM_ID (+) = RCTTA.DEFAULT_TERM
AND ARAA.SOURCE_REF_TABLE = 'RA_CUST_TRX_TYPES_ALL'
AND ARAA.SOURCE_REF_ACCOUNT_ID = RCTTA.CUST_TRX_TYPE_SEQ_ID
AND GCC1.CODE_COMBINATION_ID = ARAA.REV_CCID
AND GCC2.CODE_COMBINATION_ID = ARAA.FREIGHT_CCID
AND GCC3.CODE_COMBINATION_ID = ARAA.REC_CCID
AND GCC4.CODE_COMBINATION_ID = ARAA.CLEARING_CCID
AND GCC5.CODE_COMBINATION_ID = ARAA.UNBILLED_CCID
AND GCC6.CODE_COMBINATION_ID = ARAA.UNEARNED_CCID
AND GCC7.CODE_COMBINATION_ID = ARAA.TAX_CCID