[Home] [Help]
129: -- Bug 2300962. Removed child_collection_id in the where clause
130: -- To check, child is entered or not child plan id is enough.
131:
132: CURSOR c2(c_child_plan_id NUMBER) IS
133: SELECT 'T' FROM qa_pc_results_relationship
134: WHERE parent_plan_id = p_plan_id
135: AND parent_collection_id = p_collection_id
136: AND parent_occurrence = p_occurrence
137: AND child_plan_id = c_child_plan_id
213:
214: SELECT child_plan_id, child_collection_id, child_occurrence
215: BULK COLLECT INTO
216: x_plan_ids, x_collection_ids, x_occurrences
217: FROM qa_pc_results_relationship r
218: WHERE EXISTS (
219: SELECT 1
220: FROM qa_results qr
221: WHERE qr.plan_id = r.child_plan_id AND
1063: l_exists VARCHAR2(1);
1064:
1065: CURSOR descendant_cur IS
1066: SELECT 'T'
1067: FROM qa_pc_results_relationship
1068: WHERE parent_occurrence = p_occurrence
1069: AND rownum = 1;
1070:
1071:
1100: BEGIN
1101: SELECT child_plan_id, child_collection_id, child_occurrence
1102: BULK COLLECT INTO
1103: x_plan_ids, x_collection_ids, x_occurrences
1104: FROM qa_pc_results_relationship r
1105: WHERE EXISTS (
1106: SELECT 1
1107: FROM qa_results qr
1108: WHERE qr.plan_id = r.child_plan_id AND
1173: AND collection_id = p_collection_ids(i)
1174: AND occurrence = p_occurrences(i);
1175:
1176: FORALL i IN p_occurrences.FIRST .. p_occurrences.LAST
1177: DELETE from QA_PC_RESULTS_RELATIONSHIP
1178: WHERE child_occurrence = p_occurrences(i);
1179: END delete_child_rows;
1180:
1181:
1802: FOR cur_rec IN element_cursor LOOP
1803:
1804: -- build the required sql string
1805:
1806: l_sql_string := 'FROM qa_results qr, qa_pc_results_relationship pc'
1807: || ' WHERE qr.plan_id=pc.child_plan_id'
1808: || ' AND qr.collection_id=pc.child_collection_id'
1809: || ' AND qr.occurrence=pc.child_occurrence'
1810: || ' AND pc.parent_occurrence= :p_parent_occurrence'
3339: CURSOR children_cur IS
3340: select qprr.child_plan_id,
3341: qprr.child_collection_id,
3342: qprr.child_occurrence
3343: from qa_pc_results_relationship qprr,
3344: qa_pc_plan_relationship qpr
3345: where qprr.parent_occurrence = p_parent_occurrence
3346: and qprr.parent_plan_id = p_parent_plan_id
3347: and qprr.parent_collection_id = p_parent_collection_id
3755: FOR cur_rec IN element_cursor LOOP
3756:
3757: -- build the required sql string
3758:
3759: l_sql_string := 'FROM qa_results qr, qa_pc_results_relationship pc'
3760: || ' WHERE qr.plan_id=pc.child_plan_id'
3761: || ' AND qr.collection_id=pc.child_collection_id'
3762: || ' AND qr.occurrence=pc.child_occurrence'
3763: || ' AND pc.parent_occurrence= :p_parent_occurrence'
3973:
3974: END insert_history_auto_rec_QWB;
3975:
3976: -- The following procedure was added to remove the entry
3977: -- from QA_PC_RESULTS_RELATIONSHIP table when the user
3978: -- deletes the record from the child plan and saves the
3979: -- child plan. This procedure is called from procedure
3980: -- key_delete_dependent_rows in QLTRES.pld.
3981: -- Bug 3646166.suramasw.
3984: p_child_occurrence IN NUMBER) IS
3985:
3986: BEGIN
3987:
3988: DELETE FROM qa_pc_results_relationship
3989: WHERE child_plan_id = p_child_plan_id
3990: AND child_occurrence = p_child_occurrence;
3991:
3992: END;
4016: CURSOR c1 IS
4017: SELECT parent_plan_id,
4018: parent_collection_id,
4019: parent_occurrence
4020: FROM QA_PC_RESULTS_RELATIONSHIP
4021: WHERE child_plan_id = p_plan_id
4022: AND child_collection_id = p_collection_id
4023: AND child_occurrence = p_occurrence;
4024:
4149:
4150: l_api_name CONSTANT VARCHAR2(40) := 'CREATE_RELATIONSHIP ()';
4151:
4152: BEGIN
4153: INSERT INTO QA_PC_RESULTS_RELATIONSHIP (PARENT_PLAN_ID,
4154: PARENT_COLLECTION_ID,
4155: PARENT_OCCURRENCE,
4156: CHILD_PLAN_ID,
4157: CHILD_COLLECTION_ID,
4354: -- get history plan id
4355:
4356: FOR hst_rec IN c(p_plan_id) LOOP
4357:
4358: INSERT INTO QA_PC_RESULTS_RELATIONSHIP (
4359: PARENT_PLAN_ID,
4360: PARENT_COLLECTION_ID,
4361: PARENT_OCCURRENCE,
4362: CHILD_PLAN_ID ,
4429: ' QR.ORGANIZATION_ID, ' ||
4430: ' QPRR.CHILD_PLAN_ID, ' ||
4431: ' QPRR.CHILD_TXN_HEADER_ID, ' ||
4432: l_src_string || ' ' ||
4433: ' FROM QA_RESULTS QR, QA_PC_RESULTS_RELATIONSHIP QPRR ' ||
4434: ' WHERE QPRR.CHILD_PLAN_ID = :1 ' ||
4435: ' AND QPRR.CHILD_COLLECTION_ID = :2 ' ||
4436: ' AND QPRR.PARENT_PLAN_ID = :3 ' ||
4437: ' AND QPRR.PARENT_COLLECTION_ID = :4 ' ||
4501: -- These are returned in the three output PL/SQL tables.
4502: -- The child record itself is not included in the output.
4503: SELECT parent_plan_id, parent_collection_id, parent_occurrence
4504: BULK COLLECT INTO x_parent_plan_ids, x_parent_collection_ids, x_parent_occurrences
4505: FROM qa_pc_results_relationship
4506: START WITH child_plan_id = p_child_plan_id
4507: AND child_occurrence = p_child_occurrence
4508: AND child_collection_id = p_child_collection_id
4509: CONNECT BY PRIOR parent_occurrence = child_occurrence;
4599: --
4600: PROCEDURE delete_invalid_children(p_txn_header_id IN NUMBER) IS
4601: PRAGMA AUTONOMOUS_TRANSACTION;
4602:
4603: TYPE child_plan_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_plan_id%TYPE
4604: INDEX BY BINARY_INTEGER;
4605: TYPE child_collection_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_collection_id%TYPE
4606: INDEX BY BINARY_INTEGER;
4607: TYPE child_occurrence_tab_typ IS TABLE OF qa_pc_results_relationship.child_occurrence%TYPE
4601: PRAGMA AUTONOMOUS_TRANSACTION;
4602:
4603: TYPE child_plan_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_plan_id%TYPE
4604: INDEX BY BINARY_INTEGER;
4605: TYPE child_collection_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_collection_id%TYPE
4606: INDEX BY BINARY_INTEGER;
4607: TYPE child_occurrence_tab_typ IS TABLE OF qa_pc_results_relationship.child_occurrence%TYPE
4608: INDEX BY BINARY_INTEGER;
4609:
4603: TYPE child_plan_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_plan_id%TYPE
4604: INDEX BY BINARY_INTEGER;
4605: TYPE child_collection_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_collection_id%TYPE
4606: INDEX BY BINARY_INTEGER;
4607: TYPE child_occurrence_tab_typ IS TABLE OF qa_pc_results_relationship.child_occurrence%TYPE
4608: INDEX BY BINARY_INTEGER;
4609:
4610: child_plan_id_tab child_plan_id_tab_typ;
4611: child_collection_id_tab child_collection_id_tab_typ;
4620: child_collection_id_tab,
4621: child_occurrence_tab;
4622:
4623: FORALL cntr in 1..child_plan_id_tab.COUNT
4624: DELETE from qa_pc_results_relationship
4625: WHERE child_txn_header_id = p_txn_header_id
4626: AND child_plan_id = child_plan_id_tab(cntr)
4627: AND child_collection_id = child_collection_id_tab(cntr)
4628: AND child_occurrence = child_occurrence_tab(cntr);
4759: -- ntungare
4760: --
4761: CURSOR cur is
4762: select 'UPDATE_CHILD_Y'
4763: from qa_pc_results_relationship qpc,
4764: qa_results qr,
4765: qa_pc_plan_relationship qpr
4766: where qpc.parent_plan_id = p_plan_id and
4767: qpc.parent_collection_id = p_collection_id and
4807:
4808: childCount NUMBER := 0;
4809: BEGIN
4810: SELECT count(*) INTO childCount
4811: FROM qa_pc_results_relationship qpc,
4812: qa_results qr
4813: WHERE qpc.parent_plan_id = p_plan_id and
4814: qpc.parent_collection_id = p_collection_id and
4815: qpc.parent_occurrence = p_occurrence and
4854: CURSOR children_cur IS
4855: select qprr.child_plan_id,
4856: qprr.child_collection_id,
4857: qprr.child_occurrence
4858: from qa_pc_results_relationship qprr,
4859: qa_pc_plan_relationship qpr
4860: where qprr.parent_occurrence = p_parent_occurrence
4861: and qprr.parent_plan_id = p_parent_plan_id
4862: and qprr.parent_collection_id = p_parent_collection_id
4906: AND qr.plan_id = qapc.plan_id
4907: AND qppr.parent_plan_id = qr.plan_id
4908: AND EXISTS
4909: (SELECT 1 FROM
4910: qa_pc_results_relationship qprr
4911: WHERE qppr.child_plan_id = qprr.child_plan_id
4912: AND qppr.parent_plan_id = qprr.parent_plan_id
4913: AND qppr.child_plan_id = qprr.child_plan_id
4914: AND qprr.parent_plan_id = qr.plan_id