AP Unaccounted Transactions

        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