[Home] [Help]
241: l_sort_record_tab g_sort_record_tabtype; -- allows alternative sort
242: l_measure_tab g_measure_tabtype; -- stores measures
243: l_calc_measure VARCHAR2(2000); -- builds up calculated measures
244:
245: /* Cursor returning all AK Region Items for a given region */
246: CURSOR ak_region_info_csr IS
247: SELECT
248: itm.attribute1 column_type
249: ,itm.attribute2 level_code
259: ,lower(reg.database_object_name) object_name
260: ,reg.attribute11 region_where_clause
261: ,itm.display_sequence display_sequence
262: FROM
263: ak_region_items itm
264: ,ak_regions reg
265: WHERE reg.region_code = p_region_code
266: AND reg.region_application_id = 453
267: AND itm.region_code = reg.region_code
381: /******************************************************************************/
382: /* AK Region Loop - Builds up SQL Statement */
383: /********************************************/
384:
385: /* Get AK Region Items Information */
386: FOR measure_rec IN ak_region_info_csr LOOP
387:
388: /* The base view name - same on every cursor row */
389: IF (l_db_object_name IS NULL) THEN
398: /* AK MEASURES */
399: /***************/
400: IF (measure_rec.column_type = 'MEASURE' OR
401: measure_rec.column_type IS NULL) THEN
402: /* Ignore measures which are calculated AK Region Items */
403: IF (SUBSTR(measure_rec.column_name,1,1) <> '"' AND
404: measure_rec.aggregation IS NOT NULL) THEN
405: /* Store information about the measure */
406: l_measure_tab(measure_rec.display_sequence).column_name :=
549: END LOOP; -- Parameter Table
550:
551: END IF; -- AK Measure or Dimension
552:
553: END LOOP; -- AK Region Items
554:
555: /******************************************************************************/
556: /* BUILD UP SQL STATEMENT */
557: /**************************/
770: /* Sort order variables */
771: l_sort_record_tab g_sort_record_tabtype;
772: l_counter NUMBER;
773:
774: /* Cursor returning all AK Region Items for a given region */
775: CURSOR ak_region_info_csr IS
776: SELECT
777: itm.attribute1 column_type
778: ,itm.attribute2 level_code
786: ,itm.order_sequence order_sequence
787: ,itm.order_direction order_direction
788: ,lower(reg.database_object_name) object_name
789: FROM
790: ak_region_items itm
791: ,ak_regions reg
792: WHERE reg.region_code = p_region_code
793: AND reg.region_application_id = 453
794: AND itm.region_code = reg.region_code
816: END IF;
817:
818: END LOOP;
819:
820: /* Get AK Region Items Information */
821: FOR measure_rec IN ak_region_info_csr LOOP
822:
823: /* The base view name - same on every cursor row */
824: l_db_object_name := measure_rec.object_name;
947: l_where_clause VARCHAR2(2000); -- main WHERE
948: l_order_by_clause VARCHAR2(2000); -- main ORDER BY
949: l_sort_record_tab g_sort_record_tabtype; -- allows alternative sort
950:
951: /* Cursor returning all AK Region Items for a given region */
952: CURSOR ak_region_info_csr IS
953: SELECT
954: reg.region_code region_code
955: ,itm.attribute1 column_type
967: ,lower(reg.database_object_name) object_name
968: ,reg.attribute11 region_where_clause
969: ,itm.display_sequence display_sequence
970: FROM
971: ak_region_items itm
972: ,ak_regions reg
973: WHERE reg.region_code = p_ak_region_code
974: AND reg.region_application_id = 453
975: AND itm.region_code = reg.region_code
1098: /******************************************************************************/
1099: /* AK Region Loop - Builds up SQL Statement */
1100: /********************************************/
1101:
1102: /* Get AK Region Items Information */
1103: FOR measure_rec IN ak_region_info_csr LOOP
1104:
1105: /******************/
1106: /* Current Region */
1117:
1118: /* Display columns - Measures */
1119: IF (measure_rec.column_type = 'MEASURE' OR
1120: measure_rec.column_type IS NULL) THEN
1121: /* Ignore measures which are calculated AK Region Items */
1122: IF (SUBSTR(measure_rec.column_name,1,1) <> '"') THEN
1123: /* Check for non-default sort order */
1124: IF (measure_rec.order_sequence IS NOT NULL) THEN
1125: l_sort_record_tab(measure_rec.order_sequence).column_name :=
1274: END IF;
1275:
1276: END IF; -- Current Region Measure or Dimension
1277:
1278: END LOOP; -- AK Region Items
1279:
1280: /******************************************************************************/
1281: /* BUILD UP SQL STATEMENT */
1282: /**************************/