FSQL 13



SQL QUERY FOR TDS IN ORACLE CLOUD FUSION R13

SELECT aia.invoice_num                                                                           inv_num
       ,aila.line_number                                                                         inv_line_number
       ,TO_CHAR(aia.invoice_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN')                  inv_date
       ,TO_CHAR(aila.accounting_date, 'DD-MON-YYYY','NLS_DATE_LANGUAGE = AMERICAN')              inv_acc_date
       ,psv.vendor_name                                                                          vendor
       ,psv.segment1                                                                             vendor_num
       ,pssv.vendor_site_code                                                                    site
       ,psv.organization_type_lookup_code                                                        vendor_org_type
       ,psp.income_tax_id                                                                        pan_num
       ,gl_flexfields_pkg.get_description_sql                                                   
                                        (gcc.chart_of_accounts_id,                              
                                         1,                                                     
                                         gcc.segment1                                           
                                        )                                                        company
       ,gl_flexfields_pkg.get_description_sql                                                   
                                        (gcc.chart_of_accounts_id,                               
                                         2,                                                     
                                         gcc.segment2                                           
                                        )                                                        account
       ,gl_flexfields_pkg.get_description_sql                                                   
                                        (gcc.chart_of_accounts_id,                              
                                         3,                                                     
                                         gcc.segment3                                           
                                        )                                                        branch
       ,gl_flexfields_pkg.get_description_sql                                                   
                                        (gcc.chart_of_accounts_id,                               
                                         4,                                                     
                                         gcc.segment4                                           
                                        )                                                        department
       ,(SELECT zcgb.alphanumeric_value1                                                        
           FROM zx_condition_groups_b zcgb                                                      
          WHERE zcgb.condition_group_id = aila.awt_group_id )                                    tds_rate_name
       ,(SELECT zrb.tax                                                                          
           FROM zx_rates_b zrb                                                                  
          WHERE zrb.tax_rate_code = (SELECT zcgb.alphanumeric_value1                            
                                       FROM zx_condition_groups_b zcgb                          
                                      WHERE zcgb.condition_group_id = aila.awt_group_id )       
                                    )                                                            tax_name
       ,(SELECT zrb.percentage_rate                                                             
           FROM zx_rates_b zrb                                                                  
          WHERE zrb.tax_rate_code = (SELECT zcgb.alphanumeric_value1                            
                                       FROM zx_condition_groups_b zcgb                          
                                      WHERE zcgb.condition_group_id = aila.awt_group_id )       
                                    )                                                            tax_rate
       ,aila.amount                                                                              inv_line_amount
       ,aila.amount * ((SELECT zrb.percentage_rate
                          FROM zx_rates_b zrb
                          WHERE zrb.tax_rate_code = (SELECT zcgb.alphanumeric_value1
                                                       FROM zx_condition_groups_b zcgb
                                                      WHERE zcgb.condition_group_id = aila.awt_group_id
                                                     )
                        ) / 100
                      )                                                                       inv_line_tax_val
       ,aila.amount - (aila.amount * ((SELECT zrb.percentage_rate
                                         FROM zx_rates_b zrb
                                        WHERE zrb.tax_rate_code = (SELECT zcgb.alphanumeric_value1
                                                                     FROM zx_condition_groups_b zcgb
                                                                    WHERE zcgb.condition_group_id = aila.awt_group_id
                                                                  )
                                       ) / 100
                                      )  
                      )                                                                              inv_line_amt_payable
  FROM ap_invoices_all                     aia
       ,poz_suppliers_v                    psv
       ,poz_supplier_sites_v               pssv
       ,poz_suppliers_pii                  psp
       ,ap_invoice_lines_all               aila
       ,gl_code_combinations               gcc
 WHERE aia.vendor_id                     = psv.vendor_id
   AND aia.vendor_site_id                = pssv.vendor_site_id
   AND psv.vendor_id                     = psp.vendor_id
   AND aia.invoice_id                    = aila.invoice_id
   AND aila.line_type_lookup_code        = 'ITEM'
   AND aila.default_dist_ccid            = gcc.code_combination_id
   AND TO_DATE(aila.accounting_date, 'YYYY-MON-DD') BETWEEN NVL(:p_from_acc_date, aila.accounting_date)
                                                        AND NVL(:p_to_acc_date, aila.accounting_date)
 ORDER BY aia.invoice_num
         ,aila.line_number
         ,aia.invoice_date
         ,aila.accounting_date

No comments:

Post a Comment

Subscribe to: Posts (Atom)