[Home] [Help]
425:
426: -- Function
427: -- init_ledger_le_bsv_gt
428: -- Purpose
429: -- Initialize the global temporary table, GL_LEDGER_LE_BSV_GT for a specific
430: -- ledger and its associated ALC ledgers, if any
431: -- History
432: -- 05-JUN-03 LPOON Created (New R11i.X function)
433: -- 19-FEB-04 LPOON Modified the SQL to insert rows for specific BSV opt
477:
478: END IF; -- IF (l_ledger_category <> 'PRIMARY' ...
479:
480: --
481: -- Delete rows from GL_LEDGER_LE_BSV_GT for the passed ledger
482: --
483:
484: -- Delete the rows for the passed ledger and its associated ALC Ledgers
485: /* DELETE FROM GL_LEDGER_LE_BSV_GT
481: -- Delete rows from GL_LEDGER_LE_BSV_GT for the passed ledger
482: --
483:
484: -- Delete the rows for the passed ledger and its associated ALC Ledgers
485: /* DELETE FROM GL_LEDGER_LE_BSV_GT
486: WHERE ledger_id = p_ledger_id
487: OR ledger_id IN (
488: SELECT ledger_id FROM GL_ALC_LEDGER_RSHIPS_V
489: WHERE application_id = 101
489: WHERE application_id = 101
490: AND source_ledger_id = p_ledger_id); */
491:
492: -- Delete the all the rows from Previous Ledger contexts
493: DELETE FROM GL_LEDGER_LE_BSV_GT;
494:
495: --
496: -- Insert segment values from GL_LEDGER_NORM_SEG_VALS if the BSV option is
497: -- Specific (i.e. I)
498: --
499: IF (l_bsv_option = 'I')
500: THEN
501: -- Insert rows for the passed ledger and its associated ALC Ledgers
502: INSERT INTO GL_LEDGER_LE_BSV_GT
503: (LEDGER_ID, LEDGER_NAME, LEDGER_SHORT_NAME, LEDGER_CATEGORY_CODE,
504: CHART_OF_ACCOUNTS_ID, BAL_SEG_VALUE_OPTION_CODE, BAL_SEG_VALUE_SET_ID,
505: BAL_SEG_COLUMN_NAME, BAL_SEG_VALUE, LEGAL_ENTITY_ID, LEGAL_ENTITY_NAME,
506: START_DATE, END_DATE, RELATIONSHIP_ENABLED_FLAG
548:
549: -- Build INSERT statement of the dynamic INSERT SQL
550: l_line_no := l_line_no + 1;
551: l_insertSQL(l_line_no) :=
552: 'INSERT INTO GL_LEDGER_LE_BSV_GT';
553: l_line_no := l_line_no + 1;
554: l_insertSQL(l_line_no) :=
555: '(LEDGER_ID, LEDGER_NAME, LEDGER_SHORT_NAME, LEDGER_CATEGORY_CODE, ';
556: l_line_no := l_line_no + 1;
663: END IF; -- IF (l_bsv_option = 'I')
664:
665: -- Update RELATIONSHIP_ENABLED_FLAG to 'Y' for ALC/secondary ledgers
666: -- if they have at least one enabled ALC/secondary relationship
667: UPDATE GL_LEDGER_LE_BSV_GT gt
668: SET gt.RELATIONSHIP_ENABLED_FLAG = 'Y'
669: WHERE (gt.LEDGER_CATEGORY_CODE = 'SECONDARY'
670: AND EXISTS (
671: SELECT 'Enabled RS exists' FROM GL_SECONDARY_LEDGER_RSHIPS_V rs
857: THEN
858: IF (p_bal_seg_value IS NOT NULL)
859: THEN
860: -- CASE 1: Specific BSV option and BSV is passed
861: -- We can get LEs assigned to this ledger/BSV from GL_LEDGER_LE_BSV_GT.
862: SELECT legal_entity_id
863: , legal_entity_name
864: BULK COLLECT INTO
865: l_rec_col.legal_entity_id,
863: , legal_entity_name
864: BULK COLLECT INTO
865: l_rec_col.legal_entity_id,
866: l_rec_col.legal_entity_name
867: FROM GL_LEDGER_LE_BSV_GT
868: WHERE ledger_id = p_ledger_id
869: AND bal_seg_value = p_bal_seg_value
870: AND ((p_bsv_eff_date IS NULL)
871: OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
874: ORDER BY legal_entity_id;
875:
876: ELSE
877: -- CASE 2: Specific BSV option and BSV is NOT passed
878: -- We can get LEs assigned to this ledger from GL_LEDGER_LE_BSV_GT.
879: SELECT DISTINCT legal_entity_id
880: , legal_entity_name
881: BULK COLLECT INTO
882: l_rec_col.legal_entity_id,
880: , legal_entity_name
881: BULK COLLECT INTO
882: l_rec_col.legal_entity_id,
883: l_rec_col.legal_entity_name
884: FROM GL_LEDGER_LE_BSV_GT
885: WHERE ledger_id = p_ledger_id
886: AND ((p_bsv_eff_date IS NULL)
887: OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
888: AND p_bsv_eff_date <= NVL(end_date, p_bsv_eff_date)))
925: ** -- => If no, it will return TRUE and null LE list.
926: ** BEGIN
927: ** SELECT 'Y'
928: ** INTO l_bsv_assigned
929: ** FROM GL_LEDGER_LE_BSV_GT
930: ** WHERE ledger_id = p_ledger_id
931: ** AND bal_seg_value = p_bal_seg_value
932: ** AND ((p_bsv_eff_date IS NULL)
933: ** OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
1022:
1023: IF (x_allow_all_bsv_flag = 'N' AND p_legal_entity_id IS NOT NULL)
1024: THEN
1025: -- CASE 1: Specific BSV option and LE ID is passed
1026: -- We can just get BSV assigned to this ledger/LE from GL_LEDGER_LE_BSV_GT
1027: SELECT bal_seg_value
1028: , legal_entity_id
1029: , legal_entity_name
1030: BULK COLLECT INTO
1030: BULK COLLECT INTO
1031: l_rec_col.bal_seg_value,
1032: l_rec_col.legal_entity_id,
1033: l_rec_col.legal_entity_name
1034: FROM GL_LEDGER_LE_BSV_GT
1035: WHERE ledger_id = l_ledger_id
1036: AND legal_entity_id = p_legal_entity_id
1037: AND ((p_bsv_eff_date IS NULL)
1038: OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
1044: THEN
1045: -- CASE 2: All BSV option and LE ID is passed
1046: -- First, we need to check if the LE is assigned to this ledger.
1047: -- => If yes, it can proceed to get all BSV assigned to this ledger from
1048: -- GL_LEDGER_LE_BSV_GT.
1049: -- => If no, it will return TRUE and null BSV list
1050: BEGIN
1051: SELECT legal_entity_name
1052: INTO l_le_name
1061:
1062: END IF; -- IF (x_allow_all_bsv_flag = 'Y' AND ...
1063:
1064: -- CASE 3: LE ID is NOT passed (*Also for CASE 2)
1065: -- We can get BSV assigned to this ledger from GL_LEDGER_LE_BSV_GT
1066: SELECT bal_seg_value
1067: , NVL(legal_entity_id, p_legal_entity_id)
1068: , NVL(legal_entity_name, l_le_name)
1069: BULK COLLECT INTO
1069: BULK COLLECT INTO
1070: l_rec_col.bal_seg_value,
1071: l_rec_col.legal_entity_id,
1072: l_rec_col.legal_entity_name
1073: FROM GL_LEDGER_LE_BSV_GT
1074: WHERE ledger_id = l_ledger_id
1075: AND ((p_bsv_eff_date IS NULL)
1076: OR (p_bsv_eff_date >= NVL(start_date, p_bsv_eff_date)
1077: AND p_bsv_eff_date <= NVL(end_date, p_bsv_eff_date)))