1 PACKAGE BODY ar_movement_stat_pkg AS
2 /* $Header: armvstb.pls 115.1 99/07/17 00:56:24 porting ship $ */
3
4 --
5 -- PUBLIC PROCEDURES
6 --
7 -- PUBLIC VARIABLES
8 -- pseudo_movement_id
9 -- user_id
10 --
11
12 -- ***************************************************************
13 -- Procedure
14 -- upd_ar_invoices
15
16 -- Purpose
17 -- This procedure is called from the autoinvoice program to
18 -- update MTL_MOVEMENT_STATISTICS table with invoice price
19 -- information.
20
21 -- History
22 -- MAR-21-95 Rudolf F. Reichenberger Created
23
24 -- Arguments
25 -- p_customer_trx_id number
26 -- p_batch_id number
27
28 -- Example
29 -- mtl_movement_stat_pkg.upd_ar_invoices ()
30
31 -- Notes
32
33 PROCEDURE upd_ar_invoices (p_customer_trx_id IN NUMBER,
34 p_batch_id IN NUMBER)
35 IS
36
37 CURSOR get_invoice_lines (a_customer_trx_id NUMBER) IS
38 SELECT mtl.movement_id, lin.customer_trx_line_id
39 FROM mtl_movement_statistics mtl, ra_customer_trx_lines lin
40 WHERE lin.movement_id = mtl.movement_id
41 AND lin.movement_id is not null
42 AND lin.customer_trx_id = a_customer_trx_id
43 AND lin.line_type = 'LINE' FOR UPDATE OF
44 mtl.movement_id NOWAIT;
45
46 BEGIN
47
48 -- *** The Cursor get_invoice_lines locks all movement_id's ***
49 -- *** in mtl_movement_statistics table which exists in ***
50 -- *** ra_customer_trx_lines table related to the ***
51 -- *** requested customer_trx_id ***
52
53 -- *** This Cursor also gets all customer_trx_lines rows ***
54 -- *** for a given customer_trx_id where a movement_id ***
55 -- *** exists in the mtl_movement_statistics as well as ***
56 -- *** in the ra_customer_trx_lines table.
57
58 FOR lines IN get_invoice_lines(p_customer_trx_id) LOOP
59
60 -- *** Invoice Price Information Update ***
61
62 UPDATE mtl_movement_statistics
63 SET invoice_id = p_customer_trx_id,
64 invoice_batch_id = p_batch_id,
65 customer_trx_line_id = lines.customer_trx_line_id,
66 last_update_date = sysdate,
67 last_updated_by = user_id
68 WHERE movement_id = lines.movement_id;
69
70 END LOOP;
71 END upd_ar_invoices;
72
73 -- *******************************************************************
74 -- ** initialization part of the package ** --
75
76 -- ** FUNCTION FND_GLOBAL.USER_ID Returns the **
77 -- ** user_id for the last_updated_by column **
78
79 BEGIN
80 user_id := FND_GLOBAL.USER_ID;
81 -- *******************************************************************
82 END ar_movement_stat_pkg;