[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(vrs.period_start_date)
INTO l_period_start_date
FROM jg_zz_vat_final_reports vfr,
jg_zz_vat_rep_status vrs
WHERE vfr.report_name = p_report_name
AND vfr.vat_register_id = p_vat_register_id
AND vrs.reporting_status_id = vfr.reporting_status_id;
SELECT start_sequence_num
INTO l_start_seq
FROM jg_zz_vat_final_reports vfr,
jg_zz_vat_rep_status vrs
WHERE vfr.report_name = p_report_name
AND vfr.vat_register_id = p_vat_register_id
AND vrs.reporting_status_id = vfr.reporting_status_id
AND vrs.period_start_date = l_period_start_date;
SELECT register_name
INTO l_vat_register_name
FROM jg_zz_vat_registers_vl jzvr
,jg_zz_vat_rep_entities jzvre
WHERE ((jzvre.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
and
jzvre.entity_type_code = 'ACCOUNTING'
and
jzvre.mapping_vat_rep_entity_id = jzvr.vat_reporting_entity_id
)
OR
(jzvre.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
and
jzvre.entity_type_code = 'LEGAL'
and
jzvre.vat_reporting_entity_id = jzvr.vat_reporting_entity_id
)) --OR P_VAT_REP_ENTITY_ID is null
AND jzvr.vat_register_id = p_vat_register_id
AND jzvr.register_type = 'SALES_VAT' ;
SELECT register_name
INTO l_vat_register_name
FROM jg_zz_vat_registers_vl jzvr
,jg_zz_vat_rep_entities jzvre
WHERE ((jzvre.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
and
jzvre.entity_type_code = 'ACCOUNTING'
and
jzvre.mapping_vat_rep_entity_id = jzvr.vat_reporting_entity_id
)
OR
(jzvre.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
and
jzvre.entity_type_code = 'LEGAL'
and
jzvre.vat_reporting_entity_id = jzvr.vat_reporting_entity_id
)) --OR P_VAT_REP_ENTITY_ID is null
AND jzvr.vat_register_id = p_vat_register_id
AND jzvr.register_type = 'DEFERRED_VAT';
SELECT last_start_sequence_num
INTO l_start_seq
FROM jg_zz_vat_final_reports vfr,
jg_zz_vat_rep_status vrs
WHERE vfr.report_name = p_report_name
AND vfr.vat_register_id = p_vat_register_id
AND vrs.reporting_status_id = vfr.reporting_status_id
AND vrs.tax_calendar_period = p_period;
INSERT INTO jg_zz_vat_trx_gt (jg_info_n1,
jg_info_v1,
jg_info_v30)
VALUES (l_start_seq,
l_reporting_status,
'SEQ');
/*select count ( distinct ctl.customer_trx_id )
into l_sequence_start
from ra_customer_trx_lines ctl,
ra_customer_trx ct,
zx_rates_b zxb,
zx_taxes_b ztb,
zx_report_codes_assoc zxass
where ctl.vat_tax_id = zxb.tax_rate_id
and zxb.tax = ztb.tax
and zxb.tax_regime_code = ztb.tax_regime_code
and zxb.content_owner_id = ztb.content_owner_id
and zxb.tax_rate_id = zxass.entity_id
and zxass.entity_code = 'ZX_RATES'
and DECODE(ztb.offset_tax_flag,'Y','OFFSET',
Decode(zxb.def_rec_settlement_option_code,
'DEFERRED','DEFERRED',
zxass.REPORTING_CODE_CHAR_VALUE))= P_TAX_TYPE
and ct.customer_trx_id = ctl.customer_trx_id
and not exists (select 'x'
from ra_cust_trx_line_gl_dist gld
where gld.customer_trx_line_id = ctl.customer_trx_line_id
and gld.gl_posted_date is null)
and l_period_start_date >all
(select trx_date
from ra_customer_trx ct
where ct.customer_trx_id = ctl.customer_trx_id )
and trunc(l_period_start_date,'YYYY') <= all
(select trx_date
from ra_customer_trx ct
where ct.customer_trx_id = ctl.customer_trx_id );
SELECT count ( distinct JZVTD.trx_id )
INTO l_sequence_start
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_rep_status JZVRS
WHERE JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
FROM jg_zz_vat_rep_status JZRS
WHERE JZRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZRS.source = 'AR')
AND JZVTD.gl_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
AND JZVRS.source = 'AR'
AND ((JZVTD.tax_rate_register_type_code = 'TAX' AND P_TAX_REGISTER_TYPE = 'TAX')
OR (JZVTD.tax_rate_register_type_code = 'INTERIM' AND P_TAX_REGISTER_TYPE = 'INTERIM')
OR (JZVTD.tax_rate_register_type_code = 'NON-RECOVERABLE' AND P_TAX_REGISTER_TYPE = 'NON-RECOVERABLE'))
AND ( JZVTD.trx_tax_balancing_segment = P_BALANCING_SEGMENT OR P_BALANCING_SEGMENT is NULL )
AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZVRS.reporting_status_id in ( SELECT reporting_status_id FROM jg_zz_vat_rep_status
WHERE period_start_date < l_period_start_date
AND vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND source='AR')
AND trunc(l_period_start_date,'YYYY') <= all
(select trx_date from jg_zz_vat_trx_details A
where A.trx_id = JZVTD.trx_id );
INSERT INTO jg_zz_vat_trx_gt
(
jg_info_n1
,jg_info_v1
,jg_info_v2
,jg_info_v3
,jg_info_v4
,jg_info_v5
,jg_info_v6
,jg_info_v7
,jg_info_v8
,jg_info_v9
,jg_info_v10
,jg_info_v11
,jg_info_v12
,jg_info_v13
,jg_info_v14
,jg_info_v15
,jg_info_v16
,jg_info_v17
,jg_info_d1
,jg_info_d2
,jg_info_n2
,jg_info_v30
,jg_info_v18
,jg_info_v19
,jg_info_v20
)
VALUES
(
l_legal_entity_id
,l_company_name
,l_company_name --l_legal_entity_name
,l_tax_registration_num --l_registration_num
,l_registration_num --l_tax_payer_id
,l_contact_name
,l_address_line_1
,l_address_line_2
,l_address_line_3
,l_address_line_4
,l_city
,l_country
,l_phone_number
,l_postal_code
,l_func_curr
,l_reporting_status
,l_tax_regime
,l_vat_register_name
,decode(nvl(P_REPORT_NAME,'ZZ'),'JEESRRVR',l_to_period_end_date,l_period_end_date)
,l_period_start_date
,l_sequence_start
,'H'
,l_province
,l_comm_num
,l_vat_reg_num
);
SELECT JZVRS.tax_calendar_year PERIOD_YEAR
,JZVRS.tax_calendar_period PERIOD_NAME
,JZVTD.doc_seq_name DOCUMENT_SEQUENCE_NAME
,JZVTD.doc_seq_value DOCUMENT_SEQUENCE_NUMBER
,JZVTD.trx_date INVOICE_DATE
,SUBSTR(JZVTD.billing_tp_number,1,11) CUSTOMER_NUMBER
,SUBSTR(JZVTD.billing_tp_name,1,18) CUSTOMER_NAME
,JZVTD.trx_number INVOICE_NUMBER
,NVL(JZVTD.tax_amt_funcl_curr,0) * to_number(jzvar.tax_rec_sign_flag ||'1') VAT_AMOUNT_FUNCL_CURR --bug10103169
,NVL(JZVTD.taxable_amt_funcl_curr,0) * to_number(jzvar.taxable_rec_sign_flag ||'1') INV_AMT_WO_VAT_FUN_CURR --bug10103169
,(
NVL(JZVTD.tax_amt_funcl_curr,0) * to_number(jzvar.tax_rec_sign_flag ||'1')
+ NVL(JZVTD.taxable_amt_funcl_curr,0) * to_number(jzvar.taxable_rec_sign_flag ||'1')
) TOT_INV_AMT_W_VAT_FUN_CURR --bug10103169
,JZVTD.trx_line_type LINE_TYPE
,JZVTD.trx_line_number LINE_NUMBER
,JZVTD.account_flexfield ACCOUNT_FLEXFIELD
,JZVTD.trx_control_account_flexfield TAXABLE_ACCT_FLEXFIELD
,JZVTD.account_description ACCOUNT_DESCRIPTION
,FA_RX_FLEX_PKG.GET_DESCRIPTION ( 101, 'GL#',
(select chart_of_accounts_id from gl_ledgers where ledger_id = JZVTD.ledger_id),
'ALL', JZVTD.trx_control_account_flexfield ) TXBL_ACCT_DESCRIPTION
,NVL(JZVTD.trx_line_amt,0) ACCTD_AMOUNT
,NVL(JZVTD.tax_amt_funcl_curr,0) ACCTD_VAT_AMT
,NVL(JZVTD.taxable_amt_funcl_curr,0) ACCTD_INV_AMT
,NVL(JZVTD.tax_amt_funcl_curr,0)
+ NVL(JZVTD.taxable_amt_funcl_curr,0) ACCTD_TOT_AMT
,JZVTD.tax_rate_code VAT_CODE
,JZVTD.tax_rate_vat_trx_type_desc VAT_TRX_TYPE
,NVL(JZVBA.taxable_box, '99') VAT_TAXABLE_BOX
,NVL(JZVBA.tax_box, '99') VAT_TAX_BOX
,NVL(JZVTD.tax_amt_funcl_curr,0) * to_number(jzvar.tax_rec_sign_flag ||'1') TAX_AMOUNT --bug10103169
,NVL(JZVTD.taxable_amt_funcl_curr,0) * to_number(jzvar.taxable_rec_sign_flag ||'1') TAXABLE_AMOUNT --bug10103169
,NVL(JZVTD.taxable_amt_funcl_curr,0) * to_number(jzvar.taxable_rec_sign_flag ||'1') TAXABLE_AMT_FUN_CURR --bug10103169
,NVL(JZVTD.tax_amt_funcl_curr,0) * to_number(jzvar.tax_rec_sign_flag ||'1') TAX_AMT_FUN_CURR --bug10103169
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_rep_status JZVRS
,jg_zz_vat_box_allocs JZVBA
,ra_cust_trx_types RCTT
,jg_zz_vat_alloc_rules jzvar
WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
AND RCTT.cust_trx_type_id = JZVTD.trx_type_id
AND JZVTD.vat_transaction_id = JZVBA.vat_transaction_id
AND JZVBA.period_type = 'PERIODIC'
AND JZVRS.source = 'AR'
AND JZVAR.ALLOCATION_RULE_ID = JZVBA.ALLOCATION_RULE_ID
AND RCTT.type IN ('INV','CM','DM','CB','DEP','GUAR')
AND (JZVTD.billing_tp_name BETWEEN NVL(P_customer_name_from, JZVTD.billing_tp_name)
AND NVL(P_customer_name_to, JZVTD.billing_tp_name))
And ( (P_Document_Sequence_Name_From Is Null And P_Document_Sequence_Name_To Is Null)
Or (JZVTD.doc_seq_name BETWEEN NVL(P_DOCUMENT_SEQUENCE_NAME_FROM,JZVTD.doc_seq_name)
AND NVL(P_DOCUMENT_SEQUENCE_NAME_TO,JZVTD.doc_seq_name))
)
AND JZVRS.tax_calendar_period = P_PERIOD
AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID;
INSERT INTO jg_zz_vat_trx_gt
(
jg_info_n1
, jg_info_v1
, jg_info_v2
, jg_info_v3
, jg_info_d1
, jg_info_v4
, jg_info_v5
, jg_info_v6
, jg_info_n2
, jg_info_n3
, jg_info_n4
, jg_info_v7
, jg_info_n5 --line_number
, jg_info_v8
, jg_info_v14
, jg_info_v9
, jg_info_v15
, jg_info_n6
, jg_info_n11
, jg_info_n12
, jg_info_n13
, jg_info_v10
, jg_info_v11
, jg_info_v12 -- vat_taxable_box
, jg_info_v13 -- vat_tax_box
, jg_info_n7
, jg_info_n8
, jg_info_n9
, jg_info_n10
)
VALUES(
l_invoice.period_year
, l_invoice.period_name
, l_invoice.document_sequence_name
, l_invoice.document_sequence_number
, l_invoice.invoice_date
, l_invoice.customer_number
, l_invoice.customer_name
, l_invoice.invoice_number
, l_invoice.vat_amount_funcl_curr
, l_invoice.inv_amt_wo_vat_fun_curr
, l_invoice.tot_inv_amt_w_vat_fun_curr
, l_invoice.line_type
, l_invoice.line_number
, l_invoice.account_flexfield
, l_invoice.taxable_acct_flexfield
, l_invoice.account_description
, l_invoice.txbl_acct_description
, l_invoice.acctd_amount
, l_invoice.acctd_vat_amt
, l_invoice.acctd_inv_amt
, l_invoice.acctd_tot_amt
, l_invoice.vat_code
, l_invoice.vat_trx_type
, l_invoice.vat_taxable_box
, l_invoice.vat_tax_box
, l_invoice.tax_amount
, l_invoice.taxable_amount
, l_invoice.taxable_amt_fun_curr
, l_invoice.tax_amt_fun_curr
);
SELECT
JZVTD.doc_seq_name DOCUMENT_SEQ_NAME
,JZVTD.trx_number PRINT_SEQ
,JZVTD.gl_date GL_DATE
,JZVTD.doc_seq_value DOCUMENT_SEQ_NUMBER
,JZVTD.billing_tp_name CUSTOMER_NAME
,JZVTD.billing_tp_number CUSTOMER_NUMBER
,JZVTD.billing_tp_site_name CUSTOMER_SITE_NAME
,NVL(NVL(NVL(JZVTD.billing_tp_site_tax_reg_num,JZVTD.shipping_tp_site_tax_reg_num)
,JZVTD.billing_tp_tax_reg_num), JZVTD.shipping_tp_tax_reg_num) TAX_REFERENCE
,JZVTD.trx_number INVOICE_NUMBER
,JZVTD.trx_date INVOICE_DATE
,JZVTD.tax_rate_code TAX_CODE
,NVL(JZVTD.taxable_amt_funcl_curr,0) TAXABLE_AMT_FUNCL_CURR
,NVL(JZVTD.tax_amt_funcl_curr,0) TAX_AMT_FUNCL_CURR
,JZVTD.tax_rate TAX_RATE
,JZVTD.tax_rate_code_description DESCRIPTION
,JZVRV.effective_to_date JEITARSV_PREL_ALERT
,JZVTD.trx_line_id TRX_LINE_ID
,SUBSTR(JZVTD.billing_tp_name,1,90) PARTY_NAME
,SUBSTR(JZVTD.billing_tp_name, 1 , 22) VAT_REGISTER_NAME
,JZVTD.posted_flag POSTED_FLAG
,JZVTD.trx_currency_code TRX_CURRENCY_CODE
,JZVTD.TRX_DUE_DATE TRX_DUE_DATE
,JZVTD.ACCOUNTING_DATE ACCOUNTING_DATE
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_registers_vl JZVRV
,jg_zz_vat_rep_status JZVRS
,jg_zz_vat_doc_sequences JZVDS
WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
AND JZVRV.vat_reporting_entity_id = JZVRS.mapping_vat_rep_entity_id
AND JZVDS.doc_sequence_id = JZVTD.doc_seq_id
AND JZVDS.vat_register_id = JZVRV.vat_register_id
AND JZVRS.source = 'AR'
AND NVL(JZVTD.offset_flag,'N') <> 'Y'
AND JZVRS.tax_calendar_period = P_PERIOD
AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZVRV.vat_register_id = P_VAT_REGISTER_ID;
SELECT jg_info_n1, jg_info_v1
INTO lv_start_seq, lv_reporting_status
FROM jg_zz_vat_trx_gt
WHERE jg_info_v30 = 'SEQ';
INSERT INTO jg_zz_vat_trx_gt
(
jg_info_v2
, jg_info_v1
, jg_info_d1
, jg_info_n2
, jg_info_v3
, jg_info_v4
, jg_info_v5
, jg_info_d2
, jg_info_v6
, jg_info_n3
, jg_info_n4
, jg_info_n5 --tax_rate
, jg_info_v7
, jg_info_d3
, jg_info_v8
, jg_info_v9
, jg_info_v10 --posted_flag
, jg_info_v11 --customer_number
, jg_info_v12 --trx_currency_code
, jg_info_v13 --customer_site_name
, jg_info_d4 --trx_due_date
, jg_info_d5 --accounting_date
, jg_info_v30
)
VALUES(
l_get_std_invoice.document_seq_name
, l_get_std_invoice.print_seq
, l_get_std_invoice.gl_date
, l_get_std_invoice.document_seq_number
, l_get_std_invoice.customer_name
, l_get_std_invoice.tax_reference
, l_get_std_invoice.invoice_number
, l_get_std_invoice.invoice_date
, l_get_std_invoice.tax_code
, l_get_std_invoice.taxable_amt_funcl_curr
, l_get_std_invoice.tax_amt_funcl_curr
, l_get_std_invoice.tax_rate
, l_get_std_invoice.description
, l_get_std_invoice.jeitarsv_prel_alert
, l_get_std_invoice.party_name
, l_get_std_invoice.vat_register_name
, l_get_std_invoice.posted_flag
, l_get_std_invoice.customer_number
, l_get_std_invoice.trx_currency_code
, l_get_std_invoice.customer_site_name
, l_get_std_invoice.trx_due_date
, l_get_std_invoice.accounting_date
, 'JEITARSV'
);
SELECT count(*)
INTO l_rec_count
FROM (SELECT 1
FROM jg_zz_vat_trx_gt
WHERE jg_info_v30 = 'JEITARSV'
GROUP BY jg_info_v2
, jg_info_n2
, jg_info_v1
, jg_info_d1
, jg_info_v3
, jg_info_v11
, jg_info_v13
, jg_info_v4
, jg_info_v5
, jg_info_d2
, jg_info_d4
, jg_info_v12
, jg_info_v10
, jg_info_d5);
UPDATE jg_zz_vat_final_reports
SET start_sequence_num = lv_start_seq + l_rec_count,
last_start_sequence_num = lv_start_seq
WHERE report_name = p_report_name
AND vat_register_id = p_vat_register_id
AND reporting_status_id = (SELECT reporting_status_id
FROM jg_zz_vat_rep_status
WHERE vat_reporting_entity_id = p_vat_rep_entity_id
AND source = 'AR'
AND tax_calendar_period = p_period);
SELECT
JZVTD.doc_seq_name DOCUMENT_SEQ_NAME
,JZVTD.trx_number PRINT_SEQ_INV_NUM
,JZVTD.gl_date GL_DATE
,JZVTD.doc_seq_value DOCUMENT_SEQ_NUM
,JZVTD.billing_tp_name CUSTOMER_NAME
,NVL(NVL(NVL(JZVTD.billing_tp_site_tax_reg_num,
JZVTD.shipping_tp_site_tax_reg_num)
,JZVTD.billing_tp_tax_reg_num),
JZVTD.shipping_tp_tax_reg_num) TAX_REFERENCE
,JZVTD.trx_number INVOICE_NUMBER
,JZVTD.trx_date INVOICE_DATE
,JZVTD.trx_id INVOICE_ID
,JZVTD.tax_rate_code TAX_CODE
,NVL(JZVTD.taxable_amt_funcl_curr,0) TAXABLE_AMOUNT
,NVL(JZVTD.tax_amt_funcl_curr,0) TAX_AMOUNT
,NVL(JZVTD.tax_amt_funcl_curr,0) +
NVL(JZVTD.taxable_amt_funcl_curr,0) TOTAL_AMOUNT_FUNC_CURR
,NVL(JZVTD.taxable_amt_funcl_curr,0) INV_AMT_WITHOUT_VAT_FUNC_CURR
,NVL(JZVTD.tax_amt_funcl_curr,0) VAT_AMOUNT_FUNC_CURR
,JZVTD.applied_to_trx_number NOTE
,JZVTD.applied_to_trx_id APPLIED_TO_TRX_ID
,JZVTD.tax_rate TAX_RATE
,JZVTD.tax_rate_code_description DESCRIPTION
,JZVRV.effective_to_date JEITRDVR_PREL_ALERT
,JZVTD.trx_line_id TRX_LINE_ID
,SUBSTR(JZVTD.billing_tp_name,1,90) PARTY_NAME
,SUBSTR(JZVTD.billing_tp_name,1,22) VAT_REGISTER_NAME
,JZVTD.posted_flag POSTED_FLAG
,JZVTD.ACCOUNTING_DATE ACCOUNTING_DATE -- bug9872998
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_registers_vl JZVRV
,jg_zz_vat_rep_status JZVRS
,ar_lookups LK
,jg_zz_vat_doc_sequences JZVDS
WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZVRS.tax_calendar_period = P_PERIOD
AND JZVRV.vat_register_id = P_VAT_REGISTER_ID
AND JZVRS.source = 'AR'
AND JZVTD.reporting_status_id = JZVRS.reporting_status_id
AND JZVRV.vat_reporting_entity_id = JZVRS.mapping_vat_rep_entity_id
AND JZVRV.vat_register_id = JZVDS.vat_register_id
AND JZVDS.doc_sequence_id = JZVTD.doc_seq_id
-- Bug 6238170 Start
--AND JZVTD.tax_type_code = LK.lookup_code
AND JZVTD.reporting_code = LK.lookup_code
-- Bug 6238170 End
AND LK.lookup_type = 'JE_DEFERRED_TAX_TYPE'
AND JZVTD.tax_rate_register_type_code = 'INTERIM'
AND nvl(JZVTD.offset_flag,'N') <> 'Y';
SELECT jg_info_n1, jg_info_v1
INTO lv_start_seq, lv_reporting_status
FROM jg_zz_vat_trx_gt
WHERE jg_info_v30 = 'SEQ';
INSERT INTO jg_zz_vat_trx_gt
(
jg_info_v1
, jg_info_v2
, jg_info_d1
, jg_info_v3
, jg_info_v4
, jg_info_v5
, jg_info_v6
, jg_info_d2
, jg_info_v7
, jg_info_n1
, jg_info_n2
, jg_info_n3
, jg_info_n4
, jg_info_n5
, jg_info_v8
, jg_info_n6
, jg_info_v9
, jg_info_d3
, jg_info_n7
, jg_info_v10
, jg_info_v11
, jg_info_n8
, jg_info_v12 --posted_flag
, jg_info_n9 --APPLIED_TO_TRX_ID
, jg_info_v30
,jg_info_d5 -- ACCOUNTING_DATE
)
VALUES(
l_get_std_invoice.document_seq_name
, l_get_std_invoice.print_seq_inv_num
, l_get_std_invoice.gl_date
, l_get_std_invoice.document_seq_num
, l_get_std_invoice.customer_name
, l_get_std_invoice.tax_reference
, l_get_std_invoice.invoice_number
, l_get_std_invoice.invoice_date
, l_get_std_invoice.tax_code
, l_get_std_invoice.taxable_amount
, l_get_std_invoice.tax_amount
, l_get_std_invoice.total_amount_func_curr
, l_get_std_invoice.inv_amt_without_vat_func_curr
, l_get_std_invoice.vat_amount_func_curr
, l_get_std_invoice.note
, l_get_std_invoice.tax_rate
, l_get_std_invoice.description
, l_get_std_invoice.jeitrdvr_prel_alert
, l_get_std_invoice.trx_line_id
, l_get_std_invoice.party_name
, l_get_std_invoice.vat_register_name
, l_get_std_invoice.invoice_id
, l_get_std_invoice.posted_flag
, l_get_std_invoice.applied_to_trx_id
, 'JEITRDVR'
,l_get_std_invoice.accounting_date
);
SELECT count(*)
INTO l_rec_count
FROM (SELECT 1
FROM jg_zz_vat_trx_gt
WHERE jg_info_v30 = 'JEITRDVR'
GROUP BY jg_info_v1
,jg_info_v2
,jg_info_d1
,jg_info_v3
,jg_info_v4
,jg_info_v5
,jg_info_v6
,jg_info_d2);
UPDATE jg_zz_vat_final_reports
SET start_sequence_num = lv_start_seq + l_rec_count,
last_start_sequence_num = lv_start_seq
WHERE report_name = p_report_name
AND vat_register_id = p_vat_register_id
AND reporting_status_id = (SELECT reporting_status_id
FROM jg_zz_vat_rep_status
WHERE vat_reporting_entity_id = p_vat_rep_entity_id
AND source = 'AR'
AND tax_calendar_period = p_period);
SELECT
JZVTD.trx_id SEQ_NUM
,JZVTD.doc_seq_name ||'/'|| JZVTD.doc_seq_value DOC_SEQ_NUM
,JZVTD.trx_date INVOICE_DATE
,DECODE(JZVTD.trx_line_class,'APP'
,JZVTD.applied_to_trx_number
,JZVTD.trx_number) INVOICE_NUMBER
,SUBSTR(JZVTD.billing_tp_name,1,150)||' '||
JZVTD.billing_tp_tax_reg_num CUSTOMER_NAME
,NVL(JZVTD.taxable_amt_funcl_curr,0) NET_AMOUNT
,JZVTD.tax_rate_code TAX_CODE
,JZVTD.tax_rate TAX_RATE
,NVL(JZVTD.tax_amt_funcl_curr,0) TAX_AMOUNT
,JZVTD.tax_rate_vat_trx_type_desc TAX_DESCRIPTION
,JZVTD.REPORTING_CODE REPORTING_CODE
,TAX_RATE_REGISTER_TYPE_CODE REGISTER_TYPE
,JZVTD.trx_line_id TRX_LINE_ID
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_rep_status JZVRS
-- ,fnd_lookup_values LK
WHERE JZVTD.reporting_status_id in (SELECT DISTINCT JZRS.reporting_status_id JZRS
FROM jg_zz_vat_rep_status JZRS
WHERE JZRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZRS.source = 'AR')
AND JZVTD.gl_date BETWEEN JZVRS.period_start_date and JZVRS.period_end_date
-- JZVTD.reporting_status_id = JZVRS.reporting_status_id
-- AND JZVTD.tax_type_code = LK.lookup_code
-- AND LK.lookup_type = 'ZX_TRL_REGISTER_TYPE'
-- AND LK.source_lang = USERENV('LANG')
AND JZVRS.source = 'AR'
AND ((JZVTD.tax_rate_register_type_code = 'TAX' AND P_TAX_REGISTER_TYPE = 'TAX')
OR (JZVTD.tax_rate_register_type_code = 'INTERIM' AND P_TAX_REGISTER_TYPE = 'INTERIM')
OR (JZVTD.tax_rate_register_type_code = 'NON-RECOVERABLE' AND P_TAX_REGISTER_TYPE = 'NON-RECOVERABLE'))
AND JZVTD.reporting_code = P_TAX_TYPE --BUG:9223611
-- Bug8267272 Start
AND JZVRS.tax_calendar_period IN (SELECT RPS1.tax_calendar_period
FROM JG_ZZ_VAT_REP_STATUS RPS1,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_start_date) period_start_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period =p_period) RPS2,
(Select min(vat_reporting_entity_id) vat_reporting_entity_id,
min(period_end_date) period_end_date
From JG_ZZ_VAT_REP_STATUS
Where vat_reporting_entity_id = p_vat_rep_entity_id
And tax_calendar_period = p_period_to) RPS3
WHERE RPS1.vat_reporting_entity_id = p_vat_rep_entity_id
AND RPS2.vat_reporting_entity_id = RPS1.vat_reporting_entity_id
AND RPS3.vat_reporting_entity_id = RPS2.vat_reporting_entity_id
AND trunc(RPS1.period_start_date) >=
trunc(RPS2.period_start_date)
AND trunc(RPS1.period_end_date) <= trunc(RPS3.period_end_date)
GROUP by RPS1.tax_calendar_period)
-- Bug8267272 End
AND ( JZVTD.trx_tax_balancing_segment = P_BALANCING_SEGMENT OR P_BALANCING_SEGMENT is NULL )
AND JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID;
INSERT INTO jg_zz_vat_trx_gt
(
jg_info_n1
, jg_info_v1
, jg_info_d1
, jg_info_v2
, jg_info_v3
, jg_info_n2
, jg_info_v4
, jg_info_n3
, jg_info_n4
, jg_info_v5
, jg_info_v6
, jg_info_v7
, jg_info_n5
)
VALUES(
l_inv_lines.seq_num
, l_inv_lines.doc_seq_num
, l_inv_lines.invoice_date
, l_inv_lines.invoice_number
, l_inv_lines.customer_name
, l_inv_lines.net_amount
, l_inv_lines.tax_code
, l_inv_lines.tax_rate
, l_inv_lines.tax_amount
, l_inv_lines.tax_description
, l_inv_lines.reporting_code
, l_inv_lines.register_type
, l_inv_lines.trx_line_id
);
SELECT jg_info_n1
INTO l_start_seq
FROM jg_zz_vat_trx_gt
WHERE jg_info_v30 = 'SEQ';