[Home] [Help]
18: | 20-May-2008 schodava Bug 7109823 - TB Remodelling |
19: | 29-May-2008 rajose bug#7109823 Dynamic building of where clause |
20: | for accounting_class_code defined for |
21: | program code for an application. |
22: | 18-June-2008 rajose bug#7109823 Added index(xah XLA_AE_HEADERS_U1) |
23: | hint in the 3 sql's to improve performance. |
24: | Addition of the hint has improved performance |
25: | for US GAP customer Refer bug#6990540 |
26: | 4-Jul-2008 rajose bug#7225096 fix for ORA-00911: invalid character|
69: g_login_id NUMBER(15);
70: g_prog_appl_id NUMBER(15);
71: g_program_id NUMBER(15);
72: g_ledger_id PLS_INTEGER;
73: g_group_id xla_ae_headers.group_id%TYPE;
74: g_definition_code xla_tb_definitions_b.definition_code%TYPE;
75: g_process_mode_code VARCHAR2(30);
76: g_je_source_name gl_je_sources.je_source_name%TYPE;
77: g_application_id PLS_INTEGER;
89: +------------------------------------------------------------*/
90:
91: -- ********************** Note bug#7213289********************************
92: -- The hint in the select of C_TB_INSERT_SQL ie
93: -- /*+ index(xah XLA_AE_HEADERS_U1) */ is replaced in the
94: -- procedure insert_trial_balance_wu. If any changes are made to the hint
95: -- in the SELECT, change the replace statement accordingly in
96: -- insert_trial_balance_wu.
97: -- **********************End Note*****************************************
137: ,program_id
138: ,program_update_date)
139:
140:
141: SELECT /*+ index(xah XLA_AE_HEADERS_U1) no_index(xal MIS_XLA_AE_LINES_N1) */
142: DECODE(xdl.applied_to_entity_id
143: ,NULL
144: ,''SOURCE''
145: ,''APPLIED'') record_type_code
348: ,:6 -- g_program_id
349: ,sysdate
350:
351: FROM
352: xla_ae_headers xah
353: ,xla_ae_lines xal
354: ,xla_distribution_links xdl
355: ,xla_ledger_options xlo
356: ,fnd_currencies fdc
475: ,request_id
476: ,program_application_id
477: ,program_id
478: ,program_update_date)
479: SELECT /*+ index(xah XLA_AE_HEADERS_U1) no_index(xal MIS_XLA_AE_LINES_N1) */
480: DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',''APPLIED'',DECODE(xteu.entity_id,xah.entity_id,''SOURCE'',''APPLIED'')) record_type_code
481: ,xah.entity_id source_entity_id
482: ,xet.event_class_code event_class_code
483: ,xah.application_id source_application_id
576: ,-1 program_id
577: ,SYSDATE program_update_date
578: FROM
579: ap_liability_balance alb
580: ,xla_ae_headers PARTITION (AP) xah
581: ,xla_event_types_b xet
582: ,xla_tb_definitions_b xtd
583: $l_from$
584: ,xla_transaction_entities_upg PARTITION (AP) xteu
780: ,request_id
781: ,program_application_id
782: ,program_id
783: ,program_update_date)
784: SELECT /*+ index(xah XLA_AE_HEADERS_U1) no_index(xal MIS_XLA_AE_LINES_N1) */
785: DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',''APPLIED'',DECODE(xteu.entity_id,xah.entity_id,''SOURCE'',''APPLIED'')) record_type_code
786: ,xah.entity_id source_entity_id
787: ,xet.event_class_code event_class_code
788: ,xah.application_id source_application_id
881: ,-1 program_id
882: ,SYSDATE program_update_date
883: FROM
884: ap_liability_balance alb
885: ,xla_ae_headers PARTITION (AP) xah
886: ,xla_event_types_b xet
887: ,xla_tb_definitions_b xtd
888: $l_from$
889: ,xla_transaction_entities_upg PARTITION (AP) xteu
2685: ,g_request_id
2686: ,g_prog_appl_id
2687: ,g_program_id
2688: ,SYSDATE
2689: from xla_ae_headers xah,
2690: xla_event_types_b xet,
2691: xla_event_class_attrs xeca,
2692: xla_tb_definitions_b xtd
2693: WHERE xet.event_class_code <> 'MANUAL'
3650: --to give better performance as observed in GSI instance bug#7213289 with respect
3651: --to the group id join.
3652: --**********************End Note************************************
3653:
3654: l_sql := REPLACE(l_sql, '/*+ index(xah XLA_AE_HEADERS_U1) no_index(xal MIS_XLA_AE_LINES_N1) */', ' ');
3655:
3656:
3657: l_ledger_info := get_ledger_info(p_ledger_id => g_ledger_id);
3658: l_seg_clause := get_segment_clause(p_ledger_id => g_ledger_id);
4109: (ORDER BY ae_header_id
4110: ROWS unbounded preceding
4111: )/g_work_unit
4112: ) wu
4113: FROM xla_ae_headers
4114: WHERE group_id = p_group_id
4115: AND ledger_id IN (
4116: SELECT lg.ledger_id
4117: FROM gl_ledgers lg
4150: (ORDER BY ae_header_id
4151: ROWS unbounded preceding
4152: )/g_work_unit
4153: ) wu
4154: FROM xla_ae_headers
4155: WHERE group_id = p_group_id
4156: AND ledger_id = p_ledger_id
4157: GROUP BY ae_header_id
4158: )
4207: (ORDER BY ae_header_id
4208: ROWS unbounded preceding
4209: )/C_WORK_UNIT
4210: ) wu
4211: FROM xla_ae_headers aeh
4212: ,xla_subledgers xsu
4213: ,xla_tb_definitions_b xtd
4214: ,xla_tb_defn_je_sources xjs
4215: WHERE gl_transfer_status_code IN ('Y','NT')
4255: (ORDER BY ae_header_id
4256: ROWS unbounded preceding
4257: )/C_WORK_UNIT
4258: ) wu
4259: FROM xla_ae_headers aeh
4260: ,xla_subledgers xsu
4261: ,xla_tb_definitions_b xtd
4262: ,xla_tb_defn_je_sources xjs
4263: WHERE gl_transfer_status_code IN ('Y','NT')
4313: (ORDER BY ae_header_id
4314: ROWS unbounded preceding
4315: )/C_WORK_UNIT
4316: ) wu
4317: FROM xla_ae_headers aeh
4318: ,xla_subledgers xsu
4319: ,xla_tb_definitions_b xtd
4320: ,xla_tb_defn_je_sources xjs
4321: WHERE gl_transfer_status_code IN ('Y','NT')
4366: (ORDER BY ae_header_id
4367: ROWS unbounded preceding
4368: )/C_WORK_UNIT
4369: ) wu
4370: FROM xla_ae_headers aeh
4371: ,xla_subledgers xsu
4372: ,xla_tb_definitions_b xtd
4373: ,xla_tb_defn_je_sources xjs
4374: WHERE gl_transfer_status_code IN ('Y','NT')