PACKAGE BODY: APPS.PO_HR_INTERFACE_PVT

Source

1 PACKAGE BODY PO_HR_INTERFACE_PVT AS
2 /* $Header: POHRINTB.pls 120.3 2008/06/26 06:52:56 adbharga noship $*/
3 
4 g_pkg_name       CONSTANT VARCHAR2(30) := 'PO_HR_INTERFACE_PVT';
5 g_log_head       CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
6 g_debug_unexp    BOOLEAN := PO_DEBUG.is_debug_unexp_on;
7 g_debug_stmt     BOOLEAN := PO_DEBUG.is_debug_stmt_on;
8 
9 
10 PROCEDURE is_Supplier_Updatable (
11                  p_assignment_id        IN  NUMBER,
12                  p_effective_date IN  DATE DEFAULT NULL
13                  )
14 
15 IS
16   l_api_name           CONSTANT VARCHAR2(30) := 'isSupplierUpdatable';
17   l_log_head           CONSTANT VARCHAR2(100) := g_log_head || l_api_name;
18   l_api_version        CONSTANT NUMBER := 1.0;
19   l_progress           VARCHAR2(3) := '000';
20   X_flag               VARCHAR2(1):='N';
21 
22 
23 
24 BEGIN
25   PO_DEBUG.debug_stmt(l_log_head,l_progress,'Start');
26 
27   l_progress:=010;
28   BEGIN
29     select 'Y'
30     INTO X_flag
31     from dual
32     where exists(
33         SELECT pca.po_header_id
34         FROM po_cwk_associations pca,
35              per_all_assignments_f paaf,
36              po_headers_all pha ,
37              po_lines_all pl
38         WHERE paaf.assignment_id = p_assignment_id
39         AND paaf.person_id = pca.cwk_person_id
40         AND paaf.vendor_id = pha.vendor_id
41         AND paaf.vendor_site_id = pha.vendor_site_id
42         AND paaf.job_id = pl.job_id
43         AND paaf.person_id IS NOT NULL
44         AND paaf.vendor_id IS NOT NULL
45         AND paaf.vendor_site_id IS NOT NULL
46         AND pca.po_header_id = pha.po_header_id
47         AND pha.po_header_id = pl.po_header_id
48         AND Nvl(p_effective_date,SYSDATE)
49                BETWEEN Nvl(paaf.effective_start_date,SYSDATE-1) AND Nvl(paaf.effective_end_date,SYSDATE+1)
50      );
51   EXCEPTION
52     when NO_DATA_FOUND THEN
53     PO_DEBUG.debug_stmt(l_log_head,l_progress,'No Record found');
54     RETURN;
55   END;
56 
57   l_progress:=020;
58   IF X_flag='Y' THEN
59     PO_DEBUG.debug_stmt(l_log_head,l_progress,'Record found');
60     fnd_message.set_name('PO','PO_ACTIVE_SUPPLIER_EXISTS');
61     APP_EXCEPTION.RAISE_EXCEPTION;
62 
63   ELSE
64     PO_DEBUG.debug_stmt(l_log_head,l_progress,'No Record found');
65     RETURN;
66   END IF;
67 
68 END is_Supplier_Updatable;
69 
70 END PO_HR_INTERFACE_PVT;
71