[Home] [Help]
149: -- Define cursor check_dup_poskey to check for duplicate position key within same MC
150: CURSOR check_dup_poskey
151: IS
152: SELECT 'x'
153: FROM ahl_mc_relationships
154: WHERE mc_header_id = p_x_node_rec.mc_header_id AND
155: position_key = p_x_node_rec.position_key;
156: -- Since expired nodes are also copied, so duplicate position key check must happen for expired nodes also
157: -- AND G_TRUNC_DATE < trunc(nvl(p_x_node_rec.active_end_date, G_SYSDATE + 1));
272: 'Node validation successful'
273: );
274: END IF;
275:
276: SELECT ahl_mc_relationships_s.nextval INTO p_x_node_rec.relationship_id FROM DUAL;
277: p_x_node_rec.object_version_number := 1;
278: p_x_node_rec.security_group_id := null;
279:
280: -- 4. Insert the node record into AHL_MC_RELATIONSHIPS table
276: SELECT ahl_mc_relationships_s.nextval INTO p_x_node_rec.relationship_id FROM DUAL;
277: p_x_node_rec.object_version_number := 1;
278: p_x_node_rec.security_group_id := null;
279:
280: -- 4. Insert the node record into AHL_MC_RELATIONSHIPS table
281: INSERT INTO AHL_MC_RELATIONSHIPS
282: (
283: RELATIONSHIP_ID,
284: POSITION_REF_CODE,
277: p_x_node_rec.object_version_number := 1;
278: p_x_node_rec.security_group_id := null;
279:
280: -- 4. Insert the node record into AHL_MC_RELATIONSHIPS table
281: INSERT INTO AHL_MC_RELATIONSHIPS
282: (
283: RELATIONSHIP_ID,
284: POSITION_REF_CODE,
285: PARENT_RELATIONSHIP_ID,
534: -- Define cursor check_poskey_update to verify whether the position key for a MC node is updated
535: CURSOR check_poskey_update
536: IS
537: SELECT 'x'
538: FROM ahl_mc_relationships
539: WHERE relationship_id = p_x_node_rec.relationship_id AND
540: position_key <> p_x_node_rec.position_key;
541:
542: -- Define cursor get_node_details to retrieve details of the MC node
542: -- Define cursor get_node_details to retrieve details of the MC node
543: CURSOR get_node_details
544: IS
545: SELECT active_end_date
546: FROM ahl_mc_relationships
547: WHERE relationship_id = p_x_node_rec.relationship_id;
548:
549: --R12
550: --priyan MEL-CDL
554: p_rel_id in number
555: )
556: IS
557: SELECT rel.ata_code, rel.position_ref_meaning , mch.name
558: FROM ahl_mc_relationships_v rel, ahl_mc_headers_b mch
559: WHERE rel.relationship_id IN
560: (
561: SELECT relationship_id
562: FROM ahl_mc_config_relations
562: FROM ahl_mc_config_relations
563: WHERE mc_header_id IN
564: (
565: SELECT mc_header_id
566: FROM ahl_mc_relationships
567: WHERE relationship_id = p_rel_id
568: )
569: )
570: AND rel.mc_header_id = mch.mc_header_id;
575: p_rel_id in number
576: )
577: IS
578: SELECT rel.ata_code , mch.name, rel.position_ref_meaning
579: FROM ahl_mc_relationships_v rel, ahl_mc_headers_b mch
580: WHERE rel.mc_header_id IN
581: (
582: SELECT mc_header_id
583: FROM ahl_mc_config_relations
589: -- Define check_root_node to check whether the node to topnode of a MC
590: CURSOR check_root_node
591: IS
592: SELECT 'x'
593: FROM ahl_mc_relationships
594: WHERE parent_relationship_id is null AND
595: relationship_id = p_x_node_rec.relationship_id;
596:
597: -- Define check_leaf_node to check whether the node a leaf node
597: -- Define check_leaf_node to check whether the node a leaf node
598: CURSOR check_leaf_node
599: IS
600: SELECT 'x'
601: FROM ahl_mc_relationships
602: WHERE parent_relationship_id = p_x_node_rec.relationship_id AND
603: G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
604:
605: --End priyan Changes
734: END IF;
735:
736: p_x_node_rec.object_version_number := p_x_node_rec.object_version_number + 1;
737:
738: -- 1d. Update the node record in AHL_MC_RELATIONSHIPS table
739: UPDATE AHL_MC_RELATIONSHIPS
740: SET POSITION_REF_CODE = p_x_node_rec.position_ref_code,
741: ITEM_GROUP_ID = p_x_node_rec.item_group_id,
742: UOM_CODE = p_x_node_rec.uom_code,
735:
736: p_x_node_rec.object_version_number := p_x_node_rec.object_version_number + 1;
737:
738: -- 1d. Update the node record in AHL_MC_RELATIONSHIPS table
739: UPDATE AHL_MC_RELATIONSHIPS
740: SET POSITION_REF_CODE = p_x_node_rec.position_ref_code,
741: ITEM_GROUP_ID = p_x_node_rec.item_group_id,
742: UOM_CODE = p_x_node_rec.uom_code,
743: QUANTITY = p_x_node_rec.quantity,
1103: p_rel_id in number
1104: )
1105: IS
1106: SELECT *
1107: FROM ahl_mc_relationships
1108: CONNECT BY parent_relationship_id = PRIOR relationship_id
1109: START WITH relationship_id = p_rel_id
1110: ORDER BY relationship_id DESC;
1111:
1235: );
1236: END IF;
1237:
1238: -- 9i. Delete the MC node
1239: DELETE FROM ahl_mc_relationships
1240: WHERE relationship_id = l_node_csr_rec.relationship_id;
1241:
1242: END LOOP;
1243:
1433: -- Read the newly created node details into the in/out parameters
1434: BEGIN
1435: SELECT new.relationship_id, new.object_version_number
1436: INTO p_x_node_id, p_x_node_obj_ver_num
1437: FROM ahl_mc_relationships new, ahl_mc_relationships old
1438: WHERE new.position_ref_code = old.position_ref_code AND
1439: old.relationship_id = p_x_node_id AND
1440: new.parent_relationship_id = p_parent_rel_id;
1441: EXCEPTION
1539: p_topnode_id in number
1540: )
1541: IS
1542: SELECT *
1543: FROM ahl_mc_relationships
1544: WHERE relationship_id <> p_topnode_id
1545: -- Expired nodes also to be copied or else position path copy will fail
1546: -- AND G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1))
1547: CONNECT BY parent_relationship_id = PRIOR relationship_id
1553: p_relationship_id in number
1554: )
1555: IS
1556: select POSITION_REF_MEANING,POSITION_NECESSITY_MEANING,GROUP_NAME,ATA_MEANING -- R12 priyan MEL-CDL
1557: from ahl_mc_relationships_v
1558: where relationship_id = p_relationship_id;
1559:
1560: -- 2. Define cursor get_ctr_rule_update_csr to read all counter update rules for a particular MC node
1561: CURSOR get_ctr_rule_update_csr
1572: -- Define get_mc_header_id to read the mc_header_id of the destination MC node
1573: CURSOR get_mc_header_id
1574: IS
1575: SELECT mc_header_id
1576: FROM ahl_mc_relationships
1577: WHERE relationship_id = p_dest_rel_id;
1578:
1579: -- Define cursor get_max_dispord to read the maximum of display orders of the children of a MC node with relationship_id = p_rel_id
1580: CURSOR get_max_dispord
1582: p_rel_id in number
1583: )
1584: IS
1585: SELECT max(display_order)
1586: FROM ahl_mc_relationships
1587: WHERE parent_relationship_id = p_rel_id;
1588:
1589: -- Define cursor get_root_node to read details of the top node (in the case of copy_node)
1590: CURSOR get_root_node
1592: p_topnode_id in number
1593: )
1594: IS
1595: SELECT *
1596: FROM ahl_mc_relationships_v
1597: WHERE relationship_id = p_topnode_id;
1598:
1599: -- Define local variables
1600: l_api_name CONSTANT VARCHAR2(30) := 'Copy_MC_Nodes';
1606: l_node_rec Node_Rec_Type;
1607: l_nodes_tbl Node_Tbl_Type;
1608: l_node_csr_rec get_mc_tree_csr%rowtype;
1609:
1610: l_root_node_csr_rec ahl_mc_relationships_v%rowtype;
1611:
1612:
1613:
1614: -- declared by anraj to remove the CONNECT BY PRIOR on joins
2502: IF x_msg_count > 0 THEN
2503: RAISE FND_API.G_EXC_ERROR;
2504: END IF;
2505:
2506: UPDATE ahl_mc_relationships
2507: SET item_group_id = l_item_group_id
2508: WHERE relationship_id = p_nodes_tbl(i).relationship_id;
2509:
2510: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
2604:
2605: CURSOR check_node_exists
2606: IS
2607: SELECT object_version_number
2608: FROM ahl_mc_relationships
2609: WHERE relationship_id = p_rel_id;
2610:
2611: BEGIN
2612:
2656: CURSOR get_node_details
2657: IS
2658: SELECT quantity,
2659: active_end_date
2660: FROM ahl_mc_relationships
2661: WHERE relationship_id = p_x_node_rec.parent_relationship_id;
2662:
2663: -- Define cursor check_subconfig_assos to check whether the parent node has any subconfig associations
2664: CURSOR check_subconfig_assos
2672: -- Define cursor check_dup_pos_ref to check whether the parent node does not have the same position reference
2673: CURSOR check_dup_pos_ref
2674: IS
2675: SELECT 'x'
2676: FROM ahl_mc_relationships
2677: WHERE position_ref_code = p_x_node_rec.position_ref_code AND
2678: parent_relationship_id = p_x_node_rec.parent_relationship_id AND
2679: G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1)) AND
2680: relationship_id <> nvl(p_x_node_rec.relationship_id, -1);
2682: -- Define cursor check_topnode_exists to check whether a topnode already exists for the MC
2683: CURSOR check_topnode_exists
2684: IS
2685: SELECT 'x'
2686: FROM ahl_mc_relationships
2687: WHERE parent_relationship_id is null AND
2688: mc_header_id = p_x_node_rec.mc_header_id;
2689:
2690: -- Define cursor get_item_group_id to retrieve item group id, type and status
2711: -- Define cursor check_child_exists to check whether the node has any children in which case quantity = 1
2712: CURSOR check_child_exists
2713: IS
2714: SELECT 'x'
2715: FROM ahl_mc_relationships
2716: WHERE parent_relationship_id = p_x_node_rec.relationship_id
2717: AND G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
2718:
2719: -- Define cursor check_dup_display_order to check display order duplication
2719: -- Define cursor check_dup_display_order to check display order duplication
2720: CURSOR check_dup_display_order
2721: IS
2722: SELECT 'x'
2723: FROM ahl_mc_relationships
2724: WHERE display_order = p_x_node_rec.display_order AND
2725: parent_relationship_id = p_x_node_rec.parent_relationship_id AND
2726: G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1)) AND
2727: relationship_id <> nvl(p_x_node_rec.relationship_id, -1);
2729: -- Define cursor get_node_dates to retrieve start and end date of the node
2730: CURSOR get_node_dates
2731: IS
2732: SELECT active_start_date, active_end_date
2733: FROM ahl_mc_relationships
2734: WHERE relationship_id = p_x_node_rec.relationship_id;
2735:
2736: -- Declare local variables
2737: l_qty NUMBER;
3332: -- Define cursor get_node_posref to read the position reference of the MC node, used for displaying errors
3333: CURSOR get_node_posref
3334: IS
3335: SELECT position_ref_meaning
3336: FROM ahl_mc_relationships_v
3337: WHERE relationship_id = p_counter_rule_rec.relationship_id;
3338:
3339: -- Define cursor check_uom_rule to check whether the same combination of UOM and rule
3340: -- already exists for the node or not
3576: (
3577: -- Establish parent-child relationship between subconfiguration associations
3578: -- and the MC to which they are associated
3579: SELECT submc.mc_header_id child, node.mc_header_id parent
3580: FROM ahl_mc_config_relations submc, ahl_mc_relationships node
3581: WHERE submc.relationship_id = node.relationship_id
3582: CONNECT BY node.mc_header_id = PRIOR submc.mc_header_id
3583: START WITH node.mc_header_id = p_subconfig_id
3584: ) submc_tree, ahl_mc_relationships mc_node
3580: FROM ahl_mc_config_relations submc, ahl_mc_relationships node
3581: WHERE submc.relationship_id = node.relationship_id
3582: CONNECT BY node.mc_header_id = PRIOR submc.mc_header_id
3583: START WITH node.mc_header_id = p_subconfig_id
3584: ) submc_tree, ahl_mc_relationships mc_node
3585: WHERE submc_tree.child = mc_node.mc_header_id AND
3586: mc_node.relationship_id = p_rel_id;
3587:
3588: -- Define cursor get_node_mc_details to read detail of the MC of a MC node
3588: -- Define cursor get_node_mc_details to read detail of the MC of a MC node
3589: CURSOR get_node_mc_details
3590: IS
3591: SELECT mch.name
3592: FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
3593: WHERE mch.mc_header_id = mcr.mc_header_id AND
3594: mcr.relationship_id = p_rel_id;
3595:
3596: -- Define cursor get_mc_details to read detail of a MC
3654:
3655: CURSOR get_node_mc_status
3656: IS
3657: SELECT mch.config_status_code
3658: FROM ahl_mc_headers_v mch, ahl_mc_relationships mcr
3659: WHERE mch.mc_header_id = mcr.mc_header_id AND
3660: mcr.relationship_id = p_rel_id;
3661:
3662: l_status VARCHAR2(30);
3728: IS
3729: SELECT 'X' FROM ahl_mc_config_relations
3730: WHERE mc_header_id = p_dest_config_id
3731: AND relationship_id IN
3732: ( SELECT relationship_id from ahl_mc_relationships
3733: WHERE mc_header_id = p_subconfig_id
3734: START WITH parent_relationship_id IS NULL
3735: CONNECT BY parent_relationship_id = prior relationship_id);
3736:
3747: -- get the next level of subconfigs
3748: SELECT mc_header_id bulk collect
3749: INTO l_subconfigs_table
3750: FROM ahl_mc_config_relations WHERE relationship_id IN
3751: ( SELECT relationship_id FROM ahl_mc_relationships
3752: WHERE mc_header_id = p_subconfig_id
3753: START WITH parent_relationship_id IS NULL
3754: CONNECT BY parent_relationship_id = prior relationship_id );
3755: IF ( l_subconfigs_table.COUNT > 0 ) THEN
3774: p_rel_id in number
3775: )
3776: IS
3777: SELECT mch.mc_header_id, mch.config_status_code
3778: FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
3779: WHERE mch.mc_header_id = mcr.mc_header_id AND
3780: mcr.relationship_id = p_rel_id;
3781:
3782: l_mc_header_id NUMBER;
3927: -- Define cursor get_node_posref to read the position reference of the MC node, used for displaying errors
3928: CURSOR get_node_posref
3929: IS
3930: SELECT position_ref_meaning
3931: FROM ahl_mc_relationships_v
3932: WHERE relationship_id = p_x_counter_rule_rec.relationship_id;
3933:
3934: -- Define cursor check_uom_rule to check whether the same combination of UOM and rule
3935: -- already exists for the node or not
4079: -- Define check_root_node to check whether the node to which subconfiguration is being associated is not a topnode of a MC
4080: CURSOR check_root_node
4081: IS
4082: SELECT 'x'
4083: FROM ahl_mc_relationships
4084: WHERE parent_relationship_id is null AND
4085: relationship_id = p_x_subconfig_rec.relationship_id;
4086:
4087: -- Define check_leaf_node to check whether the node to which subconfiguration is being associated is a leaf node
4087: -- Define check_leaf_node to check whether the node to which subconfiguration is being associated is a leaf node
4088: CURSOR check_leaf_node
4089: IS
4090: SELECT 'x'
4091: FROM ahl_mc_relationships
4092: WHERE parent_relationship_id = p_x_subconfig_rec.relationship_id AND
4093: G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
4094:
4095: -- Define a cursor to get the MC header id , when a relationship id is given
4095: -- Define a cursor to get the MC header id , when a relationship id is given
4096: CURSOR get_dest_header_id(p_dest_rel_id in number)
4097: IS
4098: SELECT mc_header_id
4099: FROM ahl_mc_relationships
4100: WHERE relationship_id = p_dest_rel_id;
4101: -- Define cursor get_node_mc_details to read detail of the MC of a MC node
4102: CURSOR get_node_mc_details(p_dest_rel_id in number)
4103: IS
4101: -- Define cursor get_node_mc_details to read detail of the MC of a MC node
4102: CURSOR get_node_mc_details(p_dest_rel_id in number)
4103: IS
4104: SELECT mch.name
4105: FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
4106: WHERE mch.mc_header_id = mcr.mc_header_id AND
4107: mcr.relationship_id = p_dest_rel_id;
4108: -- Define cursor get_mc_details to read detail of a MC
4109: CURSOR get_mc_details(p_subconfig_id in number )
4680: -- Define a cursor to get the MC header id , when a relationship id is given
4681: CURSOR get_dest_header_id
4682: IS
4683: SELECT mc_header_id
4684: FROM ahl_mc_relationships
4685: WHERE relationship_id = p_dest_rel_id;
4686: -- Define cursor get_node_mc_details to read detail of the MC of a MC node
4687: CURSOR get_node_mc_details
4688: IS
4686: -- Define cursor get_node_mc_details to read detail of the MC of a MC node
4687: CURSOR get_node_mc_details
4688: IS
4689: SELECT mch.name
4690: FROM ahl_mc_headers_b mch, ahl_mc_relationships mcr
4691: WHERE mch.mc_header_id = mcr.mc_header_id AND
4692: mcr.relationship_id = p_dest_rel_id;
4693: -- Define cursor get_mc_details to read detail of a MC
4694: CURSOR get_mc_details(p_subconfig_id in number)
4711: p_rel_id in number
4712: )
4713: IS
4714: SELECT 'x'
4715: FROM ahl_mc_relationships
4716: WHERE parent_relationship_id = p_rel_id AND
4717: G_TRUNC_DATE < trunc(nvl(active_end_date, G_SYSDATE + 1));
4718:
4719: -- Declare local variables