AP Prepayment Tracking Report - Turkey

➜ Back to Library
Description
Categories: BI Publisher
Application: Payables
Source: AP Prepayment Tracking Report - Turkey
Short Name: APPPTR
DB package: AP_PREPAY_TRAK_REP_TURK_PKG
Run AP Prepayment Tracking Report - Turkey and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Operating Unit 
 
 LOV Oracle
Prepayment Status 
 
 LOV Oracle
Prepayment Date From 
 
 Date
Prepayment Date To 
 
 Date
Supplier From 
 
 LOV Oracle
Supplier To 
 
 LOV Oracle
Currency 
 
 LOV Oracle
Vendor Type 
 
 LOV Oracle
Download
Related Blitz Reports
CST AP and PO Accrual Reconciliation CAC Invoice Price Variance CST Miscellaneous Accrual Reconciliation AP Advances in Selected Currency AP Prepayment Balance Report - Entered Currency (CZech) PO Headers and Lines 11i JA India - Open Transactions Report for Prior-GST (XML Publisher) AP Invoices and Lines AP Prepayment Balance
Blitz Report™

Blitz Report™ provides multiple benefits: