PACKAGE BODY: APPS.PO_UPDATE_WO_MOD_PKG

Source

1 PACKAGE body po_update_wo_mod_pkg  AS
2 -- $Header: PO_UPDATE_WO_MOD_PKG.plb 120.1.12020000.2 2013/02/10 22:08:53 vegajula ship $
3 d_pkg_name CONSTANT VARCHAR2(50) := PO_LOG.get_package_base('PO_UPDATE_WO_MOD_PKG');
4 
5 PROCEDURE save_update_wo_mod(
6       p_po_header_id IN NUMBER,
7       p_attr_grp_id  IN NUMBER,
8       p_umb_prog_name IN VARCHAR2,
9       x_return_status OUT NOCOPY VARCHAR2 )
10 IS
11 
12 d_position NUMBER;
13 d_api_name         CONSTANT VARCHAR2(30)   := 'update_wo_mod';
14 d_module           CONSTANT VARCHAR2(2000) := d_pkg_name || d_api_name || '.';
15 
16 BEGIN
17 
18 d_position := 10;
19 
20 
21 BEGIN
22   UPDATE PO_HEADERS_ALL
23             SET CLM_COTR_OFFICE = (SELECT N_EXT_ATTR1 FROM po_headers_all_ext_b WHERE po_header_id = p_po_header_id AND ATTR_GROUP_ID= p_attr_grp_id and C_EXT_ATTR1 ='COTR_OFFICE' and DRAFT_ID = -1),
24                 CLM_COTR_CONTACT =(SELECT N_EXT_ATTR3 FROM po_headers_all_ext_b WHERE po_header_id = p_po_header_id AND ATTR_GROUP_ID= p_attr_grp_id and C_EXT_ATTR1 ='COTR_OFFICE' and DRAFT_ID = -1),
25                 UMBRELLA_PROGRAM_ID = (SELECT UMBRELLA_PROGRAM_ID FROM PON_UMBRELLA_PROGRAMS WHERE UMBRELLA_PROGRAM_NAME = p_umb_prog_name)
26                 WHERE   PO_HEADER_ID = p_po_header_id ;
27 
28   UPDATE po_drafts SET revision_num = -1 WHERE document_id =  p_po_header_id AND STATUS IN ('DRAFT','IN PROCESS','REJECTED','PRE-APPROVED','SUPPLIER SIGN') AND draft_type = 'MOD';
29  END;
30 
31 d_position := 20;
32 
33 IF (PO_LOG.d_stmt) THEN
34     PO_LOG.stmt(d_module,d_position ,'Updated po_headers_all and po_drafts');
35     PO_LOG.stmt(d_module,d_position ,'number of rows update ',SQL%ROWCOUNT);
36  END IF;
37 
38 x_return_status := FND_API.G_RET_STS_SUCCESS;
39 
40 EXCEPTION
41   WHEN OTHERS THEN
42     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
43     IF (PO_LOG.d_exc) THEN
44       PO_LOG.exc(d_module,SQLCODE || SQLERRM);
45     END IF;
46  RAISE;
47 
48 END save_update_wo_mod;
49 
50 END PO_UPDATE_WO_MOD_PKG;