463: PROCEDURE NAME: Process_unapprove_releases
464:
465: DESCRIPTION:
466: This procedure does the following:
467: - Open cursor PO_RELEASES table to select
468: Incomplete or Requires_reapproval documents.
469:
470: - For each unapprove document, initiate the
471: PO Approval Reminder workflow notification.
482:
483: CURSOR unapprove_REL IS
484: SELECT PORH.PO_release_ID, POH.Segment1, PORH.release_num,
485: POH.Type_Lookup_Code, PORH.Agent_ID
486: FROM PO_RELEASES_ALL PORH, --
488: WHERE NVL(PORH.authorization_status,'INCOMPLETE') IN
489: ('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL')
490: -- AND WF_ITEM_TYPE = NULL
1037: CURSOR REL_acceptance IS
1038: SELECT PORH.PO_release_ID, POH.Segment1, PORH.release_num,
1039: POH.Type_Lookup_Code, PORH.Agent_ID,
1040: NVL(PORH.Acceptance_Due_Date, SYSDATE)
1041: FROM PO_RELEASES_ALL PORH, --
1043: WHERE NVL(PORH.authorization_status,'INCOMPLETE') = 'APPROVED'
1044: AND NVL(PORH.acceptance_required_flag,'N')= 'Y'
1045: AND NVL(PORH.cancel_flag,'N') = 'N'
1993: ELSE
1994:
1995: SELECT NVL(acceptance_due_date, SYSDATE)
1996: INTO l_acceptance_due_date
1997: FROM PO_RELEASES
1998: WHERE po_release_id = l_doc_header_id;
1999: END IF;
2000:
2001: IF l_acceptance_due_date is not NULL AND
2880: l_doc_type := 'RELEASE';
2881:
2882: select agent_id
2883: into l_agent_id
2884: from po_releases_all /*Bug6632095: using base table instead of view */
2885: where po_release_id = p_DocumentID;
2886:
2887: --bug#3709971 modified the structure of item key from
2888: --doc_type||doc_subtype||doc_num||agent_id(old structure) to
2947: l_doc_type := 'RELEASE';
2948:
2949: select agent_id
2950: into l_agent_id
2951: from po_releases_all /*Bug6632095: using base table instead of view */
2952: where po_release_id = p_DocumentID;
2953: --bug#3709971 modified the structure of item key from
2954: --doc_type||doc_subtype||doc_num||agent_id(old structure) to
2955: --doc_type||'-'||doc_subtype||'-'||doc_num||'-'||agent_id
3003:
3004: select poh.type_lookup_code
3005: into l_doc_subtype
3006: from po_headers_all poh, --
3007: po_releases_all por /*Bug6632095: using base table instead of view */
3008: where por.po_header_id = poh.po_header_id and
3009: por.po_release_id = p_DocumentId;
3010:
3011: if (l_doc_subtype = 'PLANNED') then