AP Prepayment Tracking Report - Turkey

SELECT 	HOU.name                    					ou_name
		,POV.vendor_name                      			supplier
		,AI.invoice_num                      			pre_inv_num
        ,AID.invoice_line_number||'/'||AID.distribution_line_number line_num
        ,AI.payment_status_flag              			paid_status
		,sum(AID.amount)                     			amount
        ,DECODE ( SUM ( AID.prepay_amount_remaining ), NULL, SUM ( AID.amount )
				, SUM ( AID.prepay_amount_remaining ))	rem_amt
        ,sum(AID2.amount)*-1                 			closed_amt
        ,AI2.invoice_num                     			std_inv_num
		,AP_PREPAY_TRAK_REP_TURK_PKG.date_close(AI.invoice_id,AID.invoice_line_number
				,sum(AID.prepay_amount_remaining)) 		date_closed
FROM    ap_invoices               AI
        ,ap_invoices              AI2
        ,ap_invoice_lines         AIL
        ,ap_invoice_distributions AID
        ,ap_invoice_distributions AID2
		,ap_lookup_codes          ALC
        ,po_vendors               POV
		,hr_operating_units       HOU
WHERE   AI.invoice_id = AIL.invoice_id
AND 	AIL.invoice_id = AID.invoice_id
AND 	HOU.organization_id = AI.org_id
&c_lex_prepay_status
AND     ALC.lookup_type='PREPAY STATUS'
AND     ALC.LOOKUP_CODE = ap_invoices_pkg.get_approval_status
                    ( ai.invoice_id
                    , ai.invoice_amount
                    , ai.payment_status_flag
                    , ai.invoice_type_lookup_code
                    )
AND 	AIL.line_number = AID.invoice_line_number
AND 	AI.vendor_id = POV.vendor_id
AND 	AID.invoice_distribution_id = AID2.prepay_distribution_id
AND 	AID2.invoice_id = AI2.invoice_id
AND 	AI.invoice_type_lookup_code = 'PREPAYMENT'
AND 	AID.line_type_lookup_code='ITEM'
AND     NVL(AID2.REVERSAL_FLAG,'N')= 'N'   -- bug 12763714
&c_lex_invoice_date
&c_lex_supplier
&c_lex_currency
&c_lex_vendor_type
&gc_org_where
GROUP BY HOU.name
		 ,POV.vendor_name
         ,AI.invoice_num
         ,AID.invoice_line_number||'/'||AID.distribution_line_number
         ,AI2.invoice_num
		 ,AI.payment_status_flag
		 ,AID.invoice_line_number
		 ,AI.invoice_id
UNION ALL
SELECT 	HOU.name                    			ou_name
		,POV.vendor_name                      	supplier
		,AI.invoice_num                      	pre_inv_num
        ,AID.invoice_line_number||'/'||aid.distribution_line_number line_num
        ,AI.payment_status_flag              	paid_status
		,sum(AID.amount)                     	amount
        ,DECODE ( SUM ( AID.prepay_amount_remaining )
            , NULL, SUM ( AID.amount )
			, SUM ( AID.prepay_amount_remaining )
            )    								rem_amt
        ,NULL                                	closed_amt
        ,NULL                                	std_inv_num
		,AP_PREPAY_TRAK_REP_TURK_PKG.Date_close(AI.invoice_id,AID.invoice_line_number
			,sum(AID.prepay_amount_remaining)) date_closed
FROM 	ap_invoices               AI
        ,ap_invoice_lines         AIL
        ,ap_invoice_distributions AID
        ,po_vendors               POV
		,ap_lookup_codes          ALC
		,hr_operating_units       HOU
WHERE 	AI.invoice_id = AIL.invoice_id
AND 	AIL.invoice_id = AID.invoice_id
AND 	HOU.organization_id = AI.org_id
AND 	AIL.line_number = AID.invoice_line_number
&c_lex_prepay_status
AND     ALC.lookup_type='PREPAY STATUS'
AND     ALC.LOOKUP_CODE = ap_invoices_pkg.get_approval_status
                    ( ai.invoice_id
                    , ai.invoice_amount
                    , ai.payment_status_flag
                    , ai.invoice_type_lookup_code
                    )
AND 	AI.vendor_id = POV.vendor_id
AND 	AID.line_type_lookup_code='ITEM'
&c_lex_invoice_date
&c_lex_supplier
&c_lex_currency
&c_lex_vendor_type
&gc_org_where
AND AID.invoice_distribution_id NOT IN (SELECT AID.prepay_distribution_id
                                        FROM   ap_invoice_distributions AID
                                        WHERE  AID.prepay_distribution_id IS NOT NULL
                                        AND NVL(AID.REVERSAL_FLAG,'N')='N'  -- bug 12763714
                                        )
AND AI.invoice_type_lookup_code = 'PREPAYMENT'
GROUP BY  HOU.name
		 ,POV.vendor_name
         ,AI.invoice_num
         ,AID.invoice_line_number||'/'||AID.distribution_line_number
		 ,AI.payment_status_flag
		 ,AID.invoice_line_number
		 ,AI.invoice_id
ORDER BY 1,2,3,9