[Home] [Help]
178: Reason :
179: Issue is with cursor c_prepayment_applications ,
180: Invoice id for 3p invoice is fetched using the below:
181: select invoice_id
182: from jai_rcv_tp_invoices
183: where vendor_id
184: in (select vendor_id
185: from po_vendors
186: where trim(vendor_type_lookup_code)
1860: and aid.accounting_event_id = xah.event_id
1861: AND ai.invoice_type_lookup_code IN (lv_standard_lookup, lv_debit_lookup,lv_credit_lookup) --lv_credit_lookup is added by Eric Ma for bug#10376849 on 14-Dec-2010
1862: --AND ai.cancelled_date IS NULL --Commented out by Xiao, for POT cancellation, reg bug#12533434
1863: AND ( aid.line_type_lookup_code = jai_constants.misc_line
1864: or exists (select 1 from jai_rcv_tp_invoices jtp where AID.invoice_id = jtp.invoice_id)) /* modified by vumaasha for bug 8965721 */
1865: AND aid.posted_flag = 'Y'
1866: /*bug 7347127 - moved the trunc in following 2 expressions to the RHS, so that
1867: * the indec on accounting_date would be used in the two tables. In some cases,
1868: * this would prevent performance issue in the Service Tax Processor*/
1927:
1928: -- union added by Xiao Lv for Bug#7191302
1929: UNION
1930: select invoice_id
1931: from jai_rcv_tp_invoices
1932: where vendor_id
1933: in (select vendor_id
1934: from po_vendors
1935: where trim(vendor_type_lookup_code)
1968: FROM ap_invoice_distributions_all a, jai_rgm_trx_refs b /* second table is used for join just to take IL records */
1969: WHERE a.invoice_id = cp_invoice_id
1970: /*Added the below or condition for bug#11666653*/
1971: AND (
1972: (a.line_type_lookup_code <> 'PREPAY' And exists (select '1' from jai_rcv_tp_invoices where invoice_id=a.invoice_id))
1973: OR
1974: a.line_type_lookup_code = jai_constants.misc_line -- <> 'PREPAY'
1975: )
1976:
2074: CURSOR c_tp_inv_details (cp_regime_id IN NUMBER , cp_invoice_id IN NUMBER , cp_line_number IN NUMBER,cp_inv_distribution_id in number) IS
2075: SELECT 1 chk , a.tax_id , a.tax_rate , a.tax_amount , NULL ,a.tax_type ,
2076: a.line_number invoice_line_number, /* INVOICE LINES UPTAKE */
2077: NVL(b.mod_cr_percentage,0) recoverable_ptg, NULL
2078: FROM jai_rcv_tp_inv_details a , JAI_CMN_TAXES_ALL b , jai_rcv_tp_invoices c, JAI_RGM_REGISTRATIONS d
2079: WHERE c.invoice_id = cp_invoice_id
2080: AND a.batch_invoice_id = c.batch_invoice_id
2081: AND a.tax_id = b.tax_id
2082: AND a.line_number = cp_line_number /*INVOICE LINES UPTAKE cp_dist_line_number */
2220: * Added union clause.
2221: */
2222: UNION
2223: SELECT jrti.invoice_id
2224: FROM jai_rcv_tp_invoices jrti
2225: WHERE jrti.vendor_id = apc.vendor_id
2226: AND jrti.vendor_site_id = apc.vendor_site_id
2227: AND apc.org_id = p_org_id
2228: UNION --Added this union for bug#8943349 by JMEENA
2234: )/*5694855*/
2235:
2236: AND ( ainvd.line_type_lookup_code = jai_constants.misc_line
2237: /* modified by vumaasha for bug 8965721 */
2238: OR exists (select 1 from jai_rcv_tp_invoices jtp where jtp.invoice_id=ainvd.invoice_id ) )
2239: AND jrtr.source = jai_constants.source_ap
2240: AND jrtr.invoice_id = ainvd.invoice_id
2241: AND apinvp.invoice_id = ainvd.invoice_id --added by csahoo for bug#6436576
2242: AND jrtr.line_id = ainvd.invoice_distribution_id
2456: || For third party invoices , there will be no records in the JAI_AP_MATCH_INV_TAXES table
2457: || For third party invoices , the tax details need to be picked up from the jai_rcv_tp_inv_details table.
2458: || It should be joined to the jai_rcv_tp_.invoices table based on the batch_invoice_id column and
2459: || we arrive at the correct batch_invoice_id based on the invoice_id link between the third party
2460: || invoice and jai_rcv_tp_invoices table.
2461: || Using this link, if the r_tax_dist_dtl is populated, it will take its normal course.
2462: */
2463:
2464: --passed ap_acc_dist.invoice_distribution_id for bug#10085619
4459: AND aid.parent_reversal_id IS NULL
4460: AND nvl(aid.reversal_flag, 'N') = 'N' /*12793930 - Ensure Repository entries are not passed if a line is discarded*/
4461: AND ( aid.line_type_lookup_code = jai_constants.misc_line
4462: OR EXISTS (SELECT 1
4463: FROM jai_rcv_tp_invoices jtp
4464: WHERE AID.invoice_id = jtp.invoice_id))
4465: AND aid.posted_flag = 'Y'
4466: AND xah.accounting_date between trunc(cp_start_date) AND (trunc(cp_till_date+1)-1/(24*60*60))
4467: AND aid.accounting_date between trunc(cp_start_date) AND (trunc(cp_till_date+1)-1/(24*60*60))