JE Portuguese Receivables Taxpayer ID Exception - draft

Description
Categories: BI Publisher
Application: European Localizations
Source: Portuguese Receivables Taxpayer ID Exception Report (XML) - Not Supported: Reserved For Future Use
Short Name: JEPTARER_XML
DB package: JE_JEPTARER_XMLP_PKG
select distinct hp.PARTY_NAME , hp.PARTY_NUMBER
from    ra_cust_trx_line_gl_dist_all d,
        ra_customer_trx_lines_all l,
        ra_customer_trx_all t,
        hz_parties hp,  
        hz_cust_accounts_all hca,        
        ra_cust_trx_types_all rtt,
        zx_party_tax_profile ptp,
        zx_rates_b tax 
where (d.gl_posted_date is not null OR rtt.post_to_gl = 'N')
and upper(rtt.type) in ('INV','CM','DM')
and hca.party_id = hp.party_id
and t.complete_flag = 'Y'
and d.customer_trx_line_id = l.customer_trx_line_id
and l.customer_trx_id = t.customer_trx_id
and t.bill_to_customer_id = hca.cust_account_id
and rtt.cust_trx_type_id = t.cust_trx_type_id
and nvl(upper(substr(tax.tax_rate_code,1,2)),'EX') not in  ('ND','EX') 
and l.vat_tax_id = tax.tax_rate_id (+)
and ptp.PARTY_TAX_PROFILE_ID = tax.content_owner_id
and ptp.party_type_code = 'OU'
and ptp.party_id = t.org_id
and     t.legal_entity_id = :P_LEGAL_ENTITY_ID
and     t.set_of_books_id = nvl(:P_SET_OF_BOOKS_ID,t.set_of_books_id)
and    (:P_BALANCING_SEGMENT IS NULL
           OR
          (:P_BALANCING_SEGMENT IS NOT NULL
            AND EXISTS (SELECT 1 FROM GL_CODE_COMBINATIONS 
                                 WHERE code_combination_id = d.code_combination_id
                                 AND chart_of_accounts_id = :P_CHART_OF_ACCT
                                 AND &P_BAL_SEG_NAME)))
and not exists (select 'X'
                        from ra_customer_trx_lines tl,
                             zx_party_tax_profile ptp,
                             zx_rates_b tax1              
                         where tl.vat_tax_id = tax1.tax_rate_id (+)
                         and ptp.PARTY_TAX_PROFILE_ID = tax1.content_owner_id
                         and ptp.party_type_code = 'OU'
                         and ptp.party_id = tl.org_id
                         and tl.line_type = 'TAX'
                         and nvl(upper(substr(tax1.tax_rate_code,1,2)),'EX') in  ('ND','EX')
                         and l.customer_trx_line_id = tl.link_to_cust_trx_line_id
                         and tl.customer_trx_id = l.customer_trx_id
                         and not exists (select 'X'
                                                 from ra_customer_trx_lines tl1,
                                                      zx_party_tax_profile ptp,
                                                      zx_rates_b tax2          
                                                 where tl1.vat_tax_id = tax2.tax_rate_id (+)
                                                 and ptp.PARTY_TAX_PROFILE_ID = tax2.content_owner_id
                                                 and ptp.party_type_code = 'OU'
                                                 and ptp.party_id = tl1.org_id
                                                 and tl1.line_type = 'TAX'  
                                                 and nvl(upper(substr(tax2.tax_rate_code,1,2)) ,'EX') not in ('ND','EX')
                                                 and l.customer_trx_line_id = tl1.link_to_cust_trx_line_id
                                                 and tl1.customer_trx_id = l.customer_trx_id)) 
and hp.jgzz_fiscal_code is null
group by hp.party_name, hp.party_number         
order by hp.party_name, hp.party_number
Parameter Name SQL text Validation
FND_NUMBER
 
Number
Chart of Account
 
LOV Oracle
Balancing Segment
 
LOV Oracle
Ledger
 
LOV Oracle
Legal Entity
 
LOV Oracle