PACKAGE BODY: APPS.INV_CONSUMPTION_TXN_PVT

Source

1 PACKAGE BODY INV_CONSUMPTION_TXN_PVT AS
2 -- $Header: INVVRETB.pls 120.1 2006/04/27 14:27:22 rajkrish noship $
3 --+=======================================================================+
4 --|               Copyright (c) 2003 Oracle Corporation                   |
5 --|                       Redwood Shores, CA, USA                         |
6 --|                         All rights reserved.                          |
7 --+=======================================================================+
8 --| FILENAME                                                              |
9 --|     INVVRETB.pls                                                      |
10 --|                                                                       |
11 --| DESCRIPTION                                                           |
12 --|    Create Records in mtl_consumption_transactions                     |
13 --|                                                                       |
14 --| PROCEDURE LIST                                                        |
15 --|      Price Update Insert                                              |
16 --|                                                                       |
17 --| HISTORY                                                               |
18 --|     07/22/03 David Herring   Created procedure                        |
19 --|     10/27/03 David Herring   Added update                             |
20 --|                              to mtl_material_transactions             |
21 --+========================================================================
22 
23 --===================
24 -- GLOBALS
25 --===================
26 
27 G_PKG_NAME CONSTANT    VARCHAR2(30) := 'INV_CONSUMPTION_TXN_PVT';
28 g_user_id              NUMBER       := FND_PROFILE.value('USER_ID');
29 g_resp_id              NUMBER       := FND_PROFILE.value('RESP_ID');
30 
31 --===================
32 -- PUBLIC PROCEDURES
33 --===================
34 
35 --========================================================================
36 -- PROCEDURE : Price Update Insert     PUBLIC
37 -- PARAMETERS: p_transaction_id            IN unique id and link to mmt
38 --             p_consumption_po_header_id  IN consumption advice (global)
39 --             p_consumption_release_id    IN consumption advice (local)
40 --             p_transaction_quantity      IN quantity retroactively priced
41 --
42 -- COMMENT   : This procedure will insert records
43 --           : into mtl_consumption_transactions
44 --           : mtl_material_transactions is updated with
45 --           : the owning org of the blanket.
46 --=========================================================================
47 PROCEDURE price_update_insert
48 ( p_transaction_id               IN   NUMBER
49 , p_consumption_po_header_id     IN   NUMBER
50 , p_consumption_release_id       IN   NUMBER
51 , p_transaction_quantity         IN   NUMBER
52 , p_po_distribution_id           IN   NUMBER
53 , x_msg_count                    OUT  NOCOPY NUMBER
54 , x_msg_data                     OUT  NOCOPY VARCHAR2
55 , x_return_status                OUT  NOCOPY VARCHAR2
56 )
57 IS
58 l_debug              NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
59 l_blanket            NUMBER;
60 l_owning_org_id      NUMBER;
61 BEGIN
62 
63   IF (l_debug = 1)
64   THEN
65     INV_LOG_UTIL.trace
66     ( '>> Update Consumption','INV_CONSUMPTION_TXN_PVT'
67      , 9
68      );
69   END IF;
70 
71   INSERT INTO mtl_consumption_transactions mct
72   ( mct.transaction_id
73   , mct.consumption_po_header_id
74   , mct.consumption_release_id
75   , mct.net_qty
76   , mct.created_by
77   , mct.creation_date
78   , mct.last_updated_by
79   , mct.last_update_date
80   , mct.last_update_login
81   , mct.consumption_processed_flag
82   , mct.po_distribution_id
83   )
84   VALUES
85   ( p_transaction_id
86   , p_consumption_po_header_id
87   , p_consumption_release_id
88   , p_transaction_quantity
89   , FND_GLOBAL.user_id
90   , sysdate
91   , FND_GLOBAL.user_id
92   , sysdate
93   , FND_GLOBAL.login_id
94   , 'Y'
95   , p_po_distribution_id
96   );
97 
98   -- populate the owning org in mtl_material_transactions
99   -- with the vendor site id
100   -- The is needed for the isp page to pick up the
101   -- price update transactions bug 3209997
102 
103   SELECT DISTINCT mmt.transaction_source_id
104   INTO l_blanket
105   FROM mtl_material_transactions mmt
106   WHERE mmt.transaction_id = p_transaction_id;
107 
108   SELECT DISTINCT poh.vendor_site_id
109   INTO l_owning_org_id
110   FROM po_headers_all poh
111   WHERE poh.po_header_id = l_blanket;
112 
113   UPDATE mtl_material_transactions mmt
114   SET mmt.owning_organization_id = l_owning_org_id
115      ,mmt.owning_tp_type = 1
116   WHERE mmt.transaction_id = p_transaction_id;
117 
118   IF (l_debug = 1)
119   THEN
120     INV_LOG_UTIL.trace
121     ( '<< Update Consumption','INV_CONSUMPTION_TXN_PVT'
122      , 9
123      );
124   END IF;
125 
126 EXCEPTION
127 
128   WHEN OTHERS THEN
129     x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130     --  Get message count and data
131     FND_MSG_PUB.Count_And_Get
132     ( p_count => x_msg_count
133     , p_data  => x_msg_data
134     );
135 
136 END price_update_insert;
137 
138 END INV_CONSUMPTION_TXN_PVT;