1 PACKAGE BODY POA_EDW_RCV_TXNS_PKG AS
2 /*$Header: poafrcvb.pls 120.1 2005/06/13 13:01:37 sriswami noship $*/
3 VERSION CONSTANT CHAR(80) :=
4 '$Header: poafrcvb.pls 120.1 2005/06/13 13:01:37 sriswami noship $';
5
6 /* This function returns (total) net source_doc_quantity
7 (i.e. with corrections) for all transactions with this
8 transaction type and originated from this rcv_shipment_line.
9
10 For the receipt to this shipment line, it will return total
11 accepted, rejected, delivered, etc..., net source_doc_quantity
12 to this receipt.
13 */
14 Function Qty_Net_Child_Txns (p_shipment_line_id in NUMBER,
15 p_transaction_type in VARCHAR2)
16 return NUMBER IS
17
18 l_qty NUMBER := 0;
19 l_sum NUMBER;
20
21 BEGIN
22 if p_shipment_line_id is NULL OR
23 p_transaction_type is NULL then
24 return (0);
25 end if;
26
27 /*First, sum all quantities of this txn_type before correction */
28 select sum(nvl(source_doc_quantity, 0)) into l_sum
29 from rcv_transactions
30 where shipment_line_id = p_shipment_line_id
31 and transaction_type = p_transaction_type;
32
33 l_qty := l_qty + nvl(l_sum, 0);
34
35 /*Then, take corrections into account */
36 select sum(nvl(rcv1.source_doc_quantity, 0)) into l_sum
37 from rcv_transactions rcv1
38 where rcv1.parent_transaction_id in
39 (select transaction_id
40 from rcv_transactions rcv2
41 where rcv2.shipment_line_id = p_shipment_line_id
42 and rcv2.transaction_type = p_transaction_type)
43 and rcv1.transaction_type = 'CORRECT';
44
45 l_qty := l_qty + nvl(l_sum, 0);
46
47 return (l_qty);
48
49 EXCEPTION
50 when others then
51 return (0);
52 END Qty_Net_Child_Txns;
53
54
55 /*This function returns total corrections again this transaction */
56
57 Function Qty_Corrected (p_transaction_id in NUMBER)
58 return NUMBER IS
59
60 l_qty NUMBER := 0;
61 l_sum NUMBER;
62
63 BEGIN
64 if p_transaction_id is NULL then
65 return (0);
66 end if;
67
68 select sum(nvl(source_doc_quantity, 0)) into l_sum
69 from rcv_transactions
70 where parent_transaction_id = p_transaction_id
71 and transaction_type = 'CORRECT';
72
73 l_qty := l_qty + nvl(l_sum, 0);
74
75 return (l_qty);
76
77 EXCEPTION
78 when others then
79 return (0);
80 END Qty_Corrected;
81
82
83 /* The function returns all child-txns (total) net_qty with
84 (recursive) parent_transaction_id = p_parent_txn_id and with
85 transaction_type = this p_transaction_type.
86 net_qty = (sum) quantity + correct_quantity.
87
88 <<<Source_Doc_Quantiy is used in all quantities.>>>
89 */
90 Function Qty_Net_Child_Txns_Recursive (
91 p_parent_txn_id in NUMBER,
92 p_transaction_type in VARCHAR2) return NUMBER IS
93
94 l_qty NUMBER := 0;
95 l_txn_id NUMBER;
96 l_qty_cor_sum NUMBER;
97
98 cursor txn_cur (p_pid in NUMBER) IS
99 select transaction_id, nvl(source_doc_quantity, 0) s_qty,
100 transaction_type
101 from rcv_transactions
102 where parent_transaction_id = p_pid;
103
104 BEGIN
105 if p_parent_txn_id is NULL OR
106 p_transaction_type is NULL then
107 return (0);
108 end if;
109
110 for rec_txn in txn_cur(p_parent_txn_id) loop
111 /* consider all children txns */
112 l_txn_id := rec_txn.transaction_id;
113
114 if rec_txn.transaction_type = p_transaction_type then
115 /* this matches our type, add to qty */
116 l_qty := l_qty + rec_txn.s_qty;
117
118 if p_transaction_type <> 'CORRECT' then
119 /* take account for corrections */
120 select sum(nvl(source_doc_quantity, 0)) into l_qty_cor_sum
121 from rcv_transactions
122 where parent_transaction_id = l_txn_id
123 and transaction_type = 'CORRECT';
124
125 l_qty := l_qty + nvl(l_qty_cor_sum, 0);
126 end if;
127 end if;
128
129 /* consider recursively for this child */
130 l_qty := l_qty + Qty_Net_Child_Txns (l_txn_id,
131 p_transaction_type);
132 end loop;
133
134 return (l_qty);
135
136 EXCEPTION
137 when others then
138 if txn_cur%ISOPEN then
139 close txn_cur;
140 end if;
141 return (0);
142 END Qty_Net_Child_Txns_Recursive;
143
144
145 /*This function returns the date of last delivery against this
146 rcv_shipment_line_id. If no delivery, returns NULL.
147 */
148 Function Date_Last_Delivery (p_shipment_line_id in NUMBER)
149 return DATE IS
150 l_date DATE := NULL;
151
152 BEGIN
153
154 select max(transaction_date) into l_date
155 from rcv_transactions
156 where shipment_line_id = p_shipment_line_id
157 and transaction_type = 'DELIVER';
158
159 return l_date;
160
161 EXCEPTION
162 when others then
163 return NULL;
164 END Date_Last_Delivery;
165
166 END POA_EDW_RCV_TXNS_PKG;
[Home] [Help]