[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(SUM(amount_remaining), -9999) amount_remaining
FROM ap_invoices_all INV
,ap_invoice_payments_all PAY
,ap_checks_all CHECKS
,ap_payment_schedules_all APS
WHERE APS.invoice_id = inv.invoice_id
AND inv.invoice_id = pay.invoice_id
AND checks.check_id = pay.check_id
AND pn_inv_id = inv.invoice_id
AND checks.status_lookup_code IN ('CLEARED', 'RECONCILED','CLEARED BUT UNACCOUNTED', 'RECONCILED UNACCOUNTED')
AND trunc(checks.cleared_date) <= pd_end_date;
SELECT ABS(NVL(SUM(apid.amount), 0)) prepay_on_inv_amt
FROM ap_invoice_distributions_all apid
WHERE PREPAY_DISTRIBUTION_ID IS NOT NULL
AND invoice_id = pn_inv_id;
SELECT nvl(base_amount, invoice_amount) invoice_amount
,nvl(exchange_rate, 1) exchange_rate
FROM ap_invoices_all
WHERE invoice_id = pn_inv_id;
SELECT NVL(SUM(checks.amount),0)
FROM ap_invoices_all INV
,ap_invoice_payments_all PAY
,ap_checks_all CHECKS
WHERE pn_inv_id = inv.invoice_id
AND checks.check_id = pay.check_id
AND inv.invoice_id = pay.invoice_id
AND checks.status_lookup_code IN ('CLEARED', 'RECONCILED','CLEARED BUT UNACCOUNTED', 'RECONCILED UNACCOUNTED')
AND trunc(checks.cleared_date) <= pd_end_date;
SELECT nvl(pv.small_business_flag,'N') small_business_flag
FROM po_vendors pv, ap_invoices_all inv
WHERE inv.invoice_id = pn_inv_id
AND inv.vendor_id = pv.vendor_id;
l_cleared_select VARCHAR2(2000);
l_cleared_select1 VARCHAR2(2000);
l_unpaid_amt_select VARCHAR2(2000);
v_is_seq_updated VARCHAR2(1) := 'N';
SELECT jzvtd.doc_seq_value SEQ_NUMBER
,jzvtd.tax_invoice_date TAX_DATE
,jzvtd.billing_tp_name CUSTOMER_NAME
,jzvtd.accounting_date GL_DATE
,jzvtd.trx_currency_code
,sum(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)
+ nvl(jzvtd.taxable_amt_funcl_curr, jzvtd.taxable_amt)) TOTAL_ACCOUNTED_AMOUNT
,sum(nvl(jzvtd.tax_amt,0) + nvl(jzvtd.taxable_amt, 0)) TOTAL_ENTERED_AMOUNT
,sum(nvl(jzvtd.taxable_amt, 0)) TAXABLE_ENTERED_AMOUNT
,sum(nvl(jzvtd.taxable_amt_funcl_curr, jzvtd.taxable_amt)) TAXABLE_AMOUNT
/* fixed during UT for Bug# 5258868
,sum(nvl(jzvtd.tax_amt, 0)) tax_amount
,sum(decode(jzvtd.tax_recoverable_flag, 'Y', jzvtd.tax_amt, 0)) RECOVERABLE
,sum(decode(jzvtd.tax_recoverable_flag, 'N', 0, jzvtd.tax_amt)) NON_RECOVERABLE*/
/* following 3 columns added for Bug# 5258868 */
,sum(nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt)) tax_amount
,sum(decode(jzvtd.tax_recoverable_flag, 'Y', nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt), 0)) recoverable
,sum(decode(nvl(jzvtd.tax_recoverable_flag,'Y'), 'N', nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt), 0)) non_recoverable
,jzvtd.trx_number INV_NUMBER
,jzvtd.trx_date INV_DATE
,jzvtd.billing_tp_tax_reg_num TAX_REG_NUM
,jzvtd.tax_rate TAX_RATE
,jzvtd.trx_id
,jzvtd.tax_rate_vat_trx_type_desc
,jzvtd.tax_rate_code_vat_trx_type_mng
,jzvtd.tax_rate_code
,jzvtd.trx_line_class
,jzvtd.tax_rate_code_description
,jzvtd.tax_recoverable_flag rec_flag
,jzvtd.account_flexfield
,jzvtd.doc_seq_name
/* UT TEST ,to_char(jzvtd.tax_invoice_date,'MON') GL_PERIOD */
, jzvrs.tax_calendar_period GL_PERIOD /* UT TEST */
,jzvtd.tax_rate_vat_trx_type_code
,jzvtd.reporting_code
,jzvtd.tax_line_id
,jzvtd.offset_flag
,jzvtd.offset_tax_rate_code
,jzvtd.chk_vat_amount_paid CHK_VAT_AMOUNT_PAID
,jzvrs.period_end_date
FROM jg_zz_vat_trx_details jzvtd
,jg_zz_vat_rep_status jzvrs
WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
-- AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD --bug5867390
AND jzvtd.tax_invoice_date <= (SELECT period_end_date
FROM jg_zz_vat_rep_status jzvrs
WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jzvrs.source = 'AP'
AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD)
AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
AND jzvtd.tax_rate_register_type_code = 'TAX'
AND jzvrs.source = 'AP'
AND (P_VAT_TRX_TYPE IS NULL OR jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
AND (P_EX_VAT_TRX_TYPE is null or nvl(jzvtd.tax_rate_vat_trx_type_code,'#') <> P_EX_VAT_TRX_TYPE)
AND (P_INC_PREPAYMENTS = 'Y' OR (jzvtd.trx_line_class <> 'PREPAYMENT INVOICES' AND P_INC_PREPAYMENTS = 'N') )
GROUP BY jzvtd.reporting_code
,jzvtd.doc_seq_value
, jzvrs.tax_calendar_period /* UT TEST addition */
,jzvtd.tax_invoice_date
,jzvtd.billing_tp_name
,jzvtd.accounting_date
,jzvtd.trx_currency_code
,trx_number
,trx_date
,billing_tp_tax_reg_num
,tax_rate
,jzvtd.trx_id
,jzvtd.tax_rate_vat_trx_type_desc
,jzvtd.tax_rate_code_vat_trx_type_mng
,jzvtd.tax_rate_code
,jzvtd.trx_line_class
,jzvtd.tax_rate_code_description
,jzvtd.tax_recoverable_flag
,jzvtd.account_flexfield
,jzvtd.doc_seq_name
,jzvtd.tax_rate_vat_trx_type_code
,jzvtd.chk_vat_amount_paid
,jzvtd.tax_line_id
,jzvtd.offset_flag
,jzvtd.offset_tax_rate_code
,jzvrs.period_end_date ;
SELECT jzvtd.doc_seq_value seq_number
,jzvtd.tax_invoice_date tax_date
,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
decode(jzvtd.merchant_party_name, null,
jzvtd.billing_tp_name, jzvtd.merchant_party_name), jzvtd.billing_tp_name) customer_name
,jzvtd.accounting_date gl_date
,jzvtd.trx_currency_code
,sum(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)
+ nvl(jzvtd.taxable_amt_funcl_curr, jzvtd.taxable_amt)*jzvtd.tax_recovery_rate/100) total_accounted_amount
,sum(nvl(jzvtd.tax_amt,0) + nvl(jzvtd.taxable_amt, 0)*jzvtd.tax_recovery_rate/100) total_entered_amount
,sum(nvl(jzvtd.taxable_amt, 0)*jzvtd.tax_recovery_rate/100) taxable_entered_amount
,sum(nvl(jzvtd.taxable_amt_funcl_curr, jzvtd.taxable_amt)*jzvtd.tax_recovery_rate/100) taxable_amount
/* fixed during UT for Bug# 5258868
,sum(nvl(jzvtd.tax_amt, 0)) tax_amount
,sum(decode(jzvtd.tax_recoverable_flag, 'Y', jzvtd.tax_amt, 0)) recoverable
,sum(decode(jzvtd.tax_recoverable_flag, 'N', 0, jzvtd.tax_amt)) non_recoverable */
/* following 3 columns added for Bug# 5258868 */
,sum(nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt)) tax_amount
,sum(decode(jzvtd.tax_recoverable_flag, 'Y', nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt), 0)) recoverable
,sum(decode(nvl(jzvtd.tax_recoverable_flag,'Y'), 'N', nvl(jzvtd.tax_amt_funcl_curr, jzvtd.tax_amt), 0)) non_recoverable
,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
decode(jzvtd.merchant_party_document_number, null,
jzvtd.trx_number, jzvtd.merchant_party_document_number), jzvtd.trx_number) inv_number
,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
decode(jzvtd.start_expense_date, null,
jzvtd.trx_date, jzvtd.start_expense_date), jzvtd.trx_date) inv_date
,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
decode(jzvtd.merchant_party_tax_reg_number, null,
jzvtd.billing_tp_tax_reg_num, jzvtd.merchant_party_tax_reg_number), jzvtd.billing_tp_tax_reg_num) tax_reg_num
,jzvtd.tax_rate tax_rate
,jzvtd.trx_id
,jzvtd.tax_rate_vat_trx_type_desc
,jzvtd.tax_rate_code_vat_trx_type_mng
,jzvtd.tax_rate_code
,jzvtd.trx_line_class
,jzvtd.tax_rate_code_description
,jzvtd.doc_seq_name
,jzvrs.tax_calendar_period gl_period /* UT TEST addition */
/* UT TEST ,to_char(jzvtd.tax_invoice_date,'MON') gl_period */
,jzvtd.tax_rate_vat_trx_type_code
,jzvtd.reporting_code
--bug 13248892 ,jzvtd.tax_line_id
,jzvtd.offset_flag
,jzvtd.offset_tax_rate_code
,jzvtd.chk_vat_amount_paid chk_vat_amount_paid
,jzvrs.period_end_date
FROM jg_zz_vat_trx_details jzvtd
,jg_zz_vat_rep_status jzvrs
WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
-- AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD --bug5867390
AND jzvtd.tax_invoice_date <= (SELECT period_end_date
FROM jg_zz_vat_rep_status jzvrs
WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jzvrs.source = 'AP'
AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD)
AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
-- AND jzvtd.tax_rate_register_type_code = 'TAX'
AND jzvrs.source = 'AP'
AND (P_VAT_TRX_TYPE IS NULL OR jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
AND (P_EX_VAT_TRX_TYPE IS NULL OR nvl(jzvtd.tax_rate_vat_trx_type_code,'#') <> P_EX_VAT_TRX_TYPE)
AND ((jzvtd.trx_line_class <> 'PREPAYMENT INVOICES' AND P_INC_PREPAYMENTS = 'N') OR P_INC_PREPAYMENTS = 'Y')
GROUP BY jzvtd.tax_rate_code_vat_trx_type_mng
,jzvtd.tax_rate_vat_trx_type_desc
,jzvtd.tax_rate_code
,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
decode(jzvtd.merchant_party_document_number, null,
jzvtd.trx_number, jzvtd.merchant_party_document_number), jzvtd.trx_number)
,jzvtd.doc_seq_value
,jzvrs.tax_calendar_period /* UT TEST addition */
,jzvtd.tax_invoice_date
,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
decode(jzvtd.merchant_party_name, null,
jzvtd.billing_tp_name, jzvtd.merchant_party_name), jzvtd.billing_tp_name)
,jzvtd.accounting_date
,jzvtd.trx_currency_code
,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
decode(jzvtd.start_expense_date, null,
jzvtd.trx_date, jzvtd.start_expense_date), jzvtd.trx_date)
,decode(jzvtd.event_class_code, 'EXPENSE REPORTS',
decode(jzvtd.merchant_party_tax_reg_number, null,
jzvtd.billing_tp_tax_reg_num, jzvtd.merchant_party_tax_reg_number), jzvtd.billing_tp_tax_reg_num)
,jzvtd.tax_rate
,jzvtd.trx_id
,jzvtd.trx_line_class
,jzvtd.tax_rate_code_description
,jzvtd.doc_seq_name
,jzvtd.tax_rate_vat_trx_type_code
,jzvtd.chk_vat_amount_paid
,jzvtd.reporting_code
--bug 13248892 ,jzvtd.tax_line_id
,jzvtd.offset_flag
,jzvtd.offset_tax_rate_code
,jzvrs.period_end_date;
SELECT jg_info_n1 seq_num,
jg_info_v7 vat_code,
tmp.rowid,
jg_info_v14 tax_code,
jg_info_n5 trx_id,
jg_info_v15 reporting_code
FROM JG_ZZ_VAT_TRX_GT tmp,
gl_periods glp,
jg_zz_vat_rep_status JZVRS
WHERE jg_info_v9 = 'M'
AND JZVRS.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
AND jg_info_v16 = jzvrs.tax_calendar_period --bug5867390
AND GLP.period_set_name = jzvrs.tax_calendar_name
AND jg_info_d1 between glp.start_date and glp.end_date
AND jzvrs.source = 'AP'
ORDER BY decode(jg_info_v15, 'OFFSET','XOFFSET','VAT'),
jg_info_v7, --TAX_RATE_VAT_TRX_TYPE_CODE
jg_info_v13,
jg_info_v12, --VAT_TRANSACTION_TYPE_CODE description.
period_year desc,
period_num desc,
jg_info_v6, --TAX CODE DESC
jg_info_v14, --TAX_RATE_CODE
jg_info_d1, --TAX_INVOICE_DATE
jg_info_v1,
jg_info_n3; /*tax_rate*/
SELECT jzvtd.doc_seq_value seq_number
,jzvtd.tax_invoice_date tax_date
,jzvtd.billing_tp_name customer_name
,jzvtd.accounting_date gl_date
,jzvtd.trx_currency_code
,jzvtd.tax_amt_funcl_curr
,jzvtd.taxable_amt_funcl_curr
,jzvtd.tax_amt tax_amount
,jzvtd.taxable_amt taxable_amount
,jzvtd.tax_recoverable_flag
,jzvtd.trx_number inv_number
,jzvtd.trx_date inv_date
,jzvtd.billing_tp_tax_reg_num tax_reg_num
,jzvtd.tax_rate tax_rate
,jzvtd.trx_id
,jzvtd.tax_rate_vat_trx_type_desc
,jzvtd.tax_rate_code_vat_trx_type_mng
,jzvtd.tax_rate_code
,jzvtd.trx_line_class
,jzvtd.tax_rate_code_description
,jzvtd.tax_recoverable_flag rec_flag
,jzvtd.account_flexfield
,jzvtd.doc_seq_name
, jzvrs.tax_calendar_period gl_period /* UT TEST */
/* UT TEST ,to_char(jzvtd.tax_invoice_date,'MON') gl_period */
,jzvtd.tax_rate_vat_trx_type_code
,jzvtd.tax_type_code
,jzvtd.tax_line_id
,jzvtd.offset_flag
,jzvtd.offset_tax_rate_code
,jzvtd.chk_vat_amount_paid chk_vat_amount_paid
,jzvrs.period_end_date
,jzvtd.reporting_code
FROM jg_zz_vat_trx_details jzvtd
,jg_zz_vat_rep_status jzvrs
WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
AND jzvrs.source = 'AP'
AND jzvtd.tax_rate_register_type_code = 'TAX';
SELECT meaning
,description
FROM fnd_lookups
WHERE lookup_type = 'ZX_JEBE_VAT_TRANS_TYPE'
AND lookup_code = p_trx_type;
SELECT jzvtd.rep_context_entity_name company_name
,jzvtd.functional_currency_code functional_currency_code
FROM jg_zz_vat_trx_details jzvtd
,jg_zz_vat_rep_status jzvrs
WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jzvrs.source = 'AP'
AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
and rownum < 2; /* added during UT Bug# 5258868 */
SELECT MIN(-9999) vat_or_offset
FROM jg_zz_vat_trx_details jzvtd
,jg_zz_vat_rep_status jzvrs
WHERE jzvtd.tax_rate_code = p_tax_rate_code
AND jzvrs.reporting_status_id = jzvtd.reporting_status_id
AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jzvrs.source = 'AP'
AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
AND jzvtd.offset_tax_rate_code IS NOT NULL;
SELECT period_end_date
FROM jg_zz_vat_rep_status jzvrs
WHERE jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jzvrs.source = 'AP'
AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD;
SELECT
limit.max_recoverable_percentage,
limit.max_recoverable_amt,
limit.min_recoverable_amt,
limit.period_set_name
FROM
je_il_vat_limits limit,
JG_ZZ_VAT_REP_STATUS JZVRS
WHERE
JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REPORTING_ENTITY_ID
AND JZVRS.TAX_CALENDAR_PERIOD = P_TAX_CALENDAR_PERIOD
AND JZVRS.TAX_CALENDAR_NAME = limit.PERIOD_SET_NAME
AND limit.PERIOD_NAME = P_TAX_CALENDAR_PERIOD
AND ROWNUM = 1;
SELECT
sum(decode(tax_recoverable_flag,'Y', nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),0))
total_vat_amt
FROM
jg_zz_vat_trx_details jzvtd,
jg_zz_vat_rep_status jzvrs
WHERE jzvrs.source='AP'
and jzvrs.vat_reporting_entity_id = p_vat_reporting_entity_id
and jzvrs.tax_calendar_period = p_tax_calendar_period
and (p_vat_trx_type is null or jzvtd.tax_rate_vat_trx_type_code = p_vat_trx_type)
and jzvrs.reporting_status_id = jzvtd.reporting_status_id
and jzvtd.reporting_code in ('VAT-A','VAT-S', 'VAT-C', 'VAT-RA', 'VAT-RS', 'VAT-P', 'VAT-H');
SELECT nvl(sum(total_vatks_amt),0) total_petty_cash_vat_amt
FROM
(SELECT
sum(decode(tax_recoverable_flag,'Y', abs(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)),0))
total_vatks_amt
FROM
jg_zz_vat_trx_details jzvtd,
jg_zz_vat_rep_status jzvrs
WHERE jzvrs.source='AP'
and jzvrs.vat_reporting_entity_id = p_vat_reporting_entity_id
and jzvrs.tax_calendar_period <> p_tax_calendar_period
and jzvrs.reporting_status_id = jzvtd.reporting_status_id
and (jzvtd.il_vat_rep_status_id = -999 OR jzvtd.il_vat_rep_status_id = l_rep_status_id)
and (p_vat_trx_type is null or jzvtd.tax_rate_vat_trx_type_code = p_vat_trx_type)
and jzvtd.reporting_code in ('VAT-KS','VAT-KA')
UNION ALL
SELECT
sum(decode(tax_recoverable_flag,'Y', abs(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)),0))
total_vatks_amt
FROM
jg_zz_vat_trx_details jzvtd,
jg_zz_vat_rep_status jzvrs
WHERE jzvrs.source='AP'
and jzvrs.vat_reporting_entity_id = p_vat_reporting_entity_id
and jzvrs.tax_calendar_period = p_tax_calendar_period
and jzvrs.reporting_status_id = jzvtd.reporting_status_id
and (p_vat_trx_type is null or jzvtd.tax_rate_vat_trx_type_code = p_vat_trx_type)
and jzvtd.reporting_code in ('VAT-KS','VAT-KA'));
SELECT
jzvrs.reporting_status_id,
jzvrs.period_end_date
FROM
jg_zz_vat_rep_status jzvrs
WHERE
jzvrs.source='AP'
and jzvrs.vat_reporting_entity_id = p_vat_reporting_entity_id
and jzvrs.tax_calendar_period = p_tax_calendar_period;
SELECT
trxs.billing_tp_name,
trxs.billing_tp_number,
trxs.billing_tp_site_name,
trxs.trx_date,
trxs.trx_number,
trxs.reporting_status_id,
trxs.total_vat_amt,
SUM(trxs.total_vat_amt) over (ORDER BY trxs.billing_tp_name,
trxs.billing_tp_number,
trxs.billing_tp_site_name,
trxs.trx_date,
trxs.trx_number,
trxs.reporting_status_id) cum_total_amt
FROM
(SELECT
jzvtd.billing_tp_name,
jzvtd.billing_tp_number,
jzvtd.billing_tp_site_name,
jzvtd.trx_date,
jzvtd.trx_number,
jzvrs.reporting_status_id,
SUM(DECODE(jzvtd.tax_recoverable_flag,'Y', NVL(jzvtd.tax_amt_funcl_curr,NVL(jzvtd.tax_amt,0)),0)) total_vat_amt
FROM
jg_zz_vat_trx_details jzvtd,
jg_zz_vat_rep_status jzvrs
WHERE
jzvrs.source='AP'
and jzvrs.vat_reporting_entity_id = p_vat_reporting_entity_id
and (jzvtd.il_vat_rep_status_id = -999 OR jzvtd.il_vat_rep_status_id = l_rep_status_id)
and jzvrs.tax_calendar_period <> p_tax_calendar_period
and jzvrs.reporting_status_id = jzvtd.reporting_status_id
and jzvrs.period_end_date <= l_cur_period_end_date
and (p_vat_trx_type is null or jzvtd.tax_rate_vat_trx_type_code = p_vat_trx_type)
and jzvtd.reporting_code in ('VAT-KS','VAT-KA')
group by jzvtd.billing_tp_name,
jzvtd.billing_tp_number,
jzvtd.billing_tp_site_name,
jzvtd.trx_date,
jzvtd.trx_number,
jzvrs.reporting_status_id
UNION ALL
SELECT
jzvtd.billing_tp_name,
jzvtd.billing_tp_number,
jzvtd.billing_tp_site_name,
jzvtd.trx_date,
jzvtd.trx_number,
jzvrs.reporting_status_id,
SUM(DECODE(jzvtd.tax_recoverable_flag,'Y', NVL(jzvtd.tax_amt_funcl_curr,NVL(jzvtd.tax_amt,0)),0)) total_vat_amt
FROM
jg_zz_vat_trx_details jzvtd,
jg_zz_vat_rep_status jzvrs
WHERE
jzvrs.source='AP'
and jzvrs.vat_reporting_entity_id = p_vat_reporting_entity_id
and jzvrs.tax_calendar_period = p_tax_calendar_period
and jzvrs.reporting_status_id = jzvtd.reporting_status_id
and jzvrs.period_end_date <= l_cur_period_end_date
and (p_vat_trx_type is null or jzvtd.tax_rate_vat_trx_type_code = p_vat_trx_type)
and jzvtd.reporting_code in ('VAT-KS','VAT-KA')
GROUP BY jzvtd.billing_tp_name,
jzvtd.billing_tp_number,
jzvtd.billing_tp_site_name,
jzvtd.trx_date,
jzvtd.trx_number,
jzvrs.reporting_status_id) trxs
ORDER BY trxs.billing_tp_name,
trxs.billing_tp_number,
trxs.billing_tp_site_name,
trxs.trx_date,
trxs.trx_number,
trxs.reporting_status_id;
SELECT jzvtd.billing_tp_name vendor_name
,jzvtd.billing_tp_number vendor_number
,jzvtd.billing_tp_site_name site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
,jzvtd.trx_number inv_number
,jzvtd.trx_date inv_date
,NULL import_document_number
,NULL import_document_date
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
,jzvtd.posted_flag
,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
,sum(DECODE(tax_recoverable_flag,'Y',
(CASE
WHEN jzvtd.reporting_code IN ('VAT-A') THEN nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)
ELSE 0
END)
, 0)) vat_on_fixed_assets
,sum(DECODE(tax_recoverable_flag,'Y',
(CASE
WHEN jzvtd.reporting_code IN ('VAT-S','VAT-C','VAT-P','VAT-H') THEN nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt)
ELSE 0
END)
, 0)) vat_on_other_trxs
,(CASE
WHEN jzvtd.reporting_code = 'VAT-C' THEN 'C'
WHEN jzvtd.reporting_code = 'VAT-P' THEN 'P'
WHEN jzvtd.reporting_code = 'VAT-H' THEN 'H'
ELSE 'T'
END) class
,0 mark_trx_flag
FROM jg_zz_vat_trx_details jzvtd
,jg_zz_vat_rep_status jzvrs
,ap_invoices_all apinv
WHERE jzvrs.source='AP'
AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
AND (P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
AND jzvtd.reporting_code IN ('VAT-A','VAT-S','VAT-C','VAT-P','VAT-H')
AND apinv.invoice_id = jzvtd.trx_id
GROUP BY jzvtd.billing_tp_name
,jzvtd.billing_tp_number
,jzvtd.billing_tp_site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
,jzvtd.trx_number
,jzvtd.trx_id
,jzvtd.trx_date
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
,jzvtd.posted_flag
,(CASE
WHEN jzvtd.reporting_code = 'VAT-C' THEN 'C'
WHEN jzvtd.reporting_code = 'VAT-P' THEN 'P'
WHEN jzvtd.reporting_code = 'VAT-H' THEN 'H'
ELSE 'T'
END)
UNION ALL
-- VAT File Line Type - R
-- Reporting Code :
-- 'VAT-RS' - Other Trxs
SELECT jzvtd.billing_tp_name vendor_name
,jzvtd.billing_tp_number vendor_number
,jzvtd.billing_tp_site_name site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
,jzvtd.trx_number inv_number
,DECODE(length(nvl(apinvl.GLOBAL_ATTRIBUTE13,'A')),
1,jzvtd.trx_date,
19,TO_DATE(apinvl.GLOBAL_ATTRIBUTE13,'RRRR/MM/DD hh24:mi:ss'),
to_date(apinvl.GLOBAL_ATTRIBUTE13, 'DD-MM-RRRR')) inv_date
,NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num) import_document_number
,apinvl.GLOBAL_ATTRIBUTE13 import_document_date
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
,jzvtd.posted_flag
,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
,SUM(DECODE(jzvtd.reporting_code,'VAT-RA',DECODE(tax_recoverable_flag,'Y',nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),0),0)) vat_on_fixed_assets
,SUM(DECODE(jzvtd.reporting_code,'VAT-RS',DECODE(tax_recoverable_flag,'Y',nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),0),0)) vat_on_other_trxs
, 'R' class
,0 mark_trx_flag
FROM jg_zz_vat_trx_details jzvtd
,jg_zz_vat_rep_status jzvrs
,ap_invoices_all apinv
,ap_invoice_lines_all apinvl
,zx_lines zxl
WHERE jzvrs.source='AP'
AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
AND ( P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
AND jzvtd.reporting_code IN ('VAT-RS','VAT-RA')
AND apinv.invoice_id = jzvtd.trx_id
AND zxl.trx_id= apinv.invoice_id
AND zxl.trx_id= apinvl.invoice_id(+)
AND zxl.tax_line_id =jzvtd.tax_line_id
AND zxl.summary_tax_line_id = apinvl.summary_tax_line_id(+)
GROUP BY jzvtd.billing_tp_name
,jzvtd.billing_tp_number
,jzvtd.billing_tp_site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
,jzvtd.trx_number
,jzvtd.trx_id
,DECODE(length(nvl(apinvl.GLOBAL_ATTRIBUTE13,'A')),
1,jzvtd.trx_date,
19,TO_DATE(apinvl.GLOBAL_ATTRIBUTE13,'RRRR/MM/DD hh24:mi:ss'),
to_date(apinvl.GLOBAL_ATTRIBUTE13, 'DD-MM-RRRR'))
,NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num)
,apinvl.GLOBAL_ATTRIBUTE13
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
,jzvtd.posted_flag
UNION ALL
-- VAT File Line Type - K
-- Reporting Code :
-- 'VAT-KS' - Other Trxs
-- Transactions that are considered for the current period.
SELECT jzvtd.billing_tp_name vendor_name
,jzvtd.billing_tp_number vendor_number
,jzvtd.billing_tp_site_name site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
,jzvtd.trx_number inv_number
,jzvtd.trx_date inv_date
,NULL import_document_number
,NULL import_document_date
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
,jzvtd.posted_flag
,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
,sum(DECODE(jzvtd.reporting_code,'VAT-KA',DECODE(tax_recoverable_flag,'Y', nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt), 0),0)) vat_on_fixed_assets
,sum(DECODE(jzvtd.reporting_code,'VAT-KS',DECODE(tax_recoverable_flag,'Y', nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt), 0),0)) vat_on_other_trxs
,'K' class
,0 mark_trx_flag
FROM jg_zz_vat_trx_details jzvtd
,jg_zz_vat_rep_status jzvrs
,ap_invoices_all apinv
WHERE jzvrs.source='AP'
AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND JZVTD.IL_VAT_REP_STATUS_ID = l_rep_status_id
-- AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD -- No period check
AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
AND jzvrs.period_end_date <= l_cur_period_end_date
AND (P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
AND jzvtd.reporting_code IN ('VAT-KS','VAT-KA')
AND apinv.invoice_id = jzvtd.trx_id
GROUP BY jzvtd.billing_tp_name
,jzvtd.billing_tp_number
,jzvtd.billing_tp_site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
,jzvtd.trx_number
,jzvtd.trx_id
,jzvtd.trx_date
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
,jzvtd.posted_flag
UNION ALL
-- VAT File Line Type - K
-- Reporting Code :
-- 'VAT-KS' - Other Trxs
-- Transactions that are not stamped for the subsequent period.
SELECT jzvtd.billing_tp_name vendor_name
,jzvtd.billing_tp_number vendor_number
,jzvtd.billing_tp_site_name site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
,jzvtd.trx_number inv_number
,jzvtd.trx_date inv_date
,NULL import_document_number
,NULL import_document_date
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
,jzvtd.posted_flag
,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
,sum(DECODE(jzvtd.reporting_code,'VAT-KA',DECODE(tax_recoverable_flag,'Y', nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt), 0),0)) vat_on_fixed_assets
,sum(DECODE(jzvtd.reporting_code,'VAT-KS',DECODE(tax_recoverable_flag,'Y', nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt), 0),0)) vat_on_other_trxs
,'K' class
,1 mark_trx_flag
FROM jg_zz_vat_trx_details jzvtd
,jg_zz_vat_rep_status jzvrs
,ap_invoices_all apinv
WHERE jzvrs.source='AP'
AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
--AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD ( Other periods also possible)
AND JZVTD.IL_VAT_REP_STATUS_ID = -999
AND jzvrs.period_end_date <= l_cur_period_end_date
AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
AND (P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
AND jzvtd.reporting_code IN ('VAT-KS','VAT-KA')
AND apinv.invoice_id = jzvtd.trx_id
GROUP BY jzvtd.billing_tp_name
,jzvtd.billing_tp_number
,jzvtd.billing_tp_site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
,jzvtd.trx_number
,jzvtd.trx_id
,jzvtd.trx_date
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
,jzvtd.posted_flag
ORDER BY vendor_name
,vendor_number
,site_name
,class
,inv_date
,inv_number;
SELECT jzvtd.billing_tp_name vendor_name
,jzvtd.billing_tp_number vendor_number
,jzvtd.billing_tp_site_name site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
,jzvtd.trx_number inv_number
,jzvtd.trx_date inv_date
,NULL import_document_number
,NULL import_document_date
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
,jzvtd.posted_flag
-- Bug 7683525 ,SUM(Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION) + DECODE(tax_recoverable_flag,'N',Round(nvl(jzvtd.taxable_amt_funcl_curr,jzvtd.taxable_amt),G_PRECISION),0)) invoice_amount
,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-A',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) s_vat_on_fixed_assets
,0 p_vat_on_fixed_assets
,0 i_vat_on_fixed_assets
,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-S',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) s_vat_on_other_trx
,0 p_vat_on_other_trx
,0 i_vat_on_other_trx
,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-A',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_fixed_assets
,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-S',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_other_trx
,'S' class
FROM jg_zz_vat_trx_details jzvtd
,jg_zz_vat_rep_status jzvrs
,ap_invoices_all apinv
WHERE jzvrs.source='AP'
AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
--AND jzvtd.tax_rate_register_type_code = 'TAX'
AND ( P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
AND jzvtd.reporting_code IN ('VAT-A','VAT-S')
AND apinv.invoice_id = jzvtd.trx_id
GROUP BY jzvtd.billing_tp_name
,jzvtd.billing_tp_number
,jzvtd.billing_tp_site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
,jzvtd.trx_number
,jzvtd.trx_id
,jzvtd.trx_date
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
,jzvtd.posted_flag
UNION ALL
SELECT jzvtd.billing_tp_name vendor_name
,jzvtd.billing_tp_number vendor_number
,jzvtd.billing_tp_site_name site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
,jzvtd.trx_number inv_number
,jzvtd.trx_date inv_date
,NULL import_document_number
,NULL import_document_date
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
,jzvtd.posted_flag
-- Bug 7683525 ,SUM(Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION) + DECODE(tax_recoverable_flag,'N',Round(nvl(jzvtd.taxable_amt_funcl_curr,jzvtd.taxable_amt),G_PRECISION),0)) invoice_amount
,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
,0 s_vat_on_fixed_assets
,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-KA',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) p_vat_on_fixed_assets
,0 i_vat_on_fixed_assets
,0 s_vat_on_other_trx
,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-KS',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) p_vat_on_other_trx
,0 i_vat_on_other_trx
,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,
'VAT-KA',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_fixed_assets
,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,
'VAT-KS',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_other_trx
,'P' class
FROM jg_zz_vat_trx_details jzvtd
,jg_zz_vat_rep_status jzvrs
,ap_invoices_all apinv
WHERE jzvrs.source='AP'
AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
--AND jzvtd.tax_rate_register_type_code = 'TAX'
AND ( P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
AND jzvtd.reporting_code IN ('VAT-KA','VAT-KS')
AND apinv.invoice_id = jzvtd.trx_id
GROUP BY jzvtd.billing_tp_name
,jzvtd.billing_tp_number
,jzvtd.billing_tp_site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
,jzvtd.trx_number
,jzvtd.trx_id
,jzvtd.trx_date
-- ,jzvtd.import_document_number
-- ,jzvtd.import_document_date
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
,jzvtd.posted_flag
UNION ALL
SELECT jzvtd.billing_tp_name vendor_name
,jzvtd.billing_tp_number vendor_number
,jzvtd.billing_tp_site_name site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num) tax_reg_num
,jzvtd.trx_number inv_number
,DECODE(length(nvl(apinvl.GLOBAL_ATTRIBUTE13,'A')),
1,jzvtd.trx_date,
19,TO_DATE(apinvl.GLOBAL_ATTRIBUTE13,'RRRR/MM/DD hh24:mi:ss'),
to_date(apinvl.GLOBAL_ATTRIBUTE13, 'DD-MM-RRRR')) inv_date
,NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num) import_document_number
,apinvl.GLOBAL_ATTRIBUTE13 import_document_date
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class) trx_line_class
,jzvtd.posted_flag
-- Bug 7683525 ,SUM(Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION) + DECODE(tax_recoverable_flag,'N',Round(nvl(jzvtd.taxable_amt_funcl_curr,jzvtd.taxable_amt),G_PRECISION),0)) invoice_amount
,JG_ZZ_COMMON_PKG.get_amt_tot(jzvtd.trx_id,l_ledger_id,G_PRECISION) invoice_amount
,0 s_vat_on_fixed_assets
,0 p_vat_on_fixed_assets
,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-RA',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) i_vat_on_fixed_assets
,0 s_vat_on_other_trx
,0 p_vat_on_other_trx
,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,'VAT-RS',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) i_vat_on_other_trx
,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,
'VAT-RA',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_fixed_assets
,sum(DECODE(tax_recoverable_flag,'Y',decode(jzvtd.reporting_code,
'VAT-RS',Round(nvl(jzvtd.tax_amt_funcl_curr,jzvtd.tax_amt),G_PRECISION),0),0)) vat_other_trx
, 'I' class
FROM jg_zz_vat_trx_details jzvtd
,jg_zz_vat_rep_status jzvrs
,ap_invoices_all apinv
,ap_invoice_lines_all apinvl
,zx_lines zxl
WHERE jzvrs.source='AP'
AND jzvrs.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID
AND jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD
AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
-- AND jzvtd.tax_rate_register_type_code = 'TAX'
AND ( P_VAT_TRX_TYPE is null or jzvtd.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE)
AND jzvtd.reporting_code IN ('VAT-RA','VAT-RS')
AND apinv.invoice_id = jzvtd.trx_id
AND zxl.trx_id= apinv.invoice_id
AND zxl.trx_id= apinvl.invoice_id(+)
AND zxl.tax_line_id =jzvtd.tax_line_id
AND zxl.summary_tax_line_id = apinvl.summary_tax_line_id(+)
-- AND apinvl.line_type_lookup_code = 'TAX'
GROUP BY jzvtd.billing_tp_name
,jzvtd.billing_tp_number
,jzvtd.billing_tp_site_name
,NVL(jzvtd.billing_tp_site_tax_reg_num,jzvtd.billing_tp_tax_reg_num)
,jzvtd.trx_number
,jzvtd.trx_id
,DECODE(length(nvl(apinvl.GLOBAL_ATTRIBUTE13,'A')),
1,jzvtd.trx_date,
19,TO_DATE(apinvl.GLOBAL_ATTRIBUTE13,'RRRR/MM/DD hh24:mi:ss'),
to_date(apinvl.GLOBAL_ATTRIBUTE13, 'DD-MM-RRRR'))
,NVL(apinvl.GLOBAL_ATTRIBUTE14,apinv.invoice_num)
,apinvl.GLOBAL_ATTRIBUTE13
,DECODE(nvl2(apinv.cancelled_date,'Y','N'),'Y','CANCELLED',jzvtd.trx_line_class)
,jzvtd.posted_flag;
select meaning
into l_reporting_status_1
from fnd_lookups
where lookup_type = 'JGZZ_REPORT_TYPE'
and lookup_code = decode(l_reporting_status, 'COPY', 'R','FINAL','F','P');
select distinct nvl(jzvre.ledger_id,0)
, jzvre.entity_type_code
into l_ledger_id,l_entity_type_code
from jg_zz_vat_rep_entities jzvre
where jzvre.vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID;
select DISTINCT ledger_category_code
into l_ledger_category_code
from gl_ledgers
where ledger_id = l_ledger_id;
INSERT INTO JG_ZZ_VAT_TRX_GT
(jg_info_v1 -- curr_code
,jg_info_v2 -- entity_name
,jg_info_v3 -- taxpayer_id
,jg_info_v4 -- company_name
,jg_info_v5 -- registration_number
,jg_info_v6 -- country
,jg_info_v7 -- address1
,jg_info_v8 -- address2
,jg_info_v9 -- address3
,jg_info_v10 -- address4
,jg_info_v11 -- city
,jg_info_v12 -- postal_code
,jg_info_v13 -- contact
,jg_info_v14 -- phone_number
,jg_info_v30 -- Header record indicator
,jg_info_v15 --Tax Registration Number
,jg_info_d1 --Period start date
,jg_info_d2 --Period end date
,jg_info_v16 ) --Reporting Status
VALUES(
l_curr_code
,l_company_name ---l_rep_entity_name
,l_registration_number --l_taxpayer_id
,l_company_name
,l_tax_registration_num
,l_country
,l_address1
,l_address2
,l_address3
,l_address4
,l_city
,l_postal_code
,l_contact
,l_phone_number
,'H'
,l_tax_registration_num
,l_period_start_date
,l_period_end_date
--,l_reporting_status); Bug 12773668
if g_debug = true then fnd_file.put_line(fnd_file.log,'Inserted Company Details in JG_ZZ_VAT_TRX_GT table'); end if;
SELECT precision
INTO G_PRECISION
FROM fnd_currencies
WHERE currency_code = G_FUNCTIONAL_CURRENCY;
-- Stamp the petty cash transactions. Update the IL_REP_STATUS_ID column in JG_ZZ_VAT_TRX_DETAILS table.
IF l_count > 1 THEN
BEGIN
FORALL i_index IN 1 .. l_count-1
UPDATE JG_ZZ_VAT_TRX_DETAILS
SET IL_VAT_REP_STATUS_ID = il_rep_status_rec_tab(i_index)
WHERE REPORTING_STATUS_ID = rep_status_id_rec_tab(i_index)
AND TRX_NUMBER = trx_number_rec_tab(i_index)
AND TRX_DATE = trx_date_rec_tab(i_index);
INSERT INTO JG_ZZ_VAT_TRX_GT
(jg_info_v1 , --vendor_name
jg_info_v6 , --Vendor Number
jg_info_v2 , --SITE_NAME
jg_info_v10 , --TAX_REG_NUM
jg_info_v11 , --INV_NUMBER
jg_info_d1 , --INV_DATE
jg_info_v12 , --IMPORT_DOCUMENT_NUMBER
jg_info_v3 , --TRX_LINE_CLASS
jg_info_v4 , --POSTED_FLAG
jg_info_n5 , --INVOICE_AMOUNT
jg_info_n12 , --VAT_ON_FIXED_ASSETS
jg_info_n13 , --VAT_ON_OTHER_TRXS
jg_info_v5 , --CLASS
jg_info_n1 --MARK_TRX_FLAG
)
VALUES(
cur_israel_new_details.vendor_name
,cur_israel_new_details.vendor_number
,cur_israel_new_details.site_name
,cur_israel_new_details.tax_reg_num
,cur_israel_new_details.inv_number
,cur_israel_new_details.inv_date
,cur_israel_new_details.import_document_number
,cur_israel_new_details.trx_line_class
,cur_israel_new_details.posted_flag
,cur_israel_new_details.invoice_amount
,cur_israel_new_details.vat_on_fixed_assets
,cur_israel_new_details.vat_on_other_trxs
,cur_israel_new_details.class
,cur_israel_new_details.mark_trx_flag);
if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while Inserting the ISRAEL data into GTT. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
INSERT INTO JG_ZZ_VAT_TRX_GT
(jg_info_v1 , --vendor_name
-- jg_info_n1 , --vendor_number
jg_info_v6 , --Vendor Number
jg_info_v2 , --SITE_NAME
jg_info_v10 , --TAX_REG_NUM /* UT TEST jg_info_n2 => jg_info_v10*/
jg_info_v11 , --INV_NUMBER /* UT TEST jg_info_n3 => jg_info_v11*/
jg_info_d1 , --INV_DATE
jg_info_v12 , --IMPORT_DOCUMENT_NUMBER /* UT TEST jg_info_n4 => jg_info_v12*/
jg_info_d2 , --IMPORT_DOCUMENT_DATE
jg_info_v3 , --TRX_LINE_CLASS
jg_info_v4 , --POSTED_FLAG
jg_info_n5 , --INVOICE_AMOUNT
jg_info_n6 , --S_VAT_ON_FIXED_ASSETS
jg_info_n7 , --P_VAT_ON_FIXED_ASSETS
jg_info_n8 , --I_VAT_ON_FIXED_ASSETS
jg_info_n9 , --S_VAT_ON_OTHER_TRX
jg_info_n10 , --P_VAT_ON_OTHER_TRX
jg_info_n11 , --I_VAT_ON_OTHER_TRX
jg_info_n12 , --VAT_FIXED_ASSETS
jg_info_n13 , --VAT_OTHER_TRX
jg_info_v5 --CLASS
)
VALUES(
israel_inv_lines.vendor_name
,israel_inv_lines.vendor_number
,israel_inv_lines.site_name
,israel_inv_lines.tax_reg_num
,israel_inv_lines.inv_number
,israel_inv_lines.inv_date
,israel_inv_lines.import_document_number
,israel_inv_lines.import_document_date
,israel_inv_lines.trx_line_class
,israel_inv_lines.posted_flag
,israel_inv_lines.invoice_amount
,israel_inv_lines.s_vat_on_fixed_assets
,israel_inv_lines.p_vat_on_fixed_assets
,israel_inv_lines.i_vat_on_fixed_assets
,israel_inv_lines.s_vat_on_other_trx
,israel_inv_lines.p_vat_on_other_trx
,israel_inv_lines.i_vat_on_other_trx
,israel_inv_lines.vat_fixed_assets
,israel_inv_lines.vat_other_trx
,israel_inv_lines.class);
if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while Inserting the ISRAEL data into GTT. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
INSERT INTO JG_ZZ_VAT_TRX_GT
(jg_info_n1 -- seq_number
,jg_info_v1 -- inv_number
,jg_info_v2 -- customer_name
,jg_info_d1 -- tax_date
,jg_info_d2 -- inv_date
,jg_info_d3 -- accounting_date
,jg_info_v3 -- tax_code_description
,jg_info_n2 -- taxable_amount
,jg_info_n3 -- tax_rate
,jg_info_n4 -- tax_amount
,jg_info_n5 -- trx_id , invoice_id
,jg_info_v4 -- trx_class_code
,jg_info_v5 -- trx_currency_code
,jg_info_n6 -- Recoverable Tax
,jg_info_n7 -- Non-Recoverable Tax
,jg_info_v6 -- Tax Code Description
,jg_info_n8 -- Functional Amount
,jg_info_n10 -- Transaction Amount
,jg_info_n13 -- Transaction Amount Offset
,jg_info_v9 -- Rec flag
,jg_info_v10 -- Doc Sequence Name
,jg_info_v11 -- tax_reg_num
,jg_info_v12 -- tax_code_vat_trx_type_code
,jg_info_v13 -- TAX_CODE_VAT_TRX_TYPE_MEANING
,jg_info_v14 -- TAX_CODE
,jg_info_v7 -- TAX_RATE_VAT_TRX_TYPE_CODE
,jg_info_n12
,jg_info_v15 -- Reporting_Code
,jg_info_n15 -- Pure VAT or VAT with Offset derived in the variable l_vat_or_offset
,jg_info_v16 -- period_name
,jg_info_v17 -- offset_flag
,jg_info_v18 -- offset_tax_rate_code
,jg_info_v19 -- CHK_VAT_AMOUNT_PAID
,jg_info_n14 -- non_recoverable_unpaid amount
)
VALUES
(c_data_rec1.seq_number
,c_data_rec1.inv_number
,c_data_rec1.customer_name
,c_data_rec1.tax_date
,c_data_rec1.inv_date
,c_data_rec1.gl_date
,c_data_rec1.tax_rate_code_description
,c_data_rec1.taxable_amount
,c_data_rec1.tax_rate
,c_data_rec1.tax_amount
,c_data_rec1.trx_id
,c_data_rec1.trx_line_class
,c_data_rec1.trx_currency_code
,c_data_rec1.recoverable
,c_data_rec1.non_recoverable
,c_data_rec1.tax_rate_code_description
,c_data_rec1.total_accounted_amount
,c_data_rec1.total_entered_amount
,c_data_rec1.taxable_entered_amount
,'M'
,c_data_rec1.doc_seq_name
,c_data_rec1.tax_reg_num
,c_data_rec1.tax_rate_vat_trx_type_desc
,c_data_rec1.tax_rate_code_vat_trx_type_mng
,c_data_rec1.tax_rate_code
,c_data_rec1.tax_rate_vat_trx_type_code
,decode(NVL(c_data_rec1.chk_vat_amount_paid, 'N'), 'N', 0, 1)
,c_data_rec1.reporting_code
,l_vat_or_offset
,c_data_rec1.gl_period
,c_data_rec1.offset_flag
,c_data_rec1.offset_tax_rate_code
,c_data_rec1.chk_vat_amount_paid
,l_unpaid_amount);
INSERT INTO JG_ZZ_VAT_TRX_GT
(jg_info_n1 -- seq_number
,jg_info_v1 -- inv_number
,jg_info_v2 -- customer_name
,jg_info_d1 -- tax_date
,jg_info_d2 -- inv_date
,jg_info_d3 -- accounting_date
,jg_info_v3 -- tax_code_description
,jg_info_n2 -- taxable_amount
,jg_info_n3 -- tax_rate
,jg_info_n4 -- tax_amount
,jg_info_n5 -- trx_id
,jg_info_v4 -- trx_class_code
,jg_info_v5 -- trx_currency_code
,jg_info_n6 -- Recoverable Tax
,jg_info_n7 -- Non-Recoverable Tax
,jg_info_v6 -- Tax Code Description
,jg_info_n8 -- Functional Amount
,jg_info_n10 -- Transaction Amount
,jg_info_n13 -- Transaction Amount Offset
,jg_info_v9 -- Rec flag
,jg_info_v8 -- ACCOUNT FLEXFIELD
,jg_info_v10 -- Doc Sequence Name
,jg_info_v11 -- tax_reg_num
,jg_info_v12 -- tax_rate_vat_trx_type_desc
,jg_info_v13 -- tax_rate_code_vat_trx_type_mng
,jg_info_v14 -- TAX_RATE_CODE
,jg_info_v7 -- TAX_CODE_VAT_TRX_TYPE_CODE
,jg_info_n12
,jg_info_v15 -- REPORTING_CODE
,jg_info_v16 -- period_name
,jg_info_v17 -- offset_flag
,jg_info_v18 -- offset_tax_rate_code
,jg_info_v19 -- CHK_VAT_AMOUNT_PAID
,jg_info_n14 -- non_recoverable_unpaid amount
)
VALUES
(c_data_rec.seq_number
,c_data_rec.inv_number
,c_data_rec.customer_name
,c_data_rec.tax_date
,c_data_rec.inv_date
,c_data_rec.gl_date
,c_data_rec.tax_rate_code_description
,c_data_rec.taxable_amount
,c_data_rec.tax_rate
,c_data_rec.tax_amount
,c_data_rec.trx_id
,c_data_rec.trx_line_class
,c_data_rec.trx_currency_code
,c_data_rec.recoverable
,c_data_rec.non_recoverable
,c_data_rec.tax_rate_code_description
,c_data_rec.total_accounted_amount
,c_data_rec.total_entered_amount
,c_data_rec.taxable_entered_amount
,'S'
,c_data_rec.account_flexfield
,c_data_rec.doc_seq_name
,c_data_rec.tax_reg_num
,c_data_rec.tax_rate_vat_trx_type_desc
,c_data_rec.tax_rate_code_vat_trx_type_mng
,c_data_rec.tax_rate_code
,c_data_rec.tax_rate_vat_trx_type_code
,decode(NVL(c_data_rec.chk_vat_amount_paid, 'N'), 'N', 0, 1)
,c_data_rec.reporting_code
,c_data_rec.gl_period
,c_data_rec.offset_flag
,c_data_rec.offset_tax_rate_code
,c_data_rec.chk_vat_amount_paid
,l_unpaid_amount);
if g_debug = true then fnd_file.put_line(fnd_file.log,' Failed while selecting the period end date. Error : ' || SUBSTR(SQLERRM,1,200)); end if;
SELECT NVL(enable_report_sequence_flag ,'N')
INTO l_enable_report_sequence_flag
FROM jg_zz_vat_rep_entities
WHERE vat_reporting_entity_id = P_VAT_REPORTING_ENTITY_ID;
SELECT distinct JG_INFO_V40
INTO v_is_seq_updated
FROM JG_ZZ_VAT_TRX_GT T1
WHERE T1.jg_info_n5 = c_data.trx_id
AND T1.jg_info_v7 = c_data.vat_code
AND T1.jg_info_v15 = c_data.reporting_code;
IF NVL(v_is_seq_updated,'N') <> 'Y' THEN
v_count := v_count+1;
UPDATE JG_ZZ_VAT_TRX_GT SET jg_info_n1 = v_count ,
jg_info_v40 = 'Y'
WHERE jg_info_n5 = c_data.trx_id
AND jg_info_v7 = c_data.vat_code
AND jg_info_v15 = c_data.reporting_code;
l_cleared_select := '(SELECT NVL((SELECT NVL(SUM(pay.payment_base_amount),0)
FROM ap_invoices_all INV, ap_invoice_payments_all PAY, ap_checks_all CHECKS
WHERE inv.invoice_id = pay.invoice_id
AND checks.check_id = pay.check_id
AND jg_info_n5 = inv.invoice_id
AND checks.status_lookup_code IN ( ''CLEARED'' , ''RECONCILED'' , ''CLEARED BUT UNACCOUNTED'', ''RECONCILED UNACCOUNTED'' )';
l_cleared_select1 := '* decode(jg_info_n15, NULL, jg_info_n8, jg_info_n2)
/ (SELECT inv1.base_amount
FROM ap_invoices_all INV1
WHERE jg_info_n5 = inv1.invoice_id ),0) from dual )';
l_unpaid_amt_select := 'AND (SELECT nvl(sum(amount_remaining),99999)
FROM ap_invoices_all INV,
ap_invoice_payments_all PAY,
ap_checks_all CHECKS,
ap_payment_schedules_all APS
WHERE APS.invoice_id = inv.invoice_id
AND inv.invoice_id = pay.invoice_id
AND checks.check_id = pay.check_id
AND jg_info_n5 = inv.invoice_id
AND checks.status_lookup_code IN ( ''CLEARED'' , ''RECONCILED'' , ''CLEARED BUT UNACCOUNTED'', ''RECONCILED UNACCOUNTED'' )';
P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' (((jg_info_n4 > ' || l_cleared_select
|| ' AND trunc(checks.cleared_date) <= ''' || l_end_date
|| ''' AND NVL(jg_info_v19,''N'') = ''Y'') ' || l_cleared_select1
|| ' ) AND (jg_info_n12 = 1 ) AND jg_info_n15 IS NULL ' || l_unpaid_amt_select
|| ' AND trunc(checks.cleared_date) <= '''||l_end_date||''' ) > 0 AND '''||l_country_code||''' <> ''PL'')'
|| ' OR ((decode(jg_info_n15, NULL, jg_info_n8, jg_info_n2) > ' ||l_cleared_select
|| ' AND trunc(checks.cleared_date) <= ''' || l_end_date || ''' AND ( ''' || l_country_code || ''' = ''PL'' OR '
|| ' NVL(jg_info_v19,''N'') = ''N'' )) ' || l_cleared_select1 || ' ) AND (''' || l_country_code || ''' = ''PL'' OR '
|| 'jg_info_n12 = 0 ) ' || l_unpaid_amt_select ||' AND trunc(checks.cleared_date) <= ''' || l_end_date || ''' ) > 0 ))';
P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' ((jg_info_n4 <= ' || l_cleared_select ||' AND trunc(checks.cleared_date) <= ''' ||l_end_date
|| ''' AND jg_info_v19 = ''Y'') AND (jg_info_n12 = 1 )) OR (jg_info_n12 = 0 ))';
P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' (((jg_info_n4 > ' || l_cleared_select
|| ' AND trunc(checks.cleared_date) <= ''' ||l_end_date|| ''' AND NVL(jg_info_v19,''N'') = ''Y'') '
|| l_cleared_select1 || ' ) AND (jg_info_n12 = 1 ) AND jg_info_n15 IS NULL ' || l_unpaid_amt_select
|| ' AND trunc(checks.cleared_date) <= ''' ||l_end_date|| ''' ) > 0 AND ''' ||l_country_code|| ''' <> ''PL'' )'
|| ' OR ((decode(jg_info_n15, NULL, jg_info_n8, jg_info_n2) > ' || l_cleared_select
|| ' AND trunc(checks.cleared_date) <= ''' ||l_end_date|| ''' AND ( '''|| l_country_code
|| ''' = ''PL'' OR NVL(jg_info_v19,''N'') = ''N''))' || l_cleared_select1 || ') AND (''' ||l_country_code|| ''' = ''PL'''
|| ' OR jg_info_n12 = 0 ) ' || l_unpaid_amt_select|| ' AND trunc(checks.cleared_date) <= ''' ||l_end_date|| ''' ) > 0 ))';
P_WHERE_CLAUSE := P_WHERE_CLAUSE || ' ((jg_info_n4 <= ' || l_cleared_select
|| ' AND trunc(checks.cleared_date) <= ''' ||l_end_date
|| ''' AND jg_info_v19 = ''Y'') AND (jg_info_n12 = 1 )) OR (jg_info_n12 = 0 ))';
INSERT INTO JG_ZZ_VAT_TRX_GT
(jg_info_n1 -- seq_number
,jg_info_v1 -- inv_number
,jg_info_v2 -- customer_name
,jg_info_d1 -- tax_date
,jg_info_d2 -- inv_date
,jg_info_d3 -- accounting_date
,jg_info_v3 -- tax_code_description
,jg_info_n2 -- taxable_amount
,jg_info_n3 -- tax_rate
,jg_info_n4 -- tax_amount
,jg_info_n5 -- trx_id
,jg_info_v4 -- trx_class_code
,jg_info_v5 -- trx_currency_code
,jg_info_v20 -- TAX_RECOVERABLE_FLAG
,jg_info_v6 -- Tax Code Description
,jg_info_n8 -- TAX_AMT_FUNCL_CURR
,jg_info_n10 -- TAXABLE_AMT_FUNCL_CURR
,jg_info_v8 -- ACCOUNT FLEXFIELD
,jg_info_v10 -- Doc Sequence Name
,jg_info_v11 -- tax_reg_num
,jg_info_v12 -- TAX_RATE_VAT_TRX_TYPE_DESC
,jg_info_v13 -- TAX_CODE_VAT_TRX_TYPE_MEANING
,jg_info_v14 -- TAX_CODE
,jg_info_v7 -- TAX_CODE_VAT_TRX_TYPE_CODE
,jg_info_v15 -- TAX_CODE_TYPE_CODE
,jg_info_v16 -- period_name
,jg_info_v17 -- offset_flag
,jg_info_v18 -- offset_tax_rate_code
,jg_info_v19 -- CHK_VAT_AMOUNT_PAID
,jg_info_v21 -- reporting_code
)
VALUES
(c_data_rec2.seq_number
,c_data_rec2.inv_number
,c_data_rec2.customer_name
,c_data_rec2.tax_date
,c_data_rec2.inv_date
,c_data_rec2.gl_date
,c_data_rec2.tax_rate_code_description
,c_data_rec2.taxable_amount
,c_data_rec2.tax_rate
,c_data_rec2.tax_amount
,c_data_rec2.trx_id
,c_data_rec2.trx_line_class
,c_data_rec2.trx_currency_code
,c_data_rec2.tax_recoverable_flag
,c_data_rec2.tax_rate_code_description
,c_data_rec2.tax_amt_funcl_curr
,c_data_rec2.taxable_amt_funcl_curr
,c_data_rec2.account_flexfield
,c_data_rec2.doc_seq_name
,c_data_rec2.tax_reg_num
,c_data_rec2.tax_rate_vat_trx_type_desc
,c_data_rec2.tax_rate_code_vat_trx_type_mng
,c_data_rec2.tax_rate_code
,c_data_rec2.tax_rate_vat_trx_type_code
,c_data_rec2.tax_type_code
,c_data_rec2.gl_period
,c_data_rec2.offset_flag
,c_data_rec2.offset_tax_rate_code
,c_data_rec2.chk_vat_amount_paid
,c_data_rec2.reporting_code);
SELECT meaning
FROM fnd_lookups
WHERE lookup_code = P_INC_PREPAYMENTS
AND lookup_type = 'YES_NO';
SELECT JZVRS.TAX_REGISTRATION_NUMBER
FROM JG_ZZ_VAT_REP_STATUS JZVRS
WHERE JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REPORTING_ENTITY_ID
/* added during UT */
AND (P_TAX_CALENDAR_PERIOD is null and jzvrs.tax_calendar_period = P_TAX_CALENDAR_PERIOD)
AND jzvrs.source = 'AP'
AND rownum = 1;