[Home] [Help]
PACKAGE BODY: APPS.AS_OPP_INITIAL_LOG_PKG
Source
1 PACKAGE BODY AS_OPP_INITIAL_LOG_PKG as
2 /* $Header: asxoplgb.pls 120.2 2005/08/12 06:00:27 appldev ship $ */
3
4
5 PROCEDURE write_log(p_module VARCHAR2, p_debug_source NUMBER, p_fpt number, p_mssg varchar2) IS
6 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
7 BEGIN
8 --IF G_Debug AND p_debug_source = G_DEBUG_TRIGGER THEN
9 -- Write debug message to message stack
10 IF l_debug THEN
11 AS_UTILITY_PVT.Debug_Message(p_module, FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, p_mssg);
12 END IF;
13 --END IF;
14 IF p_debug_source = G_DEBUG_CONCURRENT THEN
15 -- p_fpt (1,2)?(log : output)
16 FND_FILE.put(p_fpt, p_mssg);
17 FND_FILE.NEW_LINE(p_fpt, 1);
18 -- If p_fpt == 2 and debug flag then also write to log file
19 IF p_fpt = 2 And G_Debug THEN
20 FND_FILE.put(1, p_mssg);
21 FND_FILE.NEW_LINE(1, 1);
22 END IF;
23 END IF;
24 EXCEPTION
25 WHEN OTHERS THEN
26 NULL;
27 END Write_Log;
28 -- Why doesn't use dbms_session.set_sql_trace(TRUE) ?
29 PROCEDURE trace (p_mode in boolean) is
30 ddl_curs integer;
31 v_Dummy integer;
32 BEGIN
33 null;
34 EXCEPTION WHEN OTHERS THEN
35 NULL;
36 END trace;
37 PROCEDURE Initial_logs( ERRBUF OUT NOCOPY VARCHAR2,
38 RETCODE OUT NOCOPY VARCHAR2,
39 p_debug_mode IN VARCHAR2,
40 p_trace_mode IN VARCHAR2)
41 IS
42 l_status Boolean;
43 l_module CONSTANT VARCHAR2(255) := 'as.plsql.opinlog.Initial_logs';
44 BEGIN
45 IF p_debug_mode = 'Y' THEN G_Debug := TRUE; ELSE G_Debug := FALSE; END IF;
46 IF p_trace_mode = 'Y' THEN trace(TRUE); ELSE trace(FALSE); END IF;
47 IF( UPPER(nvl(FND_PROFILE.VALUE('AS_OPP_ENABLE_LOG'), 'N')) = 'Y' ) THEN
48 BEGIN
49
50
51 Insert into as_leads_log(
52 LOG_ID , LEAD_ID ,CREATED_BY ,CREATION_DATE ,
53 LAST_UPDATED_BY , LAST_UPDATE_DATE , LAST_UPDATE_LOGIN , STATUS_CODE ,
54 SALES_STAGE_ID , WIN_PROBABILITY , DECISION_DATE , SECURITY_GROUP_ID ,
55 ADDRESS_ID , CHANNEL_CODE , CURRENCY_CODE , TOTAL_AMOUNT ,
56 LOG_MODE , CUSTOMER_ID , DESCRIPTION ,SOURCE_PROMOTION_ID ,
57 OFFER_ID , CLOSE_COMPETITOR_ID , VEHICLE_RESPONSE_CODE , SALES_METHODOLOGY_ID ,
58 OWNER_SALESFORCE_ID ,OWNER_SALES_GROUP_ID , ORG_ID,
59 LOG_START_DATE, LOG_END_DATE, LOG_ACTIVE_DAYS, ENDDAY_LOG_FLAG, CURRENT_LOG )
60
61 SELECT AS_LEAD_LOG_S.nextval , a.LEAD_ID ,a.CREATED_BY ,a.CREATION_DATE ,
62 a.LAST_UPDATED_BY , a.LAST_UPDATE_DATE , a.LAST_UPDATE_LOGIN , a.STATUS ,
63 a.SALES_STAGE_ID , a.WIN_PROBABILITY , a.DECISION_DATE , a.SECURITY_GROUP_ID ,
64 a.ADDRESS_ID , a.CHANNEL_CODE , a.CURRENCY_CODE , a.TOTAL_AMOUNT ,
65 'I', a.CUSTOMER_ID , a.DESCRIPTION ,a.SOURCE_PROMOTION_ID ,
66 a.OFFER_ID , a.CLOSE_COMPETITOR_ID , a.VEHICLE_RESPONSE_CODE , a.SALES_METHODOLOGY_ID ,
67 a.OWNER_SALESFORCE_ID ,a.OWNER_SALES_GROUP_ID , a.ORG_ID,
68 LAST_UPDATE_DATE, LAST_UPDATE_DATE, 0, 'Y', 1
69 FROM AS_LEADS_ALL a
70 where a.lead_id not in ( select log.lead_id from AS_LEADS_LOG log);
71
72 write_log(l_module, G_DEBUG_CONCURRENT, 1, 'Refresh of as_leads_log Process Completed @: '||to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
73 COMMIT;
74 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
75 ERRBUF := ERRBUF||'Error in logging updation of AS_LEADS_LOG:'||to_char(sqlcode)||sqlerrm;
76 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR ;
77 write_log(l_module, G_DEBUG_CONCURRENT, 1,'Error in logging updation of AS_LEADS_LOG');
78 write_log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
79 ROLLBACK;
80 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
81 IF l_status = TRUE THEN
82 write_log(l_module, G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
83 END IF;
84 WHEN OTHERS THEN
85 ERRBUF := ERRBUF||'Error :'||to_char(sqlcode)||sqlerrm;
86 RETCODE := '2';
87 write_log(l_module, G_DEBUG_CONCURRENT, 1,'Error in SC Denorm Main');
88 write_log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
89 ROLLBACK;
90 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
91 IF l_status = TRUE THEN
92 write_log(l_module, G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
93 END IF;
94 END;
95 END IF;
96 IF( UPPER(nvl(FND_PROFILE.VALUE('AS_OPP_LINE_ENABLE_LOG'), 'N')) = 'Y' ) THEN
97 BEGIN
98 Insert into AS_LEAD_LINES_LOG (
99 log_id, lead_id, lead_line_id,
100 last_update_date, last_updated_by, last_update_login, creation_date,
101 created_by, log_mode, interest_type_id, primary_interest_code_id, secondary_interest_code_id,
102 product_category_id, product_cat_set_id,
103 inventory_item_id, organization_id, source_promotion_id, offer_id, org_id,
104 forecast_date, rolling_forecast_flag, endday_log_flag)
105 SELECT AS_LEAD_LINES_LOG_S.NEXTVAL , a.lead_id, a.lead_line_id,
106 a.last_update_date, a.last_updated_by, a.last_update_login, a.creation_date,
107 a.created_by, 'I', a.interest_type_id, a.primary_interest_code_id, a.secondary_interest_code_id,
108 a.product_category_id, a.product_cat_set_id,
109 a.inventory_item_id, a.organization_id, a.source_promotion_id, a.offer_id, a.org_id,
110 a.forecast_date , a.rolling_forecast_flag, 'Y'
111 FROM AS_LEAD_LINES_ALL a
112 where a.lead_line_id not in ( select log.lead_line_id from AS_LEAD_LINES_LOG log);
113 write_log(l_module, G_DEBUG_CONCURRENT, 1, 'Refresh of as_lead_lines_log Process Completed @: '||to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
114 COMMIT;
115 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
116 ERRBUF := ERRBUF||'Error in loging of AS_LEAD_LINES_LOG:'||to_char(sqlcode)||sqlerrm;
117 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR ;
118 write_log(l_module, G_DEBUG_CONCURRENT, 1,'Error in loging of AS_LEAD_LINES_LOG');
119 write_log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
120 ROLLBACK;
121 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
122 IF l_status = TRUE THEN
123 write_log(l_module, G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
124 END IF;
125 WHEN OTHERS THEN
126 ERRBUF := ERRBUF||'Error in loging of AS_LEAD_LINES_LOG:'||to_char(sqlcode)||sqlerrm;
127 RETCODE := '2';
128 write_log(l_module, G_DEBUG_CONCURRENT, 1,'Error in loging of AS_LEAD_LINES_LOG');
129 write_log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
130 ROLLBACK;
131 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
132 IF l_status = TRUE THEN
133 write_log(l_module, G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
134 END IF;
135 END;
136 END IF;
137 IF( UPPER(nvl(FND_PROFILE.VALUE('AS_OPP_SC_ENABLE_LOG'), 'N')) = 'Y' ) THEN
138 BEGIN
139 Insert into AS_SALES_CREDITS_LOG ( log_id,
140 lead_id, lead_line_id, sales_credit_id, last_update_date,
141 last_updated_by, last_update_login, creation_date, created_by,
142 log_mode, salesforce_id, salesgroup_id, credit_type_id,
143 credit_percent, credit_amount, endday_log_flag)
144 SELECT AS_SALES_CREDIT_LOG_S.NEXTVAL ,
145 lead_id, lead_line_id, sales_credit_id, last_update_date,
146 last_updated_by, last_update_login, creation_date, created_by,
147 'I', salesforce_id, salesgroup_id, credit_type_id,
148 credit_percent, credit_amount, 'Y'
149 FROM AS_SALES_CREDITS
150 where AS_SALES_CREDITS.sales_credit_id not in (select AS_SALES_CREDITS_LOG.sales_credit_id
151 FROM AS_SALES_CREDITS_LOG);
152 write_log(l_module, G_DEBUG_CONCURRENT, 1, 'Refresh of as_sales_credits_log Process Completed @: '||to_char(sysdate,'DD-MON-RRRR:HH:MI:SS'));
153 COMMIT;
154 EXCEPTION WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
155 ERRBUF := ERRBUF||'Error in login of AS_SALES_CREDITS_LOG:'||to_char(sqlcode)||sqlerrm;
156 RETCODE := FND_API.G_RET_STS_UNEXP_ERROR ;
157 write_log(l_module, G_DEBUG_CONCURRENT, 1,'Error in login of AS_SALES_CREDITS_LOG');
158 write_log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
159 ROLLBACK;
160 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
161 IF l_status = TRUE THEN
162 write_log(l_module, G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
163 END IF;
164 WHEN OTHERS THEN
165 ERRBUF := ERRBUF||'Error in login of AS_SALES_CREDITS_LOG:'||to_char(sqlcode)||sqlerrm;
166 RETCODE := '2';
167 write_log(l_module, G_DEBUG_CONCURRENT, 1,'Error in login of AS_SALES_CREDITS_LOG');
168 write_log(l_module, G_DEBUG_CONCURRENT, 1,sqlerrm);
169 ROLLBACK;
170 l_status := fnd_concurrent.set_completion_status('ERROR',sqlerrm);
171 IF l_status = TRUE THEN
172 write_log(l_module, G_DEBUG_CONCURRENT, 1, 'Error, can not complete Concurrent Program') ;
173 END IF;
174 END;
175 END IF;
176 END Initial_logs;
177 END AS_OPP_INITIAL_LOG_PKG;