[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Brathod, Updated the cursor to remove usage of hz_cust_account_sites by removing outer join.
Directly using jg_zz_vat_trx_details.billing_tp_site_id to joing with hps.party_site_id
*/
CURSOR c_jgbevat IS
SELECT JZVTD.doc_seq_value BE_DOC_SEQ_VALUE
,SUBSTR(JZVTD.billing_tp_name,1,25) CUSTOMER_NAME
,SUBSTR(HZL.address1,1,18) ADDRESS1
,SUBSTR(JZVTD.billing_tp_number,1,24) CUSTOMER_NUMBER
,SUBSTR(HZL.postal_code,1,4) POSTAL_CODE
,SUBSTR(HZL.city,1,22) CITY
,JZVTD.trx_class_mng CLASS
,JZVTD.trx_number DOC_NUMBER
,JZVTD.trx_type_mng INVOICE_TYPE
,JZVTD.trx_date DOCUMENT_DATE
,JZVTD.gl_date GL_DATE
,JZVTD.billing_tp_site_tax_reg_num TAX_REFERENCE
,ROUND(NVL(JZVTD.taxable_amt_funcl_curr, taxable_amt)
+NVL(JZVTD.tax_amt_funcl_curr, tax_amt), l_precision) TOTAL_AMOUNT
,ROUND(NVL(JZVTD.taxable_amt_funcl_curr, taxable_amt), l_precision) TAXABLE_AMOUNT
,ROUND(NVL(JZVTD.tax_amt_funcl_curr, tax_amt), l_precision) TAX_AMOUNT
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_rep_status JZVRS
,hz_cust_acct_sites_all HZCAS
,hz_party_sites HPS
,hz_locations HZL
,ra_customer_trx_all RCTA
,ra_cust_trx_types RTT
WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.source = 'AR'
AND JZVRS.tax_calendar_year = p_year
AND JZVTD.billing_tp_address_id = HZCAS.cust_acct_site_id
AND HZCAS.party_site_id = HPS.party_site_id (+)
AND HPS.location_id = HZL.location_id (+)
AND SUBSTR(JZVTD.billing_tp_site_tax_reg_num,1,2) = 'BE'
AND JZVTD.trx_id = RCTA.customer_trx_id
AND (
p_customer_name_from IS NULL
OR
( JZVTD.billing_tp_name BETWEEN
p_customer_name_from AND NVL(p_customer_name_to,JZVTD.billing_tp_name)
)
)
AND RTT.cust_trx_type_id = JZVTD.trx_type_id
AND RTT.type IN ('INV','CM','DM')
AND NVL(UPPER(RCTA.interface_header_context),'X') <> 'CONTRA'
;
SELECT JZVTD.doc_seq_value CZ_DOC_SEQ_VALUE
,JZVTD.functional_currency_code FUNCTIONAL_CURRENCY_CODE
,JZVTD.TAX_RATE_CODE_VAT_TRX_TYPE_MNG VAT_BOX
,JZVTD.TAX_RATE_VAT_TRX_TYPE_DESC VAT_BOX_DESC
,JZVTD.trx_description TRANSACTION_DESC
,JZVTD.trx_number DOC_NUMBER
,JZVTD.tax_invoice_date TAX_DATE
,JZVTD.accounting_date GL_DATE
,NVL(JZVTD.taxable_amt_funcl_curr,taxable_amt) TAXABLE_AMOUNT
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_rep_status JZVRS
,ra_customer_trx_all RCTA
WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.tax_calendar_period = p_period
AND JZVTD.trx_id = RCTA.customer_trx_id
AND JZVRS.source = 'AR'
AND JZVTD.trx_line_class IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')
-- bug 8616974 - start
--AND (p_tax_type is null or JZVTD.tax_type_code = p_tax_type)
AND (p_tax_type is null or JZVTD.reporting_code = p_tax_type)
-- bug 8616974 - end
AND NVL(UPPER(RCTA.interface_header_context),'X') <> 'CONTRA'
;
SELECT JZVTD.doc_seq_value AR_DOC_SEQ_VALUE
,SUBSTR(JZVTD.billing_tp_name,1,25) CUSTOMER_NAME
,SUBSTR(HZ.address1,1,18) ADDRESS1
,SUBSTR(JZVTD.billing_tp_number,1,24) CUSTOMER_NUMBER
,SUBSTR(HZ.postal_code,1,4) POSTAL_CODE
,SUBSTR(HZ.city,1,22) CITY
,JZVTD.trx_class_mng CLASS
,JZVTD.trx_number DOC_NUMBER
,JZVTD.trx_type_mng INVOICE_TYPE
,JZVTD.billing_tp_site_tax_reg_num TAX_REFERENCE
,JZVTD.functional_currency_code FUNCTIONAL_CURRENCY_CODE
,JZVTD.TAX_RATE_CODE_VAT_TRX_TYPE_MNG VAT_BOX
,JZVTD.TAX_RATE_VAT_TRX_TYPE_DESC VAT_BOX_DESC
,JZVTD.trx_description TRANSACTION_DESC
,JZVTD.trx_date DOCUMENT_DATE
,JZVTD.accounting_date GL_DATE
,JZVTD.tax_invoice_date TAX_DATE
,(NVL(JZVTD.taxable_amt_funcl_curr, taxable_amt)
+NVL(JZVTD.tax_amt_funcl_curr, tax_amt)) TOTAL_AMOUNT
,NVL(JZVTD.taxable_amt_funcl_curr, taxable_amt) TAXABLE_AMOUNT
,NVL(JZVTD.tax_amt_funcl_curr, tax_amt) TAX_AMOUNT
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_rep_status JZVRS
,hz_locations HZ
,hz_party_sites HPS
,ra_customer_trx_all RCTA
WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
AND JZVRS.vat_reporting_entity_id = p_vat_rep_entity_id
AND JZVRS.source = 'AR'
AND JZVRS.tax_calendar_period = p_period
AND JZVTD.billing_tp_site_id = HPS.party_site_id (+)
AND HZ.location_id (+) = HPS.location_id
AND JZVTD.trx_id = RCTA.customer_trx_id
AND JZVTD.trx_line_class IN ('INVOICE','CREDIT_MEMO','DEBIT_MEMO')
AND NVL(UPPER(RCTA.interface_header_context),'X') <> 'CONTRA'
;
SELECT COUNT(*)
FROM jg_zz_vat_trx_gt;
SELECT precision
INTO l_precision
FROM fnd_currencies_vl
WHERE currency_code = lc_curr_code;
fnd_file.put_line(fnd_file.log,'Insert JEBEVA17 Annual VAT Audit Report Info');
INSERT INTO jg_zz_vat_trx_gt
(jg_info_v1 -- BE_DOC_SEQ_VALUE
,jg_info_v2 -- CUSTOMER_NAME
,jg_info_v3 -- ADDRESS1
,jg_info_v4 -- CUSTOMER_NUMBER
,jg_info_v5 -- POSTAL_CODE
,jg_info_v6 -- CITY
,jg_info_v7 -- CLASS
,jg_info_v8 -- DOC_NUMBER
,jg_info_v9 -- INVOICE_TYPE
,jg_info_d1 -- DOCUMENT_DATE
,jg_info_d2 -- GL_DATE
,jg_info_v10 -- TAX_REFERENCE
,jg_info_n1 -- TOTAL_AMOUNT
,jg_info_n2 -- TAXABLE_AMOUNT
,jg_info_n3 -- TAX_AMOUNT
) VALUES
(r_jgbevat.be_doc_seq_value
,r_jgbevat.customer_name
,r_jgbevat.address1
,r_jgbevat.customer_number
,r_jgbevat.postal_code
,r_jgbevat.city
,r_jgbevat.class
,r_jgbevat.doc_number
,r_jgbevat.invoice_type
,r_jgbevat.document_date
,r_jgbevat.gl_date
,r_jgbevat.tax_reference
,r_jgbevat.total_amount
,r_jgbevat.taxable_amount
,r_jgbevat.tax_amount
);
if gv_debug then fnd_file.put_line(fnd_file.log,'Insert JECZAREX Czech Export Tax Report Info'); end if;
INSERT INTO jg_zz_vat_trx_gt
(jg_info_v1 -- CZ_DOC_SEQ_VALUE
,jg_info_v2 -- FUNCTIONAL_CURRENCY_CODE
,jg_info_v3 -- VAT_BOX
,jg_info_v4 -- VAT_BOX_DESC
,jg_info_v5 -- TRANSACTION_DESC
,jg_info_v8 -- DOC_NUMBER
,jg_info_d1 -- TAX_DATE
,jg_info_d2 -- GL_DATE
,jg_info_n2 -- TAXABLE_AMOUNT
) VALUES
(r_jgczvat.cz_doc_seq_value
,r_jgczvat.functional_currency_code
,r_jgczvat.vat_box
,r_jgczvat.vat_box_desc
,r_jgczvat.transaction_desc
,r_jgczvat.doc_number
,r_jgczvat.tax_date
,r_jgczvat.gl_date
,r_jgczvat.taxable_amount
);
if gv_debug then fnd_file.put_line(fnd_file.log,'Insert AUDIT-AR Info'); end if;
INSERT INTO jg_zz_vat_trx_gt
(jg_info_v1 -- AR_DOC_SEQ_VALUE
,jg_info_v2 -- CUSTOMER_NAME
,jg_info_v3 -- ADDRESS1
,jg_info_v4 -- CUSTOMER_NUMBER
,jg_info_v5 -- POSTAL_CODE
,jg_info_v6 -- CITY
,jg_info_v7 -- CLASS
,jg_info_v8 -- DOC_NUMBER
,jg_info_v9 -- INVOICE_TYPE
,jg_info_v10 -- TAX_REFERENCE
,jg_info_v11 -- FUNCTIONAL_CURRENCY_CODE
,jg_info_v12 -- VAT_BOX
,jg_info_v13 -- VAT_BOX_DESC
,jg_info_v14 -- TRANSACTION_DESC
,jg_info_d1 -- DOCUMENT_DATE
,jg_info_d2 -- GL_DATE
,jg_info_d3 -- TAX_DATE
,jg_info_n1 -- TOTAL_AMOUNT
,jg_info_n2 -- TAXABLE_AMOUNT
,jg_info_n3 -- TAX_AMOUNT
) VALUES
(r_jgzzaudar.ar_doc_seq_value
,r_jgzzaudar.customer_name
,r_jgzzaudar.address1
,r_jgzzaudar.customer_number
,r_jgzzaudar.postal_code
,r_jgzzaudar.city
,r_jgzzaudar.class
,r_jgzzaudar.doc_number
,r_jgzzaudar.invoice_type
,r_jgzzaudar.tax_reference
,r_jgzzaudar.functional_currency_code
,r_jgzzaudar.vat_box
,r_jgzzaudar.vat_box_desc
,r_jgzzaudar.transaction_desc
,r_jgzzaudar.document_date
,r_jgzzaudar.gl_date
,r_jgzzaudar.tax_date
,r_jgzzaudar.total_amount
,r_jgzzaudar.taxable_amount
,r_jgzzaudar.tax_amount
);
if gv_debug then fnd_file.put_line(fnd_file.log,'Calling common pack Inserting Header Info'); end if;
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_v15 -- reporting mode
,jg_info_v30 -- Header record indicator
,jg_info_d1 -- start_date
,jg_info_d2 -- end_date
)
VALUES (lc_curr_code -- curr_code
,lc_company_name -- lc_rep_entity_name -- entity_name
,ln_taxpayer_id -- ln_taxpayer_id -- taxpayer_id
,lc_company_name -- company_name
,lc_tax_registration -- registration_number
,lc_country -- country
,lc_address1 -- address1
,lc_address2 -- address2
,lc_address3 -- address3
,lc_address4 -- address4
,lc_city -- city
,lc_postal_code -- postal_code
,lc_contact -- contact
,lc_phone_number -- phone_number
,lc_status -- reporting mode
,'H' -- Header record indicator
,ld_period_start_date -- start_date
,ld_period_end_date -- end_date
);