[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT xle_auth.city tax_office_location
,xle_auth.address3 tax_office_code
FROM XLE_FIRSTPARTY_INFORMATION_V xfpiv
, xle_registrations xle_reg
, xle_legalauth_v xle_auth
WHERE xle_reg.source_id = xfpiv.legal_entity_id
AND xle_reg.source_table = 'XLE_ENTITY_PROFILES'
AND xle_auth.legalauth_id (+) = xle_reg.issuing_authority_id
AND xle_reg.identifying_flag = 'Y'
AND xfpiv.legislative_cat_code = 'INCOME_TAX'
AND xfpiv.legal_entity_id = p_le_id;
SELECT
JZVRS.REPORTING_STATUS_ID
FROM
JG_ZZ_VAT_REP_STATUS JZVRS
WHERE
JZVRS.SOURCE='AP'
AND JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
AND JZVRS.TAX_CALENDAR_PERIOD = P_PERIOD;
SELECT
JLMT.VAT_AGGREGATE_LIMIT_AMT
FROM
JE_IL_VAT_LIMITS JLMT,
JG_ZZ_VAT_REP_STATUS JZVRS
WHERE
JZVRS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
AND JZVRS.TAX_CALENDAR_PERIOD = P_PERIOD
AND JZVRS.TAX_CALENDAR_NAME = JLMT.PERIOD_SET_NAME
AND JLMT.PERIOD_NAME = P_PERIOD
AND ROWNUM = 1;
SELECT activity_code
INTO l_activity_code
FROM xle_entity_profiles
WHERE legal_entity_id = l_legal_entity_id;
SELECT precision
INTO l_precision
FROM fnd_currencies
WHERE currency_code = l_func_curr;
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_v20
,jg_info_v21
,jg_info_n25
,jg_info_v30
,jg_info_v22
,jg_info_v23
,jg_info_v24
)
VALUES
(
l_legal_entity_id
,l_company_name ---l_legal_entity_name
,l_tax_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_activity_code
,l_tax_registration_num
,l_period_end_date
,l_period_start_date
,l_tax_office_location
,l_tax_office_code
,l_precision -- currency precision
,'H'
,l_province
,l_comm_num
,l_vat_reg_num
);
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_REP_ENTITY_ID;
select DISTINCT ledger_category_code
into l_ledger_category_code
from gl_ledgers
where ledger_id = l_ledger_id;
select ENTITY_IDENTIFIER
into l_ent_name_main
from jg_zz_vat_rep_entities
where VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID;
SELECT
JZVRS.tax_calendar_year PERIOD_YEAR
, JZVRS.tax_calendar_period PERIOD_NAME
, NVL(AL.tax_box, '99') TAX_BOX
, NVL(AL.taxable_box, '99') TAXABLE_BOX
, TBLLOOKUP.description TAXABLEBOX_DESCRIPTION
, TLOOKUP.description TAXBOX_DESCRIPTION
, JG.extract_source_ledger SOURCE
, JG.doc_seq_name DOCUMENT_SEQ_NAME
, JG.doc_seq_value DOCUMENT_SEQ_VALUE
/**************************************************
Commented the below code for Bug 7344931 and added GL_DATE
, fnd_date.date_to_displaydate(JG.trx_date) GL_DATE
**************************************************/
, JG.gl_date GL_DATE
, JG.billing_tp_number TRADING_PARTNER_NUMBER
, JG.billing_tp_name TRADING_PARTNER_NAME
, JG.trx_number INVOICE
, JG.trx_line_number LINE_NUMBER
, JG.tax_rate_Code VAT_CODE
, JG.tax_rate_vat_trx_type_code VAT_TRANSACTION_TYPE
, nvl(JG.taxable_amt_funcl_curr,JG.taxable_amt) * to_number (alr.taxable_rec_sign_flag || '1') TAXABLE_AMOUNT -- bug9971010
, nvl(JG.tax_amt_funcl_curr,JG.tax_amt) * to_number(decode(JG.extract_source_ledger, 'AP',
decode(jg.tax_recoverable_flag, 'Y',
alr.tax_rec_sign_flag, alr.tax_non_rec_sign_flag) || '1',
alr.tax_rec_sign_flag || '1')) TAX_AMOUNT
,JG.tax_line_id
,AL.allocation_rule_id
,nvl(JG.cancel_flag,'N') cancel_flag
,JG.TAXABLE_ITEM_SOURCE_ID source_id --Added for bug 11813288
,Jg.Tax_Recoverable_Flag
,Jg.Tax_Recovery_Rate
,zx.Record_Type_Code
FROM
jg_zz_vat_box_allocs AL
, jg_zz_vat_alloc_rules alr
, jg_zz_vat_trx_details JG
, fnd_lookup_values TLOOKUP
, fnd_lookup_values TBLLOOKUP
, Jg_Zz_Vat_Rep_Status Jzvrs
, ZX_lines zx
WHERE
NVL(AL.taxable_box, '99') = TBLLOOKUP.lookup_code
AND NVL(AL.tax_box, '99') = TLOOKUP.lookup_code
AND TLOOKUP.lookup_type = 'JGZZ_VAT_REPORT_BOXES'
AND TBLLOOKUP.lookup_type = 'JGZZ_VAT_REPORT_BOXES'
AND TLOOKUP.language = USERENV('LANG')
AND TBLLOOKUP.language = USERENV('LANG')
AND AL.PERIOD_TYPE = 'PERIODIC'
AND jg.tax_rate_vat_trx_type_code BETWEEN NVL( P_VAT_TRX_TYPE_LOW,jg.tax_rate_vat_trx_type_code )
AND NVL( P_VAT_TRX_TYPE_HIGH,jg.tax_rate_vat_trx_type_code )
AND JZVRS.reporting_status_id = JG.reporting_status_id
AND AL.vat_transaction_id = JG.vat_transaction_id
AND alr.allocation_rule_id = al.allocation_rule_id
AND (P_vat_box IS NULL OR AL.tax_box = P_vat_box OR AL.taxable_box = P_vat_box)
AND JG.extract_source_ledger = Nvl((decode(P_source,'ALL',Jg.Extract_Source_Ledger,P_source)),Jg.Extract_Source_Ledger)
AND (JG.doc_seq_name = P_doc_name OR P_doc_name IS NULL)
AND (JG.doc_seq_value = P_doc_seq_value OR P_doc_seq_value IS NULL)
AND JZVRS.vat_reporting_entity_id = P_vat_rep_entity_id
And Jzvrs.Tax_Calendar_Period = P_Period
And jg.trx_id = zx.trx_id
And jg.tax_line_id = zx.tax_line_id
AND jg.application_id = zx.application_id
AND jg.entity_code = zx.entity_code
AND jg.event_class_code = zx.event_class_code
ORDER BY
JG.tax_line_id
, AL.allocation_rule_id
,JG.taxable_item_source_id
, nvl(JG.cancel_flag,'N')
,JG.actg_source_id
, JG.trx_date
, AL.tax_box
, source
, JG.doc_seq_name
, JG.doc_seq_value
, DECODE (jg.extract_source_ledger
,'AR', JG.trx_line_number
,'AP', JG.trx_line_number
,'GL', JG.trx_line_number);
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_n2
, jg_info_d1
, jg_info_v6
, jg_info_v7
, jg_info_v8
, jg_info_n3
, jg_info_v9
, jg_info_v10
, jg_info_n4
, jg_info_n5
)
VALUES(
l_box_amount_grouped(i).period_year
, l_box_amount_grouped(i).period_name
, l_box_amount_grouped(i).vat_report_box
, l_box_amount_grouped(i).vat_box_description
, l_box_amount_grouped(i).source
, l_box_amount_grouped(i).document_seq_name
, l_box_amount_grouped(i).document_seq_value
, l_box_amount_grouped(i).gl_date
, l_box_amount_grouped(i).trading_partner_number
, l_box_amount_grouped(i).trading_partner_name
, l_box_amount_grouped(i).invoice
, l_box_amount_grouped(i).line_number
, l_box_amount_grouped(i).vat_code
, l_box_amount_grouped(i).vat_transaction_type
, l_box_amount_grouped(i).taxable_amount
, l_box_amount_grouped(i).tax_amount
);
SELECT count(*) into l_count from jg_zz_vat_trx_gt ;
fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_count );
SELECT
NVL(
DECODE(ALLOCBOX.tax_recoverable_flag,
'Y',TO_NUMBER(ALLOCBOX.taxable_box)),1000) TXBL_REC_BOX
, NVL(
DECODE(NVL(ALLOCBOX.tax_recoverable_flag,'N'),
'N',TO_NUMBER(ALLOCBOX.taxable_box)),1000) TXBL_NRC_BOX
, DECODE(JG.extract_source_ledger
,'AP', DECODE(JG.tax_recoverable_flag,'Y', NVL(JG.taxable_amt_funcl_curr ,0))
,NVL(JG.taxable_amt_funcl_curr ,0)) TXBL_AMOUNT
, DECODE(JG.extract_source_ledger
,'AP', DECODE(JG.tax_recoverable_flag,'N', NVL(JG.taxable_amt_funcl_curr ,0))
,NVL(JG.taxable_amt_funcl_curr ,0)) TXBL_NRC_AMOUNT
/* , DECODE(ALLOCBOX.tax_recoverable_flag,
'Y',taxable_amt, 0) TXBL_AMOUNT
, DECODE(ALLOCBOX.tax_recoverable_flag,'Y',0,taxable_amt ) TXBL_NRC_AMOUNT */
, NVL(TO_NUMBER(ALLOCRUL.TOTAL_BOX),1000) TOTAL_BOX
, NVL(TO_NUMBER(ALLOCBOX.tax_box),1000) TAX_BOX --Here we have to identify, whether this tax box is recoverable or non-rec box.
, NVL(DECODE(JG.extract_source_ledger,'GL',
DECODE(JG.prl_no,
'I',(NVL(JG.taxable_amt_funcl_curr ,0)*(NVL(JG.tax_rate,0)/100))*(NVL(JG.tax_recovery_rate,0)/ 100),
'O',((NVL(JG.taxable_amt_funcl_curr,0)*(NVL(JG.tax_rate,0)/100))*(NVL(JG.tax_recovery_rate,0)/ 100)*-1 ))
,DECODE(JG.extract_source_ledger,'AP',NVL(JG.tax_amt_funcl_curr , 0)
,DECODE(JG.extract_source_ledger,'AR',NVL(JG.tax_amt_funcl_curr , 0)*-1)))
,0) TAX_AMOUNT
, DECODE(JG.application_id,
200,'I',
222,'0',
101,JG.prl_no) TAX_CLASS
, JG.tax_rate_code TAX_CODE_RATE
, JG.tax_rate TAX_RATE
, JG.trx_id TRANSACTION_ID
, JG.trx_number TRX_NUMBER
, JG.extract_source_ledger SOURCE_LEDGER
, JG.tax_recovery_rate TAX_RECV_RATE
, ALLOCBOX.tax_recoverable_flag TAX_RECOVERABLE_FLAG
, JG.extract_source_ledger EXTRACT_SOURCE_LEDGER
, AllOCBOX.allocation_rule_id ALLOCATION_RULE_ID
FROM jg_zz_vat_trx_details JG
, jg_zz_vat_rep_status JZVRS
, jg_zz_vat_box_allocs AllOCBOX
, jg_zz_vat_alloc_rules ALLOCRUL
WHERE JZVRS.reporting_status_id = JG.reporting_status_id
AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.tax_calendar_year = p_period
AND JG.pt_location = p_location
AND AllOCBOX.vat_transaction_id = JG.vat_transaction_id
AND AllOCBOX.allocation_rule_id = ALLOCRUL.allocation_rule_id
AND AllOCBOX.period_type = 'ANNUAL'
ORDER BY JG.trx_id,
AllOCBOX.allocation_rule_id;
INSERT INTO jg_zz_vat_trx_gt ( jg_info_n1
,jg_info_n2
,jg_info_n3
,jg_info_n4
,jg_info_v1
)
VALUES(
i,
v_box_amounts(i).taxable_amount,
v_box_amounts(i).tax_amount,
v_box_amounts(i).tot_box_num,
v_box_amounts(i).box_type
);
INSERT INTO jg_zz_vat_trx_gt ( jg_info_n1
,jg_info_n2
,jg_info_n3
,jg_info_n5
,jg_info_v1
)
VALUES(
floor(i/10000) + decode(sign(v_box_amounts(i).tax_rate), -1, 1, 0)
,v_box_amounts(i).taxable_amount
,v_box_amounts(i).tax_amount
,v_box_amounts(i).tax_rate
,v_box_amounts(i).box_type
);
select count(*) into l_count from jg_zz_vat_trx_gt ;
fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table :' || l_count );
SELECT NVL(TO_NUMBER(al.taxable_box), 1000) TAXABLE_BOX
, NVL(taxable_amt_funcl_curr , 0) TAXABLE_AMOUNT
, NVL(TO_NUMBER(al.tax_box), 1000) TAX_BOX
, NVL(DECODE(DECODE(JG.extract_source_ledger
,'GL',DECODE(JG.PRL_NO,'I','AP','O','AR')
,JG.extract_source_ledger)
, 'AP', DECODE(SIGN(tax_amt_funcl_curr )
, 1 , DECODE(trx_line_class,'AP_CREDIT_MEMO',0,
'AP_DEBIT_MEMO',0,tax_amt_funcl_curr )
, DECODE(reporting_code
, 'OFFSET' , 0
, DECODE(trx_line_class
, 'AP_CREDIT_MEMO' , 0
, 'AP_DEBIT_MEMO' , 0
,tax_amt_funcl_curr )))
, 'AR', DECODE(reporting_code
, 'OFFSET',DECODE(SIGN(tax_amt_funcl_curr ), 1,tax_amt_funcl_curr )
, DECODE(SIGN(tax_amt_funcl_curr ), -1, tax_amt_funcl_curr * -1 , 0))), 0) DEDUCT_TAX
, NVL(DECODE(DECODE(JG.extract_source_ledger
,'GL',DECODE(JG.PRL_NO,'I','AP','O','AR')
,JG.extract_source_ledger)
, 'AP', DECODE(SIGN(tax_amt_funcl_curr )
, -1, DECODE(reporting_code
, 'OFFSET', tax_amt_funcl_curr * -1
, DECODE(trx_line_class
, 'AP_CREDIT_MEMO',tax_amt_funcl_curr *-1
, 'AP_DEBIT_MEMO',tax_amt_funcl_curr * -1,0))
, DECODE(trx_line_class
, 'AP_CREDIT_MEMO' ,tax_amt_funcl_curr
, 'AP_DEBIT_MEMO',tax_amt_funcl_curr,0))
, 'AR', DECODE(reporting_code
, 'OFFSET',DECODE(SIGN(tax_amt_funcl_curr ), -1,tax_amt* -1 )
, DECODE(SIGN(tax_amt_funcl_curr ), 1, tax_amt_funcl_curr , 0))), 0) CLEAR_TAX
, JG.trx_line_class TRX_LINE_CLASS
, JG.tax_rate_code TAX_RATE_CODE
, JG.trx_id TRX_ID
, JG.tax_recoverable_flag TAX_RECOVERABLE_FLAG
, JG.trx_number TRX_NUMBER
, DECODE(JG.application_id, 200,'I', 222,'O',
101,JG.prl_no) TAX_CLASS
,JG.extract_source_ledger EXTRACT_SOURCE_LEDGER
,JG.reporting_code REPORTING_CODE
FROM jg_zz_vat_trx_details JG
, jg_zz_vat_rep_status JZVRS
, jg_zz_vat_box_allocs AL
WHERE decode(JG.extract_source_ledger,'AP',NVL(JG.tax_recoverable_flag,'N'),'Y') ='Y' -- Bug 5561879
AND JZVRS.reporting_status_id = JG.reporting_status_id
AND AL.vat_transaction_id = JG.vat_transaction_id
AND AL.period_type = 'PERIODIC'
AND JZVRS.tax_calendar_period = p_period
AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JG.pt_location = p_location ;
INSERT INTO jg_zz_vat_trx_gt(
jg_info_n1,
jg_info_n2,
jg_info_n3,
jg_info_n4,
jg_info_v1
)
VALUES(
i,
v_box_amounts(i).taxable_amount,
v_box_amounts(i).deduct_tax,
v_box_amounts(i).clear_tax,
v_box_amounts(i).box_type
);
SELECT count(*) into l_count from jg_zz_vat_trx_gt ;
fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_count );
SELECT
tax_rate_code TAX_RATE_CODE
, tax_rate_code_description VAT_CODE_DESC
, trx_id TRX_ID
, DECODE(extract_source_ledger,'AP',taxable_amt*(tax_recovery_rate/100),taxable_amt) TAXABLE_AMT
, tax_amt TAX_AMT
/****************************************************
Re ordered the query to keep it in sync with the Insert query used
below
Bug 7355610 START
****************************************************
, DECODE(extract_source_ledger,'AP',taxable_amt_funcl_curr*(tax_recovery_rate/100),taxable_amt_funcl_curr) TAXABLE_AMT_FUNCL_CURR
, tax_amt_funcl_curr TAX_AMT_FUNCL_CURR
***************************************************/
, tax_amt_funcl_curr TAX_AMT_FUNCL_CURR
, DECODE(extract_source_ledger,'AP',taxable_amt_funcl_curr*(tax_recovery_rate/100),taxable_amt_funcl_curr) TAXABLE_AMT_FUNCL_CURR
/**************************************************
Bug 7355610 END
**************************************************/
, doc_seq_id DOC_SEQ_ID
, extract_source_ledger EXTRACT_SOURCE_LEDGER
, trx_line_type TRX_LINE_TYPE
, accounting_date ACCOUNTING_DATE
-- Bug 6238170 Start
--, tax_type_code TAX_TYPE_CODE
, reporting_code REPORTING_CODE
-- Bug 6238170 End
, gl_transfer_flag GL_TRANSFER_FLAG
, tax_rate_register_type_code TAX_RATE_REGISTER_TYPE_CODE
, trx_currency_code TRX_CURRENCY_CODE
, tax_recovery_rate TAX_RECOVERY_RATE
, tax_recoverable_flag TAX_RECOVERABLE_FLAG
, tax_rate_id TAX_RATE_ID
, trx_number TRX_NUMBER
, DECODE(credit_balance_amt,NULL,0,credit_balance_amt) CREDIT_BALANCE_AMT
, assessable_value ASSESSABLE_VALUE
, offset_tax_rate_code OFFSET_TAX_RATE_CODE
, offset_flag OFFSET_FLAG
, applied_to_trx_id APPLIED_TO_TRX_ID -- bug 12691340
, JZVRS.mapping_vat_rep_entity_id MAPPING_VAT_REP_ENTITY_ID
FROM
jg_zz_vat_trx_details JG
, jg_zz_vat_rep_status JZVRS
WHERE JZVRS.reporting_status_id = JG.reporting_status_id
AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.tax_calendar_period = p_period ;
SELECT
max(JZVRS.period_end_date)
FROM
jg_zz_vat_rep_status JZVRS
WHERE
JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.period_end_date <
(SELECT max(JZVRS1.period_end_date)
FROM jg_zz_vat_rep_status JZVRS1
WHERE JZVRS1.tax_calendar_period = p_period
AND JZVRS1.vat_reporting_entity_id = p_vat_rep_entity_id
--AND JZVRS1.final_reporting_status_flag = 'S'
)
AND (JZVRS.final_reporting_status_flag = 'S')
;
SELECT
JZVRS.CREDIT_BALANCE_AMT
FROM jg_zz_vat_rep_status JZVRS
WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.tax_calendar_period =( SELECT JZVRS1.tax_calendar_period
FROM jg_zz_vat_rep_status JZVRS1
WHERE
JZVRS1.vat_reporting_entity_id = p_vat_rep_entity_id
AND TRUNC(JZVRS1.period_end_date) = pn_last_process_date
AND ROWNUM =1)
AND ROWNUM = 1;
INSERT INTO jg_zz_vat_trx_gt
(
jg_info_v1
,jg_info_v2
,jg_info_n1
,jg_info_n2 --TAXABLE_AMT_FUNCL_CURR
,jg_info_n3
,jg_info_n4
,jg_info_n5 --TAXABLE_AMT
,jg_info_n6
,jg_info_v3 --extract_source_ledger
,jg_info_v4 --trx_line_type
,jg_info_v5
,jg_info_v6 --reporting_code
-- ,jg_info_d1 --bug5573113
,jg_info_v7 --tax_rate_register_type_code
,jg_info_v8
,jg_info_n7
,jg_info_v9 --tax_recoverable_flag
,jg_info_n9
,jg_info_v10
,jg_info_n10
,jg_info_n11
,jg_info_v12 --offset_tax_rate_code
,jg_info_v13 --offset_flag
,jg_info_n12 -- applied_to_trx_id -- bug 12691340
,jg_info_n13 --mapping_vat_rep_entity_id
)
VALUES
(l_it_vat.tax_rate_code
,l_it_vat.vat_code_desc
,l_it_vat.trx_id
,l_it_vat.taxable_amt_funcl_curr --l_it_vat.taxable_amt bug8587526
/******************************************
Reordered the way of inserting into the temp table as it was not
in sync with the Query used to fetch the values in data definition
jgzzsummaryall.xml
Bug 7355610 START
,l_it_vat.taxable_amt_funcl_curr
,l_it_vat.tax_amt
,l_it_vat.tax_amt_funcl_curr
*****************************************/
,l_it_vat.tax_amt
,l_it_vat.tax_amt_funcl_curr
,l_it_vat.taxable_amt --l_it_vat.taxable_amt_funcl_curr bug8587526
/***************************************
Bug 7355610 END
****************************************/
,l_it_vat.doc_seq_id
,l_it_vat.extract_source_ledger
,l_it_vat.trx_line_type
,l_it_vat.accounting_date
-- Bug 6238170 Start
--,l_it_vat.tax_type_code
,l_it_vat.reporting_code
-- Bug 6238170 End
--,l_it_vat.gl_transfer_flag --bug5573113
,l_it_vat.tax_rate_register_type_code
,l_it_vat.trx_currency_code
,l_it_vat.tax_recovery_rate
,l_it_vat.tax_recoverable_flag
,l_it_vat.tax_rate_id
,l_it_vat.trx_number
,l_it_vat.credit_balance_amt
,l_it_vat.assessable_value
,l_it_vat.offset_tax_rate_code
,l_it_vat.offset_flag
,l_it_vat.applied_to_trx_id -- bug 12691340
,l_it_vat.MAPPING_VAT_REP_ENTITY_ID
);
UPDATE jg_zz_vat_trx_gt
SET jg_info_n10 = CARRY_OVER;
SELECT count(*) into l_count from jg_zz_vat_trx_gt ;
fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt table: ' || l_count );
SELECT JZVRS.VAT_REPORTING_ENTITY_ID REPORTING_ENTITY
,1 COUNT_STD_INV, DECODE(JZVTD.REPORTING_CODE, 'VAT-C', 'C','VAT-P','P','VAT-H','H','T') CLASS
,JZVTD.TRX_ID INV_ID_STANDARD
,NVL(AP.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP
,JG_ZZ_SUMMARY_ALL_PKG.JEILR835_INV_NUM(JZVTD.TRX_NUMBER) INV_NUMBER_STANDARD
,TO_CHAR(JZVTD.TRX_DATE,'RRRRMMDD') INV_DATE_STANDARD
,JZVTD.TRX_LINE_CLASS TRX_LINE_CLASS
,JZVTD.BILLING_TP_NUMBER VEN_NUM_STANDARD
,NVL(LPAD(SUBSTR(NVL(JZVTD.BILLING_TP_SITE_TAX_REG_NUM, JZVTD.BILLING_TP_TAX_REG_NUM),1,9),9,'0'),'000000000') TAX_REG_NUM_STANDARD
,SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT)) VAT_AMOUNT_STANDARD
,SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT_STANDARD
FROM
JG_ZZ_VAT_TRX_DETAILS JZVTD
,JG_ZZ_VAT_REP_STATUS JZVRS
,AP_INVOICES_ALL AP
WHERE
JZVRS.VAT_REPORTING_ENTITY_ID in
(p_vat_rep_entity_id, p_vat_rep_entity_id2,
p_vat_rep_entity_id3, p_vat_rep_entity_id4,
p_vat_rep_entity_id5, p_vat_rep_entity_id6,
p_vat_rep_entity_id7, p_vat_rep_entity_id8,
p_vat_rep_entity_id9, p_vat_rep_entity_id10)
AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
AND JZVRS.SOURCE = 'AP'
AND JZVRS.TAX_CALENDAR_PERIOD = p_period
AND JZVTD.TAX_RATE_REGISTER_TYPE_CODE = 'TAX'
AND JZVTD.REPORTING_CODE IN ('VAT-A','VAT-S','VAT-C', 'VAT-P','VAT-H')
AND (p_vat_trx_type IS NULL OR
JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
AND AP.INVOICE_ID = JZVTD.TRX_ID
GROUP BY
JZVRS.VAT_REPORTING_ENTITY_ID,
DECODE(JZVTD.REPORTING_CODE, 'VAT-C', 'C','VAT-P','P','VAT-H','H','T')
,JZVTD.TRX_ID
,NVL(AP.GLOBAL_ATTRIBUTE1,'0000')
,JZVTD.TRX_NUMBER
,JZVTD.TRX_DATE
,JZVTD.BILLING_TP_NUMBER
,NVL(LPAD(SUBSTR(NVL(JZVTD.BILLING_TP_SITE_TAX_REG_NUM,JZVTD.BILLING_TP_TAX_REG_NUM),1,9),9,'0'),'000000000')
,JZVTD.TRX_LINE_CLASS
HAVING
SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT)) <> 0
ORDER BY
JZVRS.VAT_REPORTING_ENTITY_ID,
JZVTD.TRX_DATE;
SELECT JZVRS.VAT_REPORTING_ENTITY_ID REPORTING_ENTITY,
1 COUNT_IMPORT, JZVTD.TRX_ID INV_ID_IMPORT,
NVL(APINV.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP,
JG_ZZ_SUMMARY_ALL_PKG.JEILR835_INV_NUM(JZVTD.TRX_NUMBER) INV_NUMBER_IMPORT,
JZVTD.TRX_DATE INV_DATE_IMPORT,
JZVTD.BILLING_TP_NUMBER VEN_NUMBER,
NVL(LPAD(SUBSTR(NVL(JZVTD.BILLING_TP_SITE_TAX_REG_NUM, JZVTD.BILLING_TP_TAX_REG_NUM), 1, 9), 9, '0'), '000000000') TAX_REG_NUM_IMPORT,
NVL(APINVL.GLOBAL_ATTRIBUTE14, APINV.INVOICE_NUM) IMP_DOC_NUM_IMPORT,
TO_CHAR(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')),'RRRRMMDD') IMP_DOC_DATE_IMPORT,
SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT)) VAT_AMOUNT_IMPORT,
SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT_IMPORT
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 in
(p_vat_rep_entity_id, p_vat_rep_entity_id2,
p_vat_rep_entity_id3, p_vat_rep_entity_id4,
p_vat_rep_entity_id5, p_vat_rep_entity_id6,
p_vat_rep_entity_id7, p_vat_rep_entity_id8,
p_vat_rep_entity_id9, p_vat_rep_entity_id10)
AND JZVRS.TAX_CALENDAR_PERIOD = p_period
AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
AND JZVTD.TAX_RATE_REGISTER_TYPE_CODE = 'TAX'
AND JZVTD.REPORTING_CODE IN ('VAT-RS','VAT-RA')
AND(p_vat_trx_type IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
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 JZVRS.VAT_REPORTING_ENTITY_ID,
JZVTD.TRX_ID ,
NVL(APINV.GLOBAL_ATTRIBUTE1,'0000'),
JZVTD.TRX_NUMBER ,
JZVTD.TRX_DATE ,
JZVTD.BILLING_TP_NUMBER ,
NVL(LPAD(SUBSTR(NVL(JZVTD.BILLING_TP_SITE_TAX_REG_NUM, JZVTD.BILLING_TP_TAX_REG_NUM), 1, 9), 9, '0'), '000000000') ,
NVL(APINVL.GLOBAL_ATTRIBUTE14, APINV.INVOICE_NUM) ,
TO_CHAR(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')),'RRRRMMDD')
HAVING
SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT)) <> 0
ORDER BY JZVRS.VAT_REPORTING_ENTITY_ID,
JZVTD.TRX_DATE;
SELECT VAT_REPORTING_ENTITY_ID REPORTING_ENTITY,
1 COUNT_PETTY,
REFERENCE_GROUP,
COUNT(TRX_ID) NO_OF_INVOICES,
SUM(VAT_AMOUNT) VAT_AMOUNT_PETTY_CASH,
SUM(TAXABLE_AMOUNT) TAXABLE_AMOUNT_PETTY_CASH
FROM( SELECT JZVRS.VAT_REPORTING_ENTITY_ID,
NVL(APINV.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP,
JZVTD.TRX_ID TRX_ID,
SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT)) VAT_AMOUNT,
SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT
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 in
(p_vat_rep_entity_id, p_vat_rep_entity_id2,
p_vat_rep_entity_id3, p_vat_rep_entity_id4,
p_vat_rep_entity_id5, p_vat_rep_entity_id6,
p_vat_rep_entity_id7, p_vat_rep_entity_id8,
p_vat_rep_entity_id9, p_vat_rep_entity_id10)
AND JZVTD.IL_VAT_REP_STATUS_ID in (
SELECT JZVRS.REPORTING_STATUS_ID
FROM JG_ZZ_VAT_REP_STATUS JZVRS
WHERE JZVRS.SOURCE='AP'
AND JZVRS.VAT_REPORTING_ENTITY_ID in (
p_vat_rep_entity_id, p_vat_rep_entity_id2,
p_vat_rep_entity_id3, p_vat_rep_entity_id4,
p_vat_rep_entity_id5, p_vat_rep_entity_id6,
p_vat_rep_entity_id7, p_vat_rep_entity_id8,
p_vat_rep_entity_id9, p_vat_rep_entity_id10)
AND JZVRS.TAX_CALENDAR_PERIOD = P_PERIOD)
AND JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
AND JZVTD.TAX_RATE_REGISTER_TYPE_CODE = 'TAX'
AND JZVTD.REPORTING_CODE IN('VAT-KS','VAT-KA')
AND(p_vat_trx_type IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
AND APINV.INVOICE_ID = JZVTD.TRX_ID
GROUP BY JZVRS.VAT_REPORTING_ENTITY_ID,
NVL(APINV.GLOBAL_ATTRIBUTE1,'0000'),JZVTD.TRX_ID
HAVING
SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR,JZVTD.TAX_AMT)) <> 0)
GROUP by VAT_REPORTING_ENTITY_ID, REFERENCE_GROUP
ORDER by VAT_REPORTING_ENTITY_ID, REFERENCE_GROUP;
SELECT JZVRS.VAT_REPORTING_ENTITY_ID REPORTING_ENTITY,
1 COUNT_TRX,
JZVTD.REPORTING_CODE REPORTING_CODE,
JZVTD.TRX_ID TRX_ID,
NVL(AR.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP,
JG_ZZ_SUMMARY_ALL_PKG.JEILR835_INV_NUM(JZVTD.TRX_NUMBER) TRX_NUMBER,
TO_CHAR(DECODE(JZVTD.REPORTING_CODE, 'VAT-Y',DECODE(LENGTH(NVL(ARL.GLOBAL_ATTRIBUTE2,'A')),
1,jzvtd.TRX_DATE,
19,TO_DATE(ARL.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),
TO_DATE(ARL.global_attribute2, 'DD-MM-RRRR')),jzvtd.trx_date),'RRRRMMDD') TRX_DATE,
JZVTD.TRX_LINE_CLASS TRX_LINE_CLASS,
DECODE(JZVTD.REPORTING_CODE, 'VAT-Y',NVL(ARL.GLOBAL_ATTRIBUTE1,'999999999'),JZVTD.BILLING_TP_TAXPAYER_ID) TAXPAYER_ID,
DECODE(JZVTD.REPORTING_CODE, 'VAT-Y', 0, SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR, JZVTD.TAX_AMT))) VAT_AMOUNT,
SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT
FROM
JG_ZZ_VAT_TRX_DETAILS JZVTD,
JG_ZZ_VAT_REP_STATUS JZVRS,
RA_CUSTOMER_TRX_ALL AR,
RA_CUSTOMER_TRX_LINES_ALL ARL
WHERE
JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
AND JZVRS.SOURCE = 'AR'
AND JZVRS.VAT_REPORTING_ENTITY_ID in
(p_vat_rep_entity_id, p_vat_rep_entity_id2,
p_vat_rep_entity_id3, p_vat_rep_entity_id4,
p_vat_rep_entity_id5, p_vat_rep_entity_id6,
p_vat_rep_entity_id7, p_vat_rep_entity_id8,
p_vat_rep_entity_id9, p_vat_rep_entity_id10)
AND JZVRS.TAX_CALENDAR_PERIOD = p_period
AND JZVTD.REPORTING_CODE IN ('VAT-M','VAT-I','VAT-Y')
AND(p_vat_trx_type IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
AND AR.CUSTOMER_TRX_ID = JZVTD.TRX_ID
AND ARL.CUSTOMER_TRX_ID = JZVTD.TRX_ID
AND ARL.CUSTOMER_TRX_LINE_ID = JZVTD.TRX_LINE_ID
GROUP BY
JZVRS.VAT_REPORTING_ENTITY_ID,
JZVTD.REPORTING_CODE,
JZVTD.TRX_ID,
NVL(AR.GLOBAL_ATTRIBUTE1,'0000'),
JZVTD.TRX_NUMBER,
TO_CHAR(DECODE(JZVTD.REPORTING_CODE, 'VAT-Y',DECODE(LENGTH(NVL(ARL.GLOBAL_ATTRIBUTE2,'A')),
1,jzvtd.TRX_DATE,
19,TO_DATE(ARL.global_attribute2,'RRRR/MM/DD HH24:MI:SS'),
TO_DATE(ARL.global_attribute2, 'DD-MM-RRRR')),jzvtd.trx_date),'RRRRMMDD'),
JZVTD.TRX_LINE_CLASS,
DECODE(JZVTD.REPORTING_CODE, 'VAT-Y',NVL(ARL.GLOBAL_ATTRIBUTE1,'999999999'),JZVTD.BILLING_TP_TAXPAYER_ID)
ORDER BY 1,2,6;
SELECT JZVRS.VAT_REPORTING_ENTITY_ID REPORTING_ENTITY,
1 COUNT_REG_TRX,
JZVTD.REPORTING_CODE REPORTING_CODE,
JZVTD.TRX_ID TRX_ID,
NVL(AR.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP,
JG_ZZ_SUMMARY_ALL_PKG.JEILR835_INV_NUM(JZVTD.TRX_NUMBER) TRX_NUMBER,
TO_CHAR(JZVTD.TRX_DATE,'RRRRMMDD') TRX_DATE,
JZVTD.TRX_LINE_CLASS TRX_LINE_CLASS,
JZVTD.BILLING_TP_TAXPAYER_ID TAXPAYER_ID,
DECODE(JZVTD.REPORTING_CODE, 'VAT-S', SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR, JZVTD.TAX_AMT)) , 0) VAT_AMOUNT,
SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT
FROM
JG_ZZ_VAT_TRX_DETAILS JZVTD,
JG_ZZ_VAT_REP_STATUS JZVRS,
RA_CUSTOMER_TRX_ALL AR
WHERE
JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
AND JZVRS.SOURCE = 'AR'
AND JZVRS.VAT_REPORTING_ENTITY_ID in
(p_vat_rep_entity_id, p_vat_rep_entity_id2,
p_vat_rep_entity_id3, p_vat_rep_entity_id4,
p_vat_rep_entity_id5, p_vat_rep_entity_id6,
p_vat_rep_entity_id7, p_vat_rep_entity_id8,
p_vat_rep_entity_id9, p_vat_rep_entity_id10)
AND JZVRS.TAX_CALENDAR_PERIOD = p_period
AND JZVTD.REPORTING_CODE IN ('VAT-S','VAT-0','VAT-EXEMPT','IL_VAT_EXEMPT')
AND(p_vat_trx_type IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
AND AR.CUSTOMER_TRX_ID = JZVTD.TRX_ID
AND JZVTD.BILLING_TP_TAXPAYER_ID IS NOT NULL
GROUP BY
JZVRS.VAT_REPORTING_ENTITY_ID,
JZVTD.REPORTING_CODE,
JZVTD.TRX_ID,
NVL(AR.GLOBAL_ATTRIBUTE1,'0000'),
JZVTD.TRX_NUMBER,
JZVTD.TRX_DATE,
JZVTD.TRX_LINE_CLASS,
JZVTD.BILLING_TP_TAXPAYER_ID
HAVING
DECODE(JZVTD.REPORTING_CODE, 'VAT-S', SIGN(l_vat_aggregation_limit_amt - SUM(ABS(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)))) , 0) <> 1
ORDER BY 1,2,6;
SELECT JZVRS.VAT_REPORTING_ENTITY_ID REPORTING_ENTITY,
1 COUNT_UNKNOWN_TRX,
JZVTD.REPORTING_CODE REPORTING_CODE,
JZVTD.TRX_ID TRX_ID,
NVL(AR.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP,
JG_ZZ_SUMMARY_ALL_PKG.JEILR835_INV_NUM(JZVTD.TRX_NUMBER) TRX_NUMBER,
TO_CHAR(JZVTD.TRX_DATE,'RRRRMMDD') TRX_DATE,
JZVTD.TRX_LINE_CLASS TRX_LINE_CLASS,
DECODE(JZVTD.REPORTING_CODE, 'VAT-L', SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR, JZVTD.TAX_AMT)),
'VAT-S', SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR, JZVTD.TAX_AMT)), 0) VAT_AMOUNT,
SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT
FROM
JG_ZZ_VAT_TRX_DETAILS JZVTD,
JG_ZZ_VAT_REP_STATUS JZVRS,
RA_CUSTOMER_TRX_ALL AR
WHERE
JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
AND JZVRS.SOURCE = 'AR'
AND JZVRS.VAT_REPORTING_ENTITY_ID in
(p_vat_rep_entity_id, p_vat_rep_entity_id2,
p_vat_rep_entity_id3, p_vat_rep_entity_id4,
p_vat_rep_entity_id5, p_vat_rep_entity_id6,
p_vat_rep_entity_id7, p_vat_rep_entity_id8,
p_vat_rep_entity_id9, p_vat_rep_entity_id10)
AND JZVRS.TAX_CALENDAR_PERIOD = p_period
AND JZVTD.REPORTING_CODE IN ('VAT-S', 'VAT-L','VAT-0','VAT-EXEMPT','IL_VAT_EXEMPT')
AND(p_vat_trx_type IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
AND AR.CUSTOMER_TRX_ID = JZVTD.TRX_ID
AND ((JZVTD.REPORTING_CODE = 'VAT-L') OR (JZVTD.BILLING_TP_TAXPAYER_ID IS NULL AND JZVTD.REPORTING_CODE <> 'VAT-L'))
GROUP BY
JZVRS.VAT_REPORTING_ENTITY_ID,
JZVTD.REPORTING_CODE,
JZVTD.TRX_ID,
NVL(AR.GLOBAL_ATTRIBUTE1,'0000'),
JZVTD.TRX_NUMBER,
JZVTD.TRX_DATE,
JZVTD.TRX_LINE_CLASS
HAVING
DECODE(JZVTD.REPORTING_CODE, 'VAT-L', SIGN(l_vat_aggregation_limit_amt - SUM(ABS(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)))),
'VAT-S', SIGN(l_vat_aggregation_limit_amt - SUM(ABS(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)))) , 0) <> 1
ORDER BY
JZVRS.VAT_REPORTING_ENTITY_ID,
JZVTD.REPORTING_CODE,
JZVTD.TRX_NUMBER;
SELECT VAT_REPORTING_ENTITY_ID REPORTING_ENTITY,
1 COUNT_SUM_TRXS,
REFERENCE_GROUP,
COUNT(DISTINCT TRX_ID) NO_OF_AGGRE_INVOICES,
SUM(VAT_AMOUNT) SUM_OF_VAT_AMT,
SUM(TAXABLE_AMOUNT) SUM_OF_TAXABLE_AMT
FROM ( SELECT JZVRS.VAT_REPORTING_ENTITY_ID,
JZVTD.TRX_ID TRX_ID,
NVL(AR.GLOBAL_ATTRIBUTE1,'0000') REFERENCE_GROUP,
JG_ZZ_SUMMARY_ALL_PKG.JEILR835_INV_NUM(JZVTD.TRX_NUMBER) TRX_NUMBER,
JZVTD.TRX_LINE_CLASS TRX_LINE_CLASS,
SUM(NVL(JZVTD.TAX_AMT_FUNCL_CURR, JZVTD.TAX_AMT)) VAT_AMOUNT,
SUM(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT)) TAXABLE_AMOUNT
FROM
JG_ZZ_VAT_TRX_DETAILS JZVTD,
JG_ZZ_VAT_REP_STATUS JZVRS,
RA_CUSTOMER_TRX_ALL AR
WHERE
JZVRS.REPORTING_STATUS_ID = JZVTD.REPORTING_STATUS_ID
AND JZVRS.SOURCE = 'AR'
AND JZVRS.VAT_REPORTING_ENTITY_ID in
(p_vat_rep_entity_id, p_vat_rep_entity_id2,
p_vat_rep_entity_id3, p_vat_rep_entity_id4,
p_vat_rep_entity_id5, p_vat_rep_entity_id6,
p_vat_rep_entity_id7, p_vat_rep_entity_id8,
p_vat_rep_entity_id9, p_vat_rep_entity_id10)
AND JZVRS.TAX_CALENDAR_PERIOD = p_period
AND JZVTD.REPORTING_CODE IN ('VAT-S','VAT-L')
AND(p_vat_trx_type IS NULL OR JZVTD.TAX_RATE_VAT_TRX_TYPE_CODE = p_vat_trx_type)
AND AR.CUSTOMER_TRX_ID = JZVTD.TRX_ID
GROUP BY
JZVRS.VAT_REPORTING_ENTITY_ID,
JZVTD.TRX_ID,
NVL(AR.GLOBAL_ATTRIBUTE1,'0000'),
JZVTD.TRX_NUMBER,
JZVTD.TRX_LINE_CLASS
HAVING
SUM(ABS(NVL(JZVTD.TAXABLE_AMT_FUNCL_CURR, JZVTD.TAXABLE_AMT))) < l_vat_aggregation_limit_amt
) GROUP BY VAT_REPORTING_ENTITY_ID, REFERENCE_GROUP
ORDER BY VAT_REPORTING_ENTITY_ID, REFERENCE_GROUP;
SELECT REPORTING_STATUS_ID
INTO l_rep_status
FROM JG_ZZ_VAT_REP_STATUS
WHERE SOURCE='AP'
AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
AND TAX_CALENDAR_PERIOD = P_PERIOD;
SELECT REPORTING_STATUS_ID
INTO l_rep_status
FROM JG_ZZ_VAT_REP_STATUS
WHERE SOURCE='AP'
AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID2
AND TAX_CALENDAR_PERIOD = P_PERIOD;
SELECT REPORTING_STATUS_ID
INTO l_rep_status
FROM JG_ZZ_VAT_REP_STATUS
WHERE SOURCE='AP'
AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID3
AND TAX_CALENDAR_PERIOD = P_PERIOD;
SELECT REPORTING_STATUS_ID
INTO l_rep_status
FROM JG_ZZ_VAT_REP_STATUS
WHERE SOURCE='AP'
AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID4
AND TAX_CALENDAR_PERIOD = P_PERIOD;
SELECT REPORTING_STATUS_ID
INTO l_rep_status
FROM JG_ZZ_VAT_REP_STATUS
WHERE SOURCE='AP'
AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID5
AND TAX_CALENDAR_PERIOD = P_PERIOD;
SELECT REPORTING_STATUS_ID
INTO l_rep_status
FROM JG_ZZ_VAT_REP_STATUS
WHERE SOURCE='AP'
AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID6
AND TAX_CALENDAR_PERIOD = P_PERIOD;
SELECT REPORTING_STATUS_ID
INTO l_rep_status
FROM JG_ZZ_VAT_REP_STATUS
WHERE SOURCE='AP'
AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID7
AND TAX_CALENDAR_PERIOD = P_PERIOD;
SELECT REPORTING_STATUS_ID
INTO l_rep_status
FROM JG_ZZ_VAT_REP_STATUS
WHERE SOURCE='AP'
AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID8
AND TAX_CALENDAR_PERIOD = P_PERIOD;
SELECT REPORTING_STATUS_ID
INTO l_rep_status
FROM JG_ZZ_VAT_REP_STATUS
WHERE SOURCE='AP'
AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID9
AND TAX_CALENDAR_PERIOD = P_PERIOD;
SELECT REPORTING_STATUS_ID
INTO l_rep_status
FROM JG_ZZ_VAT_REP_STATUS
WHERE SOURCE='AP'
AND VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID10
AND TAX_CALENDAR_PERIOD = P_PERIOD;
SELECT
TO_CHAR(SYSDATE, 'RRRRMMDD') SYSTEM_DATE,
JLMT.PERIOD_YEAR || DECODE(LENGTH(JLMT.PERIOD_NUM), 1, 0 || JLMT.PERIOD_NUM, JLMT.PERIOD_NUM) TAX_PERIOD,
TO_CHAR(sta.period_end_date,'RRRRMMDD') LAST_DAY_TAX_PERIOD
INTO var_system_date, var_tax_period, var_last_day_tax_period
FROM
JE_IL_VAT_LIMITS JLMT,
JG_ZZ_VAT_REP_STATUS STA
WHERE
STA.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID
AND STA.TAX_CALENDAR_PERIOD = p_period
AND JLMT.PERIOD_SET_NAME = STA.TAX_CALENDAR_NAME
AND JLMT.PERIOD_NAME = p_period
AND ROWNUM = 1;
INSERT INTO jg_zz_vat_trx_gt
( -- Data mapping used by ALL CURSORS:
-- ================================
jg_info_v1 -- First position in template
,jg_info_v2 -- Tax Registration Id
,jg_info_v3 -- Trx Date
,jg_info_v4 -- Reference Group
,jg_info_v5 -- Trx Num
,jg_info_v6 -- Tax Amount
,jg_info_v7 -- Taxable amount sign
,jg_info_v8 -- Taxable Amount value
,jg_info_v35 -- Used to order records
,jg_info_v39 -- Report data identifier
,jg_info_v40 -- Used to order trx types in output
,jg_info_n29 -- Used by template to order by Rep Entity
,jg_info_n30 -- Rep Entity Id
)
VALUES
(
l_ap_invoices.class
,lpad(l_ap_invoices.tax_reg_num_standard,9,'0')
,l_ap_invoices.inv_date_standard
,l_ap_invoices.reference_group
,l_ap_invoices.inv_number_standard
,lpad(round(abs(l_ap_invoices.vat_amount_standard)),9,'0')
,decode(sign(l_ap_invoices.taxable_amount_standard),'-1','-','+')
,lpad(round(abs(nvl(l_ap_invoices.taxable_amount_standard,0))),10,'0')
,l_ap_invoices.inv_date_standard
,'JEILR835'
,decode(l_ap_invoices.class,'T','10Q_AP_INVOICES',
'C','20Q_AP_INVOICES',
'H','30Q_AP_INVOICES',
'40Q_AP_INVOICES')
,decode(l_ap_invoices.reporting_entity, p_vat_rep_entity_id, 1,
p_vat_rep_entity_id2, 2,
p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
,l_ap_invoices.reporting_entity
);
INSERT INTO jg_zz_vat_trx_gt
(
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_v35
,jg_info_v39
,jg_info_v40
,jg_info_n29
,jg_info_n30
)
VALUES
('R'
,lpad(l_rshimon_imports.imp_doc_num_import,9,'0')
,to_char(l_rshimon_imports.imp_doc_date_import)
,lpad(l_rshimon_imports.reference_group,4,'0')
,l_rshimon_imports.inv_number_import
,lpad(round(abs(l_rshimon_imports.vat_amount_import)),9,'0')
,decode(sign(l_rshimon_imports.taxable_amount_import),'-1','-','+')
,lpad(round(abs(l_rshimon_imports.taxable_amount_import)),10,'0')
,to_char(l_rshimon_imports.inv_date_import,'RRRRMMDD')
,'JEILR835'
,'50Q_RSHIMON_IMPORTS'
,decode(l_rshimon_imports.reporting_entity, p_vat_rep_entity_id, 1,
p_vat_rep_entity_id2, 2,
p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
,l_rshimon_imports.reporting_entity
);
INSERT INTO jg_zz_vat_trx_gt
(
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_v35
,jg_info_v39
,jg_info_v40
,jg_info_n29
,jg_info_n30
)
VALUES
('K'
,'000000000'
,var_last_day_tax_period
,l_petty_cash_summary.reference_group
,l_petty_cash_summary.no_of_invoices
,lpad(round(nvl(l_petty_cash_summary.vat_amount_petty_cash,0)),9,'0')
,decode(sign(l_petty_cash_summary.taxable_amount_petty_cash),'-1','-','+')
,lpad(round(abs(l_petty_cash_summary.taxable_amount_petty_cash)),10,'0')
,l_petty_cash_summary.reference_group
,'JEILR835'
,'60Q_PETTY_CASH_SUMMARY'
,decode(l_petty_cash_summary.reporting_entity, p_vat_rep_entity_id, 1,
p_vat_rep_entity_id2, 2,
p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
,l_petty_cash_summary.reporting_entity
);
INSERT INTO jg_zz_vat_trx_gt
(
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_v35
,jg_info_v39
,jg_info_v40
,jg_info_n29
,jg_info_n30
)
VALUES
(
decode(l_ar_transactions.reporting_code,'VAT-M','M',
'VAT-Y','Y',
'VAT-I','I')
,lpad(l_ar_transactions.taxpayer_id,9,'0')
,l_ar_transactions.trx_date
,l_ar_transactions.reference_group
,l_ar_transactions.trx_number
,lpad(round(abs(l_ar_transactions.vat_amount)),9,'0')
,decode(sign(l_ar_transactions.taxable_amount),'-1','-','+')
,lpad(round(abs(l_ar_transactions.taxable_amount)),10,'0')
,lpad(l_ar_transactions.trx_number,9,'0')
,'JEILR835'
,decode(l_ar_transactions.reporting_code,'VAT-M','92Q_AR_TRANSACTIONS',
'VAT-I','94Q_AR_TRANSACTIONS',
'VAT-Y','96Q_AR_TRANSACTIONS')
,decode(l_ar_transactions.reporting_entity, p_vat_rep_entity_id, 1,
p_vat_rep_entity_id2, 2,
p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
,l_ar_transactions.reporting_entity
);
INSERT INTO jg_zz_vat_trx_gt
(
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_v35
,jg_info_v39
,jg_info_v40
,jg_info_n29
,jg_info_n30
)
VALUES
('S'
,lpad(l_std_trx_registered_cust.taxpayer_id,9,'0')
,l_std_trx_registered_cust.trx_date
,l_std_trx_registered_cust.reference_group
,l_std_trx_registered_cust.trx_number
,lpad(round(abs(l_std_trx_registered_cust.vat_amount)),9,'0')
,decode(sign(l_std_trx_registered_cust.taxable_amount),'-1','-','+')
,lpad(round(abs(l_std_trx_registered_cust.taxable_amount)),10,'0')
,lpad(l_std_trx_registered_cust.trx_number,9,'0')
,'JEILR835'
,'70Q_STD_TRX_REGISTERED_CUST'
,decode(l_std_trx_registered_cust.reporting_entity, p_vat_rep_entity_id, 1,
p_vat_rep_entity_id2, 2,
p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
,l_std_trx_registered_cust.reporting_entity
);
INSERT INTO jg_zz_vat_trx_gt
(
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_v35
,jg_info_v39
,jg_info_v40
,jg_info_n29
,jg_info_n30
)
VALUES
('L'
,'000000000'
,l_std_trx_unknown_cust.trx_date
,l_std_trx_unknown_cust.reference_group
,l_std_trx_unknown_cust.trx_number
,lpad(round(abs(l_std_trx_unknown_cust.vat_amount)),9,'0')
,decode(sign(l_std_trx_unknown_cust.taxable_amount),'-1','-','+')
,lpad(round(abs(l_std_trx_unknown_cust.taxable_amount)),10,'0')
,l_std_trx_unknown_cust.reporting_code||lpad(l_std_trx_unknown_cust.trx_number,9,'0')
,'JEILR835'
,'90Q_STD_TRX_UNKNOWN_CUST'
,decode(l_std_trx_unknown_cust.reporting_entity, p_vat_rep_entity_id, 1,
p_vat_rep_entity_id2, 2,
p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
,l_std_trx_unknown_cust.reporting_entity
);
INSERT INTO jg_zz_vat_trx_gt
(
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_v35
,jg_info_v39
,jg_info_v40
,jg_info_n29
,jg_info_n30
)
VALUES
('L'
,'000000000'
,var_last_day_tax_period
,l_std_trxs_summary.reference_group
,l_std_trxs_summary.no_of_aggre_invoices
,lpad(round(abs(l_std_trxs_summary.sum_of_vat_amt)),9,'0')
,decode(sign(l_std_trxs_summary.sum_of_taxable_amt),'-1','-','+')
,lpad(round(abs(l_std_trxs_summary.sum_of_taxable_amt)),10,'0')
,l_std_trxs_summary.reference_group
,'JEILR835'
,'80Q_STD_TRXS_SUMMARY'
,decode(l_std_trxs_summary.reporting_entity, p_vat_rep_entity_id, 1,
p_vat_rep_entity_id2, 2,
p_vat_rep_entity_id3, 3, p_vat_rep_entity_id4, 4,
p_vat_rep_entity_id5, 5, p_vat_rep_entity_id6, 6,
p_vat_rep_entity_id7, 7, p_vat_rep_entity_id8, 8,
p_vat_rep_entity_id9, 9, p_vat_rep_entity_id10, 10)
,l_std_trxs_summary.reporting_entity
);
INSERT INTO jg_zz_vat_trx_gt
(
jg_info_n1
,jg_info_n2
,jg_info_n3
,jg_info_n4
,jg_info_n5
,jg_info_n6
,jg_info_n7
,jg_info_v39
)
VALUES
(no_of_invs
,no_of_imports
,no_of_pettycash
,no_of_trxs
,no_of_regis_trxs
,no_of_unknown_trxs
,no_of_summary_trxs
,'JEILR835-TOTALS'
);
select ENTITY_IDENTIFIER
into l_ent_name
from jg_zz_vat_rep_entities
where VAT_REPORTING_ENTITY_ID = l_ent_list(i).ent_id;
SELECT SUM(tax_amt_funcl_curr)
INTO invoice_values
FROM jg_zz_vat_trx_details
WHERE trx_id = p_cust_trx_id
AND application_id = 200
AND event_class_code = 'PURCHASE_TRANSACTION'
AND entity_code = 'AP_INVOICES';
SELECT SUM(tax_amt)
INTO invoice_values
FROM jg_zz_vat_trx_details
WHERE trx_id = p_cust_trx_id
AND application_id = 200
AND event_class_code = 'PURCHASE_TRANSACTION'
AND entity_code = 'AP_INVOICES';
SELECT meaning
INTO l_detail_summary
FROM fnd_lookups
WHERE lookup_type = 'JEBE_REPORT_FORMAT'
AND lookup_code = P_REPORT_FORMAT
AND p_report_name = 'JEBEVA06';
SELECT FFV.description
INTO l_location_name
FROM fnd_flex_values_vl FFV
, fnd_flex_value_sets FFVS
WHERE FFV.flex_value_set_id = FFVS.flex_value_set_id
AND FFVS.flex_value_set_name = 'JEPT_TAX_LOCATION'
AND FFV.flex_value = P_LOCATION;
SELECT DISTINCT RPS.period_start_date
INTO l_period_date
FROM JG_ZZ_VAT_REP_STATUS RPS
WHERE RPS.TAX_CALENDAR_PERIOD = P_PERIOD
AND RPS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID;
SELECT NVL(jiy.adjusted_limit_amount, 0)
INTO l_starting_limit
FROM je_it_year_ex_limit JIY,
fnd_lookups fl
WHERE fl.lookup_type = 'JEIT_MONTH'
AND JIY.month = fl.lookup_code
AND JIY.year = to_char(l_period_date,'RRRR')
AND JIY.month = to_char(l_period_date,'MM')
AND JIY.legal_entity_id = P_LEGAL_ENTITY_ID ;
SELECT DISTINCT RPS.period_start_date
INTO l_period_date
FROM JG_ZZ_VAT_REP_STATUS RPS
WHERE RPS.TAX_CALENDAR_PERIOD = P_PERIOD
AND RPS.VAT_REPORTING_ENTITY_ID = P_VAT_REP_ENTITY_ID;
SELECT NVL(JIY.adjusted_limit_amount, 0)
INTO l_adjustment_limit
FROM je_it_year_ex_limit JIY
WHERE JIY.year = to_char(l_period_date,'RRRR')
AND JIY.month = to_char(l_period_date,'MM')
AND JIY.legal_entity_id = P_LEGAL_ENTITY_ID ;
SELECT
JZVRS.CREDIT_BALANCE_AMT,
JZVRS.FINAL_REPORTING_STATUS_FLAG,
jg_zz_vat_rep_utility.get_last_processed_date(p_vat_rep_entity_id,'ALL','FINAL REPORTING') LAST_PROCESS_DATE
FROM jg_zz_vat_rep_status JZVRS
WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.tax_calendar_period = p_period
AND ROWNUM = 1;
SELECT jg_info_d2 FROM jg_zz_vat_trx_gt WHERE jg_info_v30 = 'H';
SELECT
max(JZVRS.period_end_date)
FROM
jg_zz_vat_rep_status JZVRS
WHERE
JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.period_end_date <
(SELECT max(JZVRS1.period_end_date)
FROM jg_zz_vat_rep_status JZVRS1
WHERE JZVRS1.tax_calendar_period = p_period
AND JZVRS1.vat_reporting_entity_id = p_vat_rep_entity_id
--AND JZVRS1.final_reporting_status_flag = 'S'
)
AND JZVRS.final_reporting_status_flag = 'S'
AND JZVRS.tax_calendar_period <> p_period;
SELECT
JZVRS.CREDIT_BALANCE_AMT
FROM jg_zz_vat_rep_status JZVRS
WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.tax_calendar_period =( SELECT JZVRS1.tax_calendar_period
FROM jg_zz_vat_rep_status JZVRS1
WHERE
JZVRS1.vat_reporting_entity_id = p_vat_rep_entity_id
AND TRUNC(JZVRS1.period_end_date) = pn_last_process_date
AND ROWNUM =1)
AND ROWNUM = 1;
SELECT
JZVRS.FINAL_REPORTING_STATUS_FLAG
FROM jg_zz_vat_rep_status JZVRS
WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.tax_calendar_period = p_period
AND ROWNUM = 1;
JEITPSSR_AMOUNT_TO_PAY_UPDATE function
IF FINAL_FLAG = 'S' THEN
IF AMOUNT_TO_PAY >= 0 THEN
CARRY_OVER := 0;
UPDATE jg_zz_vat_rep_status SET CREDIT_BALANCE_AMT=CARRY_OVER
WHERE vat_reporting_entity_id = p_vat_rep_entity_id
AND tax_calendar_period = p_period;
/****Added below function JEITPSSR_AMOUNT_TO_PAY_UPDATE for bug 7633948***/
FUNCTION JEITPSSR_AMOUNT_TO_PAY_UPDATE(AMOUNT_TO_PAY IN NUMBER)
RETURN BOOLEAN IS
FINAL_FLAG VARCHAR2(1);
SELECT
JZVRS.FINAL_REPORTING_STATUS_FLAG
FROM jg_zz_vat_rep_status JZVRS
WHERE JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.tax_calendar_period = p_period
AND ROWNUM = 1;
fnd_file.put_line(fnd_file.log,'Called JEITPSSR_AMOUNT_TO_PAY_UPDATE function');
fnd_file.put_line(fnd_file.log,'In JEITPSSR_AMOUNT_TO_PAY_UPDATE FINAL_FLAG:'||FINAL_FLAG);
fnd_file.put_line(fnd_file.log,'In JEITPSSR_AMOUNT_TO_PAY_UPDATE TEMP_FLAG:'||TO_CHAR(TEMP_FLAG));
fnd_file.put_line(fnd_file.log,'In JEITPSSR_AMOUNT_TO_PAY_UPDATE AMOUNT_TO_PAY:'||TO_CHAR(AMOUNT_TO_PAY));
fnd_file.put_line(fnd_file.log,'In JEITPSSR_AMOUNT_TO_PAY_UPDATE CARRY_OVER:'||TO_CHAR(CARRY_OVER));
UPDATE jg_zz_vat_rep_status SET CREDIT_BALANCE_AMT=CARRY_OVER
WHERE vat_reporting_entity_id = p_vat_rep_entity_id
AND tax_calendar_period = p_period;
fnd_file.put_line(fnd_file.log,'Error in Procedure jg_zz_summary_all_pkg.JEITPSSR_AMOUNT_TO_PAY_UPDATE ' || SUBSTR(SQLERRM,1,200));
END JEITPSSR_AMOUNT_TO_PAY_UPDATE;