[Home] [Help]
PACKAGE BODY: APPS.CST_OSFMUPGRADE_PVT
Source
1 PACKAGE BODY CST_OSFMUpgrade_PVT AS
2 /* $Header: CSTVUPGB.pls 120.2 2006/02/10 11:44:28 ssreddy noship $ */
3
4 G_PKG_NAME VARCHAR2(240) := 'CST_OSFMUpgrade_PVT';
5
6 PROCEDURE Update_Quantity_Issued(
7 ERRBUF OUT NOCOPY VARCHAR2,
8 RETCODE OUT NOCOPY NUMBER,
9 p_organization_id IN NUMBER,
10 p_api_version IN NUMBER ) IS
11 l_stmt_num NUMBER;
12 l_mmtt NUMBER;
13 l_uncosted_mmt NUMBER;
14 /* API */
15 l_api_name CONSTANT VARCHAR2(240) := 'Update_Quantity_Issued';
16 l_api_version CONSTANT NUMBER := 1.0;
17 conc_status BOOLEAN;
18 BEGIN
19
20 l_stmt_num := 5;
21 IF NOT FND_API.COMPATIBLE_API_CALL (
22 l_api_version,
23 p_api_version,
24 l_api_name,
25 G_PKG_NAME ) THEN
26 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
27 END IF;
28
29 /* Check for any uncosted WIP transactions. All WIP transactions
30 will have to be costed before the upgrade can take place */
31 l_stmt_num := 10;
32 SELECT count(*)
33 INTO l_mmtt
34 FROM mtl_material_transactions_temp mmtt,
35 mtl_parameters MP
36 WHERE mmtt.organization_id = MP.organization_id and
37 MP.wsm_enabled_flag = 'Y' and
38 mmtt.transaction_source_type_id = 5 and
39 mmtt.transaction_status <> 2 and
40 MP.organization_id = nvl(p_organization_id, MP.organization_id) and
41 rownum = 1;
42
43 l_stmt_num := 20;
44 SELECT count(*)
45 INTO l_uncosted_mmt
46 FROM mtl_material_transactions mmt,
47 mtl_parameters MP
48 WHERE mmt.organization_id = MP.organization_id and
49 mmt.costed_flag IN ('N','E') and
50 MP.wsm_enabled_flag = 'Y' and
51 mmt.transaction_source_type_id = 5 and
52 MP.organization_id = nvl(p_organization_id, MP.organization_id) and
53 rownum = 1;
54
55 if (l_mmtt <> 0 OR l_uncosted_mmt <> 0 ) THEN
56 FND_FILE.PUT_LINE(FND_FILE.LOG,'Uncosted transactions exist. Please ensure that '||
57 'all transactions are costed before running this script');
58 FND_FILE.PUT_LINE(FND_FILE.LOG,' Pending MMTT txns : '||l_mmtt||
59 ' Uncosted/Errored MMT txns :'||l_uncosted_mmt);
60 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR','Uncosted transactions exists. Please ensure that all transactions are costed');
61 RETURN;
62 END IF;
63
64 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Updating WRO ...');
65 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_organization_id : '||p_organization_id);
66
67 IF (p_organization_id IS NULL) THEN
68 l_stmt_num := 30;
69 UPDATE wip_requirement_operations wro
70 SET costed_quantity_issued = NVL(quantity_issued,0),
71 costed_quantity_relieved = NVL(quantity_relieved,0)
72 WHERE exists ( SELECT 1
73 FROM wip_entities we, wip_discrete_jobs wdj
74 WHERE we.wip_entity_id = wro.wip_entity_id
75 AND we.organization_id = wro.organization_id
76 AND we.entity_type = 5
77 AND we.wip_entity_id = wdj.wip_entity_id
78 AND we.organization_id = wdj.organization_id
79 AND wdj.status_type NOT IN (1,12));
80
81 ELSE
82 l_stmt_num := 40;
83 UPDATE wip_requirement_operations wro
84 SET costed_quantity_issued = NVL(quantity_issued,0),
85 costed_quantity_relieved = NVL(quantity_relieved,0)
86 WHERE wro.organization_id = p_organization_id
87 AND exists ( SELECT 1
88 FROM wip_entities we, wip_discrete_jobs wdj
89 WHERE we.wip_entity_id = wro.wip_entity_id
90 AND we.organization_id = wro.organization_id
91 AND we.entity_type = 5
92 AND we.wip_entity_id = wdj.wip_entity_id
93 AND we.organization_id = wdj.organization_id
94 AND wdj.status_type NOT IN (1,12));
95
96 END IF;
97 COMMIT;
98
99 RETURN;
100 EXCEPTION
101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
102 RETCODE := SQLCODE;
103 ERRBUF := 'Inconsistent API version'||l_stmt_num||'): '||
104 substr(SQLERRM, 1, 200);
105 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', ERRBUF);
106 fnd_file.put_line(fnd_file.log,ERRBUF);
107 WHEN OTHERS THEN
108 RETCODE := SQLCODE;
109 ERRBUF := 'CST_OSFMUpgrade_PVT.update_quantity_issued('||l_stmt_num||'):'||
110 substr(SQLERRM, 1, 200);
111 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', ERRBUF);
112 fnd_file.put_line(fnd_file.log,ERRBUF);
113
114 END Update_Quantity_Issued;
115
116 END CST_OSFMUpgrade_PVT;