AR Deposit Balance Report - Japan
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Receivables
Source: Deposit Balance Report - Japan
Short Name: ARDEPBALRPT
DB package: AR_DEP_BAL_RPT_PKG
Application: Receivables
Source: Deposit Balance Report - Japan
Short Name: ARDEPBALRPT
DB package: AR_DEP_BAL_RPT_PKG
Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS
Contact us to schedule a demo or if you need help with the installation
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 | |
|---|---|---|---|
| Operating Unit |
|
LOV | |
| Period From |
|
LOV Oracle | |
| Period To |
|
LOV Oracle | |
| Customer |
|
LOV Oracle | |
| Currency |
|
LOV Oracle |
