Receivables to GL Link in 11i

SELECT je_header_id, line_number, trx_number, myrank, item_number,
       customer_number, customer_name, SOURCE, CATEGORY, batch_name,
       transaction_date, posted_date, period_name, currency_code,
       account_number, description,
       CASE
          WHEN myrank = 1
             THEN debits
          ELSE NULL
       END debits, CASE
          WHEN myrank = 1
             THEN credits
          ELSE NULL
       END credits
  FROM (SELECT   rcta.trx_number, rcta.customer_trx_id, gh.je_header_id,
                 ac.customer_number, ac.customer_name, gh.je_source SOURCE,
                 gh.je_category CATEGORY, gh.NAME batch_name,
                 TO_CHAR (gh.default_effective_date,
                          'DD-MON-YYYY'
                         ) transaction_date,
                 TO_CHAR (gh.posted_date, 'DD-MON-YYYY') posted_date,
                 gh.period_name period_name, gh.currency_code currency_code,
                 gl.je_line_num line_number,
                    gcc.segment1
                 || '.'
                 || gcc.segment2
                 || '.'
                 || gcc.segment3
                 || '.'
                 || gcc.segment4
                 || '.'
                 || gcc.segment5
                 || '.'
                 || gcc.segment6 account_number,
                 gh.description description, gl.accounted_dr debits,
                 gl.accounted_cr credits, rctla.inventory_item_id,
                 (SELECT segment1
                    FROM mtl_system_items_b msib
                   WHERE msib.inventory_item_id =
                                          rctla.inventory_item_id
                     AND msib.organization_id = rctla.warehouse_id)
                                                                  item_number,
                 DENSE_RANK () OVER (PARTITION BY gh.je_header_id, gl.je_line_num ORDER BY rctlg.cust_trx_line_gl_dist_id)
                                                                    AS myrank
            FROM gl_je_headers gh,
                 gl_je_lines gl,
                 gl_code_combinations gcc,
                 fnd_user fu,
                 gl_sets_of_books gsob,
                 gl_import_references gir,
                 ra_customer_trx_all rcta,
                 ra_cust_trx_line_gl_dist_all rctlg,
                 ra_customer_trx_lines_all rctla,
                 ar_customers ac
           WHERE gh.je_header_id = gl.je_header_id
             AND gl.code_combination_id = gcc.code_combination_id
             AND gh.last_updated_by = fu.user_id
             AND gh.period_name = :p_period
             AND gh.status = 'P'
             AND gh.set_of_books_id = gsob.set_of_books_id
             AND gsob.set_of_books_id = 1
             AND gh.je_source = 'Receivables'
             AND gir.je_header_id = gh.je_header_id
             AND gir.je_line_num = gl.je_line_num
             AND gir.je_batch_id = gh.je_batch_id
             AND TO_CHAR (gir.reference_2) = TO_CHAR (rcta.customer_trx_id)
             AND TO_CHAR (gir.reference_3) =
                                      TO_CHAR (rctlg.cust_trx_line_gl_dist_id)
             AND rcta.customer_trx_id = rctlg.customer_trx_id
             AND rctla.customer_trx_line_id = rctlg.customer_trx_line_id
             AND rctlg.customer_trx_id = rctla.customer_trx_id
             AND rctla.customer_trx_id = rcta.customer_trx_id
             AND rcta.bill_to_customer_id = ac.customer_id(+)
        ORDER BY gh.je_header_id, gl.je_line_num)

Oracle Apps(EBS) - AR Receipt Register Query with Bank statement Header and Line Details

Below query is useful when you required  Non Misc Receipts Along with Bank Statement Header , Line Details and Activity name ( like Receipt Write off)

SELECT ACRA.RECEIPT_DATE
,( select distinct CSH.STATEMENT_NUMBER from  apps.ce_statement_reconcils_all CSRA,
                                    apps.ce_statement_lines CSL,
                                    apps.ce_statement_headers CSH 
                            where  CSRA.REFERENCE_ID=ACRHA.CASH_RECEIPT_HISTORY_ID
                                    AND CSRA.STATEMENT_LINE_ID=CSL.STATEMENT_LINE_ID
                                    AND CSL.STATEMENT_HEADER_ID=CSH.STATEMENT_HEADER_ID)  STATEMENT_NUMBER
,( select CSL.LINE_NUMBER from  apps.ce_statement_reconcils_all CSRA,
                                    apps.ce_statement_lines CSL,
                                    apps.ce_statement_headers CSH 
                            where  CSRA.REFERENCE_ID=ACRHA.CASH_RECEIPT_HISTORY_ID
                                    AND CSRA.STATEMENT_LINE_ID=CSL.STATEMENT_LINE_ID
                                    AND CSL.STATEMENT_HEADER_ID=CSH.STATEMENT_HEADER_ID)  LINE_NUMBER
,ACRA.RECEIPT_NUMBER
,DECODE(ARCAA.applied_payment_schedule_id, -1,NVL(SUBSTR(HCA_ONACC.ACCOUNT_NUMBER, INSTR(HCA_ONACC.ACCOUNT_NUMBER, '.')+1), HCA_ONACC.ACCOUNT_NUMBER), NVL(SUBSTR(HCA.ACCOUNT_NUMBER, INSTR(HCA.ACCOUNT_NUMBER, '.')+1), HCA.ACCOUNT_NUMBER)) CUSTOMER_NUMBER
,DECODE(ARCAA.applied_payment_schedule_id, -1,HP_ONACC.PARTY_NAME,HP.PARTY_NAME ) CUSTOMER_NAME
,DECODE(ARCAA.applied_payment_schedule_id, -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9,
arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK') ,RCTA.TRX_NUMBER) APPLIED_TO
,ARCAA.APPLY_DATE
,art.name ACTIVITY_NAME
,ARCAA.AMOUNT_APPLIED
,ACRA.AMOUNT RECEIPT_AMOUNT
,ACRA.CURRENCY_CODE TRANSACTION_CURRENCY
,FU.USER_NAME APPLIED_USER
,ACRA.CREATION_DATE
                    FROM apps.ar_receivable_applications_all ARCAA,
apps.ar_cash_receipts_all ACRA,
apps.ar_cash_receipt_history_all ACRHA,
apps.ra_customer_trx_all RCTA,
apps.hz_cust_accounts HCA,
apps.hz_parties HP,
apps.hz_cust_accounts HCA_ONACC,
apps.hz_parties HP_ONACC,
apps.fnd_user FU
,ar_receivables_trx_ALL art                         
                  WHERE ARCAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID(+)
AND ACRHA.CASH_RECEIPT_ID(+)=ACRA.CASH_RECEIPT_ID
AND ARCAA.APPLIED_CUSTOMER_TRX_ID=RCTA.CUSTOMER_TRX_ID(+)
AND RCTA.BILL_TO_CUSTOMER_ID=HCA.CUST_ACCOUNT_ID(+)
AND HCA.PARTY_ID=HP.PARTY_ID(+)
AND HCA_ONACC.CUST_ACCOUNT_ID(+)=ARCAA.ON_ACCT_CUST_ID
AND HCA_ONACC.PARTY_ID=HP_ONACC.PARTY_ID(+)
AND FU.USER_ID(+)=ARCAA.CREATED_BY
AND ARCAA.STATUS NOT IN ('UNAPP','UNID')
                            AND art.receivables_trx_id(+) = ARCAA.receivables_trx_id
                            AND ARCAA.GL_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_gl_date_from,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE) AND NVL(TO_DATE(SUBSTR(:p_gl_date_to,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE)
                            AND ARCAA.APPLY_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_apply_date_from,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE) AND NVL(TO_DATE(SUBSTR(:p_apply_date_to,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE)
                            AND(( HP.PARTY_NAME between NVL(:p_customer_name_low,HP.PARTY_NAME) AND NVL(:p_customer_name_high,HP.PARTY_NAME)) or
(HP_ONACC.PARTY_NAME between NVL(:p_customer_name_low,HP_ONACC.PARTY_NAME) AND NVL(:p_customer_name_high,HP_ONACC.PARTY_NAME))
   )
                            AND( ( HCA.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA.ACCOUNT_NUMBER) or
   (HCA_ONACC.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA_ONACC.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA_ONACC.ACCOUNT_NUMBER))
  )
   )
                            AND ARCAA.ORG_ID=:p_org AND
                            ACRA.ORG_ID=:p_org AND
                            ACRHA.ORG_ID=:p_org
AND ACRA.TYPE != 'MISC'
AND ARCAA.REVERSAL_GL_DATE is NULL
and ACRHA.REVERSAL_GL_DATE is NULL
            UNION
                    SELECT ACRA.RECEIPT_DATE
                            ,NULL STATEMENT_NUMBER
                            ,NULL LINE_NUMBER
                            ,ACRA.RECEIPT_NUMBER
                            ,NVL(SUBSTR(HCA_ONACC.ACCOUNT_NUMBER, INSTR(HCA_ONACC.ACCOUNT_NUMBER, '.')+1), HCA_ONACC.ACCOUNT_NUMBER) CUSTOMER_NUMBER
                            ,HP_ONACC.PARTY_NAME CUSTOMER_NAME
                            ,DECODE(ARCAA.applied_payment_schedule_id, -1, arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'ON_ACC'), -3,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'RCPT_WRITE_OFF'), -4,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CLAIM_INV'), -6,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_REFUND'), -8,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'REFUND'), -9,
                                        arpt_sql_func_util.get_lookup_meaning('ACTIVITY_APPS', 'CC_CHARGEBACK') ,null) APPLIED_TO
                            ,ARCAA.APPLY_DATE
                            ,art.name ACTIVITY_NAME
                            ,ARCAA.AMOUNT_APPLIED
                            ,ACRA.AMOUNT RECEIPT_AMOUNT
                            ,ACRA.CURRENCY_CODE TRANSACTION_CURRENCY
                            ,FU.USER_NAME APPLIED_USER
                            ,ACRA.CREATION_DATE
                    FROM apps.ar_receivable_applications_all ARCAA,
                            apps.ar_cash_receipts_all ACRA,
                            apps.ar_cash_receipt_history_all ACRHA,
                            apps.hz_cust_accounts HCA_ONACC,
                            apps.hz_parties HP_ONACC,
                            apps.fnd_user FU
                            ,ar_receivables_trx_ALL art
                    WHERE ARCAA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID
AND ACRHA.CASH_RECEIPT_ID=ACRA.CASH_RECEIPT_ID
AND ARCAA.APPLIED_CUSTOMER_TRX_ID IS NULL
AND (HCA_ONACC.CUST_ACCOUNT_ID=ARCAA.ON_ACCT_CUST_ID or HCA_ONACC.CUST_ACCOUNT_ID=ACRA.PAY_FROM_CUSTOMER )
AND HCA_ONACC.PARTY_ID=HP_ONACC.PARTY_ID(+)
AND FU.USER_ID(+)=ARCAA.CREATED_BY
                            AND ARCAA.STATUS NOT IN ('UNAPP','UNID')
                            AND art.receivables_trx_id(+) = ARCAA.receivables_trx_id AND
ARCAA.ORG_ID=:p_org
AND ACRA.ORG_ID=:p_org
AND ACRHA.ORG_ID=:p_org
AND ACRA.TYPE != 'MISC'                         
                            AND ARCAA.APPLIED_PAYMENT_SCHEDULE_ID <0
                            AND ARCAA.AMOUNT_APPLIED >0
                            AND ARCAA.REVERSAL_GL_DATE IS NULL
                            AND ACRHA.REVERSAL_GL_DATE IS NULL
                            AND ARCAA.GL_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_gl_date_from,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE) AND NVL(TO_DATE(SUBSTR(:p_gl_date_to,1,10),'yyyy/mm/dd'),ARCAA.GL_DATE)
                            AND ARCAA.APPLY_DATE BETWEEN NVL(TO_DATE(SUBSTR(:p_apply_date_from,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE) AND NVL(TO_DATE(SUBSTR(:p_apply_date_to,1,10),'yyyy/mm/dd'),ARCAA.APPLY_DATE)
                            AND HP_ONACC.PARTY_NAME between NVL(:p_customer_name_low,HP_ONACC.PARTY_NAME) AND NVL(:p_customer_name_high,HP_ONACC.PARTY_NAME)
                            AND HCA_ONACC.ACCOUNT_NUMBER between NVL(:p_customer_number_low,HCA_ONACC.ACCOUNT_NUMBER) AND NVL(:p_customer_number_high,HCA_ONACC.ACCOUNT_NUMBER)
                            order by RECEIPT_NUMBER,CREATION_DATE ASC  ;

Electronic File Transfer in AR – Brazil – Technical Information

Companies in Brazil use bank services to collect customers. The Brazilian Electronic File Transfer for Receivables (CLL F034) provides the functionality to electronically transfer collection documents to banks using files in the standard CNAB format. The CLL F034 features allows companies to meet Brazilian Bank Transfer requirements by providing customers with the most important bank file formats in Brazil, as each bank has its own format.

Tables:

Setup Tables:

JL_BR_AR_BANK_INSTRUCTIONS à Bank Instruction table

JL_BR_AR_BANK_OCCURENCES à Bank Occurrences table

JL_BR_AR_REC_MET_ACCTS_ExT_ALL à Receipt Method Accounts table

Transaction Tables:

JL_BR_AR_SELECT_CONTROLS_ALL à Bank Transfer Remittance Batches selection criteria table

JL_BR_AR_SELECT_ACCOUNTS_ALL à Bank Account Distributions selection criteria table

JL_BR_AR_BORDEROS_ALL à Header for Remittance and Occurrence batches

JL_BR_AR_REMIT_BORDEROS_ALL à Bordero remittance additional information

JL_BR_AR_COLLECTION_DOCS_ALL à Collection documents associated to remittance borderos

JL_BR_AR_OCCURRENCE_DOCS_ALL à All occurrences related to the collection documents

JL_BR_AR_RET_INTERFACE_ALL à Interface table for Bank returns

JL_BR_AR_RET_INTERFACE_EXT_ALL à Interface table with rejected lines

JL_BR_AR_BANK_RETURNS_ALL à Temporary table to store bank returned occurrences.

Brazil Electronic File Transfer in Receivables

Companies in Brazil use bank services to collect customers. The Brazilian Electronic File Transfer for Receivables (CLL F034) provides the functionality to electronically transfer collection documents to banks using files in the standard CNAB format. The CLL F034 features allows companies to meet Brazilian Bank Transfer requirements by providing customers with the most important bank file formats in Brazil, as each bank has its own format.

Bank Collection Flow

Bank Collection – Setup

1.       Create Bank Accounts: Receivables à Setup à Receipt à Banks à Bank Accounts

2.       Define Format Programs: Receivables à Setup à Receipt à Format Programs

3.       Define Receipt Class: Receivables à Setup à Receipt à Receipt Classes

4.       Define Receipt method Accounts: Brazilian Localizations à Setup à Bank Collections à Receipt Method Accounts

5.       Define bank Instruction Codes: Brazilian Localization à Setup à Bank Collections à Instruction Codes

6.       Define Bank Occurrence Codes: Brazilian Localization à Setup à Bank Collections à Occurrence Codes