PACKAGE BODY: APPS.IGI_IMP_IAC_PURGE_PKG

Source

1 PACKAGE BODY IGI_IMP_IAC_PURGE_PKG AS
2 -- $Header: igiimpub.pls 120.6.12000000.1 2007/08/01 16:21:46 npandya ship $
3 
4 --===========================FND_LOG.START=====================================
5 
6 g_state_level NUMBER	     :=	FND_LOG.LEVEL_STATEMENT;
7 g_proc_level  NUMBER	     :=	FND_LOG.LEVEL_PROCEDURE;
8 g_event_level NUMBER	     :=	FND_LOG.LEVEL_EVENT;
9 g_excep_level NUMBER	     :=	FND_LOG.LEVEL_EXCEPTION;
10 g_error_level NUMBER	     :=	FND_LOG.LEVEL_ERROR;
11 g_unexp_level NUMBER	     :=	FND_LOG.LEVEL_UNEXPECTED;
12 g_path        VARCHAR2(100)  := 'IGI.PLSQL.igiimpub.igi_imp_iac_purge_pkg.';
13 
14 --===========================FND_LOG.END=====================================
15 
16 PROCEDURE PURGE_IMP_DATA(ERRBUF OUT NOCOPY VARCHAR2,
17                          RETCODE OUT NOCOPY NUMBER,
18                          p_book_type_code IN VARCHAR2,
19 			 p_category_struct_id IN NUMBER,
20                          p_category_id IN NUMBER,
21                          p_asset_id IN NUMBER)IS
22 
23 V_COUNT NUMBER;
24 l_message VARCHAR2(300);
25 l_path_name VARCHAR2(150) := g_path||'purge_imp_data';
26 category_count NUMBER;
27 asset_count    NUMBER;
28 BEGIN
29 
30 SELECT COUNT(*)
31 INTO V_COUNT
32 FROM IGI_IMP_IAC_INTERFACE_CTRL
33 WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE
34 AND TRANSFER_STATUS = 'C';
35 
36 IF V_COUNT = 0 THEN
37 
38     IF p_category_id IS NOT NULL THEN
39         SELECT count(*)
40         INTO category_count
41         FROM IGI_IMP_IAC_INTERFACE_CTRL
42         WHERE BOOK_TYPE_CODE = p_book_type_code
43           AND CATEGORY_ID <> p_category_id;
44 
45         IF p_asset_id IS NOT NULL THEN
46             SELECT count(*)
47             INTO asset_count
48             FROM IGI_IMP_IAC_INTERFACE
49             WHERE BOOK_TYPE_CODE = p_book_type_code
50               AND CATEGORY_ID = p_category_id
51               AND ASSET_ID <> p_asset_id;
52         ELSE
53             asset_count := 0;
54         END IF;
55     ELSE
56         category_count := 0;
57         asset_count := 0;
58     END IF;
59 
60     IF category_count = 0 AND asset_count = 0 THEN
61 
62         DELETE FROM IGI_IMP_IAC_CONTROLS
63         WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE;
64 
65         DELETE FROM IGI_IMP_IAC_INTERFACE
66         WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE;
67 
68         DELETE FROM IGI_IMP_IAC_INTERFACE_CTRL
69         WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE;
70 
71         DELETE FROM IGI_IMP_IAC_INTERFACE_PY_ADD
72         WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE;
73 
74         DELETE FROM IGI_IMP_IAC_INTERMEDIATE
75         WHERE BOOK_TYPE_CODE = P_BOOK_TYPE_CODE;
76 
77     ELSIF category_count > 0 AND asset_count = 0 THEN
78 
79         DELETE FROM IGI_IMP_IAC_INTERFACE
80         WHERE CATEGORY_ID = p_category_id;
81 
82         DELETE FROM IGI_IMP_IAC_INTERFACE_CTRL
83         WHERE CATEGORY_ID = p_category_id;
84 
85         DELETE FROM IGI_IMP_IAC_INTERFACE_PY_ADD
86         WHERE CATEGORY_ID = p_category_id;
87 
88         DELETE FROM IGI_IMP_IAC_INTERMEDIATE
89         WHERE CATEGORY_ID = p_category_id;
90 
91     ELSE
92 
93         DELETE FROM IGI_IMP_IAC_INTERFACE
94         WHERE ASSET_ID = p_asset_id;
95 
96         DELETE FROM IGI_IMP_IAC_INTERFACE_PY_ADD
97         WHERE ASSET_ID = p_asset_id;
98 
99         DELETE FROM IGI_IMP_IAC_INTERMEDIATE
100         WHERE ASSET_ID = p_asset_id;
101 
102 
103     END IF;
104 
105 ELSE
106 
107 FND_MESSAGE.SET_NAME('IGI','IGI_IMP_IAC_ASSETS_TRF');
108 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,
109 		  p_full_path => l_path_name,
110 		  p_remove_from_stack => FALSE);
111 l_message := fnd_message.get;
112 errbuf := l_message;
113 fnd_file.put_line(fnd_file.log, errbuf);
114 END IF;
115 
116 END;
117 END; -- package