[Home] [Help]
709: (p_process_id in number,
710: l_sysdate in Date default sysdate)
711: is
712:
713: l_process_id amw_process.process_id%type;
714: l_process_rev_id amw_process.process_rev_id%type;
715: l_item_type amw_process.item_type%type;
716: l_name amw_process.name%type;
717: l_process_code amw_process.process_code%type;
710: l_sysdate in Date default sysdate)
711: is
712:
713: l_process_id amw_process.process_id%type;
714: l_process_rev_id amw_process.process_rev_id%type;
715: l_item_type amw_process.item_type%type;
716: l_name amw_process.name%type;
717: l_process_code amw_process.process_code%type;
718: l_revision_number amw_process.revision_number%type;
711: is
712:
713: l_process_id amw_process.process_id%type;
714: l_process_rev_id amw_process.process_rev_id%type;
715: l_item_type amw_process.item_type%type;
716: l_name amw_process.name%type;
717: l_process_code amw_process.process_code%type;
718: l_revision_number amw_process.revision_number%type;
719: l_approval_status amw_process.approval_status%type;
712:
713: l_process_id amw_process.process_id%type;
714: l_process_rev_id amw_process.process_rev_id%type;
715: l_item_type amw_process.item_type%type;
716: l_name amw_process.name%type;
717: l_process_code amw_process.process_code%type;
718: l_revision_number amw_process.revision_number%type;
719: l_approval_status amw_process.approval_status%type;
720: l_control_count amw_process.control_count%type;
713: l_process_id amw_process.process_id%type;
714: l_process_rev_id amw_process.process_rev_id%type;
715: l_item_type amw_process.item_type%type;
716: l_name amw_process.name%type;
717: l_process_code amw_process.process_code%type;
718: l_revision_number amw_process.revision_number%type;
719: l_approval_status amw_process.approval_status%type;
720: l_control_count amw_process.control_count%type;
721: l_risk_count amw_process.risk_count%type;
714: l_process_rev_id amw_process.process_rev_id%type;
715: l_item_type amw_process.item_type%type;
716: l_name amw_process.name%type;
717: l_process_code amw_process.process_code%type;
718: l_revision_number amw_process.revision_number%type;
719: l_approval_status amw_process.approval_status%type;
720: l_control_count amw_process.control_count%type;
721: l_risk_count amw_process.risk_count%type;
722: l_org_count amw_process.org_count%type;
715: l_item_type amw_process.item_type%type;
716: l_name amw_process.name%type;
717: l_process_code amw_process.process_code%type;
718: l_revision_number amw_process.revision_number%type;
719: l_approval_status amw_process.approval_status%type;
720: l_control_count amw_process.control_count%type;
721: l_risk_count amw_process.risk_count%type;
722: l_org_count amw_process.org_count%type;
723: l_significant_process_flag amw_process.significant_process_flag%type;
716: l_name amw_process.name%type;
717: l_process_code amw_process.process_code%type;
718: l_revision_number amw_process.revision_number%type;
719: l_approval_status amw_process.approval_status%type;
720: l_control_count amw_process.control_count%type;
721: l_risk_count amw_process.risk_count%type;
722: l_org_count amw_process.org_count%type;
723: l_significant_process_flag amw_process.significant_process_flag%type;
724: l_standard_process_flag amw_process.standard_process_flag%type;
717: l_process_code amw_process.process_code%type;
718: l_revision_number amw_process.revision_number%type;
719: l_approval_status amw_process.approval_status%type;
720: l_control_count amw_process.control_count%type;
721: l_risk_count amw_process.risk_count%type;
722: l_org_count amw_process.org_count%type;
723: l_significant_process_flag amw_process.significant_process_flag%type;
724: l_standard_process_flag amw_process.standard_process_flag%type;
725: l_certification_status amw_process.certification_status%type;
718: l_revision_number amw_process.revision_number%type;
719: l_approval_status amw_process.approval_status%type;
720: l_control_count amw_process.control_count%type;
721: l_risk_count amw_process.risk_count%type;
722: l_org_count amw_process.org_count%type;
723: l_significant_process_flag amw_process.significant_process_flag%type;
724: l_standard_process_flag amw_process.standard_process_flag%type;
725: l_certification_status amw_process.certification_status%type;
726: l_process_category amw_process.process_category%type;
719: l_approval_status amw_process.approval_status%type;
720: l_control_count amw_process.control_count%type;
721: l_risk_count amw_process.risk_count%type;
722: l_org_count amw_process.org_count%type;
723: l_significant_process_flag amw_process.significant_process_flag%type;
724: l_standard_process_flag amw_process.standard_process_flag%type;
725: l_certification_status amw_process.certification_status%type;
726: l_process_category amw_process.process_category%type;
727: l_process_owner_id amw_process.process_owner_id%type;
720: l_control_count amw_process.control_count%type;
721: l_risk_count amw_process.risk_count%type;
722: l_org_count amw_process.org_count%type;
723: l_significant_process_flag amw_process.significant_process_flag%type;
724: l_standard_process_flag amw_process.standard_process_flag%type;
725: l_certification_status amw_process.certification_status%type;
726: l_process_category amw_process.process_category%type;
727: l_process_owner_id amw_process.process_owner_id%type;
728: l_finance_owner_id amw_process.finance_owner_id%type;
721: l_risk_count amw_process.risk_count%type;
722: l_org_count amw_process.org_count%type;
723: l_significant_process_flag amw_process.significant_process_flag%type;
724: l_standard_process_flag amw_process.standard_process_flag%type;
725: l_certification_status amw_process.certification_status%type;
726: l_process_category amw_process.process_category%type;
727: l_process_owner_id amw_process.process_owner_id%type;
728: l_finance_owner_id amw_process.finance_owner_id%type;
729: l_application_owner_id amw_process.application_owner_id%type;
722: l_org_count amw_process.org_count%type;
723: l_significant_process_flag amw_process.significant_process_flag%type;
724: l_standard_process_flag amw_process.standard_process_flag%type;
725: l_certification_status amw_process.certification_status%type;
726: l_process_category amw_process.process_category%type;
727: l_process_owner_id amw_process.process_owner_id%type;
728: l_finance_owner_id amw_process.finance_owner_id%type;
729: l_application_owner_id amw_process.application_owner_id%type;
730: l_standard_variation amw_process.standard_variation%type;
723: l_significant_process_flag amw_process.significant_process_flag%type;
724: l_standard_process_flag amw_process.standard_process_flag%type;
725: l_certification_status amw_process.certification_status%type;
726: l_process_category amw_process.process_category%type;
727: l_process_owner_id amw_process.process_owner_id%type;
728: l_finance_owner_id amw_process.finance_owner_id%type;
729: l_application_owner_id amw_process.application_owner_id%type;
730: l_standard_variation amw_process.standard_variation%type;
731: l_deletion_date amw_process.deletion_date%type;
724: l_standard_process_flag amw_process.standard_process_flag%type;
725: l_certification_status amw_process.certification_status%type;
726: l_process_category amw_process.process_category%type;
727: l_process_owner_id amw_process.process_owner_id%type;
728: l_finance_owner_id amw_process.finance_owner_id%type;
729: l_application_owner_id amw_process.application_owner_id%type;
730: l_standard_variation amw_process.standard_variation%type;
731: l_deletion_date amw_process.deletion_date%type;
732: l_process_type amw_process.process_type%type;
725: l_certification_status amw_process.certification_status%type;
726: l_process_category amw_process.process_category%type;
727: l_process_owner_id amw_process.process_owner_id%type;
728: l_finance_owner_id amw_process.finance_owner_id%type;
729: l_application_owner_id amw_process.application_owner_id%type;
730: l_standard_variation amw_process.standard_variation%type;
731: l_deletion_date amw_process.deletion_date%type;
732: l_process_type amw_process.process_type%type;
733: l_control_activity_type amw_process.control_activity_type%type;
726: l_process_category amw_process.process_category%type;
727: l_process_owner_id amw_process.process_owner_id%type;
728: l_finance_owner_id amw_process.finance_owner_id%type;
729: l_application_owner_id amw_process.application_owner_id%type;
730: l_standard_variation amw_process.standard_variation%type;
731: l_deletion_date amw_process.deletion_date%type;
732: l_process_type amw_process.process_type%type;
733: l_control_activity_type amw_process.control_activity_type%type;
734: l_object_version_number amw_process.object_version_number%type;
727: l_process_owner_id amw_process.process_owner_id%type;
728: l_finance_owner_id amw_process.finance_owner_id%type;
729: l_application_owner_id amw_process.application_owner_id%type;
730: l_standard_variation amw_process.standard_variation%type;
731: l_deletion_date amw_process.deletion_date%type;
732: l_process_type amw_process.process_type%type;
733: l_control_activity_type amw_process.control_activity_type%type;
734: l_object_version_number amw_process.object_version_number%type;
735: l_attribute_category amw_process.attribute_category%type;
728: l_finance_owner_id amw_process.finance_owner_id%type;
729: l_application_owner_id amw_process.application_owner_id%type;
730: l_standard_variation amw_process.standard_variation%type;
731: l_deletion_date amw_process.deletion_date%type;
732: l_process_type amw_process.process_type%type;
733: l_control_activity_type amw_process.control_activity_type%type;
734: l_object_version_number amw_process.object_version_number%type;
735: l_attribute_category amw_process.attribute_category%type;
736: l_attribute1 amw_process.attribute1%type;
729: l_application_owner_id amw_process.application_owner_id%type;
730: l_standard_variation amw_process.standard_variation%type;
731: l_deletion_date amw_process.deletion_date%type;
732: l_process_type amw_process.process_type%type;
733: l_control_activity_type amw_process.control_activity_type%type;
734: l_object_version_number amw_process.object_version_number%type;
735: l_attribute_category amw_process.attribute_category%type;
736: l_attribute1 amw_process.attribute1%type;
737: l_attribute2 amw_process.attribute2%type;
730: l_standard_variation amw_process.standard_variation%type;
731: l_deletion_date amw_process.deletion_date%type;
732: l_process_type amw_process.process_type%type;
733: l_control_activity_type amw_process.control_activity_type%type;
734: l_object_version_number amw_process.object_version_number%type;
735: l_attribute_category amw_process.attribute_category%type;
736: l_attribute1 amw_process.attribute1%type;
737: l_attribute2 amw_process.attribute2%type;
738: l_attribute3 amw_process.attribute3%type;
731: l_deletion_date amw_process.deletion_date%type;
732: l_process_type amw_process.process_type%type;
733: l_control_activity_type amw_process.control_activity_type%type;
734: l_object_version_number amw_process.object_version_number%type;
735: l_attribute_category amw_process.attribute_category%type;
736: l_attribute1 amw_process.attribute1%type;
737: l_attribute2 amw_process.attribute2%type;
738: l_attribute3 amw_process.attribute3%type;
739: l_attribute4 amw_process.attribute4%type;
732: l_process_type amw_process.process_type%type;
733: l_control_activity_type amw_process.control_activity_type%type;
734: l_object_version_number amw_process.object_version_number%type;
735: l_attribute_category amw_process.attribute_category%type;
736: l_attribute1 amw_process.attribute1%type;
737: l_attribute2 amw_process.attribute2%type;
738: l_attribute3 amw_process.attribute3%type;
739: l_attribute4 amw_process.attribute4%type;
740: l_attribute5 amw_process.attribute5%type;
733: l_control_activity_type amw_process.control_activity_type%type;
734: l_object_version_number amw_process.object_version_number%type;
735: l_attribute_category amw_process.attribute_category%type;
736: l_attribute1 amw_process.attribute1%type;
737: l_attribute2 amw_process.attribute2%type;
738: l_attribute3 amw_process.attribute3%type;
739: l_attribute4 amw_process.attribute4%type;
740: l_attribute5 amw_process.attribute5%type;
741: l_attribute6 amw_process.attribute6%type;
734: l_object_version_number amw_process.object_version_number%type;
735: l_attribute_category amw_process.attribute_category%type;
736: l_attribute1 amw_process.attribute1%type;
737: l_attribute2 amw_process.attribute2%type;
738: l_attribute3 amw_process.attribute3%type;
739: l_attribute4 amw_process.attribute4%type;
740: l_attribute5 amw_process.attribute5%type;
741: l_attribute6 amw_process.attribute6%type;
742: l_attribute7 amw_process.attribute7%type;
735: l_attribute_category amw_process.attribute_category%type;
736: l_attribute1 amw_process.attribute1%type;
737: l_attribute2 amw_process.attribute2%type;
738: l_attribute3 amw_process.attribute3%type;
739: l_attribute4 amw_process.attribute4%type;
740: l_attribute5 amw_process.attribute5%type;
741: l_attribute6 amw_process.attribute6%type;
742: l_attribute7 amw_process.attribute7%type;
743: l_attribute8 amw_process.attribute8%type;
736: l_attribute1 amw_process.attribute1%type;
737: l_attribute2 amw_process.attribute2%type;
738: l_attribute3 amw_process.attribute3%type;
739: l_attribute4 amw_process.attribute4%type;
740: l_attribute5 amw_process.attribute5%type;
741: l_attribute6 amw_process.attribute6%type;
742: l_attribute7 amw_process.attribute7%type;
743: l_attribute8 amw_process.attribute8%type;
744: l_attribute9 amw_process.attribute9%type;
737: l_attribute2 amw_process.attribute2%type;
738: l_attribute3 amw_process.attribute3%type;
739: l_attribute4 amw_process.attribute4%type;
740: l_attribute5 amw_process.attribute5%type;
741: l_attribute6 amw_process.attribute6%type;
742: l_attribute7 amw_process.attribute7%type;
743: l_attribute8 amw_process.attribute8%type;
744: l_attribute9 amw_process.attribute9%type;
745: l_attribute10 amw_process.attribute10%type;
738: l_attribute3 amw_process.attribute3%type;
739: l_attribute4 amw_process.attribute4%type;
740: l_attribute5 amw_process.attribute5%type;
741: l_attribute6 amw_process.attribute6%type;
742: l_attribute7 amw_process.attribute7%type;
743: l_attribute8 amw_process.attribute8%type;
744: l_attribute9 amw_process.attribute9%type;
745: l_attribute10 amw_process.attribute10%type;
746: l_attribute11 amw_process.attribute11%type;
739: l_attribute4 amw_process.attribute4%type;
740: l_attribute5 amw_process.attribute5%type;
741: l_attribute6 amw_process.attribute6%type;
742: l_attribute7 amw_process.attribute7%type;
743: l_attribute8 amw_process.attribute8%type;
744: l_attribute9 amw_process.attribute9%type;
745: l_attribute10 amw_process.attribute10%type;
746: l_attribute11 amw_process.attribute11%type;
747: l_attribute12 amw_process.attribute12%type;
740: l_attribute5 amw_process.attribute5%type;
741: l_attribute6 amw_process.attribute6%type;
742: l_attribute7 amw_process.attribute7%type;
743: l_attribute8 amw_process.attribute8%type;
744: l_attribute9 amw_process.attribute9%type;
745: l_attribute10 amw_process.attribute10%type;
746: l_attribute11 amw_process.attribute11%type;
747: l_attribute12 amw_process.attribute12%type;
748: l_attribute13 amw_process.attribute13%type;
741: l_attribute6 amw_process.attribute6%type;
742: l_attribute7 amw_process.attribute7%type;
743: l_attribute8 amw_process.attribute8%type;
744: l_attribute9 amw_process.attribute9%type;
745: l_attribute10 amw_process.attribute10%type;
746: l_attribute11 amw_process.attribute11%type;
747: l_attribute12 amw_process.attribute12%type;
748: l_attribute13 amw_process.attribute13%type;
749: l_attribute14 amw_process.attribute14%type;
742: l_attribute7 amw_process.attribute7%type;
743: l_attribute8 amw_process.attribute8%type;
744: l_attribute9 amw_process.attribute9%type;
745: l_attribute10 amw_process.attribute10%type;
746: l_attribute11 amw_process.attribute11%type;
747: l_attribute12 amw_process.attribute12%type;
748: l_attribute13 amw_process.attribute13%type;
749: l_attribute14 amw_process.attribute14%type;
750: l_attribute15 amw_process.attribute15%type;
743: l_attribute8 amw_process.attribute8%type;
744: l_attribute9 amw_process.attribute9%type;
745: l_attribute10 amw_process.attribute10%type;
746: l_attribute11 amw_process.attribute11%type;
747: l_attribute12 amw_process.attribute12%type;
748: l_attribute13 amw_process.attribute13%type;
749: l_attribute14 amw_process.attribute14%type;
750: l_attribute15 amw_process.attribute15%type;
751:
744: l_attribute9 amw_process.attribute9%type;
745: l_attribute10 amw_process.attribute10%type;
746: l_attribute11 amw_process.attribute11%type;
747: l_attribute12 amw_process.attribute12%type;
748: l_attribute13 amw_process.attribute13%type;
749: l_attribute14 amw_process.attribute14%type;
750: l_attribute15 amw_process.attribute15%type;
751:
752: l_created_from amw_process.created_from%type;
745: l_attribute10 amw_process.attribute10%type;
746: l_attribute11 amw_process.attribute11%type;
747: l_attribute12 amw_process.attribute12%type;
748: l_attribute13 amw_process.attribute13%type;
749: l_attribute14 amw_process.attribute14%type;
750: l_attribute15 amw_process.attribute15%type;
751:
752: l_created_from amw_process.created_from%type;
753: l_program_update_date amw_process.program_update_date%type;
746: l_attribute11 amw_process.attribute11%type;
747: l_attribute12 amw_process.attribute12%type;
748: l_attribute13 amw_process.attribute13%type;
749: l_attribute14 amw_process.attribute14%type;
750: l_attribute15 amw_process.attribute15%type;
751:
752: l_created_from amw_process.created_from%type;
753: l_program_update_date amw_process.program_update_date%type;
754: l_program_id amw_process.program_id%type;
748: l_attribute13 amw_process.attribute13%type;
749: l_attribute14 amw_process.attribute14%type;
750: l_attribute15 amw_process.attribute15%type;
751:
752: l_created_from amw_process.created_from%type;
753: l_program_update_date amw_process.program_update_date%type;
754: l_program_id amw_process.program_id%type;
755: l_program_application_id amw_process.program_application_id%type;
756: l_request_id amw_process.request_id%type;
749: l_attribute14 amw_process.attribute14%type;
750: l_attribute15 amw_process.attribute15%type;
751:
752: l_created_from amw_process.created_from%type;
753: l_program_update_date amw_process.program_update_date%type;
754: l_program_id amw_process.program_id%type;
755: l_program_application_id amw_process.program_application_id%type;
756: l_request_id amw_process.request_id%type;
757: l_risk_count_latest amw_process.risk_count_latest%type;
750: l_attribute15 amw_process.attribute15%type;
751:
752: l_created_from amw_process.created_from%type;
753: l_program_update_date amw_process.program_update_date%type;
754: l_program_id amw_process.program_id%type;
755: l_program_application_id amw_process.program_application_id%type;
756: l_request_id amw_process.request_id%type;
757: l_risk_count_latest amw_process.risk_count_latest%type;
758: l_control_count_latest amw_process.control_count_latest%type;
751:
752: l_created_from amw_process.created_from%type;
753: l_program_update_date amw_process.program_update_date%type;
754: l_program_id amw_process.program_id%type;
755: l_program_application_id amw_process.program_application_id%type;
756: l_request_id amw_process.request_id%type;
757: l_risk_count_latest amw_process.risk_count_latest%type;
758: l_control_count_latest amw_process.control_count_latest%type;
759:
752: l_created_from amw_process.created_from%type;
753: l_program_update_date amw_process.program_update_date%type;
754: l_program_id amw_process.program_id%type;
755: l_program_application_id amw_process.program_application_id%type;
756: l_request_id amw_process.request_id%type;
757: l_risk_count_latest amw_process.risk_count_latest%type;
758: l_control_count_latest amw_process.control_count_latest%type;
759:
760: l_new_process_rev_id number;
753: l_program_update_date amw_process.program_update_date%type;
754: l_program_id amw_process.program_id%type;
755: l_program_application_id amw_process.program_application_id%type;
756: l_request_id amw_process.request_id%type;
757: l_risk_count_latest amw_process.risk_count_latest%type;
758: l_control_count_latest amw_process.control_count_latest%type;
759:
760: l_new_process_rev_id number;
761:
754: l_program_id amw_process.program_id%type;
755: l_program_application_id amw_process.program_application_id%type;
756: l_request_id amw_process.request_id%type;
757: l_risk_count_latest amw_process.risk_count_latest%type;
758: l_control_count_latest amw_process.control_count_latest%type;
759:
760: l_new_process_rev_id number;
761:
762: l_display_name amw_process_names_tl.display_name%type;
758: l_control_count_latest amw_process.control_count_latest%type;
759:
760: l_new_process_rev_id number;
761:
762: l_display_name amw_process_names_tl.display_name%type;
763: l_description amw_process_names_tl.description%type;
764: l_language amw_process_names_tl.language%type;
765: l_source_lang amw_process_names_tl.source_lang%type;
766:
759:
760: l_new_process_rev_id number;
761:
762: l_display_name amw_process_names_tl.display_name%type;
763: l_description amw_process_names_tl.description%type;
764: l_language amw_process_names_tl.language%type;
765: l_source_lang amw_process_names_tl.source_lang%type;
766:
767: l_classification amw_process.classification%type;
760: l_new_process_rev_id number;
761:
762: l_display_name amw_process_names_tl.display_name%type;
763: l_description amw_process_names_tl.description%type;
764: l_language amw_process_names_tl.language%type;
765: l_source_lang amw_process_names_tl.source_lang%type;
766:
767: l_classification amw_process.classification%type;
768:
761:
762: l_display_name amw_process_names_tl.display_name%type;
763: l_description amw_process_names_tl.description%type;
764: l_language amw_process_names_tl.language%type;
765: l_source_lang amw_process_names_tl.source_lang%type;
766:
767: l_classification amw_process.classification%type;
768:
769: -- l_sysdate DATE := sysdate;
763: l_description amw_process_names_tl.description%type;
764: l_language amw_process_names_tl.language%type;
765: l_source_lang amw_process_names_tl.source_lang%type;
766:
767: l_classification amw_process.classification%type;
768:
769: -- l_sysdate DATE := sysdate;
770:
771: l_dummy number;
810: l_risk_count_latest, l_control_count_latest,
811: l_classification
812:
813:
814: from amw_process
815:
816: where process_id = p_process_id
817: and (end_date is null or end_date > l_sysdate);
818:
828:
829: --revise the parent process
830: then
831:
832: insert into amw_process (PROCESS_ID,
833: ITEM_TYPE,
834: NAME,
835: PROCESS_CODE,
836: REVISION_NUMBER,
887: l_item_type,
888: l_name,
889: l_process_code,
890: l_revision_number + 1,
891: AMW_PROCESS_S.nextval,
892: 'D',
893: l_sysdate,
894: l_control_count,
895: l_risk_count,
943:
944:
945:
946:
947: update amw_process set
948: last_update_date = l_sysdate,
949: last_updated_by = G_USER_ID,
950: last_update_login = G_LOGIN_ID,
951: end_date = l_sysdate,
974: select display_name, description, language, source_lang
975:
976: into l_display_name, l_description, l_language, l_source_lang
977:
978: from amw_process_names_tl
979:
980: where process_id = l_process_id and
981: revision_number = l_revision_number and
982: language = userenv('LANG');
988: end if;
989:
990:
991:
992: insert into amw_process_names_tl
993: (
994: process_id
995: ,revision_number
996: ,process_rev_id
1023: where L.INSTALLED_FLAG in ('I', 'B');
1024:
1025: --write code to copy attachments of the previous rev_id to the new one
1026: fnd_attached_documents2_pkg.copy_attachments(
1027: X_from_entity_name => 'AMW_PROCESS',
1028: X_from_pk1_value => l_process_rev_id,
1029: X_to_entity_name => 'AMW_PROCESS',
1030: X_to_pk1_value => l_new_process_rev_id,
1031: X_created_by => g_user_id,
1025: --write code to copy attachments of the previous rev_id to the new one
1026: fnd_attached_documents2_pkg.copy_attachments(
1027: X_from_entity_name => 'AMW_PROCESS',
1028: X_from_pk1_value => l_process_rev_id,
1029: X_to_entity_name => 'AMW_PROCESS',
1030: X_to_pk1_value => l_new_process_rev_id,
1031: X_created_by => g_user_id,
1032: X_last_update_login => g_login_id,
1033: X_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
1096: function get_approval_status(p_process_id in number)
1097: return varchar2
1098: is
1099:
1100: l_approval_status amw_process.approval_status%type;
1101: begin
1102: l_approval_status := 'D';
1103:
1104: select approval_status into l_approval_status
1101: begin
1102: l_approval_status := 'D';
1103:
1104: select approval_status into l_approval_status
1105: from amw_process where process_id = p_process_id and
1106: end_date is null;
1107:
1108: return l_approval_status;
1109:
1133: p_init_msg_list varchar2(10) := FND_API.G_FALSE;
1134:
1135: l_dummy pls_integer;
1136:
1137: l_approval_status amw_process.approval_status%type;
1138:
1139: l_child_order_number amw_latest_hierarchies.child_order_number%type;
1140:
1141: CURSOR c1 is
1180:
1181: --check if parent process is locked
1182: if (is_locked_process(p_parent_process_id))
1183: then
1184: raise amw_process_locked_exception;
1185: end if;
1186:
1187:
1188: /* check if the child is an ancestor of parent already
1245: l_child_order_number);
1246:
1247:
1248: /* update the denorm table
1249: * can throw an amw_processing_exception, so it has been handled.
1250: */
1251: update_denorm_add_child(p_parent_id => p_parent_process_id,
1252: p_child_id => p_child_process_id,
1253: l_sysdate => l_sysdate);
1318: FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1319: p_count => x_msg_count,
1320: p_data => x_msg_data);
1321:
1322: when amw_process_locked_exception then
1323: x_return_status := FND_API.G_RET_STS_ERROR;
1324: x_msg_count := x_msg_count + 1;
1325: fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
1326: x_msg_data := fnd_message.get;
1321:
1322: when amw_process_locked_exception then
1323: x_return_status := FND_API.G_RET_STS_ERROR;
1324: x_msg_count := x_msg_count + 1;
1325: fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
1326: x_msg_data := fnd_message.get;
1327: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1328: p_procedure_name => 'add_existing_process_as_child',
1329: p_error_text => x_msg_data);
1343: p_error_text => x_msg_count);
1344: raise FND_API.G_EXC_ERROR;
1345:
1346:
1347: when amw_processing_exception then
1348: ROLLBACK;
1349: x_return_status := FND_API.G_RET_STS_ERROR;
1350: FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1351: p_count => x_msg_count,
1384:
1385: l_api_name constant varchar2(30) := 'delete_child';
1386: p_init_msg_list varchar2(10) := FND_API.G_FALSE;
1387:
1388: l_approval_status amw_process.approval_status%type;
1389:
1390: l_dummy number;
1391:
1392: CURSOR c3 is
1421:
1422: --check if parent process is locked
1423: if (is_locked_process(p_parent_process_id))
1424: then
1425: raise amw_process_locked_exception;
1426: end if;
1427:
1428:
1429:
1536: FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1537: p_count => x_msg_count,
1538: p_data => x_msg_data);
1539:
1540: when amw_process_locked_exception then
1541: x_return_status := FND_API.G_RET_STS_ERROR;
1542: x_msg_count := x_msg_count + 1;
1543: fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
1544: x_msg_data := fnd_message.get;
1539:
1540: when amw_process_locked_exception then
1541: x_return_status := FND_API.G_RET_STS_ERROR;
1542: x_msg_count := x_msg_count + 1;
1543: fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
1544: x_msg_data := fnd_message.get;
1545: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1546: p_procedure_name => 'delete_child',
1547: p_error_text => x_msg_data);
1547: p_error_text => x_msg_data);
1548: raise FND_API.G_EXC_ERROR;
1549:
1550:
1551: when amw_processing_exception then
1552: ROLLBACK;
1553: x_return_status := FND_API.G_RET_STS_ERROR;
1554: FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1555: p_count => x_msg_count,
1584: */
1585: function is_locked_process(p_process_id in number) return boolean is
1586:
1587: cursor c_locks(l_process_id number) is
1588: select locked_process_id from amw_process_locks
1589: where (organization_id is null or organization_id=-1) and
1590: locked_process_id=l_process_id;
1591: l_dummy number;
1592: begin
1620:
1621:
1622: /* this is called to get the process_id from the name and item
1623: * type. This function will RAISE AN EXCEPTION (not return null)
1624: * if it does not find such a process in amw_process table
1625: */
1626: function get_process_id_from_wf_params(p_name in varchar2,
1627: p_item_type in varchar2)
1628: return number is
1626: function get_process_id_from_wf_params(p_name in varchar2,
1627: p_item_type in varchar2)
1628: return number is
1629:
1630: l_process_id amw_process.process_id%type;
1631:
1632: begin
1633:
1634: select process_id into l_process_id from amw_process
1630: l_process_id amw_process.process_id%type;
1631:
1632: begin
1633:
1634: select process_id into l_process_id from amw_process
1635: where name = p_name
1636: and item_type = p_item_type
1637: and end_date is null;
1638:
1651: l_dummy pls_integer;
1652:
1653: begin
1654:
1655: select 1 into l_dummy from amw_process
1656: where name = p_name
1657: and item_type = p_item_type
1658: and end_date is null;
1659:
1684: p_param_name => 'PROCESS_CODE_PREFIX');
1685:
1686:
1687: while (true) loop
1688: select l_prefix || to_char(AMW_PROCESS_CODE_S.nextval) into l_code from dual;
1689:
1690: select 1 into l_dummy from amw_process
1691: where process_code = l_code
1692: and end_date is null;
1686:
1687: while (true) loop
1688: select l_prefix || to_char(AMW_PROCESS_CODE_S.nextval) into l_code from dual;
1689:
1690: select 1 into l_dummy from amw_process
1691: where process_code = l_code
1692: and end_date is null;
1693:
1694: end loop;
1711: return number
1712:
1713: is
1714:
1715: l_process_id amw_process.process_id%type;
1716: l_process_rev_id amw_process.process_rev_id%type;
1717: l_new_code amw_process.process_code%type;
1718: begin
1719: l_new_code := get_process_code;
1712:
1713: is
1714:
1715: l_process_id amw_process.process_id%type;
1716: l_process_rev_id amw_process.process_rev_id%type;
1717: l_new_code amw_process.process_code%type;
1718: begin
1719: l_new_code := get_process_code;
1720:
1713: is
1714:
1715: l_process_id amw_process.process_id%type;
1716: l_process_rev_id amw_process.process_rev_id%type;
1717: l_new_code amw_process.process_code%type;
1718: begin
1719: l_new_code := get_process_code;
1720:
1721: insert into amw_process (PROCESS_ID,
1717: l_new_code amw_process.process_code%type;
1718: begin
1719: l_new_code := get_process_code;
1720:
1721: insert into amw_process (PROCESS_ID,
1722: ITEM_TYPE,
1723: NAME,
1724: PROCESS_CODE,
1725: REVISION_NUMBER,
1745: STANDARD_PROCESS_FLAG,
1746: PROCESS_CATEGORY)
1747:
1748: VALUES
1749: (AMW_PROCESS_S.nextval,
1750: p_item_type,
1751: p_name,
1752: l_new_code,
1753: 1,
1750: p_item_type,
1751: p_name,
1752: l_new_code,
1753: 1,
1754: AMW_PROCESS_S.nextval,
1755: 'D',
1756: g_sysdate,
1757: 0,
1758: 0,
1777: INTO
1778: l_process_id, l_process_rev_id;
1779:
1780:
1781: insert into amw_process_names_tl
1782: (
1783: process_id
1784: ,revision_number
1785: ,process_rev_id
1819: function is_deleted_process(p_process_id in number)
1820: return boolean is
1821: l_dummy pls_integer;
1822: begin
1823: select 1 into l_dummy from amw_process
1824: where process_id = p_process_id
1825: and end_date is null
1826: and deletion_date is not null;
1827:
1922: -- if for first time : value is 'ARRIVED'
1923: -- if already visited : value is 'VISITED'
1924:
1925: visiting_status varchar2(8);
1926: l_process_id amw_process.process_id%type;
1927: l_dummy pls_integer;
1928: exist_in_icm boolean;
1929: cur_child_id amw_process.process_id%type;
1930: l_approval_status amw_process.approval_status%type;
1925: visiting_status varchar2(8);
1926: l_process_id amw_process.process_id%type;
1927: l_dummy pls_integer;
1928: exist_in_icm boolean;
1929: cur_child_id amw_process.process_id%type;
1930: l_approval_status amw_process.approval_status%type;
1931: security_check varchar2(1);
1932:
1933: begin
1926: l_process_id amw_process.process_id%type;
1927: l_dummy pls_integer;
1928: exist_in_icm boolean;
1929: cur_child_id amw_process.process_id%type;
1930: l_approval_status amw_process.approval_status%type;
1931: security_check varchar2(1);
1932:
1933: begin
1934:
2047: l_process_id := get_process_id_from_wf_params(p_name, p_item_type);
2048:
2049: if(is_deleted_process(l_process_id))
2050: then
2051: raise amw_process_deleted_exception;
2052: end if;
2053:
2054: if(is_ancestor_in_hierarchy(l_process_id, p_parent_process_id))
2055: then
2060: --process to be able to add it under a process on which the user has the
2061: --privilege!
2062:
2063: security_check := amw_security_utils_pvt.check_function(p_function => 'AMW_UPD_RL_PROC'
2064: ,p_object_name => 'AMW_PROCESS_APPR_ETTY'
2065: ,p_instance_pk1_value => to_char(l_process_id)
2066: ,p_instance_pk2_value => '*NULL*'
2067: ,p_instance_pk3_value => '*NULL*'
2068: ,p_instance_pk4_value => '*NULL*'
2139: if(wf_diff_icm.exists(1) or icm_diff_wf.exists(1))
2140: then
2141: if(is_locked_process(l_process_id))
2142: then
2143: raise amw_process_locked_exception;
2144: end if;
2145: revise_process_if_necessary(p_process_id => l_process_id,
2146: l_sysdate => g_sysdate);
2147: end if;
2188:
2189: if(p_parent_process_id = -1)
2190: then
2191: select approval_status into l_approval_status
2192: from amw_process
2193: where process_id = l_process_id
2194: and end_date is null;
2195:
2196: if(l_approval_status = 'A')
2381: update_all_latest_rc_counts(p_mode => 'RC');
2382: END IF;
2383:
2384: exception
2385: when amw_process_locked_exception then
2386: ROLLBACK;
2387: x_return_status := FND_API.G_RET_STS_ERROR;
2388: x_msg_count := x_msg_count + 1;
2389: fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
2385: when amw_process_locked_exception then
2386: ROLLBACK;
2387: x_return_status := FND_API.G_RET_STS_ERROR;
2388: x_msg_count := x_msg_count + 1;
2389: fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
2390: x_msg_data := fnd_message.get;
2391: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2392: p_procedure_name => 'import_wf_process',
2393: p_error_text => x_msg_data);
2404: p_error_text => x_msg_data);
2405: raise FND_API.G_EXC_ERROR;
2406:
2407:
2408: when amw_process_deleted_exception then
2409: ROLLBACK;
2410: x_return_status := FND_API.G_RET_STS_ERROR;
2411: x_msg_count := x_msg_count + 1;
2412: fnd_message.set_name('AMW','AMW_DELETED_PROC_MODIF');
2479: l_api_name constant varchar2(30) := 'add_WEBADI_HIERARCHY_LINKS';
2480: p_init_msg_list varchar2(10) := FND_API.G_FALSE;
2481: l_dummy pls_integer;
2482: L_CHILD_ORDER NUMBER;
2483: l_approval_status amw_process.approval_status%type;
2484: l_child_order_number amw_latest_hierarchies.child_order_number%type;
2485:
2486: CURSOR c1 is
2487: (select parent_process_id,
2527:
2528: --check if parent process is locked
2529: if (is_locked_process(p_parent_process_id))
2530: then
2531: raise amw_process_locked_exception;
2532: end if;
2533:
2534:
2535: /* check if the child is an ancestor of parent already
2601: l_child_order_number);
2602:
2603:
2604: /* update the denorm table
2605: * can throw an amw_processing_exception, so it has been handled.
2606: */
2607: ---COMMENTING THE BELOW ... DON'T NEED THIS FROM WEBADI
2608: /*
2609: update_denorm_add_child(p_parent_id => p_parent_process_id,
2615: * and its parent was -1
2616: * then the approved hierarchy needs to be modified
2617: */
2618: select approval_status into l_approval_status
2619: from amw_process where process_id = p_child_process_id and
2620: end_date is null;
2621:
2622: if(l_approval_status = 'A' and p_parent_process_id = -1)
2623:
2677: FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2678: p_count => x_msg_count,
2679: p_data => x_msg_data);
2680:
2681: when amw_process_locked_exception then
2682: x_return_status := FND_API.G_RET_STS_ERROR;
2683: x_msg_count := x_msg_count + 1;
2684: fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
2685: x_msg_data := fnd_message.get;
2680:
2681: when amw_process_locked_exception then
2682: x_return_status := FND_API.G_RET_STS_ERROR;
2683: x_msg_count := x_msg_count + 1;
2684: fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
2685: x_msg_data := fnd_message.get;
2686: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2687: p_procedure_name => 'add_WEBADI_HIERARCHY_LINKS',
2688: p_error_text => x_msg_data);
2702: p_error_text => x_msg_data);
2703: raise FND_API.G_EXC_ERROR;
2704:
2705:
2706: when amw_processing_exception then
2707: ROLLBACK;
2708: x_return_status := FND_API.G_RET_STS_ERROR;
2709: FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2710: p_count => x_msg_count,
2742: is
2743:
2744:
2745: cursor c is
2746: (select process_id from amw_process where process_id in
2747: ((select parent_child_id
2748: from amw_proc_hierarchy_denorm
2749: where process_id = p_process_id
2750: and up_down_ind = 'U'
2760: close c;
2761:
2762: if x.exists(1) then
2763: forall ctr in x.first .. x.last
2764: update amw_process
2765: set risk_count = (select count(*) from (
2766: select distinct risk_id from amw_risk_associations
2767: where pk1 in ( ( select parent_child_id
2768: from amw_proc_hierarchy_denorm
2807: */
2808: procedure update_approved_control_counts(p_process_id in number)
2809: is
2810: cursor c is
2811: (select process_id from amw_process where process_id in
2812: ((select parent_child_id
2813: from amw_proc_hierarchy_denorm
2814: where process_id = p_process_id
2815: and up_down_ind = 'U'
2826: close c;
2827:
2828: if x.exists(1) then
2829: forall ctr in x.first .. x.last
2830: update amw_process
2831: set control_count = (select count(*) from (
2832: select distinct control_id from amw_control_associations
2833: where pk1 in ( ( select parent_child_id
2834: from amw_proc_hierarchy_denorm
2863: */
2864: procedure update_appr_risk_counts is
2865:
2866: cursor c is
2867: (select process_id from amw_process where process_id in
2868: (select parent_child_id
2869: from amw_proc_hierarchy_denorm
2870: where process_id = -1
2871: and up_down_ind = 'D'
2879: close c;
2880:
2881: if x.exists(1) then
2882: forall ctr in x.first .. x.last
2883: update amw_process
2884: set risk_count = (select count(*) from (
2885: select distinct risk_id from amw_risk_associations
2886: where pk1 in ( ( select parent_child_id
2887: from amw_proc_hierarchy_denorm
2910: end update_appr_risk_counts;
2911:
2912: procedure update_appr_control_counts is
2913: cursor c is
2914: (select process_id from amw_process where process_id in
2915: (select parent_child_id
2916: from amw_proc_hierarchy_denorm
2917: where process_id = -1
2918: and up_down_ind = 'D'
2928: close c;
2929:
2930: if x.exists(1) then
2931: forall ctr in x.first .. x.last
2932: update amw_process
2933: set control_count = (select count(*) from (
2934: select distinct control_id from amw_control_associations
2935: where pk1 in ( (select parent_child_id
2936: from amw_proc_hierarchy_denorm
2970: procedure update_latest_control_counts(p_process_id in number)
2971: is
2972:
2973: cursor c is
2974: (select process_id from amw_process where process_id in
2975: (select parent_child_id
2976: from amw_proc_hierarchy_denorm
2977: where process_id = p_process_id
2978: and up_down_ind = 'U'
2987: close c;
2988:
2989: if x.exists(1) then
2990: forall ctr in x.first .. x.last
2991: update amw_process
2992: set control_count_latest = (select count(*) from (
2993: select distinct control_id from amw_control_associations
2994: where pk1 in ( ( select parent_child_id
2995: from amw_proc_hierarchy_denorm
3022:
3023: procedure update_latest_risk_counts(p_process_id in number)
3024: is
3025: cursor c is
3026: (select process_id from amw_process where process_id in
3027: (select parent_child_id
3028: from amw_proc_hierarchy_denorm
3029: where process_id = p_process_id
3030: and up_down_ind = 'U'
3041: close c;
3042:
3043: if x.exists(1) then
3044: forall ctr in x.first .. x.last
3045: update amw_process
3046: set risk_count_latest = (select count(*) from (
3047: select distinct risk_id from amw_risk_associations
3048: where pk1 in ( (select parent_child_id
3049: from amw_proc_hierarchy_denorm
3117: procedure update_all_latest_rc_counts(p_mode in varchar2)
3118:
3119: is
3120: cursor c is
3121: (select process_id from amw_process where end_date is null and process_id <> -1);
3122: type t_n is table of number;
3123: x t_n;
3124:
3125: begin
3130: if x.exists(1) then
3131: if(p_mode = 'R')
3132: then
3133: forall ctr in x.first .. x.last
3134: update amw_process
3135: set risk_count_latest = (select count(*) from (
3136: select distinct risk_id
3137: from amw_risk_associations
3138: where pk1 in ((select parent_child_id
3152:
3153: elsif (p_mode = 'C')
3154: then
3155: forall ctr in x.first .. x.last
3156: update amw_process
3157: set control_count_latest = (select count(*) from
3158:
3159: (select distinct control_id from amw_control_associations
3160: where pk1 in ((select parent_child_id
3173: and end_date is null;
3174: elsif (p_mode = 'RC')
3175: then
3176: forall ctr in x.first .. x.last
3177: update amw_process
3178: set risk_count_latest = (select count(*) from (
3179: select distinct risk_id
3180: from amw_risk_associations
3181: where pk1 in ((select parent_child_id
3234: */
3235: procedure update_all_org_counts
3236:
3237: is
3238: cursor c is (select process_id from amw_process where
3239: approval_date is not null
3240: and approval_end_date is null
3241: and process_id <> -1);
3242: type t_n is table of number;
3247: close c;
3248:
3249: if x.exists(1) then
3250: forall ctr in x.first .. x.last
3251: update amw_process
3252: set org_count = (select count(*) from
3253: (select distinct organization_id
3254: from amw_process_organization
3255: where process_id = x(ctr)
3250: forall ctr in x.first .. x.last
3251: update amw_process
3252: set org_count = (select count(*) from
3253: (select distinct organization_id
3254: from amw_process_organization
3255: where process_id = x(ctr)
3256: and end_date is null
3257: and (deletion_date is null or (deletion_date is not null and approval_date is null)))),
3258: object_version_number = object_version_number + 1
3272:
3273: is
3274:
3275: begin
3276: update amw_process
3277: set org_count = (select count(*) from
3278: (select distinct organization_id
3279: from amw_process_organization
3280: where process_id = p_process_id
3275: begin
3276: update amw_process
3277: set org_count = (select count(*) from
3278: (select distinct organization_id
3279: from amw_process_organization
3280: where process_id = p_process_id
3281: and end_date is null
3282: and (deletion_date is null or (deletion_date is not null and approval_date is null)))),
3283: object_version_number = object_version_number + 1
3326: raise FND_API.G_EXC_ERROR;
3327: end if;
3328:
3329: --First remove all the old attachments
3330: FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS',
3331: X_pk1_value => p_old_prev_id);
3332:
3333: --copy over all attachments of the temporary rev id
3334: fnd_attached_documents2_pkg.copy_attachments(
3331: X_pk1_value => p_old_prev_id);
3332:
3333: --copy over all attachments of the temporary rev id
3334: fnd_attached_documents2_pkg.copy_attachments(
3335: X_from_entity_name => 'AMW_PROCESS',
3336: X_from_pk1_value => p_new_prev_id,
3337: X_to_entity_name => 'AMW_PROCESS',
3338: X_to_pk1_value => p_old_prev_id,
3339: X_created_by => g_user_id,
3333: --copy over all attachments of the temporary rev id
3334: fnd_attached_documents2_pkg.copy_attachments(
3335: X_from_entity_name => 'AMW_PROCESS',
3336: X_from_pk1_value => p_new_prev_id,
3337: X_to_entity_name => 'AMW_PROCESS',
3338: X_to_pk1_value => p_old_prev_id,
3339: X_created_by => g_user_id,
3340: X_last_update_login => g_login_id,
3341: X_program_id => FND_GLOBAL.CONC_PROGRAM_ID,
3343:
3344:
3345: --remove all the attachments of the temporary id
3346:
3347: FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS',
3348: X_pk1_value => p_new_prev_id);
3349:
3350:
3351: exception
3373: l_name number;
3374:
3375: begin
3376:
3377: select amw_process_name_s.nextval into l_name from dual;
3378:
3379: l_pid := create_new_icm_process(
3380: p_name => to_char(l_name),
3381: p_item_type => p_item_type,
3381: p_item_type => p_item_type,
3382: p_display_name => p_display_name,
3383: p_description => p_description);
3384:
3385: update amw_process
3386: set PROCESS_TYPE = decode(p_control_type, 'A', 'C', 'M', 'C', 'B', 'C', PROCESS_TYPE),
3387: CONTROL_ACTIVITY_TYPE = decode(p_control_type, '-1', CONTROL_ACTIVITY_TYPE, p_control_type)
3388: where process_id = l_pid
3389: and end_date is null;
3466: AMW_SECURITY_PUB.grant_role_guid
3467: (
3468: p_api_version => 1,
3469: p_role_name => 'AMW_RL_PROC_OWNER_ROLE',
3470: p_object_name => 'AMW_PROCESS_APPR_ETTY',
3471: p_instance_type => 'INSTANCE',
3472: p_instance_set_id => null,
3473: p_instance_pk1_value => l_process_id,
3474: p_instance_pk2_value => null,
3485: /* AMW_SECURITY_PUB.grant_role_guid
3486: (
3487: p_api_version => 1,
3488: p_role_name => 'AMW_RL_PROC_FINANCE_OWNER_ROLE',
3489: p_object_name => 'AMW_PROCESS_APPR_ETTY',
3490: p_instance_type => 'INSTANCE',
3491: p_instance_set_id => null,
3492: p_instance_pk1_value => l_process_id,
3493: p_instance_pk2_value => null,
3504: AMW_SECURITY_PUB.grant_role_guid
3505: (
3506: p_api_version => 1,
3507: p_role_name => 'AMW_RL_PROC_APPL_OWNER_ROLE',
3508: p_object_name => 'AMW_PROCESS_APPR_ETTY',
3509: p_instance_type => 'INSTANCE',
3510: p_instance_set_id => null,
3511: p_instance_pk1_value => l_process_id,
3512: p_instance_pk2_value => null,
3567: amw_rl_hierarchy_pkg.update_denorm (-1, sysdate);
3568: --updates approved hier denorm
3569: amw_rl_hierarchy_pkg.update_approved_denorm (-1, sysdate);
3570:
3571: update amw_process
3572: set risk_count = null,
3573: control_count = null,
3574: risk_count_latest = null,
3575: control_count_latest = null;
3678: l_dummy number;
3679: begin
3680: select 1
3681: into l_dummy
3682: from amw_process
3683: where process_id = p_process_id
3684: and approval_status = 'A';
3685:
3686: return 'Y';
3720: ret_value := 'N';
3721:
3722: -- check if the process is draft
3723:
3724: select approval_status into appstatus from amw_process
3725: where process_id = p_process_id and end_date is null;
3726:
3727: if appstatus <> 'D' then
3728: return;
3803:
3804:
3805: --ko we need this to be outside of IF block to delete the attachments.
3806: select process_rev_id
3807: into previd from amw_process
3808: where process_id = p_process_id
3809: and end_date is null;
3810:
3811: appexst := does_apprvd_ver_exst(p_process_id);
3832: raise FND_API.G_EXC_UNEXPECTED_ERROR;
3833: end if;
3834:
3835:
3836: delete from amw_process
3837: where process_id = p_process_id
3838: and end_date is null;
3839:
3840: delete from amw_process_names_tl
3836: delete from amw_process
3837: where process_id = p_process_id
3838: and end_date is null;
3839:
3840: delete from amw_process_names_tl
3841: where process_rev_id = previd;
3842:
3843: update amw_process
3844: set end_date = null
3839:
3840: delete from amw_process_names_tl
3841: where process_rev_id = previd;
3842:
3843: update amw_process
3844: set end_date = null
3845: where process_id = p_process_id
3846: and approval_date is not null
3847: and approval_end_date is null;
3848:
3849: else -- appexst = 'N'
3850:
3851: select standard_process_flag, process_rev_id
3852: into l_flag, previd from amw_process
3853: where process_id = p_process_id;
3854:
3855: if l_flag = 'Y' then
3856: begin
3855: if l_flag = 'Y' then
3856: begin
3857: select 1 into l_dummy from dual
3858: where exists
3859: (select 1 from amw_process
3860: where standard_variation = previd
3861: and end_date is null);
3862:
3863: fnd_message.set_name('AMW','AMW_CANT_DEL_DRAFT_NS');
3885: --kosriniv need to de link the children this process has..
3886: delete from amw_latest_hierarchies
3887: where parent_id = p_process_id
3888: and (organization_id is null or organization_id = -1);
3889: delete from amw_process where process_id = p_process_id;
3890: delete from amw_process_names_tl where process_rev_id = previd;
3891:
3892: end if;
3893:
3886: delete from amw_latest_hierarchies
3887: where parent_id = p_process_id
3888: and (organization_id is null or organization_id = -1);
3889: delete from amw_process where process_id = p_process_id;
3890: delete from amw_process_names_tl where process_rev_id = previd;
3891:
3892: end if;
3893:
3894: -- perform other common delete operations
3971: and deletion_date is not null
3972: and deletion_approval_date is null;
3973:
3974:
3975: FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments(X_entity_name => 'AMW_PROCESS',
3976: X_pk1_value => previd);
3977:
3978: -- cancel existing change requests
3979:
4058: AMW_SECURITY_PUB.grant_role_guid
4059: (
4060: p_api_version => 1,
4061: p_role_name => 'AMW_RL_PROC_OWNER_ROLE',
4062: p_object_name => 'AMW_PROCESS_APPR_ETTY',
4063: p_instance_type => 'INSTANCE',
4064: p_instance_set_id => null,
4065: p_instance_pk1_value => p_process_id,
4066: p_instance_pk2_value => null,
4174: x_msg_data out nocopy varchar2)
4175: is
4176: l_api_name constant varchar2(30) := 'add_activities';
4177: l_dummy pls_integer;
4178: l_approval_status amw_process.approval_status%type;
4179: l_child_order_number amw_latest_hierarchies.child_order_number%type;
4180: p_init_msg_list varchar2(10) := FND_API.G_TRUE;
4181: str varchar2(4000);
4182: diff number;
4201:
4202: --check if parent process is locked
4203: if (is_locked_process(p_parent_process_id))
4204: then
4205: raise amw_process_locked_exception;
4206: end if;
4207:
4208: l_child_string := p_child_id_string;
4209: while LENGTH(l_child_string) <> 0 loop
4279: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4280: FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4281: p_count => x_msg_count,
4282: p_data => x_msg_data);
4283: when amw_process_locked_exception then
4284: x_return_status := FND_API.G_RET_STS_ERROR;
4285: x_msg_count := x_msg_count + 1;
4286: fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
4287: x_msg_data := fnd_message.get;
4282: p_data => x_msg_data);
4283: when amw_process_locked_exception then
4284: x_return_status := FND_API.G_RET_STS_ERROR;
4285: x_msg_count := x_msg_count + 1;
4286: fnd_message.set_name('AMW','AMW_PROCESS_LOCKED');
4287: x_msg_data := fnd_message.get;
4288: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
4289: p_procedure_name => l_api_name,
4290: p_error_text => x_msg_data);
4297: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
4298: p_procedure_name => l_api_name,
4299: p_error_text => x_msg_count);
4300: raise FND_API.G_EXC_ERROR;
4301: when amw_processing_exception then
4302: ROLLBACK;
4303: x_return_status := FND_API.G_RET_STS_ERROR;
4304: FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4305: p_count => x_msg_count,