307: | p_resp_id -> optional, default is current logged in
308: | responsibility
309: | p_org_id -> optional, default is current logged in
310: | operating unit
311: | Output: system.qp_inst_pk_vals ->
312: | all the object ids which can be accessed with the specific
313: | function for current user
314: +----------------------------------------------------------------------
315: */
319: p_instance_type IN VARCHAR2 default null,
320: p_user_name IN VARCHAR2 default G_USER_NAME,
321: p_resp_id IN NUMBER default G_RESP_ID,
322: p_org_id IN NUMBER default G_ORG_ID
323: ) RETURN system.qp_inst_pk_vals IS
324: l_instance_pk1_value fnd_grants.instance_pk1_value%TYPE;
325: l_instance_pk2_value fnd_grants.instance_pk2_value%TYPE;
326: l_instance_pk_values system.qp_inst_pk_vals := system.qp_inst_pk_vals();
327: TYPE qp_inst_pk_vals_cur_type IS REF CURSOR;
322: p_org_id IN NUMBER default G_ORG_ID
323: ) RETURN system.qp_inst_pk_vals IS
324: l_instance_pk1_value fnd_grants.instance_pk1_value%TYPE;
325: l_instance_pk2_value fnd_grants.instance_pk2_value%TYPE;
326: l_instance_pk_values system.qp_inst_pk_vals := system.qp_inst_pk_vals();
327: TYPE qp_inst_pk_vals_cur_type IS REF CURSOR;
328: qp_inst_pk_vals_cur qp_inst_pk_vals_cur_type;
329: err_msg varchar2(256);
330: count_num number := 0;
323: ) RETURN system.qp_inst_pk_vals IS
324: l_instance_pk1_value fnd_grants.instance_pk1_value%TYPE;
325: l_instance_pk2_value fnd_grants.instance_pk2_value%TYPE;
326: l_instance_pk_values system.qp_inst_pk_vals := system.qp_inst_pk_vals();
327: TYPE qp_inst_pk_vals_cur_type IS REF CURSOR;
328: qp_inst_pk_vals_cur qp_inst_pk_vals_cur_type;
329: err_msg varchar2(256);
330: count_num number := 0;
331:
324: l_instance_pk1_value fnd_grants.instance_pk1_value%TYPE;
325: l_instance_pk2_value fnd_grants.instance_pk2_value%TYPE;
326: l_instance_pk_values system.qp_inst_pk_vals := system.qp_inst_pk_vals();
327: TYPE qp_inst_pk_vals_cur_type IS REF CURSOR;
328: qp_inst_pk_vals_cur qp_inst_pk_vals_cur_type;
329: err_msg varchar2(256);
330: count_num number := 0;
331:
332: l_database_object_name fnd_objects.database_object_name%TYPE;
442:
443: l_instance_pk_values.extend;
444: count_num:= count_num + 1;
445: l_instance_pk_values(l_instance_pk_values.last):=
446: system.qp_inst_pk_vals_object(l_instance_pk1_value,
447: l_instance_pk2_value);
448: END LOOP;
449: CLOSE l_pricelist_id_cur;
450: ELSIF( p_instance_type = G_MODIFIER_OBJECT) THEN
457:
458: l_instance_pk_values.extend;
459: count_num:= count_num + 1;
460: l_instance_pk_values(l_instance_pk_values.last):=
461: system.qp_inst_pk_vals_object(l_instance_pk1_value,
462: l_instance_pk2_value);
463: END LOOP;
464: CLOSE l_modifier_id_cur;
465: ELSIF( p_instance_type = G_AGREEMENT_OBJECT) THEN
472:
473: l_instance_pk_values.extend;
474: count_num:= count_num + 1;
475: l_instance_pk_values(l_instance_pk_values.last):=
476: system.qp_inst_pk_vals_object(l_instance_pk1_value,
477: l_instance_pk2_value);
478: END LOOP;
479: CLOSE l_agreement_id_cur;
480: ELSE /*null for all*/
487:
488: l_instance_pk_values.extend;
489: count_num:= count_num + 1;
490: l_instance_pk_values(l_instance_pk_values.last):=
491: system.qp_inst_pk_vals_object(l_instance_pk1_value,
492: l_instance_pk2_value);
493: END LOOP;
494: CLOSE l_list_headers_id_cur;
495: END IF;
553: l_instance_pk_values.extend;
554: count_num:= count_num + 1;
555:
556: l_instance_pk_values(l_instance_pk_values.last):=
557: system.qp_inst_pk_vals_object(l_instance_pk1_value,
558: l_instance_pk2_value);
559: END LOOP;
560: CLOSE l_grants_list_headers_id_cur;
561: ELSIF( p_instance_type = G_PRICELIST_OBJECT or
576: l_instance_pk_values.extend;
577: count_num:= count_num + 1;
578:
579: l_instance_pk_values(l_instance_pk_values.last):=
580: system.qp_inst_pk_vals_object(l_instance_pk1_value,
581: l_instance_pk2_value);
582: END LOOP;
583: CLOSE l_grants_entities_id_cur;
584: END IF;
614: nvl(l_pk1_column_name, 'NULL') ||
615: ' from ' || l_database_object_name ||
616: ' where ' || l_predicate;
617: -- return collection of authorized objects ids
618: OPEN qp_inst_pk_vals_cur FOR l_sql_stmt;
619: LOOP
620: FETCH qp_inst_pk_vals_cur
621: INTO l_instance_pk1_value;
622: EXIT WHEN qp_inst_pk_vals_cur%NOTFOUND;
616: ' where ' || l_predicate;
617: -- return collection of authorized objects ids
618: OPEN qp_inst_pk_vals_cur FOR l_sql_stmt;
619: LOOP
620: FETCH qp_inst_pk_vals_cur
621: INTO l_instance_pk1_value;
622: EXIT WHEN qp_inst_pk_vals_cur%NOTFOUND;
623: l_instance_pk2_value := null;
624: l_instance_pk_values.extend;
618: OPEN qp_inst_pk_vals_cur FOR l_sql_stmt;
619: LOOP
620: FETCH qp_inst_pk_vals_cur
621: INTO l_instance_pk1_value;
622: EXIT WHEN qp_inst_pk_vals_cur%NOTFOUND;
623: l_instance_pk2_value := null;
624: l_instance_pk_values.extend;
625: count_num:= count_num + 1;
626:
624: l_instance_pk_values.extend;
625: count_num:= count_num + 1;
626:
627: l_instance_pk_values(l_instance_pk_values.last) :=
628: system.qp_inst_pk_vals_object(l_instance_pk1_value,
629: l_instance_pk2_value);
630: END LOOP;
631: CLOSE qp_inst_pk_vals_cur;
632: ELSIF(l_return_status = 'E' or
627: l_instance_pk_values(l_instance_pk_values.last) :=
628: system.qp_inst_pk_vals_object(l_instance_pk1_value,
629: l_instance_pk2_value);
630: END LOOP;
631: CLOSE qp_inst_pk_vals_cur;
632: ELSIF(l_return_status = 'E' or
633: l_return_status = 'U' or
634: l_return_status = 'L') THEN /*E, U, L*/
635: error_message := FND_MESSAGE.GET_ENCODED;