[Home] [Help]
PACKAGE BODY: APPS.WIP_MOVE_ALLOC_CLEANUP
Source
1 PACKAGE BODY WIP_MOVE_ALLOC_CLEANUP AS
2 /* $Header: wipmaclb.pls 115.6 2002/11/28 13:23:49 rmahidha ship $ */
3
4 procedure fetch_and_delete(
5 p_mov_grp_id in number,
6 p_mov_allocs in out nocopy wip_move_txn_allocations_rec) is
7
8 i number := 0;
9
10 cursor get_move_allocs(c_mov_grp_id number) is
11 select
12 TRANSACTION_ID,
13 REPETITIVE_SCHEDULE_ID,
14 ORGANIZATION_ID,
15 LAST_UPDATE_DATE,
16 LAST_UPDATED_BY,
17 CREATION_DATE,
18 CREATED_BY,
19 LAST_UPDATE_LOGIN,
20 REQUEST_ID,
21 PROGRAM_APPLICATION_ID,
22 PROGRAM_ID,
23 PROGRAM_UPDATE_DATE,
24 TRANSACTION_QUANTITY,
25 PRIMARY_QUANTITY
26 from wip_move_txn_allocations
27 where transaction_id in
28 (select transaction_id
29 from wip_move_txn_interface
30 where group_id = c_mov_grp_id);
31
32 mov_alloc_rec get_move_allocs%rowtype;
33 begin
34 -- initialize
35 if (p_mov_allocs.numrecs is NULL) then
36 -- p_mov_allocs is empty
37 p_mov_allocs.numrecs := i;
38 else
39 -- p_mov_allocs already has records
40 i := p_mov_allocs.numrecs;
41 end if;
42
43 open get_move_allocs(c_mov_grp_id => p_mov_grp_id);
44
45 loop
46 fetch get_move_allocs into mov_alloc_rec;
47
48 exit when (get_move_allocs%NOTFOUND);
49
50 i := i + 1;
51 p_mov_allocs.numrecs := i;
52 p_mov_allocs.TRANSACTION_ID(i) := mov_alloc_rec.TRANSACTION_ID;
53 p_mov_allocs.REPETITIVE_SCHEDULE_ID(i) := mov_alloc_rec.REPETITIVE_SCHEDULE_ID;
54 p_mov_allocs.ORGANIZATION_ID(i) := mov_alloc_rec.ORGANIZATION_ID;
55 p_mov_allocs.LAST_UPDATE_DATE(i) := mov_alloc_rec.LAST_UPDATE_DATE;
56 p_mov_allocs.LAST_UPDATED_BY(i) := mov_alloc_rec.LAST_UPDATED_BY;
57 p_mov_allocs.CREATION_DATE(i) := mov_alloc_rec.CREATION_DATE;
58 p_mov_allocs.CREATED_BY(i) := mov_alloc_rec.CREATED_BY;
59 p_mov_allocs.LAST_UPDATE_LOGIN(i) := mov_alloc_rec.LAST_UPDATE_LOGIN;
60 p_mov_allocs.REQUEST_ID(i) := mov_alloc_rec.REQUEST_ID;
61 p_mov_allocs.PROGRAM_APPLICATION_ID(i) := mov_alloc_rec.PROGRAM_APPLICATION_ID;
62 p_mov_allocs.PROGRAM_ID(i) := mov_alloc_rec.PROGRAM_ID;
63 p_mov_allocs.PROGRAM_UPDATE_DATE(i) := mov_alloc_rec.PROGRAM_UPDATE_DATE;
64 p_mov_allocs.TRANSACTION_QUANTITY(i) := mov_alloc_rec.TRANSACTION_QUANTITY;
65 p_mov_allocs.PRIMARY_QUANTITY(i) := mov_alloc_rec.PRIMARY_QUANTITY;
66 end loop;
67
68 if (p_mov_allocs.numrecs > 0) then
69 delete from wip_move_txn_allocations
70 where transaction_id in
71 (select transaction_id
72 from wip_move_txn_interface
73 where group_id = p_mov_grp_id);
74 end if;
75 end fetch_and_delete;
76
77 procedure insert_rows(
78 p_mov_allocs in wip_move_txn_allocations_rec) is
79 i number := 1;
80 begin
81 while (i <= nvl(p_mov_allocs.numrecs, 0)) loop
82 insert into wip_move_txn_allocations (
83 TRANSACTION_ID,
84 REPETITIVE_SCHEDULE_ID,
85 ORGANIZATION_ID,
86 LAST_UPDATE_DATE,
87 LAST_UPDATED_BY,
88 CREATION_DATE,
89 CREATED_BY,
90 LAST_UPDATE_LOGIN,
91 REQUEST_ID,
92 PROGRAM_APPLICATION_ID,
93 PROGRAM_ID,
94 PROGRAM_UPDATE_DATE,
95 TRANSACTION_QUANTITY,
96 PRIMARY_QUANTITY
97 ) values (
98 p_mov_allocs.TRANSACTION_ID(i),
99 p_mov_allocs.REPETITIVE_SCHEDULE_ID(i),
100 p_mov_allocs.ORGANIZATION_ID(i),
101 p_mov_allocs.LAST_UPDATE_DATE(i),
102 p_mov_allocs.LAST_UPDATED_BY(i),
103 p_mov_allocs.CREATION_DATE(i),
104 p_mov_allocs.CREATED_BY(i),
105 p_mov_allocs.LAST_UPDATE_LOGIN(i),
106 p_mov_allocs.REQUEST_ID(i),
107 p_mov_allocs.PROGRAM_APPLICATION_ID(i),
108 p_mov_allocs.PROGRAM_ID(i),
109 p_mov_allocs.PROGRAM_UPDATE_DATE(i),
110 p_mov_allocs.TRANSACTION_QUANTITY(i),
111 p_mov_allocs.PRIMARY_QUANTITY(i)
112 );
113
114 i := i + 1;
115 end loop;
116 end insert_rows;
117
118 END WIP_MOVE_ALLOC_CLEANUP;