JE Spanish B2G Electronic Invoices XML File Output

Description
Categories: BI Publisher
Application: European Localizations
Source: Spanish B2G Electronic Invoices XML File Output
Short Name: JEESEIFO_XMLP
DB package: JE_ES_B2G_ELECTRONIC_FILE

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

SELECT trx_id,
                           trx_type,       
                           trx_cur_code,
                           exchange_rate,
                           exchange_date,
                           trx_date,
                           trx_number,
                           trx_doc_type,
                           trx_class,
                           trx_lang,
                           func_cur_code ,
                           batch_name,
                           seq_name,
                           period_set
                    FROM  (SELECT trx.customer_trx_id trx_id,
                                  trx_types.type trx_type,
                                  trx.invoice_currency_code  trx_cur_code,
                                  decode(sob.currency_code,'EUR', trx.exchange_rate,rates.conversion_rate)  exchange_rate,
                                  to_char(trx.exchange_date,'YYYY-MM-DD')  exchange_date,
                                  to_char(trx.trx_date,'YYYY-MM-DD')      trx_date,
                                  trx.trx_number     trx_number,
                                  NVL(trx.global_attribute17,'FC') trx_doc_type,                     -- trx_doc_type
                                  :p_gen_option||trx.global_attribute1 trx_class,         -- trx_type (original,corrective,consolidated)
                                  LOWER(NVL(trx.global_attribute18,'ES'))   trx_lang ,                                -- language                                      
                                  sob.currency_code  func_cur_code,
                                  batch.name batch_name,
                                  seqs.name seq_name,
                                  sob.period_set_name period_set
                            FROM  ra_customer_trx  trx,
                                  ra_cust_trx_types trx_types,
                                  gl_sets_of_books sob,
                                  gl_daily_rates rates,
                                  ra_batches batch,
                                  fnd_document_sequences seqs 
                           WHERE  trx.legal_entity_id     = :P_LEGAL_ENTITY_ID
                             AND  trx.bill_to_customer_id = :P_CUST_ACCOUNT_ID
                             AND  trx.bill_to_site_use_id = :P_BILL_TO_SITE_USE_ID
                             AND  trx.cust_trx_type_id    = trx_types.cust_trx_type_id
                             AND  trx_types.type in ('INV','CM')
                             AND  trx.org_id = trx_types.org_id
                             AND  trx.complete_flag       = 'Y'
                             AND  trx.customer_trx_id     =  NVL(:p_trx_id,trx.customer_trx_id)
                             AND  trx.invoice_currency_code = :P_TRX_CUR
                             AND  trx.trx_date between :P_TRX_DATE_FROM  and :P_TRX_DATE_TO
                             AND  ((:p_gen_option='O' and (trx.global_attribute16 is null OR trx.global_attribute16 = 'TBR'))
                                    OR (:p_gen_option = 'C' and trx.global_attribute16 = 'ACC'))
                             AND  ((trx_types.type    ='INV') 
                                    OR (trx_types.type='CM' AND (trx.previous_customer_trx_id is not null 
                                                                 OR (( (select count(distinct ara.applied_customer_trx_id) 
                                                                          from ar_Receivable_applications ara
                                                                         where ara.customer_trx_id = trx.customer_trx_id) = 1 )
                                                                           AND not exists (select 1 from ar_payment_schedules 
                                                                                            where customer_trx_id = trx.customer_trx_id
                                                                                              and amount_due_remaining <> 0)))
                                                             AND trx.global_attribute19 is not null and trx.global_attribute20 is not null))
                             AND  exists (select 1 
                                            from ra_customer_trx_lines_all trx_lines
                                           where trx_lines.customer_trx_id = trx.customer_trx_id    
                                             and trx_lines.line_type = 'TAX' )
                             AND  trx.set_of_books_id = sob.set_of_books_id
                             AND  rates.from_currency(+) = trx.invoice_currency_code
                             AND  rates.to_currency(+) ='EUR'
                             AND  rates.conversion_type(+) = trx.exchange_rate_type
                             AND  rates.conversion_date(+) = trx.trx_date
                             AND  trx.batch_id = batch.batch_id(+)
                             AND  trx.doc_sequence_id = seqs.doc_sequence_id(+)
                        ORDER BY  trx.trx_number)
                WHERE ROWNUM <= :p_max_no_of_invs