Query to get the Receipt Class and Receipt Method setup in the system

Posted by

Please find below the SQL Query to retrieve the Receipt Class and Receipt Method setup in the system

Navigation in front End: Receivables Manager -> Setup -> Receipts -> Receipt Classes

Query to verify the setup from backend

SELECT CLASS.NAME CLASS_NAME,
CLASS.CREATION_METHOD_CODE,
CLASS.REMIT_METHOD_CODE,
METHOD.NAME METHOD_NAME,
METHOD.ATTRIBUTE_CATEGORY,
METHOD.ATTRIBUTE1,
METHOD.ATTRIBUTE6,
METHOD.ATTRIBUTE7,
METHOD.ATTRIBUTE9,
METHOD.ATTRIBUTE10,
METHOD.ATTRIBUTE11,
BNKACCT.BANK_ACCOUNT_NAME,
BNKACCT.CURRENCY_CODE,
BNKBRCH.BANK_NAME,
BNKBRCH.BANK_BRANCH_NAME,
ACCT.ATTRIBUTE_CATEGORY ACCT_ATTRIBUTE_CATEGORY,
ACCT.ATTRIBUTE1 ACCT_ATTRIBUTE1,
ACCT.ATTRIBUTE2 ACCT_ATTRIBUTE2,
GCC1.SEGMENT1||’-‘||GCC1.SEGMENT2||’-‘||GCC1.SEGMENT3||’-‘||GCC1.SEGMENT4||’-‘||GCC1.SEGMENT5||’-‘||GCC1.SEGMENT6||’-‘||GCC1.SEGMENT7||’-‘||GCC1.SEGMENT8 AS CASH_ACCOUNT,
GCC2.SEGMENT1||’-‘||GCC2.SEGMENT2||’-‘||GCC2.SEGMENT3||’-‘||GCC2.SEGMENT4||’-‘||GCC2.SEGMENT5||’-‘||GCC2.SEGMENT6||’-‘||GCC2.SEGMENT7||’-‘||GCC2.SEGMENT8 AS REMITTANCE_ACCOUNT,
GCC3.SEGMENT1||’-‘||GCC3.SEGMENT2||’-‘||GCC3.SEGMENT3||’-‘||GCC3.SEGMENT4||’-‘||GCC3.SEGMENT5||’-‘||GCC3.SEGMENT6||’-‘||GCC3.SEGMENT7||’-‘||GCC3.SEGMENT8 AS RECEIPT_CLEARING_ACCOUNT,
GCC4.SEGMENT1||’-‘||GCC4.SEGMENT2||’-‘||GCC4.SEGMENT3||’-‘||GCC4.SEGMENT4||’-‘||GCC4.SEGMENT5||’-‘||GCC4.SEGMENT6||’-‘||GCC4.SEGMENT7||’-‘||GCC4.SEGMENT8 AS ON_ACCOUNT,
GCC5.SEGMENT1||’-‘||GCC5.SEGMENT2||’-‘||GCC5.SEGMENT3||’-‘||GCC5.SEGMENT4||’-‘||GCC5.SEGMENT5||’-‘||GCC5.SEGMENT6||’-‘||GCC5.SEGMENT7||’-‘||GCC5.SEGMENT8 AS UNAPPLIED_ACCOUNT,
GCC6.SEGMENT1||’-‘||GCC6.SEGMENT2||’-‘||GCC6.SEGMENT3||’-‘||GCC6.SEGMENT4||’-‘||GCC6.SEGMENT5||’-‘||GCC6.SEGMENT6||’-‘||GCC6.SEGMENT7||’-‘||GCC6.SEGMENT8 AS UNIDENTIFIED_ACCOUNT,
GCC7.SEGMENT1||’-‘||GCC7.SEGMENT2||’-‘||GCC7.SEGMENT3||’-‘||GCC7.SEGMENT4||’-‘||GCC7.SEGMENT5||’-‘||GCC7.SEGMENT6||’-‘||GCC7.SEGMENT7||’-‘||GCC7.SEGMENT8 AS BANK_CHARGES_ACCOUNT
FROM AR_RECEIPT_METHODS             METHOD,
AR_RECEIPT_CLASSES             CLASS,
AR_RECEIPT_METHOD_ACCOUNTS_ALL ACCT,
CE_BANK_ACCT_USES_ALL          BNKUSE,
CE_BANK_ACCOUNTS               BNKACCT,
CE_BANK_BRANCHES_V             BNKBRCH,
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 METHOD.RECEIPT_CLASS_ID = CLASS.RECEIPT_CLASS_ID
AND ACCT.RECEIPT_METHOD_ID(+) = METHOD.RECEIPT_METHOD_ID
AND ACCT.REMIT_BANK_ACCT_USE_ID  = BNKUSE.BANK_ACCT_USE_ID(+)
AND BNKUSE.BANK_ACCOUNT_ID = BNKACCT.BANK_ACCOUNT_ID(+)
AND BNKBRCH.BANK_PARTY_ID(+) = BNKACCT.BANK_ID
AND BNKBRCH.BRANCH_PARTY_ID(+) = BNKACCT.BANK_BRANCH_ID
AND GCC1.CODE_COMBINATION_ID(+) = ACCT.CASH_CCID
AND GCC2.CODE_COMBINATION_ID(+) = ACCT.REMITTANCE_CCID
AND GCC3.CODE_COMBINATION_ID(+) = ACCT.RECEIPT_CLEARING_CCID
AND GCC4.CODE_COMBINATION_ID(+)= ACCT.ON_ACCOUNT_CCID
AND GCC5.CODE_COMBINATION_ID(+)= ACCT.UNAPPLIED_CCID
AND GCC6.CODE_COMBINATION_ID(+)= ACCT.UNIDENTIFIED_CCID
AND GCC7.CODE_COMBINATION_ID(+)= ACCT.BANK_CHARGES_CCID
–and method.name in (‘<ENTER_METHOD_NAME>)