[Home] [Help]
174: PROCEDURE enable_and_fire_actions(p_collection_id NUMBER) IS
175:
176: ---
177: --- This procedure commits all the records corresponding to one single session with status = 2.
178: --- This is required since all the child records will be saved with qa_results.status =1
179: --- When the child record is enabled, status code will be changed to 2. The status of
180: --- child record is changed to 2 only when Parent record gets committed
181: ---
182:
180: --- child record is changed to 2 only when Parent record gets committed
181: ---
182:
183: BEGIN
184: qa_results_api.enable_and_fire_action(p_collection_id);
185: END enable_and_fire_actions;
186:
187: FUNCTION get_descendants(
188: p_plan_id NUMBER,
196: --- Given a parent record (plan/collection/occurrence), this procedure finds all the child and
197: --- grandchildren records (therefore, descendants) of the record. These are returned in the
198: --- three output PL/SQL tables. The parent record itself is not included in the output.
199: --- The query technical is called hierarchical subquery. The final where clause makes
200: --- sure the child record is actually enabled in the qa_results table.
201: ---
202:
203: BEGIN
204:
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
213: qr.collection_id = r.child_collection_id AND
214: qr.occurrence = r.child_occurrence AND
215: (qr.status IS NULL or qr.status=2) )
570: PROCEDURE parse_list(x_result IN VARCHAR2,
571: x_array OUT NOCOPY ChildPlanArray) IS
572:
573: -- For longcomments enhancement, Bug 2234299
574: -- changed 'value' type from qa_results.character1%TYPE to varchar2(2000)
575: -- rponnusa Thu Mar 14 21:27:04 PST 2002
576:
577: value VARCHAR2(2000);
578: c VARCHAR2(10);
650:
651: -- anagarwa Mon Dec 16 16:55:09 PST 2002
652: -- Bug 2701777
653: -- if parent or child elements are disabled and the parent child relationship
654: -- still exists for them then insert API qa_mqa_results.post_result raises
655: -- returns an error and prevents the history as well as automatic results
656: -- from being saved. It causes a ON-INSERT trigger being raised on forms
657: -- and even the parent results cannot be saved.
658: -- To fix the problem, qa_pc_result_column_v is being modified to have parent
843:
844: -- anagarwa Thu Dec 19 15:43:27 PST 2002
845: -- Bug 2701777
846: -- post_result_with_no_validation inserts records into
847: -- qa_results without any validations. This prevents any
848: -- errors if user changes element values in parent plan
849: -- but not the History plan
850:
851: --
852: -- bug 5383667
853: -- Passing the Id string as well
854: -- ntungare
855: --
856: l_return_int:= qa_mqa_results.post_result_with_no_validation(
857: l_occurrence,
858: p_org_id,
859: l_child_plan_id, p_spec_id,
860: p_collection_id,
874: -- following added to copy attachments to History records.
875:
876: IF p_data_entry_mode = 4 THEN
877: FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
878: X_from_entity_name => 'QA_RESULTS',
879: X_from_pk1_value => to_char(p_occurrence),
880: X_from_pk2_value => to_char(p_collection_id),
881: X_from_pk3_value => to_char(p_plan_id),
882: X_to_entity_name => 'QA_RESULTS',
878: X_from_entity_name => 'QA_RESULTS',
879: X_from_pk1_value => to_char(p_occurrence),
880: X_from_pk2_value => to_char(p_collection_id),
881: X_from_pk3_value => to_char(p_plan_id),
882: X_to_entity_name => 'QA_RESULTS',
883: X_to_pk1_value => to_char(l_occurrence),
884: X_to_pk2_value => to_char(p_collection_id),
885: X_to_pk3_value => to_char(l_child_plan_id));
886: END IF;
921: -- nutngare Thu Jul 5 05:21:16 PDT 2007
922: --
923: SELECT MAX(occurrence)
924: into l_child_occurrence
925: FROM qa_results
926: WHERE plan_id = l_child_plan_id and
927: collection_id = p_collection_id and
928: organization_id = p_org_id and
929: txn_header_id = p_txn_header_id;
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
1036: qr.collection_id = r.child_collection_id AND
1037: qr.occurrence = r.child_occurrence AND
1038: qr.status = 1 )
1063:
1064: IS
1065: ---
1066: --- The following procedure takes in plan_id, collection id and occurrece and
1067: --- deletes these rows from QA_RESULTS. It also deletes entry for these rows
1068: --- from relationships tables.
1069: ---
1070: --- p_enabled_flag holds 'T' => delete only enabled child records
1071: --- 'F' => delete only disabled child records
1094: p_parent_occurrence);
1095: END IF;
1096:
1097: FORALL i IN p_occurrences.FIRST .. p_occurrences.LAST
1098: DELETE from QA_RESULTS
1099: WHERE plan_id = p_plan_ids(i)
1100: AND collection_id = p_collection_ids(i)
1101: AND occurrence = p_occurrences(i);
1102:
1111:
1112: BEGIN
1113:
1114: IF p_txn_header_id is not null THEN
1115: UPDATE qa_results
1116: SET status = 2
1117: WHERE txn_header_id = p_txn_header_id;
1118:
1119: flag := QLTDACTB.DO_ACTIONS(p_txn_header_id, 1, NULL, NULL,
1138:
1139: BEGIN
1140: IF p_txn_header_id is not null THEN
1141:
1142: -- Updating the rows in the QA_RESULTS which are currently
1143: -- invalid
1144: --
1145: UPDATE qa_results
1146: SET status = 2
1141:
1142: -- Updating the rows in the QA_RESULTS which are currently
1143: -- invalid
1144: --
1145: UPDATE qa_results
1146: SET status = 2
1147: WHERE txn_header_id = p_txn_header_id
1148: and status =1
1149: RETURNING plan_id, collection_id, occurrence
1190: -- is passed. First find out parent_plan_id from qa_pc_plan_relationship. Then findout all
1191: -- element ids with which parent and child plans are related. Take only those elements which have
1192: -- link_flag = 1 in qa_pc_element_relationship table.
1193:
1194: -- Find the values of the elements from qa_results for the child plan. Then find the first record
1195: -- for the parent plan which has all the elements (only those related in the qa_pc_element_relation)
1196: -- same value for those of child plan. Return the parent record information.
1197:
1198: l_plan_relationship_id NUMBER;
1197:
1198: l_plan_relationship_id NUMBER;
1199: l_parent_plan_id NUMBER;
1200: l_temp_var NUMBER;
1201: l_res_col VARCHAR2(150); -- stores result column name in qa_results
1202: l_res_value VARCHAR2(150); -- stores result column value in qa_results
1203:
1204: query_clause VARCHAR2(32000):= NULL;
1205: select_clause VARCHAR2(80) := NULL;
1198: l_plan_relationship_id NUMBER;
1199: l_parent_plan_id NUMBER;
1200: l_temp_var NUMBER;
1201: l_res_col VARCHAR2(150); -- stores result column name in qa_results
1202: l_res_value VARCHAR2(150); -- stores result column value in qa_results
1203:
1204: query_clause VARCHAR2(32000):= NULL;
1205: select_clause VARCHAR2(80) := NULL;
1206: from_clause CONSTANT VARCHAR2(80) := ' FROM QA_RESULTS ';
1202: l_res_value VARCHAR2(150); -- stores result column value in qa_results
1203:
1204: query_clause VARCHAR2(32000):= NULL;
1205: select_clause VARCHAR2(80) := NULL;
1206: from_clause CONSTANT VARCHAR2(80) := ' FROM QA_RESULTS ';
1207: where_clause VARCHAR2(5000) := NULL;
1208: parent_where_clause VARCHAR2(5000):= NULL;
1209:
1210:
1395:
1396: l_child_txn_header_id NUMBER;
1397:
1398: CURSOR c IS
1399: SELECT txn_header_id FROM qa_results
1400: WHERE plan_id = p_child_plan_id AND
1401: collection_id = p_child_collection_id AND
1402: occurrence = p_child_occurrence;
1403:
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'
1732: --
1733: || ' AND (qr.status = 2 OR qr.status IS NULL)';
1734:
1735: -- Bug 2427337. Fix here is not related this bug. To use aggregate functions
1736: -- on a element which is stored in character col in qa_results table, we need
1737: -- to use to_number function, or else, unwanted value will be returned.
1738: -- rponnusa Tue Jun 25 06:15:48 PDT 2002
1739:
1740: IF (cur_rec.element_relationship_type = 2 ) THEN -- sum
1797: -- bhsankar Sun Sep 30 23:38:58 PDT 2007
1798: --
1799: l_select_sql := 'SELECT '
1800: || cur_rec.parent_database_column
1801: || ' FROM qa_results WHERE plan_id = :p_parent_plan_id'
1802: || ' AND collection_id= :p_parent_collection_id'
1803: || ' AND occurrence= :p_parent_occurrence FOR UPDATE NOWAIT';
1804:
1805: BEGIN
1807: USING p_parent_plan_id,p_parent_collection_id,p_parent_occurrence;
1808:
1809: -- now we need to update the parent record. Build the sql here.
1810:
1811: l_update_parent_sql := 'UPDATE qa_results SET '
1812: || cur_rec.parent_database_column || ' = :l_value'
1813: || ' WHERE plan_id= :p_parent_plan_id'
1814: || ' AND collection_id= :p_parent_collection_id'
1815: || ' AND occurrence= :p_parent_occurrence';
1958:
1959: --
1960: -- bug 6266439
1961: -- New procedure to peform the date conversions while
1962: -- selecting and updating the data in the QA_RESULTS
1963: -- table, while peforming a Child record update.
1964: -- ntungare Thu Aug 2 03:32:32 PDT 2007
1965: --
1966: PROCEDURE DATE_SELECT_UPDATE(p_parent_result_column IN VARCHAR2,
2103:
2104: --
2105: -- bug 6266439
2106: -- New variable to hold the name of the column
2107: -- to be selected from the QA_RESULTS table
2108: -- ntungare Thu Aug 2 03:40:42 PDT 2007
2109: --
2110: select_column varchar2(2000);
2111:
2109: --
2110: select_column varchar2(2000);
2111:
2112: -- New variable to hold the bind variable
2113: -- to be updated in the QA_RESULTS table
2114: update_column varchar2(2000);
2115:
2116: BEGIN
2117: For element_cntr in 1..p_parentchild_element_tab.count
2155: --
2156: -- bug 6266477
2157: -- Added the following to execute the query
2158: -- to fetch all the parent result column values
2159: -- in a single hit to qa_results table
2160: -- The string is built as 1=
2161: -- so that the result_to_array can be reused to collect into an array.
2162: -- skolluku Sun Oct 14 03:26:31 PDT 2007
2163: --
2175: -- ntungare Thu Aug 2 03:42:18 PDT 2007
2176: --
2177: /*
2178: l_sql_string := 'SELECT ' || p_parentchild_element_tab(element_cntr).parent_database_column
2179: || ' FROM qa_results '
2180: || ' WHERE plan_id= :p_parent_plan_id'
2181: || ' AND collection_id= :p_parent_collection_id'
2182: || ' AND occurrence= :p_parent_occurrence';
2183: */
2189: -- skolluku Sun Oct 14 03:26:31 PDT 2007
2190: --
2191: /*
2192: l_sql_string := 'SELECT ' || NVL(select_column, p_parentchild_element_tab(element_cntr).parent_database_column)
2193: || ' FROM qa_results '
2194: || ' WHERE plan_id= :p_parent_plan_id'
2195: || ' AND collection_id= :p_parent_collection_id'
2196: || ' AND occurrence= :p_parent_occurrence';
2197:
2248:
2249: --
2250: -- bug 6266477
2251: -- Execute the select statement here to hit the table
2252: -- QA_RESULTS only once to improve performance and get
2253: -- the values into anl_bind_var array.
2254: -- skolluku Sun Oct 14 03:26:31 PDT 2007
2255: --
2256: l_sql_string := 'Select ' || l_sql_string
2253: -- the values into anl_bind_var array.
2254: -- skolluku Sun Oct 14 03:26:31 PDT 2007
2255: --
2256: l_sql_string := 'Select ' || l_sql_string
2257: || ' FROM qa_results '
2258: || ' WHERE plan_id= :p_parent_plan_id'
2259: || ' AND collection_id= :p_parent_collection_id'
2260: || ' AND occurrence= :p_parent_occurrence';
2261: BEGIN
2266: END;
2267:
2268: l_bind_var := qa_txn_grp.result_to_array(l_value);
2269:
2270: l_update_clause := 'UPDATE qa_results SET ' || l_update_clause
2271: || ' WHERE plan_id= :p_child_plan_id'
2272: || ' AND collection_id= :p_child_collection_id'
2273: || ' AND occurrence= :p_child_occurrence';
2274:
2674: history or automatic plans in collection imports, it (histor/automatic
2675: functionality) fails.
2676: By selecting form_field instead of database_column we can fix it.
2677: However, item, comp_item, locator and comp_locator don't exist in
2678: QA_RESULTS_V. So we add special handling for these later.
2679: */
2680: --SELECT char_id,database_column
2681: SELECT char_id, replace(form_field, 'DISPLAY' , 'CHARACTER') database_column,
2682: datatype
2693: l_string VARCHAR2(32000);
2694: l_append BOOLEAN;
2695:
2696: select_clause VARCHAR2(32000);
2697: from_clause CONSTANT VARCHAR2(80) := ' FROM QA_RESULTS_V ';
2698: where_clause VARCHAR2(5000);
2699: query_clause VARCHAR2(32000);
2700: -- anagarwa Tue Jul 16 18:36:52 PDT 2002
2701: -- Bug 2465920: new variable to handle item, comp_item, locator and
2703: column_name VARCHAR2(150);
2704:
2705:
2706: -- Bug 3776542. Performance issue due to use of literals in the SQL to fetch
2707: -- criteria value from QA_RESULTS_V. Earlier we were using reference cursor to
2708: -- fetch the value with a SQL that had literals. After fix, we are using EXECUTE_IMMEDIATE
2709: -- with SQL containing bind variables. This ref cursor is needed no more, hence commenting
2710: -- it out.Thu Jul 29 02:02:03 PDT 2004.
2711: -- srhariha.
2741: ELSIF column_name = 'COMP_LOCATOR' THEN
2742: column_name := 'COMP_LOCATOR_ID';
2743:
2744: -- Bug 2694385. Added bill_reference,routing_reference,to_locator since
2745: -- these elements will not present in qa_results_v.
2746: -- rponnusa Wed Dec 18 05:38:40 PST 2002
2747:
2748: ELSIF column_name = 'BILL_REFERENCE' THEN
2749: column_name := 'BILL_REFERENCE_ID';
2762: END IF;
2763: --
2764: -- bug 6266477
2765: -- Commenting the below code since the handling is done
2766: -- differently to avoid multiple hits to QA_RESULTS_V
2767: -- skolluku Mon Oct 15 02:57:40 PDT 2007
2768: --
2769: /*select_clause := 'SELECT ' || column_name;
2770: query_clause := select_clause || from_clause || where_clause;
2769: /*select_clause := 'SELECT ' || column_name;
2770: query_clause := select_clause || from_clause || where_clause;
2771:
2772: -- Bug 3776542. Performance issue due to use of literals in the SQL to fetch
2773: -- criteria value from QA_RESULTS_V. Earlier we were using reference cursor to
2774: -- fetch the value with a SQL that had literals. After fix, we are using EXECUTE_IMMEDIATE
2775: -- with SQL containing bind variables. This ref cursor is needed no more, hence commenting
2776: -- it out.
2777: -- srhariha.Thu Jul 29 02:02:03 PDT 2004
2823: */
2824: --
2825: -- bug 6266477
2826: -- Added the below code to enhance performance
2827: -- by hitting the view QA_RESULTS_V just once
2828: -- skolluku Mon Oct 15 02:57:40 PDT 2007
2829: --
2830: IF parent_rec.char_id IN (qa_ss_const.item, qa_ss_const.comp_item,
2831: qa_ss_const.routing_reference, qa_ss_const.bill_reference) THEN
2886: AND data_entry_mode = p_data_entry_mode;
2887:
2888: CURSOR res_cur IS
2889: SELECT collection_id,occurrence,organization_id
2890: FROM qa_results
2891: WHERE plan_id = p_parent_plan_id
2892: AND txn_header_id = p_txn_header_id;
2893:
2894:
2980: -- Return true if the given parent record is saved in enable status
2981:
2982: CURSOR c IS
2983: SELECT 1
2984: FROM qa_results
2985: WHERE plan_id = p_plan_id
2986: AND collection_id = p_collection_id
2987: AND occurrence = p_occurrence
2988: AND status = 2;
3274: cnt NUMBER;
3275:
3276: cursor c is
3277: select count(plan_id)
3278: from qa_results
3279: where plan_id = p_plan_id
3280: and txn_header_id = p_txn_header_id;
3281:
3282: BEGIN
3301: ---
3302:
3303: BEGIN
3304:
3305: IF (qa_web_txn_api.allowed_for_plan('QA_RESULTS_VIEW', p_plan_id) = 'T') or
3306: (qa_web_txn_api.allowed_for_plan('QA_RESULTS_DELETE', p_plan_id) = 'T') or
3307: (qa_web_txn_api.allowed_for_plan('QA_RESULTS_UPDATE', p_plan_id) = 'T') THEN
3308: RETURN 'T';
3309: ELSE
3302:
3303: BEGIN
3304:
3305: IF (qa_web_txn_api.allowed_for_plan('QA_RESULTS_VIEW', p_plan_id) = 'T') or
3306: (qa_web_txn_api.allowed_for_plan('QA_RESULTS_DELETE', p_plan_id) = 'T') or
3307: (qa_web_txn_api.allowed_for_plan('QA_RESULTS_UPDATE', p_plan_id) = 'T') THEN
3308: RETURN 'T';
3309: ELSE
3310: RETURN 'F';
3303: BEGIN
3304:
3305: IF (qa_web_txn_api.allowed_for_plan('QA_RESULTS_VIEW', p_plan_id) = 'T') or
3306: (qa_web_txn_api.allowed_for_plan('QA_RESULTS_DELETE', p_plan_id) = 'T') or
3307: (qa_web_txn_api.allowed_for_plan('QA_RESULTS_UPDATE', p_plan_id) = 'T') THEN
3308: RETURN 'T';
3309: ELSE
3310: RETURN 'F';
3311: END IF;
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'
3406: --
3407: || ' AND (qr.status = 2 OR qr.status IS NULL)';
3408:
3409: -- Bug 2427337. Fix here is not related this bug. To use aggregate functions
3410: -- on a element which is stored in character col in qa_results table, we need
3411: -- to use to_number function, or else, unwanted value will be returned.
3412: -- rponnusa Tue Jun 25 06:15:48 PDT 2002
3413:
3414: IF (cur_rec.element_relationship_type = 2 ) THEN -- sum
3465: -- Bug 4270911. CU2 SQL Literal fix.TD #19
3466: -- Use bind variable for child txn hdr id.
3467: -- srhariha. Fri Apr 15 05:55:04 PDT 2005.
3468:
3469: l_update_parent_sql := 'UPDATE qa_results SET '
3470: || cur_rec.parent_database_column || ' = :l_value'
3471: || ' ,txn_header_id = :p_child_txn_hdr_id'
3472: || ' WHERE plan_id= :p_parent_plan_id'
3473: || ' AND collection_id= :p_parent_collection_id'
3671: 'ENTERING PROCEDURE: P_PLAN_ID: ' || p_plan_id || ' P_COLLECTION_ID: ' || p_collection_id || ' P_OCCURRENCE: ' || p_occurrence || ' P_ENABLED: ' || p_enabled
3672: );
3673: END IF;
3674:
3675: DELETE QA_RESULTS
3676: WHERE occurrence = p_occurrence
3677: AND plan_id = p_plan_id
3678: AND collection_id = p_collection_id;
3679:
3681: FND_LOG.string
3682: (
3683: FND_LOG.level_statement,
3684: g_pkg_name || '.' || l_api_name,
3685: 'DELETED ROW IN QA RESULTS. GETTING PARENT'
3686: );
3687: END IF;
3688:
3689: -- Bug 4343758. Oa Framework Integration project.
3811: SYSDATE,
3812: FND_GLOBAL.USER_ID,
3813: FND_GLOBAL.USER_ID,
3814: QR.TXN_HEADER_ID
3815: FROM QA_RESULTS QR
3816: WHERE QR.PLAN_ID = p_child_plan_id
3817: AND QR.COLLECTION_ID = p_child_collection_id
3818: AND QR.ORGANIZATION_ID = p_org_id;
3819:
3914: p_child_plan_id => p_child_plan_id,
3915: x_parent_rc_str => l_src_string,
3916: x_child_rc_str => l_dest_string);
3917:
3918: l_sql_string := ' UPDATE QA_RESULTS ' ||
3919: ' SET ( ' || l_dest_string || ' ) = ' ||
3920: ' ( SELECT ' || l_src_string || ' ' ||
3921: ' FROM QA_RESULTS QR1 ' ||
3922: ' WHERE QR1.PLAN_ID = :1 ' ||
3917:
3918: l_sql_string := ' UPDATE QA_RESULTS ' ||
3919: ' SET ( ' || l_dest_string || ' ) = ' ||
3920: ' ( SELECT ' || l_src_string || ' ' ||
3921: ' FROM QA_RESULTS QR1 ' ||
3922: ' WHERE QR1.PLAN_ID = :1 ' ||
3923: ' AND QR1.COLLECTION_ID = :2' ||
3924: ' AND QR1.OCCURRENCE = :3) ' ||
3925: ' WHERE PLAN_ID = :4 ' ||
4017: SYSDATE,
4018: FND_GLOBAL.USER_ID,
4019: FND_GLOBAL.USER_ID,
4020: p_txn_header_id
4021: FROM QA_RESULTS QR
4022: WHERE QR.PLAN_ID = p_plan_id
4023: AND QR.COLLECTION_ID = p_collection_id
4024: AND QR.ORGANIZATION_ID = p_org_id;
4025:
4031: x_parent_rc_str => l_src_string,
4032: x_child_rc_str => l_dest_string);
4033:
4034:
4035: l_sql_string := ' INSERT INTO qa_results ( collection_id, ' ||
4036: ' occurrence, ' ||
4037: ' last_update_date, ' ||
4038: ' qa_last_update_date, '||
4039: ' last_updated_by, ' ||
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 ' ||
4247: child_collection_id_tab child_collection_id_tab_typ;
4248: child_occurrence_tab child_occurrence_tab_typ;
4249:
4250: BEGIN
4251: DELETE FROM qa_results
4252: WHERE txn_header_id = p_txn_header_id
4253: AND status = 1
4254: RETURNING plan_id, collection_id, occurrence
4255: BULK COLLECT INTO child_plan_id_tab,
4303: END LOOP;
4304: str := rtrim(str, '||''@');
4305:
4306: -- Use the columns list built above to query
4307: -- qa_results table, to build the result_string
4308: EXECUTE IMMEDIATE
4309: 'Select '''||str||
4310: ' from qa_results where plan_id = :plan_id and
4311: collection_id = :collection_id and
4306: -- Use the columns list built above to query
4307: -- qa_results table, to build the result_string
4308: EXECUTE IMMEDIATE
4309: 'Select '''||str||
4310: ' from qa_results where plan_id = :plan_id and
4311: collection_id = :collection_id and
4312: occurrence = :occurrence'
4313: INTO result_string USING p_plan_id,
4314: p_collection_id ,
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
4357: qpc.parent_occurrence = p_occurrence and
4361: (qr.status = 2 or qr.status is NULL) and
4362: qpr.parent_plan_id = p_plan_id and
4363: qpr.child_plan_id = qpc.child_plan_id and
4364: qpr.data_entry_mode <> 4 and
4365: qa_web_txn_api.allowed_for_plan('QA_RESULTS_UPDATE', qpc.child_plan_id) = 'T';
4366: --rownum =1;
4367:
4368: has_child VARCHAR2(100) :='UPDATE_CHILD_N';
4369: BEGIN
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
4405: qpc.child_plan_id = qr.plan_id and
4482: 'CHARACTER','DISPLAY') FORM_FIELD
4483: BULK COLLECT INTO result_column_name_tab
4484: FROM qa_pc_plan_relationship qppr,
4485: qa_pc_criteria qpc,
4486: qa_results qr,
4487: qa_plan_chars qapc,
4488: qa_chars qc
4489: WHERE qpc.plan_relationship_id = qppr.plan_relationship_id
4490: AND qapc.char_id = qpc.char_id