AP Unaccounted Transactions

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: Unaccounted Transactions Report (XML)
Short Name: APXUATR_XMLP
DB package: ap_period_close_pkg
        SELECT /*+ NO_MERGE (ai) */ UPPER(decode(asp.SORT_BY_ALTERNATE_FIELD
                   , 'Y', nvl(ap.vendor_name_alt,nvl(ap.vendor_name, hp.party_name))
                   , nvl(ap.vendor_name, hp.party_name)
                   )
             )                        upp_vend_name_inv,        
               ap.segment1                     vendor_num_inv,
               ai.invoice_num                invoice_num,
               nvl(to_char(ai.doc_sequence_value),ai.voucher_num) voucher_num,
               ai.invoice_id                 invoice_id,
               to_char(ai.invoice_date,'DD-Mon-RRRR')  invoice_date,
               ai.invoice_currency_code      invoice_curr_code,
               ph.segment1                    po_number,
               nvl(ai.invoice_amount,0) invoice_amt,
               decode(ahc.postable_flag,'N',ah.hold_lookup_code, '') hold_code,
               alc.displayed_field            exception_inv,
               nvl(ap.vendor_name , hp.party_name)              vendor_name_inv,
               fc.precision                    precision
        FROM    (   select distinct
                        invoice_id
                       ,invoice_num
                       ,invoice_date
                       ,invoice_currency_code
                       ,invoice_amount
                       ,doc_sequence_value
                       ,voucher_num
                       ,org_id
					   ,party_id                     
		               ,vendor_id
                    from ap_period_close_excps_gt apexp             
                    where apexp.source_type in (    'LINES_WITHOUT_DISTS'
                                               ,    'UNACCT_DISTS', 'UNACCT_PREPAY_HIST'
                                               ) 
                    and   (NVL(:G_SWEEP_NOW,'N') = 'N' OR (:G_SWEEP_NOW = 'Y' AND apexp.process_status_flag = 'Y'))
                ) ai
                ,ap_holds_all ah,
                ap_hold_codes ahc,
                ap_lookup_codes alc,
                po_line_locations_all pll,
                po_headers_all ph,
                ap_suppliers ap,                                
				hz_parties hp ,                                    
                ap_system_parameters_all asp,
                fnd_currencies fc
        WHERE    ai.vendor_id = ap.vendor_id(+)                   
          AND   ai.party_id = hp.party_id                        
          AND   ai.invoice_id = ah.invoice_id
          AND   pll.po_header_id = ph.po_header_id(+)
          AND   ah.line_location_id = pll.line_location_id(+)
          AND   ah.hold_lookup_code = ahc.hold_lookup_code
          AND  (ah.release_lookup_code is null and
                      ahc.postable_flag = 'N'  and
                      ah.hold_lookup_code is not null and
                      alc.lookup_type = 'HOLD CODE' and
                      alc.lookup_code = ah.hold_lookup_code)
          AND  ai.org_id = asp.org_id
          AND  asp.base_currency_code = fc.currency_code
        UNION
        SELECT /*+ NO_MERGE (ai) */ upper(decode(asp.SORT_BY_ALTERNATE_FIELD, 'Y',
                     nvl(ap.vendor_name_alt, nvl(ap.vendor_name, hp.party_name)),
                     nvl(ap.vendor_name, hp.party_name)))  upp_vend_name_inv,       
               ap.segment1                             vendor_num_inv,
               ai.invoice_num                         invoice_num,
               nvl(to_char(ai.doc_sequence_value),ai.voucher_num)  voucher_num,
               ai.invoice_id                          invoice_id,
               to_char(ai.invoice_date,'DD-Mon-RRRR') invoice_date,
               ai.invoice_currency_code               invoice_curr_code,
               null                                   po_number,
               nvl(ai.invoice_amount,0)               invoice_amt,
               null                                   hold_code,
               alc.displayed_field                    exception_inv,
               nvl(ap.vendor_name , hp.party_name)    vendor_name_inv,
               fc.precision                           precision
          FROM
               (select distinct
                                invoice_id
                                ,invoice_num
                                ,invoice_date
                                ,invoice_currency_code
                                ,invoice_amount
                                ,org_id
                                ,party_id                      
				                ,vendor_id
                                ,voucher_num
                                ,doc_sequence_value
                        from ap_period_close_excps_gt apexp
                        where apexp.source_type in ('LINES_WITHOUT_DISTS'
                                                   ,'UNACCT_DISTS', 'UNACCT_PREPAY_HIST'
                                                   )
                        and   (NVL(:G_SWEEP_NOW,'N') = 'N' OR (:G_SWEEP_NOW = 'Y' AND process_status_flag = 'Y'))
               ) ai
               , ap_suppliers ap                      
			   ,hz_parties hp                         
               , ap_system_parameters_all asp
               , fnd_currencies fc
               , ap_lookup_codes alc
       WHERE    ai.vendor_id = ap.vendor_id(+)            
          AND   ai.party_id = hp.party_id                 
          AND   (not exists (select  1
                             from    ap_holds_all ah1,
                                     ap_hold_codes ahc1
                             where   ah1.release_lookup_code is null
                             and     ahc1.postable_flag = 'N'
                             and     ahc1.hold_lookup_code = ah1.hold_lookup_code
                             and     ah1.invoice_id = AI.invoice_id))
          AND   ai.org_id = asp.org_id
          AND   asp.base_currency_code = fc.currency_code
          AND   alc.lookup_type = 'NLS TRANSLATION'
          and   alc.lookup_code =  ap_utilities_pkg.get_invoice_status(ai.invoice_id, 'APXUATR')
        ORDER BY exception_inv, upp_vend_name_inv, invoice_date, invoice_num
Parameter Name SQL text Validation
Trace Switch
 
LOV Oracle
Debug Switch
 
LOV Oracle
Sweep Now
 
LOV Oracle
Ledger Id
 
Number
Period Name
 
LOV Oracle
To Accounting Date
 
Date
From Accounting Date
 
Date
Reporting Context
 
LOV Oracle
Reporting Level
 
LOV Oracle