[Home] [Help]
120: -- Bug 2300962. Removed child_collection_id in the where clause
121: -- To check, child is entered or not child plan id is enough.
122:
123: CURSOR c2(c_child_plan_id NUMBER) IS
124: SELECT 'T' FROM qa_pc_results_relationship
125: WHERE parent_plan_id = p_plan_id
126: AND parent_collection_id = p_collection_id
127: AND parent_occurrence = p_occurrence
128: AND child_plan_id = c_child_plan_id
204:
205: SELECT child_plan_id, child_collection_id, child_occurrence
206: BULK COLLECT INTO
207: x_plan_ids, x_collection_ids, x_occurrences
208: FROM qa_pc_results_relationship r
209: WHERE EXISTS (
210: SELECT 1
211: FROM qa_results qr
212: WHERE qr.plan_id = r.child_plan_id AND
990: l_exists VARCHAR2(1);
991:
992: CURSOR descendant_cur IS
993: SELECT 'T'
994: FROM qa_pc_results_relationship
995: WHERE parent_occurrence = p_occurrence
996: AND rownum = 1;
997:
998:
1027: BEGIN
1028: SELECT child_plan_id, child_collection_id, child_occurrence
1029: BULK COLLECT INTO
1030: x_plan_ids, x_collection_ids, x_occurrences
1031: FROM qa_pc_results_relationship r
1032: WHERE EXISTS (
1033: SELECT 1
1034: FROM qa_results qr
1035: WHERE qr.plan_id = r.child_plan_id AND
1100: AND collection_id = p_collection_ids(i)
1101: AND occurrence = p_occurrences(i);
1102:
1103: FORALL i IN p_occurrences.FIRST .. p_occurrences.LAST
1104: DELETE from QA_PC_RESULTS_RELATIONSHIP
1105: WHERE child_occurrence = p_occurrences(i);
1106: END delete_child_rows;
1107:
1108:
1717: FOR cur_rec IN element_cursor LOOP
1718:
1719: -- build the required sql string
1720:
1721: l_sql_string := 'FROM qa_results qr, qa_pc_results_relationship pc'
1722: || ' WHERE qr.plan_id=pc.child_plan_id'
1723: || ' AND qr.collection_id=pc.child_collection_id'
1724: || ' AND qr.occurrence=pc.child_occurrence'
1725: || ' AND pc.parent_occurrence= :p_parent_occurrence'
3022: CURSOR children_cur IS
3023: select qprr.child_plan_id,
3024: qprr.child_collection_id,
3025: qprr.child_occurrence
3026: from qa_pc_results_relationship qprr,
3027: qa_pc_plan_relationship qpr
3028: where qprr.parent_occurrence = p_parent_occurrence
3029: and qprr.parent_plan_id = p_parent_plan_id
3030: and qprr.parent_collection_id = p_parent_collection_id
3391: FOR cur_rec IN element_cursor LOOP
3392:
3393: -- build the required sql string
3394:
3395: l_sql_string := 'FROM qa_results qr, qa_pc_results_relationship pc'
3396: || ' WHERE qr.plan_id=pc.child_plan_id'
3397: || ' AND qr.collection_id=pc.child_collection_id'
3398: || ' AND qr.occurrence=pc.child_occurrence'
3399: || ' AND pc.parent_occurrence= :p_parent_occurrence'
3609:
3610: END insert_history_auto_rec_QWB;
3611:
3612: -- The following procedure was added to remove the entry
3613: -- from QA_PC_RESULTS_RELATIONSHIP table when the user
3614: -- deletes the record from the child plan and saves the
3615: -- child plan. This procedure is called from procedure
3616: -- key_delete_dependent_rows in QLTRES.pld.
3617: -- Bug 3646166.suramasw.
3620: p_child_occurrence IN NUMBER) IS
3621:
3622: BEGIN
3623:
3624: DELETE FROM qa_pc_results_relationship
3625: WHERE child_plan_id = p_child_plan_id
3626: AND child_occurrence = p_child_occurrence;
3627:
3628: END;
3652: CURSOR c1 IS
3653: SELECT parent_plan_id,
3654: parent_collection_id,
3655: parent_occurrence
3656: FROM QA_PC_RESULTS_RELATIONSHIP
3657: WHERE child_plan_id = p_plan_id
3658: AND child_collection_id = p_collection_id
3659: AND child_occurrence = p_occurrence;
3660:
3785:
3786: l_api_name CONSTANT VARCHAR2(40) := 'CREATE_RELATIONSHIP ()';
3787:
3788: BEGIN
3789: INSERT INTO QA_PC_RESULTS_RELATIONSHIP (PARENT_PLAN_ID,
3790: PARENT_COLLECTION_ID,
3791: PARENT_OCCURRENCE,
3792: CHILD_PLAN_ID,
3793: CHILD_COLLECTION_ID,
3990: -- get history plan id
3991:
3992: FOR hst_rec IN c(p_plan_id) LOOP
3993:
3994: INSERT INTO QA_PC_RESULTS_RELATIONSHIP (
3995: PARENT_PLAN_ID,
3996: PARENT_COLLECTION_ID,
3997: PARENT_OCCURRENCE,
3998: CHILD_PLAN_ID ,
4065: ' QR.ORGANIZATION_ID, ' ||
4066: ' QPRR.CHILD_PLAN_ID, ' ||
4067: ' QPRR.CHILD_TXN_HEADER_ID, ' ||
4068: l_src_string || ' ' ||
4069: ' FROM QA_RESULTS QR, QA_PC_RESULTS_RELATIONSHIP QPRR ' ||
4070: ' WHERE QPRR.CHILD_PLAN_ID = :1 ' ||
4071: ' AND QPRR.CHILD_COLLECTION_ID = :2 ' ||
4072: ' AND QPRR.PARENT_PLAN_ID = :3 ' ||
4073: ' AND QPRR.PARENT_COLLECTION_ID = :4 ' ||
4137: -- These are returned in the three output PL/SQL tables.
4138: -- The child record itself is not included in the output.
4139: SELECT parent_plan_id, parent_collection_id, parent_occurrence
4140: BULK COLLECT INTO x_parent_plan_ids, x_parent_collection_ids, x_parent_occurrences
4141: FROM qa_pc_results_relationship
4142: START WITH child_plan_id = p_child_plan_id
4143: AND child_occurrence = p_child_occurrence
4144: AND child_collection_id = p_child_collection_id
4145: CONNECT BY PRIOR parent_occurrence = child_occurrence;
4235: --
4236: PROCEDURE delete_invalid_children(p_txn_header_id IN NUMBER) IS
4237: PRAGMA AUTONOMOUS_TRANSACTION;
4238:
4239: TYPE child_plan_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_plan_id%TYPE
4240: INDEX BY BINARY_INTEGER;
4241: TYPE child_collection_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_collection_id%TYPE
4242: INDEX BY BINARY_INTEGER;
4243: TYPE child_occurrence_tab_typ IS TABLE OF qa_pc_results_relationship.child_occurrence%TYPE
4237: PRAGMA AUTONOMOUS_TRANSACTION;
4238:
4239: TYPE child_plan_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_plan_id%TYPE
4240: INDEX BY BINARY_INTEGER;
4241: TYPE child_collection_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_collection_id%TYPE
4242: INDEX BY BINARY_INTEGER;
4243: TYPE child_occurrence_tab_typ IS TABLE OF qa_pc_results_relationship.child_occurrence%TYPE
4244: INDEX BY BINARY_INTEGER;
4245:
4239: TYPE child_plan_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_plan_id%TYPE
4240: INDEX BY BINARY_INTEGER;
4241: TYPE child_collection_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_collection_id%TYPE
4242: INDEX BY BINARY_INTEGER;
4243: TYPE child_occurrence_tab_typ IS TABLE OF qa_pc_results_relationship.child_occurrence%TYPE
4244: INDEX BY BINARY_INTEGER;
4245:
4246: child_plan_id_tab child_plan_id_tab_typ;
4247: child_collection_id_tab child_collection_id_tab_typ;
4256: child_collection_id_tab,
4257: child_occurrence_tab;
4258:
4259: FORALL cntr in 1..child_plan_id_tab.COUNT
4260: DELETE from qa_pc_results_relationship
4261: WHERE child_txn_header_id = p_txn_header_id
4262: AND child_plan_id = child_plan_id_tab(cntr)
4263: AND child_collection_id = child_collection_id_tab(cntr)
4264: AND child_occurrence = child_occurrence_tab(cntr);
4348: -- ntungare
4349: --
4350: CURSOR cur is
4351: select 'UPDATE_CHILD_Y'
4352: from qa_pc_results_relationship qpc,
4353: qa_results qr,
4354: qa_pc_plan_relationship qpr
4355: where qpc.parent_plan_id = p_plan_id and
4356: qpc.parent_collection_id = p_collection_id and
4396:
4397: childCount NUMBER := 0;
4398: BEGIN
4399: SELECT count(*) INTO childCount
4400: FROM qa_pc_results_relationship qpc,
4401: qa_results qr
4402: WHERE qpc.parent_plan_id = p_plan_id and
4403: qpc.parent_collection_id = p_collection_id and
4404: qpc.parent_occurrence = p_occurrence and
4443: CURSOR children_cur IS
4444: select qprr.child_plan_id,
4445: qprr.child_collection_id,
4446: qprr.child_occurrence
4447: from qa_pc_results_relationship qprr,
4448: qa_pc_plan_relationship qpr
4449: where qprr.parent_occurrence = p_parent_occurrence
4450: and qprr.parent_plan_id = p_parent_plan_id
4451: and qprr.parent_collection_id = p_parent_collection_id
4495: AND qr.plan_id = qapc.plan_id
4496: AND qppr.parent_plan_id = qr.plan_id
4497: AND EXISTS
4498: (SELECT 1 FROM
4499: qa_pc_results_relationship qprr
4500: WHERE qppr.child_plan_id = qprr.child_plan_id
4501: AND qppr.parent_plan_id = qprr.parent_plan_id
4502: AND qppr.child_plan_id = qprr.child_plan_id
4503: AND qprr.parent_plan_id = qr.plan_id