[Home] [Help]
334:
335: CURSOR c_ec_obj_exist
336: IS
337: SELECT 'x'
338: FROM xla_extract_objects e
339: WHERE application_id = p_application_id
340: AND entity_code = p_entity_code
341: AND event_class_code = p_event_class_code;
342:
351: AND h.product_rule_type_code = p_product_rule_type_code
352: AND h.product_rule_code = p_product_rule_code
353: AND h.accounting_required_flag = 'Y'
354: AND NOT EXISTS (SELECT 'x'
355: FROM xla_extract_objects e
356: WHERE e.application_id = h.application_id
357: AND e.entity_code = h.entity_code
358: AND e.event_class_code = h.event_class_code);
359:
366: IS
367: SELECT object_name
368: ,object_type_code
369: ,C_REF_OBJECT_FLAG_N ref_object_flag
370: FROM xla_extract_objects e
371: WHERE application_id = p_application_id
372: AND entity_code = p_entity_code
373: AND event_class_code = p_event_class_code
374: AND not exists (SELECT 'x'
371: WHERE application_id = p_application_id
372: AND entity_code = p_entity_code
373: AND event_class_code = p_event_class_code
374: AND not exists (SELECT 'x'
375: FROM xla_extract_objects_gt o
376: WHERE o.object_name = e.object_name)
377: --
378: -- Get all reference objects for the event class that are not defined in the
379: -- database
381: SELECT r.reference_object_name
382: ,e.object_type_code
383: ,C_REF_OBJECT_FLAG_Y ref_object_flag
384: FROM xla_reference_objects r
385: ,xla_extract_objects e
386: WHERE r.application_id = p_application_id
387: AND r.entity_code = p_entity_code
388: AND r.event_class_code = p_event_class_code
389: AND e.application_id = r.application_id
406: ,e.event_class_code
407: ,e.object_name
408: ,e.object_type_code
409: ,C_REF_OBJECT_FLAG_N ref_object_flag
410: FROM xla_extract_objects e, xla_prod_acct_headers h
411: WHERE h.application_id = p_application_id
412: AND h.amb_context_code = p_amb_context_code
413: AND h.product_rule_type_code = p_product_rule_type_code
414: AND h.product_rule_code = p_product_rule_code
416: AND e.application_id = h.application_id
417: AND e.entity_code = h.entity_code
418: AND e.event_class_code = h.event_class_code
419: AND not exists (SELECT 'x'
420: FROM xla_extract_objects_gt o
421: WHERE o.object_name = e.object_name)
422: UNION ALL
423: SELECT r.entity_code
424: ,r.event_class_code
425: ,r.reference_object_name
426: ,e.object_type_code
427: ,C_REF_OBJECT_FLAG_Y ref_object_flag
428: FROM xla_reference_objects r,
429: xla_extract_objects e,
430: xla_prod_acct_headers h
431: WHERE h.application_id = p_application_id
432: AND h.amb_context_code = p_amb_context_code
433: AND h.product_rule_type_code = p_product_rule_type_code
834: o.object_name extract_object_name,
835: o.object_type_code extract_object_type,
836: o.always_populated_flag extract_object_pop_flag,
837: g.source_datatype_code column_datatype_code
838: FROM xla_evt_class_sources_gt g, xla_extract_objects o,
839: xla_extract_objects_gt og
840: WHERE g.application_id = o.application_id
841: AND g.entity_code = o.entity_code
842: AND g.event_class_code = o.event_class_code
835: o.object_type_code extract_object_type,
836: o.always_populated_flag extract_object_pop_flag,
837: g.source_datatype_code column_datatype_code
838: FROM xla_evt_class_sources_gt g, xla_extract_objects o,
839: xla_extract_objects_gt og
840: WHERE g.application_id = o.application_id
841: AND g.entity_code = o.entity_code
842: AND g.event_class_code = o.event_class_code
843: AND g.source_level_code = o.object_type_code
866: o.object_type_code extract_object_type,
867: r.always_populated_flag extract_object_pop_flag,
868: g.source_datatype_code column_datatype_code,
869: r.join_condition, r.linked_to_ref_obj_name
870: FROM xla_evt_class_sources_gt g, xla_reference_objects r, xla_extract_objects o,
871: xla_reference_objects_gt og
872: WHERE g.application_id = r.application_id
873: AND g.entity_code = r.entity_code
874: AND g.event_class_code = r.event_class_code
902: o.object_name extract_object_name,
903: o.object_type_code extract_object_type,
904: o.always_populated_flag extract_object_pop_flag,
905: g.source_datatype_code column_datatype_code
906: FROM xla_evt_class_sources_gt g, xla_extract_objects o,
907: xla_extract_objects_gt og
908: WHERE g.application_id = o.application_id
909: AND g.entity_code = o.entity_code
910: AND g.event_class_code = o.event_class_code
903: o.object_type_code extract_object_type,
904: o.always_populated_flag extract_object_pop_flag,
905: g.source_datatype_code column_datatype_code
906: FROM xla_evt_class_sources_gt g, xla_extract_objects o,
907: xla_extract_objects_gt og
908: WHERE g.application_id = o.application_id
909: AND g.entity_code = o.entity_code
910: AND g.event_class_code = o.event_class_code
911: AND g.source_level_code = o.object_type_code
933: o.object_type_code extract_object_type,
934: r.always_populated_flag extract_object_pop_flag,
935: g.source_datatype_code column_datatype_code,
936: r.join_condition, r.linked_to_ref_obj_name
937: FROM xla_evt_class_sources_gt g, xla_reference_objects r, xla_extract_objects o,
938: xla_reference_objects_gt og
939: WHERE g.application_id = r.application_id
940: AND g.entity_code = r.entity_code
941: AND g.event_class_code = r.event_class_code
973: FROM dba_tab_columns T
974: WHERE og.owner = t.owner
975: AND o.object_name = t.table_name
976: AND t.column_name = g.source_code)
977: FROM xla_evt_class_sources_gt g, xla_extract_objects o,
978: xla_extract_objects_gt og
979: WHERE g.application_id = o.application_id
980: AND g.entity_code = o.entity_code
981: AND g.event_class_code = o.event_class_code
974: WHERE og.owner = t.owner
975: AND o.object_name = t.table_name
976: AND t.column_name = g.source_code)
977: FROM xla_evt_class_sources_gt g, xla_extract_objects o,
978: xla_extract_objects_gt og
979: WHERE g.application_id = o.application_id
980: AND g.entity_code = o.entity_code
981: AND g.event_class_code = o.event_class_code
982: AND g.source_level_code = o.object_type_code
1008: AND t.column_name = g.source_code)
1009: ,r.join_condition
1010: FROM xla_evt_class_sources_gt g
1011: ,xla_reference_objects r
1012: ,xla_extract_objects o
1013: ,xla_reference_objects_gt og
1014: WHERE g.application_id = r.application_id
1015: AND g.entity_code = r.entity_code
1016: AND g.event_class_code = r.event_class_code
1421:
1422: CURSOR c_aad_objects
1423: IS
1424: SELECT distinct ext.object_name, C_REF_OBJECT_FLAG_N reference_object_flag
1425: FROM xla_extract_objects ext, xla_prod_acct_headers hdr
1426: WHERE ext.application_id = hdr.application_id
1427: AND ext.entity_code = hdr.entity_code
1428: AND ext.event_class_code = hdr.event_class_code
1429: AND hdr.application_id = p_application_id
1497: l_amb_context_code := p_amb_context_code;
1498: l_product_rule_code := p_product_rule_code;
1499: l_product_rule_type_code := p_product_rule_type_code;
1500:
1501: DELETE FROM xla_extract_objects_gt;
1502: DELETE FROM xla_reference_objects_gt;
1503:
1504: -- Get owner for current schema
1505: SELECT user
1509: IF p_product_rule_code is NULL THEN
1510:
1511: -- Insert objects for an event class and current owner in GT table
1512: INSERT
1513: INTO xla_extract_objects_gt
1514: (object_name
1515: ,owner)
1516: SELECT ext.object_name, l_user
1517: FROM xla_extract_objects ext
1513: INTO xla_extract_objects_gt
1514: (object_name
1515: ,owner)
1516: SELECT ext.object_name, l_user
1517: FROM xla_extract_objects ext
1518: WHERE EXISTS (SELECT /*+ no_unnest */ 'c'
1519: FROM user_objects usr
1520: WHERE ext.object_name = usr.object_name
1521: AND usr.object_type <> 'SYNONYM' )
1541: AND rfr.event_class_code = p_event_class_code;
1542:
1543: -- Insert objects for an event class and different owner in GT table
1544: INSERT
1545: INTO xla_extract_objects_gt
1546: (object_name
1547: ,owner)
1548: SELECT ext.object_name
1549: ,(SELECT syn.table_owner
1551: ,user_synonyms syn
1552: WHERE ext.object_name = usr.object_name
1553: AND usr.object_name = syn.synonym_name
1554: AND usr.object_type = 'SYNONYM')
1555: FROM xla_extract_objects ext
1556: WHERE EXISTS (SELECT /*+ no_unnest */ 'c'
1557: FROM user_objects usr
1558: ,user_synonyms syn
1559: WHERE ext.object_name = usr.object_name
1606: IF l_ref_object_flag = 'N' THEN
1607:
1608: BEGIN
1609: INSERT
1610: INTO xla_extract_objects_gt
1611: (object_name
1612: ,owner)
1613: VALUES(l_object_name
1614: ,l_user);
1661:
1662: IF l_ref_object_flag = 'N' THEN
1663: BEGIN
1664: INSERT
1665: INTO xla_extract_objects_gt
1666: (object_name
1667: ,owner)
1668: VALUES(l_object_name
1669: ,l_syn_owner);
1779: -- Get all extract objects for an AAD which do not have event_id column
1780: CURSOR c_aad_event_id
1781: IS
1782: SELECT distinct extract_object_name, extract_object_type_code
1783: FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
1784: WHERE application_id = p_application_id
1785: AND entity_code = p_entity_code
1786: AND event_class_code = p_event_class_code
1787: AND extract_object_name IS NOT NULL
1799: -- Get all extract objects for an AAD which do not have language column
1800: CURSOR c_aad_language
1801: IS
1802: SELECT distinct extract_object_name, extract_object_type_code
1803: FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
1804: WHERE application_id = p_application_id
1805: AND entity_code = p_entity_code
1806: AND event_class_code = p_event_class_code
1807: AND extract_object_name IS NOT NULL
1820: -- Get all extract objects for an AAD which do not have line_number column
1821: CURSOR c_aad_line_number
1822: IS
1823: SELECT distinct extract_object_name, extract_object_type_code
1824: FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og
1825: WHERE application_id = p_application_id
1826: AND entity_code = p_entity_code
1827: AND event_class_code = p_event_class_code
1828: AND extract_object_name IS NOT NULL
1841: -- Get all extract objects for an AAD which do not have ledger_id column
1842: CURSOR c_aad_ledger_id
1843: IS
1844: SELECT distinct extract_object_name, extract_object_type_code
1845: FROM xla_evt_class_sources_gt e, xla_extract_objects_gt og, xla_subledgers app
1846: WHERE e.application_id = p_application_id
1847: AND e.entity_code = p_entity_code
1848: AND e.event_class_code = p_event_class_code
1849: AND e.extract_object_name IS NOT NULL
1863: -- Get all extract objects for an event class which do not have event_id column
1864: CURSOR c_event_id
1865: IS
1866: SELECT e.object_name, object_type_code
1867: FROM xla_extract_objects e, xla_extract_objects_gt og
1868: WHERE application_id = p_application_id
1869: AND entity_code = p_entity_code
1870: AND event_class_code = p_event_class_code
1871: AND e.object_name = og.object_name
1876: AND t.column_name = 'EVENT_ID'
1877: AND t.data_type = 'NUMBER')
1878: -- 4420371 AND t.nullable = 'N')
1879: AND EXISTS (SELECT 'y'
1880: FROM xla_extract_objects_gt a
1881: WHERE a.object_name = e.object_name);
1882:
1883: l_event_id c_event_id%rowtype;
1884:
1885: -- Get all extract objects for an event class which do not have language column
1886: CURSOR c_language
1887: IS
1888: SELECT e.object_name, object_type_code
1889: FROM xla_extract_objects e, xla_extract_objects_gt og
1890: WHERE application_id = p_application_id
1891: AND entity_code = p_entity_code
1892: AND event_class_code = p_event_class_code
1893: AND object_type_code IN ('HEADER_MLS','LINE_MLS')
1899: AND t.column_name = 'LANGUAGE'
1900: AND t.data_type = 'VARCHAR2')
1901: -- 4420371 AND t.nullable = 'N')
1902: AND EXISTS (SELECT 'y'
1903: FROM xla_extract_objects_gt a
1904: WHERE a.object_name = e.object_name);
1905:
1906: l_language c_language%rowtype;
1907:
1908: -- Get all extract objects for an event class which do not have line_number column
1909: CURSOR c_line_number
1910: IS
1911: SELECT e.object_name, object_type_code
1912: FROM xla_extract_objects e, xla_extract_objects_gt og
1913: WHERE application_id = p_application_id
1914: AND entity_code = p_entity_code
1915: AND event_class_code = p_event_class_code
1916: AND object_type_code IN ('LINE','LINE_MLS')
1922: AND t.column_name = 'LINE_NUMBER'
1923: AND t.data_type = 'NUMBER')
1924: -- 4420371 AND t.nullable = 'N')
1925: AND EXISTS (SELECT 'y'
1926: FROM xla_extract_objects_gt a
1927: WHERE a.object_name = e.object_name);
1928:
1929: l_line_number c_line_number%rowtype;
1930:
1931: -- Get all extract objects for an event class which do not have ledger_id column
1932: CURSOR c_ledger_id
1933: IS
1934: SELECT e.object_name, object_type_code
1935: FROM xla_extract_objects e, xla_extract_objects_gt og, xla_subledgers app
1936: WHERE e.application_id = p_application_id
1937: AND e.entity_code = p_entity_code
1938: AND e.event_class_code = p_event_class_code
1939: AND e.object_type_code IN ('LINE','LINE_MLS')
1946: AND og.owner = t.owner
1947: AND t.column_name = 'LEDGER_ID'
1948: AND t.data_type = 'NUMBER')
1949: AND EXISTS (SELECT 'y'
1950: FROM xla_extract_objects_gt a
1951: WHERE a.object_name = e.object_name);
1952:
1953: l_ledger_id c_ledger_id%rowtype;
1954:
2856:
2857: CURSOR c_mls
2858: IS
2859: SELECT distinct c.column_name
2860: FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
2861: WHERE c.table_name = e.object_name
2862: AND e.object_name = og.object_name
2863: AND og.owner = c.owner
2864: AND e.application_id = p_application_id
2878: WHEN 'HEADER' THEN 'N'
2879: WHEN 'LINE' THEN 'N'
2880: ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
2881: END translated_flag
2882: FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
2883: WHERE c.table_name = e.object_name
2884: AND e.object_name = og.object_name
2885: AND og.owner = c.owner
2886: --
2913: WHEN 'LINE' THEN 'N'
2914: ELSE decode(c.data_type,'NUMBER','N','DATE','N', decode(c.column_name,'LANGUAGE','N','Y'))
2915: END translated_flag
2916: FROM dba_tab_columns c, xla_reference_objects r,
2917: xla_reference_objects_gt og, xla_extract_objects e
2918: WHERE c.table_name = r.reference_object_name
2919: AND r.reference_object_name = og.reference_object_name
2920: AND og.owner = c.owner
2921: AND r.application_id = p_application_id
3400: ,g_created_by
3401: ,g_last_updated_by
3402: ,g_last_update_date
3403: ,g_last_update_login
3404: FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
3405: WHERE c.table_name = e.object_name
3406: AND og.object_name = e.object_name
3407: AND og.owner = c.owner
3408: AND e.object_type_code IN ('HEADER','HEADER_MLS')
3447: ,g_last_updated_by
3448: ,g_last_update_date
3449: ,g_last_update_login
3450: FROM dba_tab_columns c, xla_reference_objects r,
3451: xla_reference_objects_gt og, xla_extract_objects e
3452: WHERE c.table_name = r.reference_object_name
3453: AND og.reference_object_name = r.reference_object_name
3454: AND og.owner = c.owner
3455: AND e.application_id = p_application_id
3497: ,g_created_by
3498: ,g_last_updated_by
3499: ,g_last_update_date
3500: ,g_last_update_login
3501: FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
3502: WHERE c.table_name = e.object_name
3503: AND og.object_name = e.object_name
3504: AND og.owner = c.owner
3505: AND e.object_type_code IN ('LINE','LINE_MLS')
3544: ,g_last_updated_by
3545: ,g_last_update_date
3546: ,g_last_update_login
3547: FROM dba_tab_columns c, xla_reference_objects r,
3548: xla_reference_objects_gt og, xla_extract_objects e
3549: WHERE c.table_name = r.reference_object_name
3550: AND og.reference_object_name = r.reference_object_name
3551: AND og.owner = c.owner
3552: AND e.application_id = p_application_id
3594: ,g_created_by
3595: ,g_last_updated_by
3596: ,g_last_update_date
3597: ,g_last_update_login
3598: FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
3599: WHERE c.table_name = e.object_name
3600: AND og.object_name = e.object_name
3601: AND og.owner = c.owner
3602: AND e.object_type_code IN ('HEADER','HEADER_MLS')
3641: ,g_last_updated_by
3642: ,g_last_update_date
3643: ,g_last_update_login
3644: FROM dba_tab_columns c, xla_reference_objects r,
3645: xla_reference_objects_gt og, xla_extract_objects e
3646: WHERE c.table_name = r.reference_object_name
3647: AND og.reference_object_name = r.reference_object_name
3648: AND og.owner = c.owner
3649: AND e.application_id = p_application_id
3691: ,g_created_by
3692: ,g_last_updated_by
3693: ,g_last_update_date
3694: ,g_last_update_login
3695: FROM dba_tab_columns c, xla_extract_objects e, xla_extract_objects_gt og
3696: WHERE c.table_name = e.object_name
3697: AND og.object_name = e.object_name
3698: AND og.owner = c.owner
3699: AND e.object_type_code IN ('LINE','LINE_MLS')
3738: ,g_last_updated_by
3739: ,g_last_update_date
3740: ,g_last_update_login
3741: FROM dba_tab_columns c, xla_reference_objects r,
3742: xla_reference_objects_gt og, xla_extract_objects e
3743: WHERE c.table_name = r.reference_object_name
3744: AND og.reference_object_name = r.reference_object_name
3745: AND og.owner = c.owner
3746: AND e.application_id = p_application_id