[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|
54: g_login_id NUMBER(15);
55: g_prog_appl_id NUMBER(15);
56: g_program_id NUMBER(15);
57: g_ledger_id PLS_INTEGER;
58: g_group_id xla_ae_headers.group_id%TYPE;
59: g_definition_code xla_tb_definitions_b.definition_code%TYPE;
60: g_process_mode_code VARCHAR2(30);
61: g_je_source_name gl_je_sources.je_source_name%TYPE;
62: g_application_id PLS_INTEGER;
74: +------------------------------------------------------------*/
75:
76: -- ********************** Note bug#7213289********************************
77: -- The hint in the select of C_TB_INSERT_SQL ie
78: -- /*+ index(xah XLA_AE_HEADERS_U1) */ is replaced in the
79: -- procedure insert_trial_balance_wu. If any changes are made to the hint
80: -- in the SELECT, change the replace statement accordingly in
81: -- insert_trial_balance_wu.
82: -- **********************End Note*****************************************
122: ,program_id
123: ,program_update_date)
124:
125:
126: SELECT /*+ index(xah XLA_AE_HEADERS_U1) */
127: DECODE(xdl.applied_to_entity_id
128: ,NULL
129: ,''SOURCE''
130: ,''APPLIED'') record_type_code
333: ,:6 -- g_program_id
334: ,sysdate
335:
336: FROM
337: xla_ae_headers xah
338: ,xla_ae_lines xal
339: ,xla_distribution_links xdl
340: ,xla_ledger_options xlo
341: ,fnd_currencies fdc
456: ,request_id
457: ,program_application_id
458: ,program_id
459: ,program_update_date)
460: SELECT /*+ index(xah XLA_AE_HEADERS_U1) */
461: DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',''APPLIED'',DECODE(xteu.entity_id,xah.entity_id,''SOURCE'',''APPLIED'')) record_type_code
462: ,xah.entity_id source_entity_id
463: ,xet.event_class_code event_class_code
464: ,xah.application_id source_application_id
557: ,-1 program_id
558: ,SYSDATE program_update_date
559: FROM
560: ap_liability_balance alb
561: ,xla_ae_headers PARTITION (AP) xah
562: ,xla_event_types_b xet
563: ,xla_tb_defn_details xdd
564: ,xla_tb_definitions_b xtd
565: ,xla_tb_defn_je_sources xjs
746: ,request_id
747: ,program_application_id
748: ,program_id
749: ,program_update_date)
750: SELECT /*+ index(xah XLA_AE_HEADERS_U1) */
751: DECODE(xet.event_class_code,''PREPAYMENT APPLICATIONS'',''APPLIED'',DECODE(xteu.entity_id,xah.entity_id,''SOURCE'',''APPLIED'')) record_type_code
752: ,xah.entity_id source_entity_id
753: ,xet.event_class_code event_class_code
754: ,xah.application_id source_application_id
847: ,-1 program_id
848: ,SYSDATE program_update_date
849: FROM
850: ap_liability_balance alb
851: ,xla_ae_headers PARTITION (AP) xah
852: ,xla_event_types_b xet
853: ,xla_tb_defn_details xdd
854: ,xla_tb_definitions_b xtd
855: ,xla_tb_defn_je_sources xjs
2633: ,g_request_id
2634: ,g_prog_appl_id
2635: ,g_program_id
2636: ,SYSDATE
2637: from xla_ae_headers xah,
2638: xla_event_types_b xet,
2639: xla_event_class_attrs xeca,
2640: xla_tb_definitions_b xtd
2641: WHERE xet.event_class_code <> 'MANUAL'
3497: --to give better performance as observed in GSI instance bug#7213289 with respect
3498: --to the group id join.
3499: --**********************End Note************************************
3500:
3501: l_sql := REPLACE(l_sql, '/*+ index(xah XLA_AE_HEADERS_U1) */', ' ');
3502:
3503:
3504: l_ledger_info := get_ledger_info(p_ledger_id => g_ledger_id);
3505: l_seg_clause := get_segment_clause(p_ledger_id => g_ledger_id);
3956: (ORDER BY ae_header_id
3957: ROWS unbounded preceding
3958: )/g_work_unit
3959: ) wu
3960: FROM xla_ae_headers
3961: WHERE group_id = p_group_id
3962: AND ledger_id IN (
3963: SELECT lg.ledger_id
3964: FROM gl_ledgers lg
3997: (ORDER BY ae_header_id
3998: ROWS unbounded preceding
3999: )/g_work_unit
4000: ) wu
4001: FROM xla_ae_headers
4002: WHERE group_id = p_group_id
4003: AND ledger_id = p_ledger_id
4004: GROUP BY ae_header_id
4005: )
4054: (ORDER BY ae_header_id
4055: ROWS unbounded preceding
4056: )/C_WORK_UNIT
4057: ) wu
4058: FROM xla_ae_headers aeh
4059: ,xla_subledgers xsu
4060: ,xla_tb_definitions_b xtd
4061: ,xla_tb_defn_je_sources xjs
4062: WHERE gl_transfer_status_code IN ('Y','NT')
4102: (ORDER BY ae_header_id
4103: ROWS unbounded preceding
4104: )/C_WORK_UNIT
4105: ) wu
4106: FROM xla_ae_headers aeh
4107: ,xla_subledgers xsu
4108: ,xla_tb_definitions_b xtd
4109: ,xla_tb_defn_je_sources xjs
4110: WHERE gl_transfer_status_code IN ('Y','NT')
4160: (ORDER BY ae_header_id
4161: ROWS unbounded preceding
4162: )/C_WORK_UNIT
4163: ) wu
4164: FROM xla_ae_headers aeh
4165: ,xla_subledgers xsu
4166: ,xla_tb_definitions_b xtd
4167: ,xla_tb_defn_je_sources xjs
4168: WHERE gl_transfer_status_code IN ('Y','NT')
4213: (ORDER BY ae_header_id
4214: ROWS unbounded preceding
4215: )/C_WORK_UNIT
4216: ) wu
4217: FROM xla_ae_headers aeh
4218: ,xla_subledgers xsu
4219: ,xla_tb_definitions_b xtd
4220: ,xla_tb_defn_je_sources xjs
4221: WHERE gl_transfer_status_code IN ('Y','NT')