[Home] [Help]
35: Performance tuning for the cursor get_invoices SQL_ID 14827611. Cost = 1287 and sharabale memory 182519
36:
37:
38: Fix :-
39: Modified the cursor get_invoices. Added the IL table jai_ar_trxs to the ra_customer_trx_all
40: and also merged the correlated query to the main query thus reducing the sharable memory also
41:
42:
43: 01/11/2006 SACSETHI for bug 5228046, File version 120.10
130: -- TILL HERE
131:
132: /*
133: || Cursor modified by aiyer for the bug 4947681.SQL ID 14827611
134: || Added the IL table jai_ar_trxs to the ra_customer_trx_all and
135: || also merged the correlated query to the main query thus reducing the sharable memory also
136: || New Cost = 28 Sharable memory = 113499
137: */
138: CURSOR GET_INVOICE ( effective_from_date DATE ,
136: || New Cost = 28 Sharable memory = 113499
137: */
138: CURSOR GET_INVOICE ( effective_from_date DATE ,
139: p_effective_to_date DATE ,
140: cp_int_hdr_cxt RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_CONTEXT%TYPE
141: )
142: IS
143: SELECT
144: rcta.customer_trx_id,
151: rcta.interface_header_attribute1,
152: rcta.interface_header_attribute6,
153: rcta.interface_header_attribute7,
154: rcta.invoice_currency_code
155: FROM ra_customer_trx_all rcta,
156: jai_ar_trxs jat ,
157: ra_cust_trx_types_all rctta
158: WHERE
159: rcta.customer_trx_id = jat.customer_trx_id
646: d.new_type_id,
647: /* Bug 4224466. Added by LGOPALSA */
648: a.invoice_currency_code
649: ,b.global_attribute_category --Added by zhiwei for Bug#12615714
650: FROM ra_customer_trx_all a,
651: ra_customer_trx_lines_all b,
652: mtl_system_items c,
653: jai_ar_trx_sup_inv_t d, -- added by sriram - bug # 3143813 - 19/09/2003
654: jai_ar_trxs jat /* added the table and join condition - aiyer for the fwd porting bug 5225768*/
1994: B.primary_salesrep_id,
1995: B.term_id,
1996: C.organization_id
1997: FROM JAI_AR_SUP_HDRS_ALL A,
1998: RA_CUSTOMER_TRX_ALL B,
1999: JAI_AR_TRXS C
2000: where A.CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
2001: AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
2002: AND A.chosen_for_process = 'Y'
2013: cursor get_curr(id number,cid number,sid number) is
2014: select distinct invoice_currency_code,
2015: primary_salesrep_id,
2016: term_id
2017: from ra_customer_trx_all
2018: where set_of_books_id = id
2019: and bill_to_customer_id = cid
2020: and bill_to_site_use_id = sid;
2021: