AP Prepayment Tracking Report - Turkey
Description
Categories: BI Publisher
Application: Payables
Source: AP Prepayment Tracking Report - Turkey
Short Name: APPPTR
DB package: AP_PREPAY_TRAK_REP_TURK_PKG
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 |