[Home] [Help]
PACKAGE BODY: APPS.PERIOD_SUMMARY_TRANSFER_UTIL
Source
1 PACKAGE BODY period_summary_TRANSFER_UTIL AS
2 /* $Header: INVPSTUB.pls 120.1 2005/06/11 12:24:58 appldev $ */
3
4 -- global constant
5 g_pkg_name VARCHAR2(100) := 'Period Summary Transfer(INV - WMS)';
6
7
8 PROCEDURE period_summary_transfer(
9 p_organization_id IN MTL_PARAMETERS.organization_id%TYPE,
10 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
11 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
12 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
13 IS
14
15 CURSOR org_cursor IS
16 SELECT mps.acct_period_id,
17 mps.organization_id,
18 mps.inventory_type,
19 sum(mps.inventory_value) sumvalue,
20 msi.default_cost_group_id
21 FROM mtl_period_summary mps, mtl_secondary_inventories msi
22 WHERE mps.organization_id = p_organization_id
23 AND mps.organization_id = msi.organization_id
24 AND mps.secondary_inventory = msi.secondary_inventory_name
25 GROUP BY mps.acct_period_id,
26 mps.organization_id,
27 mps.inventory_type,
28 msi.default_cost_group_id;
29
30 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
31 BEGIN
32
33 SAVEPOINT period_summary_transfer;
34 x_return_status := fnd_api.g_ret_sts_success;
35
36 FOR l_record IN org_cursor LOOP
37
38 IF (l_debug = 1) THEN
39 INV_TRX_UTIL_PUB.trace('organization_id = ' || l_record.organization_id, g_pkg_name,9);
40 INV_TRX_UTIL_PUB.trace('= ' || l_record.acct_period_id, g_pkg_name,9);
41 INV_TRX_UTIL_PUB.trace('= ' || l_record.organization_id, g_pkg_name,9);
42 INV_TRX_UTIL_PUB.trace('= ' || l_record.inventory_type, g_pkg_name,9);
43 INV_TRX_UTIL_PUB.trace('= ' || l_record.sumvalue, g_pkg_name,9);
44 INV_TRX_UTIL_PUB.trace('= ' || l_record.default_cost_group_id, g_pkg_name,9);
45 END IF;
46 INSERT INTO
47 mtl_period_cg_summary(acct_period_id,
48 organization_id,
49 inventory_type,
50 inventory_value,
51 cost_group_id,
52 last_update_date,
53 last_updated_by,
54 creation_date,
55 created_by,
56 last_update_login,
57 request_id,
58 program_application_id,
59 program_id,
60 program_update_date)
61 VALUES (l_record.acct_period_id,
62 l_record.organization_id,
63 l_record.inventory_type,
64 l_record.sumvalue,
65 l_record.default_cost_group_id,
66 SYSDATE,
67 fnd_global.user_id,
68 SYSDATE,
69 fnd_global.user_id,
70 fnd_global.login_id,
71 fnd_global.conc_request_id,
72 fnd_global.prog_appl_id,
73 fnd_global.conc_program_id,
74 SYSDATE);
75
76 END LOOP;
77
78 EXCEPTION
79 WHEN DUP_VAL_ON_INDEX THEN
80 fnd_message.set_name('INV', 'INV_SAVE_FAILURE');
81 fnd_msg_pub.add;
82 ROLLBACK TO period_summary_transfer ;
83 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
84 FND_MSG_PUB.Count_And_Get
85 (p_encoded => FND_API.G_FALSE,
86 p_count => x_msg_count,
87 p_data => x_msg_data);
88 WHEN OTHERS THEN
89 ROLLBACK TO period_summary_transfer ;
90 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
91 IF fnd_msg_pub.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
92 THEN
93 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name,
94 'period_summary_transfer' );
95 END IF;
96 FND_MSG_PUB.Count_And_Get
97 (p_encoded => FND_API.G_FALSE,
98 p_count => x_msg_count,
99 p_data => x_msg_data);
100
101 END period_summary_transfer;
102 --
103 END period_summary_TRANSFER_UTIL;