[Home] [Help]
183: PROCEDURE enable_and_fire_actions(p_collection_id NUMBER) IS
184:
185: ---
186: --- This procedure commits all the records corresponding to one single session with status = 2.
187: --- This is required since all the child records will be saved with qa_results.status =1
188: --- When the child record is enabled, status code will be changed to 2. The status of
189: --- child record is changed to 2 only when Parent record gets committed
190: ---
191:
189: --- child record is changed to 2 only when Parent record gets committed
190: ---
191:
192: BEGIN
193: qa_results_api.enable_and_fire_action(p_collection_id);
194: END enable_and_fire_actions;
195:
196: FUNCTION get_descendants(
197: p_plan_id NUMBER,
205: --- Given a parent record (plan/collection/occurrence), this procedure finds all the child and
206: --- grandchildren records (therefore, descendants) of the record. These are returned in the
207: --- three output PL/SQL tables. The parent record itself is not included in the output.
208: --- The query technical is called hierarchical subquery. The final where clause makes
209: --- sure the child record is actually enabled in the qa_results table.
210: ---
211:
212: BEGIN
213:
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
222: qr.collection_id = r.child_collection_id AND
223: qr.occurrence = r.child_occurrence AND
224: (qr.status IS NULL or qr.status=2) )
579: PROCEDURE parse_list(x_result IN VARCHAR2,
580: x_array OUT NOCOPY ChildPlanArray) IS
581:
582: -- For longcomments enhancement, Bug 2234299
583: -- changed 'value' type from qa_results.character1%TYPE to varchar2(2000)
584: -- rponnusa Thu Mar 14 21:27:04 PST 2002
585:
586: value VARCHAR2(2000);
587: c VARCHAR2(10);
679:
680: -- anagarwa Mon Dec 16 16:55:09 PST 2002
681: -- Bug 2701777
682: -- if parent or child elements are disabled and the parent child relationship
683: -- still exists for them then insert API qa_mqa_results.post_result raises
684: -- returns an error and prevents the history as well as automatic results
685: -- from being saved. It causes a ON-INSERT trigger being raised on forms
686: -- and even the parent results cannot be saved.
687: -- To fix the problem, qa_pc_result_column_v is being modified to have parent
907:
908: -- anagarwa Thu Dec 19 15:43:27 PST 2002
909: -- Bug 2701777
910: -- post_result_with_no_validation inserts records into
911: -- qa_results without any validations. This prevents any
912: -- errors if user changes element values in parent plan
913: -- but not the History plan
914:
915: --
916: -- bug 5383667
917: -- Passing the Id string as well
918: -- ntungare
919: --
920: l_return_int:= qa_mqa_results.post_result_with_no_validation(
921: l_occurrence,
922: p_org_id,
923: l_child_plan_id, p_spec_id,
924: p_collection_id,
938: -- following added to copy attachments to History records.
939:
940: IF p_data_entry_mode = 4 THEN
941: FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
942: X_from_entity_name => 'QA_RESULTS',
943: X_from_pk1_value => to_char(p_occurrence),
944: X_from_pk2_value => to_char(p_collection_id),
945: X_from_pk3_value => to_char(p_plan_id),
946: X_to_entity_name => 'QA_RESULTS',
942: X_from_entity_name => 'QA_RESULTS',
943: X_from_pk1_value => to_char(p_occurrence),
944: X_from_pk2_value => to_char(p_collection_id),
945: X_from_pk3_value => to_char(p_plan_id),
946: X_to_entity_name => 'QA_RESULTS',
947: X_to_pk1_value => to_char(l_occurrence),
948: X_to_pk2_value => to_char(p_collection_id),
949: X_to_pk3_value => to_char(l_child_plan_id));
950: END IF;
994: -- nutngare Thu Jul 5 05:21:16 PDT 2007
995: --
996: SELECT MAX(occurrence)
997: into l_child_occurrence
998: FROM qa_results
999: WHERE plan_id = l_child_plan_id and
1000: collection_id = p_collection_id and
1001: organization_id = p_org_id and
1002: txn_header_id = p_txn_header_id;
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
1109: qr.collection_id = r.child_collection_id AND
1110: qr.occurrence = r.child_occurrence AND
1111: qr.status = 1 )
1136:
1137: IS
1138: ---
1139: --- The following procedure takes in plan_id, collection id and occurrece and
1140: --- deletes these rows from QA_RESULTS. It also deletes entry for these rows
1141: --- from relationships tables.
1142: ---
1143: --- p_enabled_flag holds 'T' => delete only enabled child records
1144: --- 'F' => delete only disabled child records
1167: p_parent_occurrence);
1168: END IF;
1169:
1170: FORALL i IN p_occurrences.FIRST .. p_occurrences.LAST
1171: DELETE from QA_RESULTS
1172: WHERE plan_id = p_plan_ids(i)
1173: AND collection_id = p_collection_ids(i)
1174: AND occurrence = p_occurrences(i);
1175:
1184:
1185: BEGIN
1186:
1187: IF p_txn_header_id is not null THEN
1188: UPDATE qa_results
1189: SET status = 2
1190: WHERE txn_header_id = p_txn_header_id;
1191:
1192: flag := QLTDACTB.DO_ACTIONS(p_txn_header_id, 1, NULL, NULL,
1211:
1212: BEGIN
1213: IF p_txn_header_id is not null THEN
1214:
1215: -- Updating the rows in the QA_RESULTS which are currently
1216: -- invalid
1217: --
1218: UPDATE qa_results
1219: SET status = 2
1214:
1215: -- Updating the rows in the QA_RESULTS which are currently
1216: -- invalid
1217: --
1218: UPDATE qa_results
1219: SET status = 2
1220: WHERE txn_header_id = p_txn_header_id
1221: and status =1
1222: RETURNING plan_id, collection_id, occurrence
1263: -- is passed. First find out parent_plan_id from qa_pc_plan_relationship. Then findout all
1264: -- element ids with which parent and child plans are related. Take only those elements which have
1265: -- link_flag = 1 in qa_pc_element_relationship table.
1266:
1267: -- Find the values of the elements from qa_results for the child plan. Then find the first record
1268: -- for the parent plan which has all the elements (only those related in the qa_pc_element_relation)
1269: -- same value for those of child plan. Return the parent record information.
1270:
1271: l_plan_relationship_id NUMBER;
1270:
1271: l_plan_relationship_id NUMBER;
1272: l_parent_plan_id NUMBER;
1273: l_temp_var NUMBER;
1274: l_res_col VARCHAR2(150); -- stores result column name in qa_results
1275: l_res_value VARCHAR2(150); -- stores result column value in qa_results
1276:
1277: query_clause VARCHAR2(32000):= NULL;
1278: select_clause VARCHAR2(80) := NULL;
1271: l_plan_relationship_id NUMBER;
1272: l_parent_plan_id NUMBER;
1273: l_temp_var NUMBER;
1274: l_res_col VARCHAR2(150); -- stores result column name in qa_results
1275: l_res_value VARCHAR2(150); -- stores result column value in qa_results
1276:
1277: query_clause VARCHAR2(32000):= NULL;
1278: select_clause VARCHAR2(80) := NULL;
1279: from_clause CONSTANT VARCHAR2(80) := ' FROM QA_RESULTS ';
1275: l_res_value VARCHAR2(150); -- stores result column value in qa_results
1276:
1277: query_clause VARCHAR2(32000):= NULL;
1278: select_clause VARCHAR2(80) := NULL;
1279: from_clause CONSTANT VARCHAR2(80) := ' FROM QA_RESULTS ';
1280: where_clause VARCHAR2(5000) := NULL;
1281: parent_where_clause VARCHAR2(5000):= NULL;
1282:
1283:
1468:
1469: l_child_txn_header_id NUMBER;
1470:
1471: CURSOR c IS
1472: SELECT txn_header_id FROM qa_results
1473: WHERE plan_id = p_child_plan_id AND
1474: collection_id = p_child_collection_id AND
1475: occurrence = p_child_occurrence;
1476:
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'
1817: --
1818: || ' AND (qr.status = 2 OR qr.status IS NULL)';
1819:
1820: -- Bug 2427337. Fix here is not related this bug. To use aggregate functions
1821: -- on a element which is stored in character col in qa_results table, we need
1822: -- to use to_number function, or else, unwanted value will be returned.
1823: -- rponnusa Tue Jun 25 06:15:48 PDT 2002
1824:
1825: IF (cur_rec.element_relationship_type = 2 ) THEN -- sum
1882: -- bhsankar Sun Sep 30 23:38:58 PDT 2007
1883: --
1884: l_select_sql := 'SELECT '
1885: || cur_rec.parent_database_column
1886: || ' FROM qa_results WHERE plan_id = :p_parent_plan_id'
1887: || ' AND collection_id= :p_parent_collection_id'
1888: || ' AND occurrence= :p_parent_occurrence FOR UPDATE NOWAIT';
1889:
1890: BEGIN
1892: USING p_parent_plan_id,p_parent_collection_id,p_parent_occurrence;
1893:
1894: -- now we need to update the parent record. Build the sql here.
1895:
1896: l_update_parent_sql := 'UPDATE qa_results SET '
1897: || cur_rec.parent_database_column || ' = :l_value'
1898: || ' WHERE plan_id= :p_parent_plan_id'
1899: || ' AND collection_id= :p_parent_collection_id'
1900: || ' AND occurrence= :p_parent_occurrence';
2050:
2051: --
2052: -- bug 6266439
2053: -- New procedure to peform the date conversions while
2054: -- selecting and updating the data in the QA_RESULTS
2055: -- table, while peforming a Child record update.
2056: -- ntungare Thu Aug 2 03:32:32 PDT 2007
2057: --
2058: PROCEDURE DATE_SELECT_UPDATE(p_parent_result_column IN VARCHAR2,
2329:
2330: --
2331: -- bug 6266439
2332: -- New variable to hold the name of the column
2333: -- to be selected from the QA_RESULTS table
2334: -- ntungare Thu Aug 2 03:40:42 PDT 2007
2335: --
2336: select_column varchar2(2000);
2337:
2335: --
2336: select_column varchar2(2000);
2337:
2338: -- New variable to hold the bind variable
2339: -- to be updated in the QA_RESULTS table
2340: update_column varchar2(2000);
2341: --
2342: -- Bug 7588376
2343: -- A new variable to get the plan view naem for
2427: --
2428: -- bug 6266477
2429: -- Added the following to execute the query
2430: -- to fetch all the parent result column values
2431: -- in a single hit to qa_results table
2432: -- The string is built as 1=
2433: -- so that the result_to_array can be reused to collect into an array.
2434: -- skolluku Sun Oct 14 03:26:31 PDT 2007
2435: --
2447: -- ntungare Thu Aug 2 03:42:18 PDT 2007
2448: --
2449: /*
2450: l_sql_string := 'SELECT ' || p_parentchild_element_tab(element_cntr).parent_database_column
2451: || ' FROM qa_results '
2452: || ' WHERE plan_id= :p_parent_plan_id'
2453: || ' AND collection_id= :p_parent_collection_id'
2454: || ' AND occurrence= :p_parent_occurrence';
2455: */
2461: -- skolluku Sun Oct 14 03:26:31 PDT 2007
2462: --
2463: /*
2464: l_sql_string := 'SELECT ' || NVL(select_column, p_parentchild_element_tab(element_cntr).parent_database_column)
2465: || ' FROM qa_results '
2466: || ' WHERE plan_id= :p_parent_plan_id'
2467: || ' AND collection_id= :p_parent_collection_id'
2468: || ' AND occurrence= :p_parent_occurrence';
2469:
2525: END IF;
2526: --
2527: -- Bug 7588376
2528: -- Fetch the plan view name for the parent plan. The deref view
2529: -- will be used instead of QA_RESULTS table to copy the values
2530: -- from parent to child because QA_RESULTS does not contain the
2531: -- dereferenced values for hardcoded elements. If the parent
2532: -- element is HC and child is SC, the value, instead of the ID,
2533: -- should be copied, and using QA_RESULTS will not accomplish that.
2526: --
2527: -- Bug 7588376
2528: -- Fetch the plan view name for the parent plan. The deref view
2529: -- will be used instead of QA_RESULTS table to copy the values
2530: -- from parent to child because QA_RESULTS does not contain the
2531: -- dereferenced values for hardcoded elements. If the parent
2532: -- element is HC and child is SC, the value, instead of the ID,
2533: -- should be copied, and using QA_RESULTS will not accomplish that.
2534: -- The value will be picked from the deref_view only if the value
2529: -- will be used instead of QA_RESULTS table to copy the values
2530: -- from parent to child because QA_RESULTS does not contain the
2531: -- dereferenced values for hardcoded elements. If the parent
2532: -- element is HC and child is SC, the value, instead of the ID,
2533: -- should be copied, and using QA_RESULTS will not accomplish that.
2534: -- The value will be picked from the deref_view only if the value
2535: -- is not cached in the collection
2536: -- skolluku
2537: --
2548: WHERE plan_id = p_parent_plan_id;
2549:
2550: --
2551: -- Bug 7588376
2552: -- Replace QA_RESULTS with the l_plan_view_name for the reason explained above.
2553: -- skolluku
2554: --
2555: --
2556: --
2555: --
2556: --
2557: -- bug 6266477
2558: -- Execute the select statement here to hit the table
2559: -- QA_RESULTS only once to improve performance and get
2560: -- the values into anl_bind_var array.
2561: -- skolluku Sun Oct 14 03:26:31 PDT 2007
2562: --
2563: l_sql_string := 'Select ' || l_sql_string
2560: -- the values into anl_bind_var array.
2561: -- skolluku Sun Oct 14 03:26:31 PDT 2007
2562: --
2563: l_sql_string := 'Select ' || l_sql_string
2564: -- || ' FROM qa_results '
2565: || ' FROM ' || l_plan_view_name
2566: || ' WHERE plan_id= :p_parent_plan_id'
2567: || ' AND collection_id= :p_parent_collection_id'
2568: || ' AND occurrence= :p_parent_occurrence';
2583: END IF;
2584:
2585: l_bind_var := qa_txn_grp.result_to_array(l_value);
2586:
2587: l_update_clause := 'UPDATE qa_results SET ' || l_update_clause
2588: || ' WHERE plan_id= :p_child_plan_id'
2589: || ' AND collection_id= :p_child_collection_id'
2590: || ' AND occurrence= :p_child_occurrence';
2591:
2991: history or automatic plans in collection imports, it (histor/automatic
2992: functionality) fails.
2993: By selecting form_field instead of database_column we can fix it.
2994: However, item, comp_item, locator and comp_locator don't exist in
2995: QA_RESULTS_V. So we add special handling for these later.
2996: */
2997: --SELECT char_id,database_column
2998: SELECT char_id, replace(form_field, 'DISPLAY' , 'CHARACTER') database_column,
2999: datatype
3010: l_string VARCHAR2(32000);
3011: l_append BOOLEAN;
3012:
3013: select_clause VARCHAR2(32000);
3014: from_clause CONSTANT VARCHAR2(80) := ' FROM QA_RESULTS_V ';
3015: where_clause VARCHAR2(5000);
3016: query_clause VARCHAR2(32000);
3017: -- anagarwa Tue Jul 16 18:36:52 PDT 2002
3018: -- Bug 2465920: new variable to handle item, comp_item, locator and
3020: column_name VARCHAR2(150);
3021:
3022:
3023: -- Bug 3776542. Performance issue due to use of literals in the SQL to fetch
3024: -- criteria value from QA_RESULTS_V. Earlier we were using reference cursor to
3025: -- fetch the value with a SQL that had literals. After fix, we are using EXECUTE_IMMEDIATE
3026: -- with SQL containing bind variables. This ref cursor is needed no more, hence commenting
3027: -- it out.Thu Jul 29 02:02:03 PDT 2004.
3028: -- srhariha.
3058: ELSIF column_name = 'COMP_LOCATOR' THEN
3059: column_name := 'COMP_LOCATOR_ID';
3060:
3061: -- Bug 2694385. Added bill_reference,routing_reference,to_locator since
3062: -- these elements will not present in qa_results_v.
3063: -- rponnusa Wed Dec 18 05:38:40 PST 2002
3064:
3065: ELSIF column_name = 'BILL_REFERENCE' THEN
3066: column_name := 'BILL_REFERENCE_ID';
3079: END IF;
3080: --
3081: -- bug 6266477
3082: -- Commenting the below code since the handling is done
3083: -- differently to avoid multiple hits to QA_RESULTS_V
3084: -- skolluku Mon Oct 15 02:57:40 PDT 2007
3085: --
3086: /*select_clause := 'SELECT ' || column_name;
3087: query_clause := select_clause || from_clause || where_clause;
3086: /*select_clause := 'SELECT ' || column_name;
3087: query_clause := select_clause || from_clause || where_clause;
3088:
3089: -- Bug 3776542. Performance issue due to use of literals in the SQL to fetch
3090: -- criteria value from QA_RESULTS_V. Earlier we were using reference cursor to
3091: -- fetch the value with a SQL that had literals. After fix, we are using EXECUTE_IMMEDIATE
3092: -- with SQL containing bind variables. This ref cursor is needed no more, hence commenting
3093: -- it out.
3094: -- srhariha.Thu Jul 29 02:02:03 PDT 2004
3140: */
3141: --
3142: -- bug 6266477
3143: -- Added the below code to enhance performance
3144: -- by hitting the view QA_RESULTS_V just once
3145: -- skolluku Mon Oct 15 02:57:40 PDT 2007
3146: --
3147: IF parent_rec.char_id IN (qa_ss_const.item, qa_ss_const.comp_item,
3148: qa_ss_const.routing_reference, qa_ss_const.bill_reference) THEN
3203: AND data_entry_mode = p_data_entry_mode;
3204:
3205: CURSOR res_cur IS
3206: SELECT collection_id,occurrence,organization_id
3207: FROM qa_results
3208: WHERE plan_id = p_parent_plan_id
3209: AND txn_header_id = p_txn_header_id;
3210:
3211:
3297: -- Return true if the given parent record is saved in enable status
3298:
3299: CURSOR c IS
3300: SELECT 1
3301: FROM qa_results
3302: WHERE plan_id = p_plan_id
3303: AND collection_id = p_collection_id
3304: AND occurrence = p_occurrence
3305: AND status = 2;
3638: cnt NUMBER;
3639:
3640: cursor c is
3641: select count(plan_id)
3642: from qa_results
3643: where plan_id = p_plan_id
3644: and txn_header_id = p_txn_header_id;
3645:
3646: BEGIN
3665: ---
3666:
3667: BEGIN
3668:
3669: IF (qa_web_txn_api.allowed_for_plan('QA_RESULTS_VIEW', p_plan_id) = 'T') or
3670: (qa_web_txn_api.allowed_for_plan('QA_RESULTS_DELETE', p_plan_id) = 'T') or
3671: (qa_web_txn_api.allowed_for_plan('QA_RESULTS_UPDATE', p_plan_id) = 'T') THEN
3672: RETURN 'T';
3673: ELSE
3666:
3667: BEGIN
3668:
3669: IF (qa_web_txn_api.allowed_for_plan('QA_RESULTS_VIEW', p_plan_id) = 'T') or
3670: (qa_web_txn_api.allowed_for_plan('QA_RESULTS_DELETE', p_plan_id) = 'T') or
3671: (qa_web_txn_api.allowed_for_plan('QA_RESULTS_UPDATE', p_plan_id) = 'T') THEN
3672: RETURN 'T';
3673: ELSE
3674: RETURN 'F';
3667: BEGIN
3668:
3669: IF (qa_web_txn_api.allowed_for_plan('QA_RESULTS_VIEW', p_plan_id) = 'T') or
3670: (qa_web_txn_api.allowed_for_plan('QA_RESULTS_DELETE', p_plan_id) = 'T') or
3671: (qa_web_txn_api.allowed_for_plan('QA_RESULTS_UPDATE', p_plan_id) = 'T') THEN
3672: RETURN 'T';
3673: ELSE
3674: RETURN 'F';
3675: END IF;
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'
3770: --
3771: || ' AND (qr.status = 2 OR qr.status IS NULL)';
3772:
3773: -- Bug 2427337. Fix here is not related this bug. To use aggregate functions
3774: -- on a element which is stored in character col in qa_results table, we need
3775: -- to use to_number function, or else, unwanted value will be returned.
3776: -- rponnusa Tue Jun 25 06:15:48 PDT 2002
3777:
3778: IF (cur_rec.element_relationship_type = 2 ) THEN -- sum
3829: -- Bug 4270911. CU2 SQL Literal fix.TD #19
3830: -- Use bind variable for child txn hdr id.
3831: -- srhariha. Fri Apr 15 05:55:04 PDT 2005.
3832:
3833: l_update_parent_sql := 'UPDATE qa_results SET '
3834: || cur_rec.parent_database_column || ' = :l_value'
3835: || ' ,txn_header_id = :p_child_txn_hdr_id'
3836: || ' WHERE plan_id= :p_parent_plan_id'
3837: || ' AND collection_id= :p_parent_collection_id'
4035: 'ENTERING PROCEDURE: P_PLAN_ID: ' || p_plan_id || ' P_COLLECTION_ID: ' || p_collection_id || ' P_OCCURRENCE: ' || p_occurrence || ' P_ENABLED: ' || p_enabled
4036: );
4037: END IF;
4038:
4039: DELETE QA_RESULTS
4040: WHERE occurrence = p_occurrence
4041: AND plan_id = p_plan_id
4042: AND collection_id = p_collection_id;
4043:
4045: FND_LOG.string
4046: (
4047: FND_LOG.level_statement,
4048: g_pkg_name || '.' || l_api_name,
4049: 'DELETED ROW IN QA RESULTS. GETTING PARENT'
4050: );
4051: END IF;
4052:
4053: -- Bug 4343758. Oa Framework Integration project.
4175: SYSDATE,
4176: FND_GLOBAL.USER_ID,
4177: FND_GLOBAL.USER_ID,
4178: QR.TXN_HEADER_ID
4179: FROM QA_RESULTS QR
4180: WHERE QR.PLAN_ID = p_child_plan_id
4181: AND QR.COLLECTION_ID = p_child_collection_id
4182: AND QR.ORGANIZATION_ID = p_org_id;
4183:
4278: p_child_plan_id => p_child_plan_id,
4279: x_parent_rc_str => l_src_string,
4280: x_child_rc_str => l_dest_string);
4281:
4282: l_sql_string := ' UPDATE QA_RESULTS ' ||
4283: ' SET ( ' || l_dest_string || ' ) = ' ||
4284: ' ( SELECT ' || l_src_string || ' ' ||
4285: ' FROM QA_RESULTS QR1 ' ||
4286: ' WHERE QR1.PLAN_ID = :1 ' ||
4281:
4282: l_sql_string := ' UPDATE QA_RESULTS ' ||
4283: ' SET ( ' || l_dest_string || ' ) = ' ||
4284: ' ( SELECT ' || l_src_string || ' ' ||
4285: ' FROM QA_RESULTS QR1 ' ||
4286: ' WHERE QR1.PLAN_ID = :1 ' ||
4287: ' AND QR1.COLLECTION_ID = :2' ||
4288: ' AND QR1.OCCURRENCE = :3) ' ||
4289: ' WHERE PLAN_ID = :4 ' ||
4381: SYSDATE,
4382: FND_GLOBAL.USER_ID,
4383: FND_GLOBAL.USER_ID,
4384: p_txn_header_id
4385: FROM QA_RESULTS QR
4386: WHERE QR.PLAN_ID = p_plan_id
4387: AND QR.COLLECTION_ID = p_collection_id
4388: AND QR.ORGANIZATION_ID = p_org_id;
4389:
4395: x_parent_rc_str => l_src_string,
4396: x_child_rc_str => l_dest_string);
4397:
4398:
4399: l_sql_string := ' INSERT INTO qa_results ( collection_id, ' ||
4400: ' occurrence, ' ||
4401: ' last_update_date, ' ||
4402: ' qa_last_update_date, '||
4403: ' last_updated_by, ' ||
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 ' ||
4611: child_collection_id_tab child_collection_id_tab_typ;
4612: child_occurrence_tab child_occurrence_tab_typ;
4613:
4614: BEGIN
4615: DELETE FROM qa_results
4616: WHERE txn_header_id = p_txn_header_id
4617: AND status = 1
4618: RETURNING plan_id, collection_id, occurrence
4619: BULK COLLECT INTO child_plan_id_tab,
4675: END LOOP;
4676: str := rtrim(str, '||''@');
4677:
4678: -- Use the columns list built above to query
4679: -- qa_results table, to build the result_string
4680: -- Bug 9382356
4681: -- Added NULL check as this would error out in case there
4682: -- is only COMMENTS element in the plan.
4683: -- skolluku
4683: -- skolluku
4684: IF str IS NOT NULL THEN
4685: EXECUTE IMMEDIATE
4686: 'Select '''||str||
4687: ' from qa_results where plan_id = :plan_id and
4688: collection_id = :collection_id and
4689: occurrence = :occurrence'
4690: INTO result_string USING p_plan_id,
4691: p_collection_id ,
4705: '=''||REPLACE('||res_col_tab(cntr).res_col_name||
4706: ',''@'',''@@'')';
4707: EXECUTE IMMEDIATE
4708: 'Select '''||str||
4709: ' from qa_results where plan_id = :plan_id and
4710: collection_id = :collection_id and
4711: occurrence = :occurrence'
4712: INTO comments_result_string USING p_plan_id,
4713: p_collection_id ,
4737: IF plans(cntr).VALUE <>4 THEN
4738: -- bug 14773742
4739: -- Check if child plan has security access
4740: --
4741: IF QA_WEB_TXN_API.ALLOWED_FOR_PLAN('QA_RESULTS_ENTER', cntr) <> 'F' THEN
4742: RETURN 'CHILD_Y';
4743: END IF;
4744: END IF;
4745: cntr := plans.next(cntr);
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
4768: qpc.parent_occurrence = p_occurrence and
4772: (qr.status = 2 or qr.status is NULL) and
4773: qpr.parent_plan_id = p_plan_id and
4774: qpr.child_plan_id = qpc.child_plan_id and
4775: qpr.data_entry_mode <> 4 and
4776: qa_web_txn_api.allowed_for_plan('QA_RESULTS_UPDATE', qpc.child_plan_id) = 'T';
4777: --rownum =1;
4778:
4779: has_child VARCHAR2(100) :='UPDATE_CHILD_N';
4780: BEGIN
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
4816: qpc.child_plan_id = qr.plan_id and
4893: 'CHARACTER','DISPLAY') FORM_FIELD
4894: BULK COLLECT INTO result_column_name_tab
4895: FROM qa_pc_plan_relationship qppr,
4896: qa_pc_criteria qpc,
4897: qa_results qr,
4898: qa_plan_chars qapc,
4899: qa_chars qc
4900: WHERE qpc.plan_relationship_id = qppr.plan_relationship_id
4901: AND qapc.char_id = qpc.char_id