[Home] [Help]
PACKAGE BODY: APPS.CSTPSMCM
Source
1 PACKAGE BODY CSTPSMCM AS
2 /* $Header: CSTSMCMB.pls 115.5 2004/08/03 18:42:31 shizhang ship $ */
3 PROCEDURE WSM_COST_MANAGER(RETCODE out NOCOPY number,
4 ERRBUF out NOCOPY varchar2) IS
5
6 l_stmt_num NUMBER;
7 l_org_id NUMBER;
8 l_group_id NUMBER;
9 l_dummy NUMBER;
10 l_request_id NUMBER;
11 l_user_id NUMBER;
12 l_login_id NUMBER;
13 l_program_id NUMBER;
14 l_prog_appl_id NUMBER;
15 l_conc_program_id NUMBER;
16 l_debug varchar(80);
17 l_submit_req_id NUMBER;
18 l_err_code VARCHAR2(8000);
19 l_err_msg VARCHAR2(8000);
20 l_err_num NUMBER;
21 conc_status BOOLEAN;
22
23 CST_CSTPSMCW_RUNNING EXCEPTION;
24
25 CURSOR c_ORG_ID IS
26 SELECT DISTINCT organization_id
27 FROM WSM_SPLIT_MERGE_TRANSACTIONS
28 WHERE STATUS = WIP_CONSTANTS.COMPLETED
29 AND COSTED IN (WIP_CONSTANTS.PENDING,WIP_CONSTANTS.ERROR);
30 BEGIN
31 l_stmt_num := 5;
32
33 l_user_id := FND_GLOBAL.USER_ID;
34 l_login_id := FND_GLOBAL.LOGIN_ID;
35 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
36 l_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
37 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
38 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
39 l_err_code := '';
40 l_err_msg := '';
41 l_err_num := 0;
42
43 l_stmt_num := 7;
44
45 BEGIN
46 SELECT fcr.request_id
47 INTO l_dummy
48 FROM fnd_concurrent_requests fcr
49 WHERE program_application_id = 702
50 AND concurrent_program_id = l_program_id
51 AND phase_code IN ('I','P','R')
52 AND fcr.request_id <> l_request_id
53 AND ROWNUM=1;
54
55 EXCEPTION
56 WHEN NO_DATA_FOUND THEN
57 l_dummy := -1;
58 END;
59
60 IF (l_dummy <> -1) THEN
61 RAISE CST_CSTPSMCW_RUNNING;
62 END IF;
63
64
65 l_stmt_num := 10;
66 open c_ORG_ID;
67 Loop
68 FETCH c_ORG_ID into l_org_id;
69 EXIT WHEN c_ORG_ID%NOTFOUND;
70
71 l_stmt_num := 20;
72 SELECT wsm_split_merge_transactions_s.nextval
73 INTO l_group_id
74 FROM dual;
75
76 l_stmt_num := 30;
77 UPDATE wsm_split_merge_transactions
78 SET group_id = l_group_id,
79 costed = WIP_CONSTANTS.PENDING
80 WHERE organization_id = l_org_id
81 AND status = WIP_CONSTANTS.COMPLETED
82 AND COSTED IN (WIP_CONSTANTS.PENDING, WIP_CONSTANTS.ERROR);
83
84 l_submit_req_id := FND_REQUEST.SUBMIT_REQUEST('BOM',
85 'CSTPSMCW',
86 NULL,
87 NULL,
88 FALSE,
89 l_org_id,
90 l_group_id);
91 COMMIT;
92
93 IF (l_debug = 'Y') THEN
94 FND_FILE.PUT_LINE(FND_FILE.LOG,'Submitted CSTPSMCW: '
95 || TO_CHAR(l_submit_req_id)
96 || ', org_id: '|| l_org_id
97 || ', group_id: '|| l_group_id);
98 END IF;
99
100 END LOOP;
101
102 close c_ORG_ID;
103
104 IF (l_debug = 'Y') THEN
105 FND_FILE.PUT_LINE(FND_FILE.LOG,'CSTPSMCM Processing Complete.') ;
106 END IF;
107
108
109 EXCEPTION
110 WHEN CST_CSTPSMCW_RUNNING THEN
111
112 ROLLBACK;
113
114 l_err_code := SUBSTR('CSTPSMCM.wsm_cost_manager('
115 || to_char(l_stmt_num)
116 || '): - 24143 '
117 || 'Req_id: '
118 || TO_CHAR(l_dummy)
119 || ' . ',1,240);
120
121 fnd_message.set_name('BOM', 'CST_CSTPSMCW_RUNNING');
122 l_err_msg := fnd_message.get;
123 l_err_msg := SUBSTR(l_err_msg,1,240);
124 FND_FILE.PUT_LINE(fnd_file.log,SUBSTR(l_err_code
125 ||' '
126 ||l_err_msg,1,240));
127 CONC_STATUS := FND_CONCURRENT.
128 SET_COMPLETION_STATUS('ERROR',l_err_msg);
129
130
131 WHEN OTHERS THEN
132
133 ROLLBACK;
134 l_err_num := SQLCODE;
135 l_err_code := NULL;
136 l_err_msg := SUBSTR('CSTPSMCM.wsm_cost_manager('
137 || to_char(l_stmt_num)
138 || '): '
139 ||SQLERRM,1,240);
140 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
141 CONC_STATUS := FND_CONCURRENT.
142 SET_COMPLETION_STATUS('ERROR',l_err_msg);
143
144
145
146 END WSM_COST_MANAGER;
147
148 END CSTPSMCM;