[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
118: -- TILL HERE
119:
120: /*
121: || Cursor modified by aiyer for the bug 4947681.SQL ID 14827611
122: || Added the IL table jai_ar_trxs to the ra_customer_trx_all and
123: || also merged the correlated query to the main query thus reducing the sharable memory also
124: || New Cost = 28 Sharable memory = 113499
125: */
126: CURSOR GET_INVOICE ( effective_from_date DATE ,
124: || New Cost = 28 Sharable memory = 113499
125: */
126: CURSOR GET_INVOICE ( effective_from_date DATE ,
127: p_effective_to_date DATE ,
128: cp_int_hdr_cxt RA_CUSTOMER_TRX_ALL.INTERFACE_HEADER_CONTEXT%TYPE
129: )
130: IS
131: SELECT
132: rcta.customer_trx_id,
139: rcta.interface_header_attribute1,
140: rcta.interface_header_attribute6,
141: rcta.interface_header_attribute7,
142: rcta.invoice_currency_code
143: FROM ra_customer_trx_all rcta,
144: jai_ar_trxs jat ,
145: ra_cust_trx_types_all rctta
146: WHERE
147: rcta.customer_trx_id = jat.customer_trx_id
575: d.mapping_type, -- added by sriram - bug # 3143813 - 19/09/2003
576: d.new_type_id,
577: /* Bug 4224466. Added by LGOPALSA */
578: a.invoice_currency_code
579: FROM ra_customer_trx_all a,
580: ra_customer_trx_lines_all b,
581: mtl_system_items c,
582: jai_ar_trx_sup_inv_t d, -- added by sriram - bug # 3143813 - 19/09/2003
583: jai_ar_trxs jat /* added the table and join condition - aiyer for the fwd porting bug 5225768*/
1866: B.primary_salesrep_id,
1867: B.term_id,
1868: C.organization_id
1869: FROM JAI_AR_SUP_HDRS_ALL A,
1870: RA_CUSTOMER_TRX_ALL B,
1871: JAI_AR_TRXS C
1872: where A.CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
1873: AND A.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
1874: AND A.chosen_for_process = 'Y'
1885: cursor get_curr(id number,cid number,sid number) is
1886: select distinct invoice_currency_code,
1887: primary_salesrep_id,
1888: term_id
1889: from ra_customer_trx_all
1890: where set_of_books_id = id
1891: and bill_to_customer_id = cid
1892: and bill_to_site_use_id = sid;
1893: