JA India - Debtors Ledger Report- Not Supported: Reserved For Future Use
Description
- Categories
- BI Publisher
- Application
- Asia/Pacific Localizations
- Source
- India - Debtors Ledger Report (XML) - Not Supported: Reserved For Future Use
- Short Name
- JAINARDR_XML
- DB package
- JA_JAINARDR_XMLP_PKG
Imported from BI Publisher
Debtors Ledger Report
Debtors Ledger Report
SQL 354 lines
Select hzca.cust_account_id CUSTOMER_ID1 , a.party_number customer_number , a.party_name customer_name , d.gl_date , B.CUSTOMER_TRX_ID , b.trx_number , TO_CHAR(b.trx_date, 'DD-MM-YYYY') trx_date , NULL receipt_number , NULL receipt_date , SUBSTR(b.comments,1,50) remarks , d.code_combination_id account_id , b.invoice_currency_code currency_code , b.exchange_rate , d.amount amount , (d.amount * NVL(b.exchange_rate,1)) amount_other_currency, f.type , b.customer_trx_id , d.customer_trx_line_id , b.rowid, JA_JAINARDR_XMLP_PKG.cf_1formula0031(b.customer_trx_id) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(b.customer_trx_id) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(d.code_combination_id) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(d.code_combination_id) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037(f.type, d.amount, ( d.amount * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( b.comments , 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt From hz_parties a , hz_cust_accounts hzca, ra_customer_trx_ALL B, RA_CUST_TRX_LINE_GL_DIST_ALL D, GL_CODE_COMBINATIONS E, RA_CUST_TRX_TYPES_ALL F, ar_payment_schedules_all G Where a.party_id = hzca.party_id AND b.bill_to_customer_id = hzca.cust_account_id AND b.complete_flag = 'Y' AND d.customer_trx_id = b.customer_trx_id AND d.account_class = 'REC' AND e.code_combination_id = d.code_combination_id AND f.cust_trx_type_id = b.cust_trx_type_id AND f.type in ('INV','CM','DM','DEP') AND d.latest_rec_flag = 'Y' AND g.customer_trx_id = b.customer_trx_id AND b.org_id =:p_org_id AND trunc(G.gl_date) between trunc(:p_start_date) AND trunc(:p_end_date) and g.payment_schedule_id in (select min(payment_schedule_id) from ar_payment_schedules_all where customer_trx_id = g.customer_trx_id) and hzca.cust_account_id=:customer_id UNION Select hzca.cust_account_id CUSTOMER_ID1 , a.party_number customer_number , a.party_name customer_name , e.gl_date , 0 , NULL , NULL , b.receipt_number , TO_CHAR(b.receipt_date, 'DD-MM-YYYY') , NULL , d.cash_ccid account_id , b.currency_code , b.exchange_rate , b.amount amount , (b.amount * NVL(b.exchange_rate,1)) amount_other_currency, 'REC' type , 0 , 0 , b.rowid , JA_JAINARDR_XMLP_PKG.cf_1formula0031(0) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(0) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(d.cash_ccid ) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(d.cash_ccid) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037('REC', b.amount, ( b.amount * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( null , 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt From hz_parties a, hz_cust_accounts hzca, ar_cash_receipts_all B, gl_code_combinations C, ar_receipt_method_accounts_all D, ar_cash_receipt_history_all E, ar_payment_schedules_all F, (SELECT MIN(cash_receipt_history_id) cash_receipt_history_id, cash_receipt_id FROM ar_cash_receipt_history_all WHERE TRUNC(gl_date) BETWEEN TRUNC(:p_start_date) and TRUNC(:p_end_date) AND org_id = :p_org_id GROUP BY cash_receipt_id) G Where a.party_id = hzca.party_id AND b.pay_from_customer = hzca.cust_account_id AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id AND d.receipt_method_id = b.receipt_method_id AND d.cash_ccid = c.code_combination_id AND e.cash_receipt_id = b.cash_receipt_id AND e.cash_receipt_id = g.cash_receipt_id AND e.cash_receipt_history_id = g.cash_receipt_history_id AND f.cash_receipt_id = b.cash_receipt_id AND b.org_id =:p_org_id AND trunc(e.gl_date) between trunc(:p_start_date) AND trunc(:p_end_date) and hzca.cust_account_id=:customer_id UNION Select hzca.cust_account_id CUSTOMER_ID1 , a.party_number customer_number , a.party_name customer_name , e.gl_date , 0 , NULL , NULL , b.receipt_number , TO_CHAR(b.receipt_date, 'DD-MM-YYYY') , NULL , d.cash_ccid account_id , b.currency_code , b.exchange_rate , g.amount_applied amount , (g.amount_applied * NVL(b.exchange_rate,1)) amount_other_currency, 'W/O' type , 0 , 0 , b.rowid , JA_JAINARDR_XMLP_PKG.cf_1formula0031(0) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(0) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(d.cash_ccid ) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(d.cash_ccid) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037('W/O',g.amount_applied, ( g.amount_applied * NVL ( b.exchange_rate , 1 ) ), SUBSTR (null, 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt From hz_parties a, hz_cust_accounts hzca , ar_cash_receipts_all B, gl_code_combinations C, ar_receipt_method_accounts_all D, ar_cash_receipt_history_all E, ar_payment_schedules_all F, ar_receivable_applications_all G Where a.party_id = hzca.party_id AND b.pay_from_customer = hzca.cust_account_id AND g.applied_payment_schedule_id = -3 AND g.cash_receipt_id = b.cash_receipt_id and g.cash_receipt_history_id = e.cash_receipt_history_id AND g.status = 'ACTIVITY' AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id AND d.receipt_method_id = b.receipt_method_id AND d.cash_ccid = c.code_combination_id AND e.cash_receipt_id = b.cash_receipt_id AND f.cash_receipt_id = b.cash_receipt_id AND b.org_id =:p_org_id AND trunc(e.gl_date) between trunc(:p_start_date) AND trunc(:p_end_date) and not exists (select 1 from ar_cash_receipt_history_all where cash_receipt_id = b.cash_receipt_id and status = 'REVERSED' ) and hzca.cust_account_id=:customer_id UNION Select hzca.cust_account_id CUSTOMER_ID1 , a.party_number customer_number , a.party_name customer_name , e.gl_date gl_date , 0 , NULL , to_char(e.trx_date,'DD-MM-YYYY') trx_date , b.receipt_number , TO_CHAR(b.receipt_date, 'DD-MM-YYYY') , NULL , c.code_combination_id account_id , b.currency_code , b.exchange_rate , b.amount amount , (b.amount * NVL(b.exchange_rate,1)) amount_other_currency , 'REV' type , 0 , 0 , b.rowid , JA_JAINARDR_XMLP_PKG.cf_1formula0031(0) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(0) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(c.code_combination_id) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(c.code_combination_id) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037('REV', b.amount, ( b.amount * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( null , 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt From hz_parties a, hz_cust_accounts hzca , ar_cash_receipts_all B, gl_code_combinations C, ar_receipt_method_accounts_all D, ar_cash_receipt_history_all E, ar_payment_schedules_all F Where a.party_id = hzca.party_id AND b.pay_from_customer = hzca.cust_account_id AND b.remit_bank_acct_use_id = d.remit_bank_acct_use_id AND d.receipt_method_id = b.receipt_method_id AND d.cash_ccid = c.code_combination_id AND e.cash_receipt_id = b.cash_receipt_id AND f.cash_receipt_id = b.cash_receipt_id AND b.org_id = :p_org_id AND e.status = 'REVERSED' AND trunc(e.gl_date) between trunc(:p_start_date) AND trunc(:p_end_date) and b.reversal_date is not null and hzca.cust_account_id=:customer_id UNION SELECT HZCA.CUST_ACCOUNT_ID CUSTOMER_ID1 , A.PARTY_NUMBER CUSTOMER_NUMBER , A.PARTY_NAME CUSTOMER_NAME , B.GL_DATE , 0 , B.ADJUSTMENT_NUMBER , TO_CHAR(B.APPLY_DATE,'DD-MM-YYYY') trx_date , NULL receipt_number , NULL receipt_date , SUBSTR(b.comments,1,50) remarks , b.code_combination_id account_id , c.invoice_currency_code currency_code , c.exchange_rate , b.amount amount , (b.amount*NVL(c.exchange_rate,1)) amount_other_currency , 'ADJ' type , 0 , 0 , b.rowid , JA_JAINARDR_XMLP_PKG.cf_1formula0031(0) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(0) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(b.code_combination_id) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(b.code_combination_id) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037('ADJ', b.amount, ( b.amount * NVL ( c.exchange_rate , 1 ) ), SUBSTR ( b.comments , 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt FROM HZ_PARTIES A, HZ_CUST_ACCOUNTS HZCA, ar_adjustments_all b, ra_customer_trx_all c, ar_payment_schedules_all d, gl_code_combinations e WHERE b.customer_trx_id = c.customer_trx_id AND a.party_id = hzca.party_id AND c.bill_to_customer_id = hzca.cust_account_id AND b.status = 'A' AND e.code_combination_id = b.code_combination_id AND b.payment_schedule_id = d.payment_schedule_id AND b.customer_trx_id = d.customer_trx_id AND c.org_id =:p_org_id AND trunc(b.gl_date) between trunc(:p_start_date) AND trunc(:p_end_date) and hzca.cust_account_id=:customer_id UNION Select hzca.cust_account_id CUSTOMER_ID1 , a.party_number customer_number , a.party_name customer_name , d.gl_date , B.CUSTOMER_TRX_ID , b.trx_number , TO_CHAR(b.trx_date, 'DD-MM-YYYY') trx_date , NULL receipt_number , NULL receipt_date , SUBSTR(b.comments,1,50) remarks , earned_discount_ccid account_id , b.invoice_currency_code currency_code , b.exchange_rate , d.EARNED_discount_taken amount , d.ACCTD_EARNED_DISCOUNT_TAKEN amount_other_currency , 'DSC' type , b.customer_trx_id , 0 , b.rowid , JA_JAINARDR_XMLP_PKG.cf_1formula0031(b.customer_trx_id) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(b.customer_trx_id) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(earned_discount_ccid ) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(earned_discount_ccid) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037('DSC', d.EARNED_discount_taken, ( d.EARNED_discount_taken * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( b.comments , 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt From hz_parties a, hz_cust_accounts hzca , ra_customer_trx_all B, ar_receivable_applications_all D Where a.party_id = hzca.party_id AND b.bill_to_customer_id = hzca.cust_account_id AND b.complete_flag = 'Y' AND D.EARNED_DISCOUNT_TAKEN is not null and D.EARNED_DISCOUNT_TAKEN <> 0 AND b.org_id = :p_org_id and b.customer_trx_id = d.applied_customer_trx_id and d.application_type = 'CASH' and d.display = 'Y' AND trunc(d.GL_DATE) between trunc(:p_start_date) AND trunc(:p_end_date) and hzca.cust_account_id=:customer_id UNION ALL SELECT hzca.cust_account_id CUSTOMER_ID1 , a.party_number customer_number , a.party_name customer_name , d.gl_date , b.customer_trx_id , b.trx_number , to_char(b.trx_date,'DD-MM-YYYY') trx_date , c.receipt_number , to_char(c.receipt_date,'DD-MM-yyyy') receipt_date , decode(e.amount_dr, null, 'CR','DR') comments , e.code_combination_id , b.INVOICE_CURRENCY_CODE , b.exchange_rate , nvl(e.AMOUNT_DR, e.AMOUNT_CR) amount , nvl(e.ACCTD_AMOUNT_DR,e.ACCTD_AMOUNT_CR) acctd_amount , e.source_type , 0 customer_trx_id , 0 customer_trx_line_id , b.ROWID , JA_JAINARDR_XMLP_PKG.cf_1formula0031(b.customer_trx_id) order_number, JA_JAINARDR_XMLP_PKG.cf_excise_invformula(b.customer_trx_id) CF_EXCISE_INV, JA_JAINARDR_XMLP_PKG.account_codeformula(e.code_combination_id) account_code, JA_JAINARDR_XMLP_PKG.descriptionformula(e.code_combination_id) description, JA_JAINARDR_XMLP_PKG.cf_1formula0037(e.source_type, nvl(e.AMOUNT_DR, e.AMOUNT_CR), ( nvl(e.AMOUNT_DR, e.AMOUNT_CR) * NVL ( b.exchange_rate , 1 ) ), SUBSTR ( decode(e.amount_dr, null, 'CR','DR') , 1 , 50 )) tran_dr_amt, JA_JAINARDR_XMLP_PKG.CP_EXCISE_INV_NO_p CP_EXCISE_INV_NO, JA_JAINARDR_XMLP_PKG.tran_Cr_amt_p tran_Cr_amt, JA_JAINARDR_XMLP_PKG.func_dr_amt_p func_dr_amt, JA_JAINARDR_XMLP_PKG.func_cr_amt_p func_cr_amt FROM hz_parties a, hz_cust_accounts hzca , ra_customer_trx_all b, ar_cash_receipts_all c, ar_receivable_applications_all d, ar_distributions_all e WHERE a.party_id = hzca.party_id AND hzca.cust_account_id = b.BILL_TO_CUSTOMER_ID AND b.customer_trx_id = d.APPLIED_CUSTOMER_TRX_ID AND c.cash_receipt_id = d.cash_receipt_id AND e.SOURCE_ID = d.receivable_application_id AND b.org_id = :p_org_id AND e.source_Type IN ('EXCH_LOSS', 'EXCH_GAIN') AND TRUNC(d.gl_date) BETWEEN trunc(:p_start_date) AND trunc(:p_end_date) and hzca.cust_account_id=:customer_id ORDER BY 1,4 |
Parameters 6
| Parameter | Type | List of values SQL |
|---|---|---|
| Operating Unit | LOV | |
| Customer Name | LOV Oracle | |
| Customer Number | LOV Oracle | |
| Customer Type | LOV Oracle | |
| Start Date | Date | |
| End Date | Date |