[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
MIN(JZVTD.billing_trading_partner_id ) CUSTOMER_ID
,MIN(JZVTD.billing_tp_name) CUSTOMER_NAME
,MIN(HZL.address1) STREET_NAME
,MIN(HZL.postal_code) POSTAL_CODE
,MIN(HZL.city) TOWN
,JZVTD.billing_tp_site_tax_reg_num VAT_NUMBER
,DECODE(P_REPORT_NAME,
'JEBEVA24',SUM(ROUND(NVL(JZVTD.taxable_amt_funcl_curr,0),l_precision)*l_curr_factor)
,SUM(ROUND(NVL(JZVTD.taxable_amt_funcl_curr,0),l_precision))) AMOUNT
,DECODE(P_REPORT_NAME,
'JEBEVA24',SUM(ROUND(NVL(JZVTD.tax_amt_funcl_curr,0),l_precision)*l_curr_factor)
,SUM(ROUND(NVL(JZVTD.tax_amt_funcl_curr,0),l_precision))) TAX_AMOUNT
FROM jg_zz_vat_trx_details JZVTD
,jg_zz_vat_rep_status JZVRS
,ra_cust_trx_types RTT
,ra_customer_trx RCT
,hz_cust_acct_sites HZCAS
,hz_party_sites HPS
,hz_locations HZL
WHERE JZVRS.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JZVTD.reporting_status_id = JZVRS.reporting_status_id
AND RTT.cust_trx_type_id = JZVTD.trx_type_id
AND RTT.type IN ('INV','CM','DM')
AND JZVRS.tax_calendar_year = P_PERIOD
AND JZVRS.source = 'AR'
--AND JZVTD.billing_tp_address_id = HZL.location_id
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 RCT.customer_trx_id = JZVTD.trx_id
AND JZVTD.application_id = 222
AND JZVTD.entity_code = 'TRANSACTIONS'
AND NVL(RCT.interface_header_context,'X') <> 'Contra'
GROUP BY JZVTD.billing_tp_site_tax_reg_num
HAVING SUM(ROUND(NVL(JZVTD.taxable_amt_funcl_curr,0),l_precision)) >= P_MIN_AMOUNT
ORDER BY JZVTD.billing_tp_site_tax_reg_num;
SELECT activity_code
INTO l_activity_code
FROM xle_entity_profiles
WHERE legal_entity_id = l_legal_entity_id;
SELECT hp.primary_phone_number PHONE_NUMBER
,hp.email_address EMAIL_ADDRESS
INTO l_phone_number
,l_email_address
FROM hz_parties hp,
(SELECT subject_id
FROM hz_relationships
WHERE object_id = (
SELECT party_id
FROM xle_firstparty_information_v
WHERE legal_entity_id = l_legal_entity_id
)
AND relationship_code = 'CONTACT_OF'
AND directional_flag = 'F'
) q1
WHERE hp.party_id = q1.subject_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_v29
,jg_info_v30
)
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_email_address
,'H'
);
select precision
into l_precision
from fnd_currencies_vl
where currency_code = l_func_curr;
INSERT INTO jg_zz_vat_trx_gt
( jg_info_n1
, jg_info_n2
, jg_info_v1
, jg_info_v2
, jg_info_v3
, jg_info_v4
, jg_info_v5
, jg_info_n3
, jg_info_n4
, jg_info_v6
)
VALUES
(cnt
, t_customer_id
, t_customer_name
, t_street
, t_postal_code
, t_town
, t_VAT_number
, t_loop_amount
, t_loop_tax
, l_func_curr);
INSERT INTO jg_zz_vat_trx_gt
( jg_info_n1
, jg_info_n2
, jg_info_v1
, jg_info_v2
, jg_info_v3
, jg_info_v4
, jg_info_v5
, jg_info_n3
, jg_info_n4
, jg_info_v6
)
VALUES
(cnt
, t_customer_id
, t_customer_name
, t_street
, t_postal_code
, t_town
, t_VAT_number
, t_loop_amount
, t_tax_value
, l_func_curr);
INSERT INTO jg_zz_vat_trx_gt
( jg_info_n1
, jg_info_n2
, jg_info_v1
, jg_info_v2
, jg_info_v3
, jg_info_v4
, jg_info_v5
, jg_info_n3
, jg_info_n4
, jg_info_v6
)
VALUES
(cnt
, t_customer_id
, t_customer_name
, t_street
, t_postal_code
, t_town
, t_VAT_number
, t_value
, t_loop_tax
, l_func_curr);
INSERT INTO jg_zz_vat_trx_gt
( jg_info_n1
, jg_info_n2
, jg_info_v1
, jg_info_v2
, jg_info_v3
, jg_info_v4
, jg_info_v5
, jg_info_n3
, jg_info_n4
, jg_info_v6
)
VALUES
(cnt
, t_customer_id
, t_customer_name
, t_street
, t_postal_code
, t_town
, t_VAT_number
, t_value
, t_tax_value
, l_func_curr);
INSERT INTO jg_zz_vat_trx_gt
( jg_info_n1
, jg_info_n2
, jg_info_v1
, jg_info_v2
, jg_info_v3
, jg_info_v4
, jg_info_v5
, jg_info_n3
, jg_info_n4
, jg_info_v6)
VALUES
(cnt
, t_customer_id
, t_customer_name
, t_street
, t_postal_code
, t_town
, t_VAT_number
, t_amount
, t_tax_amount
, l_func_curr);
/* Format reports totals for column2 then insert trailing record */
IF t_total_amount >= 0 and t_total_tax_amount >= 0 THEN
t_file_totals :=
lpad(t_total_amount,16,'0')||lpad(t_total_tax_amount,16,'0');
INSERT INTO jg_zz_vat_trx_gt
( jg_info_n1
, jg_info_v1
, jg_info_v2
, jg_info_v30
)
VALUES
( 999999
,t_file_totals
,l_tax_registration_num
,'JEBEV24-T');
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_n2
, jg_info_n3
, jg_info_v30
)
VALUES
( 000000
, l_company_name
, l_address_line_1
, l_postal_code
, l_city
, l_tax_registration_num
, l_func_curr
, p_period
, 000
, 'JEBEV24-H');
SELECT count(1)
INTO l_vat_count
FROM jg_zz_vat_trx_gt
WHERE jg_info_v30 IS NULL;
fnd_file.put_line(fnd_file.log,'Number of records inserted into jg_zz_vat_trx_gt: ' || l_vat_count);