AP 1099 Invoice Exceptions - draft

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: 1099 Invoice Exceptions Report (XML) - Not Supported: Reserved For Future Use
Short Name: APXTRINE_XML
DB package: AP_APXT7INE_XMLP_PKG
            SELECT    pv.vendor_name vendor_name1,
                            pv.segment1 segment2,
                            i.invoice_num invoice_num1,
                            i.invoice_date invoice_date1,
                            i.invoice_amount invoice_amount1,
                            id.distribution_line_number distribution_line_number1,
                            id.amount amount1,
                            substr(id.income_tax_region,1,10) SUBSTR_ID_INCOME_TAX_REGION_1_
            FROM    AP_invoice_distributions id,
                            AP_Invoices i,
                            PO_Vendors pv,
                            GL_code_combinations gcc,
                            AP_Reporting_Entity_Lines_all rel,                  ap_invoice_payments ip,
                            ap_checks ac,
                            ap_bank_accounts aba
                    WHERE   id.invoice_id = i.invoice_id
                    AND     i.vendor_id = pv.vendor_id
                    AND     pv.federal_reportable_flag = 'Y'          AND     (id.income_tax_region is null
                    OR      id.income_tax_region not in (SELECT region_short_name
                                                         FROM   ap_income_tax_regions
                                                         WHERE  sysdate <
                                                               nvl(inactive_date,sysdate+1)
                                                         AND    sysdate >=
                                                                nvl(active_date,sysdate)))
                    AND     ip.check_id = ac.check_id                                                                      AND     ac.bank_account_id = aba.bank_account_id                                              AND     decode(:P_query_driver, 'INV', ID.dist_code_combination_id,                                  ABA.asset_code_combination_id) = GCC.code_combination_id
                    AND     rel.tax_entity_id = :p_rep_entity_id
                    AND     rel.balancing_segment_value =
                            decode(:p_balancing_segment_column,
                            'SEGMENT1',gcc.segment1,'SEGMENT2',gcc.segment2,
                            'SEGMENT3',gcc.segment3,'SEGMENT4',gcc.segment4,
                            'SEGMENT5',gcc.segment5,'SEGMENT6',gcc.segment6,
                            'SEGMENT7',gcc.segment7,'SEGMENT8',gcc.segment8,
                            'SEGMENT9',gcc.segment9,'SEGMENT10',gcc.segment10,
                            'SEGMENT11',gcc.segment11,'SEGMENT12',gcc.segment12,
                            'SEGMENT13',gcc.segment13,'SEGMENT14',gcc.segment14,
                            'SEGMENT15',gcc.segment15,'SEGMENT16',gcc.segment16,
                            'SEGMENT17',gcc.segment17,'SEGMENT18',gcc.segment18,
                            'SEGMENT19',gcc.segment19,'SEGMENT20',gcc.segment20,
                            'SEGMENT21',gcc.segment21,'SEGMENT22',gcc.segment22,
                            'SEGMENT23',gcc.segment23,'SEGMENT24',gcc.segment24,
                            'SEGMENT25',gcc.segment25,'SEGMENT26',gcc.segment26,
                            'SEGMENT27',gcc.segment27,'SEGMENT28',gcc.segment28,
                            'SEGMENT29',gcc.segment29,'SEGMENT30',gcc.segment30,null)
                    AND     ip.invoice_id = i.invoice_id
                    AND     ip.accounting_date between
                    (:p_start_date) and (:p_end_date)
                   GROUP BY pv.vendor_name,
                            pv.vendor_name,
                            pv.segment1,
                            i.invoice_num,
                            i.invoice_date,
                            i.invoice_amount,
                            id.distribution_line_number,
                            id.amount,
                            id.income_tax_region
                    --ORDER BY  upper(pv.vendor_name), i.invoice_num, i.invoice_date
                    ORDER BY 1, 2, 3, 4, 5, 8, 6, 7
Parameter Name SQL text Validation
Ledger
 
To Payment Date
 
Date
From Payment Date
 
Date
Reporting Entity
 
LOV Oracle