AP Prepayment Tracking Report - Turkey

Description
Categories: BI Publisher, Financials, Procurement
Application: Payables
Source: AP Prepayment Tracking Report - Turkey
Short Name: APPPTR
DB package: AP_PREPAY_TRAK_REP_TURK_PKG
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
Parameter Name SQL text Validation
Vendor Type
 
LOV Oracle
Currency
 
LOV Oracle
Supplier To
 
LOV Oracle
Supplier From
 
LOV Oracle
Prepayment Date To
 
Date
Prepayment Date From
 
Date
Prepayment Status
 
LOV Oracle
Operating Unit
 
LOV Oracle