AR Deposit Balance Report - Japan

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Deposit Balance Report - Japan
Short Name: ARDEPBALRPT
DB package: AR_DEP_BAL_RPT_PKG
SELECT HCA.account_number                  customer_code
      ,ARL.meaning                         invoice_type
      ,RCT.trx_number                      invoice_number
      ,RCT.trx_date                        transaction_date
      ,RCT.customer_trx_id                 customer_trx_id
      ,HZP.party_name                      customer_name
      ,APSA.gl_date                        gl_date
      ,RCT.invoice_currency_code           currency_code
      ,APSA.amount_due_original            amount
      ,AR_DEP_BAL_RPT_PKG.Description(RCT.attribute5,'ATTRIBUTE5') issuing_dept_name
	  ,RCT1.trx_number                     source_invoice_number
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('company_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE')  company
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'VALUE') department
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('account_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE')  account
      ,GCC.segment1     segment1
      ,GCC.segment2     segment2
      ,GCC.segment3     segment3
      ,GCC.segment4     segment4
      ,GCC.segment5     segment5
      ,GCC.segment6     segment6
      ,GCC.segment7     segment7
      ,GCC.segment8     segment8
      ,GCC.segment9     segment9
      ,GCC.segment10    segment10
      ,GCC.segment11    segment11
      ,GCC.segment12    segment12
      ,GCC.segment13    segment13
      ,GCC.segment14    segment14
      ,GCC.segment15    segment15
      ,GCC.segment16    segment16
      ,GCC.segment17    segment17
      ,GCC.segment18    segment18
      ,GCC.segment19    segment19
      ,GCC.segment20    segment20
      ,GCC.segment21    segment21
      ,GCC.segment22    segment22
      ,GCC.segment23    segment23
      ,GCC.segment24    segment24
      ,GCC.segment25    segment25
      ,GCC.segment26    segment26
      ,GCC.segment27    segment27
      ,GCC.segment28    segment28
      ,GCC.segment29    segment29
      ,GCC.segment30    segment30
	  ,RCT.attribute1   attribute1
	  ,RCT.attribute2   attribute2
	  ,RCT.attribute3   attribute3
	  ,RCT.attribute4   attribute4
	  ,RCT.attribute5   attribute5
	  ,RCT.attribute6   attribute6
	  ,RCT.attribute7   attribute7
	  ,RCT.attribute8   attribute8
	  ,RCT.attribute9   attribute9
	  ,RCT.attribute10  attribute10
	  ,RCT.attribute11  attribute11
	  ,RCT.attribute12  attribute12
	  ,RCT.attribute13  attribute13
	  ,RCT.attribute14  attribute14
	  ,RCT.attribute15  attribute15
	  ,fnd_flex_xml_publisher_apis.process_kff_combination_1('company_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'FULL_DESCRIPTION')    company_desc
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('account_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'FULL_DESCRIPTION')    account_desc
      ,AR_DEP_BAL_RPT_PKG.description(GCC.segment4,'SEGMENT4') segment4_desc
 FROM ra_customer_trx              RCT
     ,ra_customer_trx              RCT1
     ,ra_cust_trx_line_gl_dist_all RCTD
     ,ra_cust_trx_types_all        RCTT
     ,hz_cust_accounts             HCA
     ,hz_parties                   HZP
     ,gl_code_combinations         GCC
     ,ar_payment_schedules_all     APSA
	 ,ar_lookups                   ARL
WHERE RCT.previous_customer_trx_id = :MAIN_CUSTOMER_TRX_ID
AND   RCT1.customer_trx_id         = RCT.previous_customer_trx_id
AND   RCTD.customer_trx_id         = RCT.customer_trx_id
AND   RCT.cust_trx_type_id         = RCTT.cust_trx_type_id
AND   RCT.org_id                   = RCTT.org_id
AND   RCT.bill_to_customer_id      = HCA.cust_account_id
AND   HCA.party_id                 = HZP.party_id
AND   APSA.customer_trx_id         = RCT.customer_trx_id
AND   RCTD.code_combination_id     = GCC.code_combination_id
AND   RCTD.gl_date                 >= :gc_per_start_date
AND   RCTD.gl_date                 <= :gc_per_end_date
AND   ARL.lookup_type               ='INVOICE_TYPE'
AND   ARL.lookup_code               ='CD'
AND   RCTT.type                     = 'CM'
AND   RCT.complete_flag             = 'Y'
AND   RCTT.post_to_gl               = 'Y'
AND   RCTD.account_class            = 'REC'
AND   RCTD.latest_rec_flag          = 'Y'
&gc_customer
&gc_currency
UNION ALL
--Queries all the Deposits for adjustments are done
SELECT HCA.account_number                  customer_code
      ,ARL.meaning                         invoice_type
      ,RCT.trx_number                      invoice_number
      ,RCT.trx_date                        transaction_date
      ,RCT.customer_trx_id                 customer_trx_id
      ,HZP.party_name                      customer_name
      ,ARA.gl_date                          gl_date
      ,RCT.invoice_currency_code           currency_code
      ,ARA.amount                           amount
      ,AR_DEP_BAL_RPT_PKG.Description(RCT.attribute5,'ATTRIBUTE5') issuing_dept_name
	  ,NULL                                source_invoice_number
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('company_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE')  company
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'VALUE') department
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('account_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE')  account
      ,gcc.segment1     segment1
      ,gcc.segment2     segment2
      ,gcc.segment3     segment3
      ,gcc.segment4     segment4
      ,gcc.segment5     segment5
      ,gcc.segment6     segment6
      ,gcc.segment7     segment7
      ,gcc.segment8     segment8
      ,gcc.segment9     segment9
      ,gcc.segment10    segment10
      ,gcc.segment11    segment11
      ,gcc.segment12    segment12
      ,gcc.segment13    segment13
      ,gcc.segment14    segment14
      ,gcc.segment15    segment15
      ,gcc.segment16    segment16
      ,gcc.segment17    segment17
      ,gcc.segment18    segment18
      ,gcc.segment19    segment19
      ,gcc.segment20    segment20
      ,gcc.segment21    segment21
      ,gcc.segment22    segment22
      ,gcc.segment23    segment23
      ,gcc.segment24    segment24
      ,gcc.segment25    segment25
      ,gcc.segment26    segment26
      ,gcc.segment27    segment27
      ,gcc.segment28    segment28
      ,gcc.segment29    segment29
      ,gcc.segment30    segment30
	  ,RCT.attribute1   attribute1
	  ,RCT.attribute2   attribute2
	  ,RCT.attribute3   attribute3
	  ,RCT.attribute4   attribute4
	  ,RCT.attribute5   attribute5
	  ,RCT.attribute6   attribute6
	  ,RCT.attribute7   attribute7
	  ,RCT.attribute8   attribute8
	  ,RCT.attribute9   attribute9
	  ,RCT.attribute10  attribute10
	  ,RCT.attribute11  attribute11
	  ,RCT.attribute12  attribute12
	  ,RCT.attribute13  attribute13
	  ,RCT.attribute14  attribute14
	  ,RCT.attribute15  attribute15
	  ,fnd_flex_xml_publisher_apis.process_kff_combination_1('company_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'FULL_DESCRIPTION')                                           company_desc
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('account_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'FULL_DESCRIPTION')                                           account_desc
      ,AR_DEP_BAL_RPT_PKG.description(gcc.segment4,'SEGMENT4') segment4_desc
FROM  ra_customer_trx              RCT
     ,ra_cust_trx_types_all        RCTT
     ,ra_cust_trx_line_gl_dist_all RCTD
     ,gl_code_combinations         GCC
     ,hz_cust_accounts             HCA
     ,hz_parties                   HZP
     ,ar_adjustments_all           ARA
     ,ar_distributions_all         ARD
	 ,ar_lookups                   ARL
WHERE RCTD.customer_trx_id         = RCT.customer_trx_id
AND   RCT.customer_trx_id          = :MAIN_CUSTOMER_TRX_ID
AND   RCT.cust_trx_type_id         = RCTT.cust_trx_type_id
AND   RCT.org_id                   = RCTT.org_id
AND   RCTT.type                    = 'DEP'
AND   RCT.complete_flag            = 'Y'
AND   RCTT.post_to_gl              = 'Y'
AND   ARA.gl_date                 >= :gc_per_start_date
AND   ARA.gl_date                 <= :gc_per_end_date
AND   ARL.lookup_type              ='INVOICE_TYPE'
AND   ARL.lookup_code               ='CD'
AND   RCTD.account_class            = 'REC'
AND   RCTD.latest_rec_flag          = 'Y'
AND   ARA.adjustment_id             = ARD.source_id
AND   ARD.source_table              = 'ADJ'
AND   ARD.source_type               = 'REC'
AND   ARD.code_combination_id       = GCC.code_combination_id
AND   RCT.bill_to_customer_id       = HCA.cust_account_id
AND   HCA.party_id                  = HZP.party_id
AND   RCT.customer_trx_id           = ARA.customer_trx_id
AND   ARA.subsequent_trx_id         IS NULL
&gc_customer
&gc_currency
UNION ALL
--Queries all the Invoices for which the Deposits are applied
SELECT HCA.account_number                  customer_code
      ,ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('INV/CM',RCTT.type) invoice_type
      ,RCT.trx_number                      invoice_number
      ,RCT.trx_date                        transaction_date
      ,RCT.customer_trx_id                 customer_trx_id
      ,HZP.party_name                      customer_name
      ,ARA.gl_date                         gl_date
      ,RCT.invoice_currency_code           currency_code
      ,ARA.amount                          amount
      ,AR_DEP_BAL_RPT_PKG.Description(RCT.attribute5,'ATTRIBUTE5') issuing_dept_name
	  ,NULL                                source_invoice_number
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('company_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE')  company
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'VALUE') department
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('account_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE')  account
      ,GCC.segment1     segment1
      ,GCC.segment2     segment2
      ,GCC.segment3     segment3
      ,GCC.segment4     segment4
      ,GCC.segment5     segment5
      ,GCC.segment6     segment6
      ,GCC.segment7     segment7
      ,GCC.segment8     segment8
      ,GCC.segment9     segment9
      ,GCC.segment10    segment10
      ,GCC.segment11    segment11
      ,GCC.segment12    segment12
      ,GCC.segment13    segment13
      ,GCC.segment14    segment14
      ,GCC.segment15    segment15
      ,GCC.segment16    segment16
      ,GCC.segment17    segment17
      ,GCC.segment18    segment18
      ,GCC.segment19    segment19
      ,GCC.segment20    segment20
      ,GCC.segment21    segment21
      ,GCC.segment22    segment22
      ,GCC.segment23    segment23
      ,GCC.segment24    segment24
      ,GCC.segment25    segment25
      ,GCC.segment26    segment26
      ,GCC.segment27    segment27
      ,GCC.segment28    segment28
      ,GCC.segment29    segment29
      ,GCC.segment30    segment30
	  ,RCT.attribute1   attribute1
	  ,RCT.attribute2   attribute2
	  ,RCT.attribute3   attribute3
	  ,RCT.attribute4   attribute4
	  ,RCT.attribute5   attribute5
	  ,RCT.attribute6   attribute6
	  ,RCT.attribute7   attribute7
	  ,RCT.attribute8   attribute8
	  ,RCT.attribute9   attribute9
	  ,RCT.attribute10  attribute10
	  ,RCT.attribute11  attribute11
	  ,RCT.attribute12  attribute12
	  ,RCT.attribute13  attribute13
	  ,RCT.attribute14  attribute14
	  ,RCT.attribute15  attribute15
	  ,fnd_flex_xml_publisher_apis.process_kff_combination_1('company_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'FULL_DESCRIPTION')    company_desc
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('account_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'FULL_DESCRIPTION')    account_desc
      ,AR_DEP_BAL_RPT_PKG.description(GCC.segment4,'SEGMENT4') segment4_desc
 FROM ra_customer_trx              RCT
     ,ra_cust_trx_line_gl_dist_all RCTD
     ,ra_cust_trx_types_all        RCTT
     ,hz_cust_accounts             HCA
     ,hz_parties                   HZP
     ,gl_code_combinations         GCC
     ,ar_adjustments_all           ARA
     ,ar_distributions_all         ARD
WHERE RCT.initial_customer_trx_id = :MAIN_CUSTOMER_TRX_ID
AND   RCTD.customer_trx_id         = RCT.customer_trx_id
AND   RCT.customer_trx_id          = ARA.customer_trx_id
AND   RCT.cust_trx_type_id         = RCTT.cust_trx_type_id
AND   RCT.org_id                   = RCTT.org_id
AND   RCT.bill_to_customer_id      = HCA.cust_account_id
AND   HCA.party_id                 = HZP.party_id
AND   ARD.code_combination_id      = GCC.code_combination_id
AND   ARA.gl_date                 >= :gc_per_start_date
AND   ARA.gl_date                 <= :gc_per_end_date
AND   ARA.adjustment_id             = ARD.source_id
AND   ARD.source_table              = 'ADJ'
AND   ARD.source_type               = 'REC'
AND   RCTT.type                     = 'INV'
AND   RCT.complete_flag             = 'Y'
AND   ARA.subsequent_trx_id           IS NULL
AND   RCTT.post_to_gl               = 'Y'
AND   RCTD.account_class            = 'REC'
AND   RCTD.latest_rec_flag          = 'Y'
&gc_customer
&gc_currency
UNION ALL
--Queries all the Credit Memos that are mapped to Invoices
-- for which the Deposits are applied
SELECT HCA.account_number                  customer_code
      ,ARL.meaning                         invoice_type
      ,RCT.trx_number                      invoice_number
      ,RCT.trx_date                        transaction_date
      ,RCT.customer_trx_id                 customer_trx_id
      ,HZP.party_name                      customer_name
      ,ARA.gl_date                         gl_date
      ,RCT.invoice_currency_code           currency_code
      ,ARA.amount                          amount
      ,NULL                                issuing_dept_name
	  ,RCT1.trx_number                     source_invoice_number
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('company_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'VALUE')  company
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('cost_ctr_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'FA_COST_CTR', 'N', 'VALUE') department
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('account_select', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'VALUE')  account
      ,GCC.segment1     segment1
      ,GCC.segment2     segment2
      ,GCC.segment3     segment3
      ,GCC.segment4     segment4
      ,GCC.segment5     segment5
      ,GCC.segment6     segment6
      ,GCC.segment7     segment7
      ,GCC.segment8     segment8
      ,GCC.segment9     segment9
      ,GCC.segment10    segment10
      ,GCC.segment11    segment11
      ,GCC.segment12    segment12
      ,GCC.segment13    segment13
      ,GCC.segment14    segment14
      ,GCC.segment15    segment15
      ,GCC.segment16    segment16
      ,GCC.segment17    segment17
      ,GCC.segment18    segment18
      ,GCC.segment19    segment19
      ,GCC.segment20    segment20
      ,GCC.segment21    segment21
      ,GCC.segment22    segment22
      ,GCC.segment23    segment23
      ,GCC.segment24    segment24
      ,GCC.segment25    segment25
      ,GCC.segment26    segment26
      ,GCC.segment27    segment27
      ,GCC.segment28    segment28
      ,GCC.segment29    segment29
      ,GCC.segment30    segment30
	  ,RCT.attribute1   attribute1
	  ,RCT.attribute2   attribute2
	  ,RCT.attribute3   attribute3
	  ,RCT.attribute4   attribute4
	  ,NULL             attribute5
	  ,RCT.attribute6   attribute6
	  ,RCT.attribute7   attribute7
	  ,RCT.attribute8   attribute8
	  ,RCT.attribute9   attribute9
	  ,RCT.attribute10  attribute10
	  ,RCT.attribute11  attribute11
	  ,RCT.attribute12  attribute12
	  ,RCT.attribute13  attribute13
	  ,RCT.attribute14  attribute14
	  ,RCT.attribute15  attribute15
	  ,fnd_flex_xml_publisher_apis.process_kff_combination_1('company_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_BALANCING', 'N', 'FULL_DESCRIPTION')    company_desc
      ,fnd_flex_xml_publisher_apis.process_kff_combination_1('account_desc', 'SQLGL', 'GL#', GCC.CHART_OF_ACCOUNTS_ID, NULL, GCC.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'N', 'FULL_DESCRIPTION')    account_desc
      ,AR_DEP_BAL_RPT_PKG.description(GCC.segment4,'SEGMENT4') segment4_desc
 FROM ra_customer_trx              RCT
     ,ra_customer_trx              RCT1
     ,ra_cust_trx_line_gl_dist_all RCTD
     ,ra_cust_trx_types_all        RCTT
     ,hz_cust_accounts             HCA
     ,hz_parties                   HZP
     ,gl_code_combinations         GCC
     ,ar_adjustments_all           ARA
	 ,ar_distributions_all         ARD
	 ,ar_lookups                   ARL
WHERE RCT.initial_customer_trx_id   = :MAIN_CUSTOMER_TRX_ID
AND   RCT1.customer_trx_id          = RCT.previous_customer_trx_id
AND   RCTD.customer_trx_id          = RCT.customer_trx_id
AND   RCT.customer_trx_id           = ARA.subsequent_trx_id
AND   RCT.cust_trx_type_id          = RCTT.cust_trx_type_id
AND   RCT.org_id                    = RCTT.org_id
AND   RCT.bill_to_customer_id       = HCA.cust_account_id
AND   HCA.party_id                  = HZP.party_id
AND   ARD.code_combination_id       = GCC.code_combination_id
AND   ARA.gl_date                   >= :gc_per_start_date
AND   ARA.gl_date                   <= :gc_per_end_date
AND   ARL.lookup_type               ='INVOICE_TYPE'
AND   ARL.lookup_code               ='CAI'
AND   ARA.adjustment_id             = ARD.source_id
AND   ARD.source_table              = 'ADJ'
AND   ARD.source_type               = 'REC'
AND   RCTT.type                     = 'CM'
AND   RCT.complete_flag             = 'Y'
AND   RCTT.post_to_gl               = 'Y'
AND   RCTD.account_class            = 'REC'
AND   RCTD.latest_rec_flag          = 'Y'
&gc_customer
&gc_currency
Parameter Name SQL text Validation
Customer Dummy
 
Currency
 
LOV Oracle
Customer
 
LOV Oracle
Period To
 
LOV Oracle
Period From
 
LOV Oracle