[Home] [Help]
PACKAGE BODY: APPS.ASL_SUMM_LOAD_GLBL_PKG
Source
1 PACKAGE BODY asl_summ_load_glbl_pkg AS
2 /* $Header: aslmlogb.pls 120.1 2006/02/14 00:05:05 vjayamoh noship $ */
3
4
5 PROCEDURE Delete_Rows (
6 p_table_name IN VARCHAR2
7 , p_category_set_id IN NUMBER
8 , p_organization_id IN NUMBER
9 , p_category_id IN NUMBER
10 , x_err_msg OUT NOCOPY VARCHAR2
11 , x_err_code OUT NOCOPY VARCHAR2
12 ) IS
13
14 l_del_sql VARCHAR2(2000);
15 l_del_where1 VARCHAR2(1000);
16 l_del_where2 VARCHAR2(1000);
17 l_del_where3 VARCHAR2(1000);
18 l_del_where4 VARCHAR2(1000);
19 l_select VARCHAR2(2000);
20 l_where VARCHAR2(2000);
21 l_close VARCHAR2(200);
22
23 BEGIN
24 IF p_table_name = 'ASL_INVENTORY_ITEM_DENORM' THEN
25
26 l_del_sql := 'DELETE FROM ' || p_table_name ;
27 l_del_where1 := ' WHERE CATEGORY_SET_ID = :1 ';
28 l_del_where2 := ' AND ORGANIZATION_ID = :2 ';
29 l_del_where3 := ' AND CATEGORY_ID = :3 ';
30 l_del_where4 := ' AND LANGUAGE_CODE = USERENV (''LANG'') ';
31
32 IF p_category_id IS NULL THEN
33 EXECUTE IMMEDIATE l_del_sql||l_del_where1||l_del_where2||l_del_where4 using p_category_set_id, p_organization_id;
34 ELSE
35 EXECUTE IMMEDIATE l_del_sql||l_del_where1||l_del_where2||l_del_where3||l_del_where4 using p_category_set_id, p_organization_id, p_category_id;
36 END IF;
37
38 ELSIF p_table_name = 'ASL_INVENTORY_PRICING' THEN
39 l_del_sql := ' DELETE FROM ASL_INVENTORY_PRICING AI WHERE AI.INVENTORY_ITEM_ID IN (';
40 l_select := ' SELECT AI.INVENTORY_ITEM_ID FROM ASL_INVENTORY_PRICING AI, ASL_INVENTORY_ITEM_DENORM AD';
41 l_where := ' WHERE AI.INVENTORY_ITEM_ID = AD.INVENTORY_ITEM_ID AND AI.ORGANIZATION_ID = AD.ORGANIZATION_ID ';
42 l_del_where1 := ' AND AD.CATEGORY_SET_ID = :1 ';
43 l_del_where2 := ' AND AD.ORGANIZATION_ID = :2 ';
44 l_del_where3 := ' AND AD.CATEGORY_ID = :3 ';
45 l_close := ' )';
46
47 IF p_category_id IS NULL THEN
48 EXECUTE IMMEDIATE l_del_sql||l_select||l_where||l_del_where1||l_del_where2||l_close using p_category_set_id, p_organization_id;
49 ELSE
50 EXECUTE IMMEDIATE l_del_sql||l_select||l_where||l_del_where1||l_del_where2||l_del_where3||l_close using p_category_set_id, p_organization_id, p_category_id;
51 END IF;
52 ELSE
53 NULL;
54 END IF;
55
56
57 IF SQL%NOTFOUND THEN
58 NULL;
59 ELSE
60 COMMIT;
61 END IF;
62
63 x_err_msg := 'Delete_Rows: PASS';
64 x_err_code := '0';
65
66 END Delete_Rows;
67
68
69 PROCEDURE Write_Log(
70 p_table IN VARCHAR2 DEFAULT NULL
71 , p_action IN VARCHAR2 DEFAULT NULL
72 , p_procedure IN VARCHAR2 DEFAULT NULL
73 , p_num_rows IN NUMBER DEFAULT 0
74 , p_load_mode IN VARCHAR2 DEFAULT NULL
75 , p_message IN VARCHAR2 DEFAULT NULL
76 , p_start IN VARCHAR2 DEFAULT NULL
77 , p_end IN VARCHAR2 DEFAULT NULL
78 , p_load_year IN NUMBER DEFAULT NULL
79 , p_delete_mode IN VARCHAR2 DEFAULT NULL
80 ) IS
81
82 BEGIN
83
84 IF p_action = 'I' -- Insert
85 THEN
86 FND_MESSAGE.SET_NAME( 'ASL', 'ASL_LOAD_ROWS_INSERTED' );
87 FND_MESSAGE.SET_TOKEN( 'PROCEDURE', p_procedure );
88 FND_MESSAGE.SET_TOKEN( 'NUMBER_OF_ROWS', p_num_rows );
89
90 ELSIF p_action = 'U' -- Update
91 THEN
92 FND_MESSAGE.SET_NAME( 'ASL', 'ASL_LOAD_ROWS_UPDATED' );
93 FND_MESSAGE.SET_TOKEN( 'PROCEDURE', p_procedure );
94 FND_MESSAGE.SET_TOKEN( 'NUMBER_OF_ROWS', p_num_rows );
95
96 ELSIF p_action = 'D' -- Delete
97 THEN
98 FND_MESSAGE.SET_NAME( 'ASL', 'ASL_LOAD_ROWS_DELETED' );
99 FND_MESSAGE.SET_TOKEN( 'NUMBER_OF_ROWS', p_num_rows );
100
101 ELSIF p_action = 'E' -- Error
102 THEN
103 FND_MESSAGE.SET_NAME( 'ASL', 'ASL_LOAD_ERROR' );
104 FND_MESSAGE.SET_TOKEN( 'PROCEDURE', p_procedure );
105 FND_MESSAGE.SET_TOKEN( 'LOAD_MODE', p_load_mode );
106 FND_MESSAGE.SET_TOKEN( 'ERROR_MESSAGE', p_message );
107
108 ELSIF p_action = 'C' -- Completed
109 THEN
110 FND_MESSAGE.SET_NAME( 'ASL', 'ASL_LOAD_COMPLETED' );
111
112 ELSIF p_action = 'B' -- Load Begin
113 THEN
114 FND_MESSAGE.SET_NAME( 'ASL', 'ASL_LOAD_BEGIN' );
115 FND_MESSAGE.SET_TOKEN( 'START_RANGE', p_start );
116 FND_MESSAGE.SET_TOKEN( 'END_RANGE', p_end );
117 FND_MESSAGE.SET_TOKEN( 'LOAD_YEAR', p_load_year );
118 FND_MESSAGE.SET_TOKEN( 'LOAD_MODE', p_load_mode );
119 FND_MESSAGE.SET_TOKEN( 'DELETE_MODE', p_delete_mode );
120
121 ELSE -- Message
122 FND_MESSAGE.SET_NAME( 'ASL', 'ASL_LOAD_MESSAGE' );
123 FND_MESSAGE.SET_TOKEN( 'MESSAGE', p_message );
124 END IF;
125
126 FND_MESSAGE.SET_TOKEN( 'TABLE_NAME', p_table );
127 FND_MESSAGE.SET_TOKEN( 'DATE_TIME', to_char(SYSDATE,'DD-MON-YYYY HH:MI:SS') );
128
129 FND_FILE.PUT_LINE( FND_FILE.LOG, FND_MESSAGE.GET );
130 FND_MESSAGE.CLEAR;
131
132 END Write_Log;
133
134 END asl_summ_load_glbl_pkg;
135