[Home] [Help]
18: -- Bug 12377269. Added below TYPEs to support
19: -- methods to get heirarchical ICCs
20: --
21: TYPE t_icc_id_rec IS RECORD (
22: item_catalog_group_id ego_mtl_catalog_grp_vers_b.item_catalog_group_id%TYPE,
23: version_seq_id ego_mtl_catalog_grp_vers_b.version_seq_id%TYPE,
24: catalog_concat_segs mtl_item_catalog_groups_v.catalog_concat_segs%TYPE
25: );
26:
19: -- methods to get heirarchical ICCs
20: --
21: TYPE t_icc_id_rec IS RECORD (
22: item_catalog_group_id ego_mtl_catalog_grp_vers_b.item_catalog_group_id%TYPE,
23: version_seq_id ego_mtl_catalog_grp_vers_b.version_seq_id%TYPE,
24: catalog_concat_segs mtl_item_catalog_groups_v.catalog_concat_segs%TYPE
25: );
26:
27: TYPE t_icc_id_recs IS TABLE OF t_icc_id_rec INDEX BY BINARY_INTEGER;
67: BEGIN
68: SELECT version_seq_id INTO l_cur_icc_ver_id
69: FROM
70: ( SELECT item_catalog_group_id,version_seq_id,MAX(start_active_date) start_active_date
71: FROM EGO_MTL_CATALOG_GRP_VERS_B
72: WHERE NVL(end_active_date, sysdate) >= SYSDATE
73: AND start_active_date <= SYSDATE
74: AND ITEM_CATALOG_GROUP_ID = p_icc_id
75: AND version_seq_id > 0
226:
227: BEGIN
228: SELECT version_seq_id, end_active_date
229: INTO l_valid_icc_ver_id, l_end_date
230: FROM ego_mtl_catalog_grp_vers_b
231: WHERE item_catalog_group_id = To_Number(p_icc_id)
232: AND version_seq_id = To_Number(p_icc_ver_id)
233: /*AND start_active_date <= SYSDATE commented this filter since we should be able to sync future eff versions*/
234: AND version_seq_id > 0;
812: FUNCTION get_active_date(
813: p_item_catalog_group_id IN NUMBER,
814: p_version_seq_id IN NUMBER) RETURN DATE IS
815:
816: l_ret ego_mtl_catalog_grp_vers_b.end_active_date%TYPE;
817:
818: CURSOR c
819: IS
820: SELECT NVL(vers.end_active_date,SYSDATE)
817:
818: CURSOR c
819: IS
820: SELECT NVL(vers.end_active_date,SYSDATE)
821: FROM ego_mtl_catalog_grp_vers_b vers
822: WHERE item_catalog_group_id = p_item_catalog_group_id
823: AND version_seq_id = NVL(p_version_seq_id,0);
824:
825: BEGIN
915: FROM mtl_item_catalog_groups_b
916: START WITH item_catalog_group_id = p_item_catalog_group_id
917: CONNECT BY PRIOR item_catalog_group_id = parent_catalog_group_id
918: ) hier,
919: ego_mtl_catalog_grp_vers_b iccb
920: WHERE cat.item_catalog_group_id = hier.item_catalog_group_id
921: AND hier.lev > 1
922: AND iccb.item_catalog_group_id = hier.item_catalog_group_id
923: AND iccb.version_seq_id >= p_versions_from
992: FROM mtl_item_catalog_groups_b
993: START WITH item_catalog_group_id = p_item_catalog_group_id
994: CONNECT BY PRIOR parent_catalog_group_id = item_catalog_group_id
995: ) hier,
996: ego_mtl_catalog_grp_vers_b iccb
997: WHERE cat.item_catalog_group_id = hier.item_catalog_group_id
998: AND hier.lev > 1
999: AND iccb.item_catalog_group_id = hier.item_catalog_group_id
1000: AND iccb.version_seq_id >= p_versions_from
1051: IS
1052: l_children t_icc_id_recs;
1053: l_parents t_icc_id_recs;
1054: l_hier_iccs t_icc_id_recs;
1055: l_active_date ego_mtl_catalog_grp_vers_b.end_active_date%TYPE;
1056: l_versions_from NUMBER;
1057: l_get_versions BOOLEAN;
1058: L_PROC_NAME VARCHAR2(50);
1059:
1494: ,ETAVB.ASSOCIATION_ID,ETAVB.ATTR_ID,ETAVB.ATTR_DISPLAY_NAME,ETAVB.SEQUENCE
1495: , FFVS.FLEX_VALUE_SET_ID, FFVS.FLEX_VALUE_SET_NAME
1496: , EFVVB.VERSION_SEQ_ID AS vs_seq_id, EFVVB.START_ACTIVE_DATE AS vs_start_date, EFVVB.END_ACTIVE_DATE AS vs_end_date
1497: , EPWE.PK1_VALUE, EPWE.PK2_VALUE
1498: FROM EGO_MTL_CATALOG_GRP_VERS_B EMCGVB,
1499: EGO_TRANS_ATTR_VERS_B ETAVB
1500: , FND_FLEX_VALUE_SETS FFVS,
1501: EGO_FLEX_VALUESET_VERSION_B EFVVB
1502: , EGO_PUB_WS_ENTITIES EPWE