JG EMEA VAT: Turnover AR Non TRL Extract
Description
- Categories
- BI Publisher
- Application
- Regional Localizations
- Source
- EMEA VAT: Turnover AR Non TRL Extract
- Short Name
- JGZZARTE_NTRL_XMLP
- DB package
- JG_ZZ_RTCE_DT_PKG
Imported from BI Publisher
Turnover AR Non TRL Extract
Turnover AR Non TRL Extract
SQL 262 lines
SELECT CURR_INV_FLAG , RACL_INV_CUSTOMER_ID , INVOICE_COUNT , PRIOR_INVOICE_COUNT , TAXABLE_AMOUNT , TAX_AMOUNT , EXEMPT_AMOUNT , NONTAXABLE_AMOUNT , CF_TOTAL_AMOUNT FROM (SELECT 'Y' CURR_INV_FLAG ,nvl( invlines.jg_info_n3 /*sold_to_customer_id*/ , invlines.jg_info_n4 /*bill_to_customer_id*/ ) racl_inv_customer_id /* curr_inv_customer_id */ ,count(distinct invlines.jg_info_n1 /*customer_trx_id*/) INVOICE_COUNT , 0 PRIOR_INVOICE_COUNT ,sum(decode(l1.lookup_code || l2.lookup_code, '' ,decode(invlines.jg_info_v3 /*line_type*/, 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0) ,0) ) TAXABLE_AMOUNT ,sum(decode(l1.lookup_code || l2.lookup_code, '' ,decode(taxlines.jg_info_v3 /*line_type*/, 'TAX' ,round(taxlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0) ,0) ) TAX_AMOUNT ,sum(decode(l1.lookup_code, '' ,0 ,decode(invlines.jg_info_v3 /*line_type*/, 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 )) ) EXEMPT_AMOUNT ,sum(decode(l2.lookup_code, '' ,0 ,decode(invlines.jg_info_v3 /*line_type*/ , 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ) ) NONTAXABLE_AMOUNT, jg_zz_rtce_dt_pkg.cf_total_amount(sum ( decode ( l1.lookup_code || l2.lookup_code , '' , decode ( invlines.jg_info_v3 , 'LINE' , round ( invlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) , 0 ) ), sum ( decode ( l1.lookup_code || l2.lookup_code , '' , decode ( taxlines.jg_info_v3 , 'TAX' , round ( taxlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) , 0 ) ), sum ( decode ( l1.lookup_code , '' , 0 , decode ( invlines.jg_info_v3 , 'LINE' , round ( invlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) ) ), sum ( decode ( l2.lookup_code , '' , 0 , decode ( invlines.jg_info_v3 , 'LINE' , round ( invlines.jg_info_n10 * nvl ( invlines.jg_info_n5 , 1 ) , 0 ) , 0 ) ) )) CF_TOTAL_AMOUNT from jg_zz_vat_trx_gt invlines ,jg_zz_vat_trx_gt taxlines ,zx_rates_b zxrates ,zx_taxes_b zxtaxes ,zx_report_codes_assoc zxass ,ar_lookups l1 ,ar_lookups l2 ,ar_lookups l3 where :P_CALLED_FROM = 'JEITRACL' and nvl(invlines.jg_info_n3, invlines.jg_info_n4) = :customer_account_id and invlines.jg_info_v3 /*line_type*/ = 'LINE' and invlines.jg_info_n7 /*customer_trx_line_id*/ = taxlines.jg_info_n9 /*link_to_cust_trx_line_id*/ and taxlines.jg_info_v3 /*line_type*/ = 'TAX' and taxlines.jg_info_n11 /*vat_tax_id*/ = zxrates.tax_rate_id(+) and zxrates.content_owner_id = zxtaxes.content_owner_id and zxrates.tax_regime_code = zxtaxes.tax_regime_code and zxrates.tax = zxtaxes.tax and zxrates.tax_rate_id = zxass.entity_id and zxass.entity_code = 'ZX_RATES' and l1.lookup_type(+) = 'JE_EXEMPT_TAX_TYPE' and zxass.REPORTING_CODE_CHAR_VALUE = l1.lookup_code(+) and l2.lookup_type(+) = 'JE_NONTAXABLE_TAX_TYPE' and zxass.REPORTING_CODE_CHAR_VALUE = l2.lookup_code(+) and l3.lookup_type(+) = 'JE_DEFERRED_TAX_TYPE' and zxrates.def_rec_settlement_option_code = l3.lookup_code(+) and ( ( invlines.JG_INFO_D2 /* printing_original_date*/ between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date and not exists ( select 1 from ra_cust_trx_line_gl_dist rctlgd where rctlgd.customer_trx_line_id = invlines.jg_info_n7 /*customer_trx_line_id*/ /* and rctlgd.gl_posted_date is null */ and rctlgd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date ) and l3.lookup_type is null ) or ( exists ( select ara.gl_date from ar_receivable_applications ara ,ar_cash_receipts acr where ara.applied_customer_trx_id = invlines.jg_info_n1/*customer_trx_id*/ and ara.status = 'APP' and ara.cash_receipt_id = acr.cash_receipt_id group by ara.gl_date having min(ara.gl_date) between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date ) and l3.lookup_type is not null ) or ( exists ( select null from ra_cust_trx_line_gl_dist crd ,ra_customer_trx_lines crl ,ra_customer_trx crh where crh.customer_trx_id = invlines.jg_info_n6 /*previous_customer_trx_id*/ and crh.customer_trx_id = crl.customer_trx_id and crl.customer_trx_line_id = crd.customer_trx_line_id /* and crd.gl_posted_date is not null */ and crd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date ) and l3.lookup_type is not null ) ) group by nvl(invlines.jg_info_n3 /*sold_to_customer_id*/, invlines.jg_info_n4 /*bill_to_customer_id*/) UNION SELECT 'Y' CURR_INV_FLAG ,null RACL_INV_CUSTOMER_ID ,0 INVOICE_COUNT ,0 PRIOR_INVOICE_COUNT ,0 TAXABLE_AMOUNT ,0 TAX_AMOUNT ,0 EXEMPT_AMOUNT ,0 NONTAXABLE_AMOUNT ,0 CF_TOTAL_AMOUNT FROM DUAL UNION /* Following query will return the prior year invoices accounted this year */ select 'N' CURR_INV_FLAG ,nvl( invlines.jg_info_n3 /*sold_to_customer_id*/ , invlines.jg_info_n4 /*bill_to_customer_id*/ ) racl_inv_customer_id /* prior_inv_customer_id */ , 0 INVOICE_COUNT ,count(distinct invlines.jg_info_n1 /*customer_trx_id*/) PRIOR_INVOICE_COUNT ,sum(decode(l1.lookup_code || l2.lookup_code, '' ,decode(invlines.jg_info_v3 /*line_type*/, 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ,0) ) TAXABLE_AMOUNT ,sum(decode(l1.lookup_code || l2.lookup_code, '' ,decode(taxlines.jg_info_v3 /*line_type*/, 'TAX' ,round(taxlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ,0) ) TAX_AMOUNT ,sum(decode(l1.lookup_code, '' ,0 ,decode(invlines.jg_info_v3 /*line_type*/, 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ) ) EXEMPT_AMOUNT ,sum(decode(l2.lookup_code, '' ,0 ,decode(invlines.jg_info_v3 /*line_type*/ , 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ) ) NONTAXABLE_AMOUNT ,jg_zz_rtce_dt_pkg.cf_total_amount ( sum(decode(l1.lookup_code || l2.lookup_code, '' ,decode(invlines.jg_info_v3 /*line_type*/, 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ,0) ) /*taxable_amount*/ , sum(decode(l1.lookup_code || l2.lookup_code, '' ,decode(taxlines.jg_info_v3 /*line_type*/, 'TAX' ,round(taxlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ,0) ) /*tax_amount*/ , sum(decode(l1.lookup_code, '' ,0 ,decode(invlines.jg_info_v3 /*line_type*/, 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ) ) /*exempt_amount*/ , sum(decode(l2.lookup_code, '' ,0 ,decode(invlines.jg_info_v3 /*line_type*/ , 'LINE' ,round(invlines.jg_info_n10 /*extended_amount*/ * nvl(invlines.jg_info_n5 /*exchange_rate*/,1),0) ,0 ) ) ) /*nontaxable_amount*/ ) CF_TOTAL_AMOUNT from jg_zz_vat_trx_gt invlines ,jg_zz_vat_trx_gt taxlines ,zx_rates_b zxrates ,zx_taxes_b zxtaxes ,zx_report_codes_assoc zxass ,ar_lookups l1 ,ar_lookups l2 ,ar_lookups l3 where :P_CALLED_FROM = 'JEITRACL' and nvl( invlines.jg_info_n3,invlines.jg_info_n4) = :customer_account_id and invlines.jg_info_v3 /*line_type*/ = 'LINE' and invlines.jg_info_n7 /*customer_trx_line_id*/ = taxlines.jg_info_n9 /*link_to_cust_trx_line_id*/ and taxlines.jg_info_v3 /*line_type*/ = 'TAX' and taxlines.jg_info_n11 /*vat_tax_id*/ = zxrates.tax_rate_id(+) and zxrates.content_owner_id = zxtaxes.content_owner_id and zxrates.tax_regime_code = zxtaxes.tax_regime_code and zxrates.tax = zxtaxes.tax and zxrates.tax_rate_id = zxass.entity_id and zxass.entity_code = 'ZX_RATES' and l1.lookup_type(+) = 'JE_EXEMPT_TAX_TYPE' and zxass.REPORTING_CODE_CHAR_VALUE = l1.lookup_code(+) and l2.lookup_type(+) = 'JE_NONTAXABLE_TAX_TYPE' and zxass.REPORTING_CODE_CHAR_VALUE = l2.lookup_code(+) and l3.lookup_type(+) = 'JE_DEFERRED_TAX_TYPE' and zxrates.def_rec_settlement_option_code = l3.lookup_code(+) and invlines.JG_INFO_D2 /* printing_original_date */ between add_months( jg_zz_rtce_dt_pkg.cf_period_start_date,-12) and add_months(jg_zz_rtce_dt_pkg.cf_period_end_date, -12) and exists ( select 1 from ra_cust_trx_line_gl_dist rctlgd where rctlgd.customer_trx_line_id = invlines.jg_info_n7 /*customer_trx_line_id*/ /* and rctlgd.gl_posted_date is not null */ and rctlgd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date ) and ( exists ( select 'x' from ar_receivable_applications ara ,ar_cash_receipts acr where ara.applied_customer_trx_id = invlines.jg_info_n1/*customer_trx_id*/ and ara.status = 'APP' and ara.cash_receipt_id = acr.cash_receipt_id ) or l3.lookup_type is null ) and ( exists ( select null from ra_cust_trx_line_gl_dist crd ,ra_customer_trx_lines crl ,ra_customer_trx crh where crh.customer_trx_id = invlines.jg_info_n6 /*previous_customer_trx_id*/ and crh.customer_trx_id = crl.customer_trx_id and crl.customer_trx_line_id = crd.customer_trx_line_id /* and crd.gl_posted_date is not null */ and crd.gl_date between jg_zz_rtce_dt_pkg.cf_period_start_date and jg_zz_rtce_dt_pkg.cf_period_end_date ) or l3.lookup_type is not null ) group by nvl( invlines.jg_info_n3 /*sold_to_customer_id*/ , invlines.jg_info_n4 /*bill_to_customer_id*/ ) UNION SELECT 'N' CURR_INV_FLAG ,null RACL_INV_CUSTOMER_ID ,0 INVOICE_COUNT ,0 PRIOR_INVOICE_COUNT ,0 TAXABLE_AMOUNT ,0 TAX_AMOUNT ,0 EXEMPT_AMOUNT ,0 NONTAXABLE_AMOUNT ,0 CF_TOTAL_AMOUNT FROM DUAL) ORDER BY DECODE(CURR_INV_FLAG, 'Y', 1, 'N', 2) |
Parameters 2
| Parameter | Type | List of values SQL |
|---|---|---|
| Reporting Identifier | LOV Oracle | |
| Fiscal Year | LOV Oracle |