[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
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
,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
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
,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 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 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,
9,to_date(substr(apinvl.GLOBAL_ATTRIBUTE13, 1, 9), 'DD-MM-RRRR'),
19,TO_DATE(SUBSTR(apinvl.GLOBAL_ATTRIBUTE13, 1, 19),'RRRR/MM/DD hh24:mi:ss'),
to_date(substr(apinvl.GLOBAL_ATTRIBUTE13, 1, 11), '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 apinvl.invoice_id= jzvtd.trx_id
AND zxl.trx_id= jzvtd.trx_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,
9,to_date(substr(apinvl.GLOBAL_ATTRIBUTE13, 1, 9), 'DD-MM-RRRR'),
19,TO_DATE(SUBSTR(apinvl.GLOBAL_ATTRIBUTE13, 1, 19),'RRRR/MM/DD hh24:mi:ss'),
to_date(substr(apinvl.GLOBAL_ATTRIBUTE13, 1, 11), '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 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);
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;
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;