[Home] [Help]
PACKAGE BODY: APPS.RCV_GET_DELIVERED_QTY
Source
1 PACKAGE BODY RCV_GET_DELIVERED_QTY AS
2 /* $Header: RCVDELQB.pls 115.6 2004/05/26 01:35:53 wkunz ship $*/
3
4 PROCEDURE GET_TRANSACTION_DETAILS ( x_vendor_id in number,
5 x_vendor_site_id in number,
6 x_item_id in number,
7 x_start_date in date,
8 x_end_date in date,
9 x_delivered_qty out NOCOPY number ) IS
10
11 x_progress VARCHAR2(3) := '000';
12
13 cursor transaction_details_c is
14
15 /*
16 SELECT rct.transaction_type trans_type,
17 rct.transaction_id trans_id,
18 rct.parent_transaction_id parent_trans_id,
19 rct.primary_quantity trans_qty
20 FROM rcv_shipment_lines rsl,
21 po_headers_all poh,
22 rcv_transactions rct
23 WHERE rct.source_document_code = 'PO'
24 AND rsl.item_id = x_item_id
25 AND rsl.po_header_id = poh.po_header_id
26 AND poh.vendor_id = x_vendor_id
27 AND NVL(poh.vendor_site_id,-99) = NVL(x_vendor_site_id,-99)
28 AND rct.shipment_line_id = rsl.shipment_line_id
29 AND rct.transaction_type = 'DELIVER'
30 AND rct.transaction_date between x_start_date and x_end_date;
31 */
32
33 /* Bug# 2449044 */
34
35 SELECT rct.transaction_type trans_type,
36 rct.transaction_id trans_id,
37 rct.parent_transaction_id parent_trans_id,
38 rct.primary_quantity trans_qty
39 FROM rcv_shipment_lines rsl,
40 rcv_transactions rct
41 WHERE rct.source_document_code = 'PO'
42 AND rsl.item_id = x_item_id
43 AND rct.shipment_line_id = rsl.shipment_line_id
44 AND rct.transaction_type = 'DELIVER'
45 AND rct.transaction_date between x_start_date and x_end_date
46 AND exists
47 (SELECT 1 FROM po_headers_all poh
48 WHERE rsl.po_header_id = poh.po_header_id
49 AND NVL(poh.vendor_site_id,-99) = NVL(x_vendor_site_id,-99)
50 AND poh.vendor_id = x_vendor_id
51 AND rownum = 1);
52
53 std_rec transaction_details_c%rowtype;
54
55 cursor child_details_c is
56 SELECT rct.transaction_type trans_type,
57 rct.transaction_id trans_id,
58 rct.parent_transaction_id parent_trans_id,
59 rct.primary_quantity trans_qty
60 FROM rcv_transactions rct
61 WHERE rct.parent_transaction_id = std_rec.trans_id;
62
63 child_rec child_details_c%rowtype;
64
65 begin
66
67 x_delivered_qty := 0;
68
69 open transaction_details_c;
70
71 x_progress := '001';
72
73 loop
74 fetch transaction_details_c into std_rec;
75 exit when transaction_details_c%notfound;
76
77 x_delivered_qty := x_delivered_qty + std_rec.trans_qty;
78
79 open child_details_c;
80
81 x_progress := '002';
82
83 loop
84 fetch child_details_c into child_rec;
85 exit when child_details_c%notfound;
86
87 if (child_rec.trans_type = 'CORRECT') then
88 x_delivered_qty := x_delivered_qty + child_rec.trans_qty;
89 elsif (child_rec.trans_type = 'RETURN TO VENDOR' or
90 child_rec.trans_type = 'RETURN TO RECEIVING') then
91 x_delivered_qty := x_delivered_qty - child_rec.trans_qty;
92 end if;
93 end loop;
94
95 close child_details_c;
96
97 end loop;
98
99 close transaction_details_c;
100 exception
101 when others then
102 po_message_s.sql_error('GET_TRANSACTION_DETAILS',x_progress,sqlcode);
103 raise;
104 end GET_TRANSACTION_DETAILS;
105
106 PROCEDURE GET_INTERNAL_DETAILS ( x_from_org_id in number,
107 x_to_org_id in number,
108 x_item_id in number,
109 x_start_date in date,
110 x_end_date in date,
111 x_delivered_qty out NOCOPY number ) IS
112
113 x_progress VARCHAR2(3) := '000';
114
115 cursor internal_details_c is
116 SELECT rct.transaction_type trans_type,
117 rct.transaction_id trans_id,
118 rct.parent_transaction_id parent_trans_id,
119 rct.primary_quantity trans_qty
120 FROM rcv_shipment_lines rsl,
121 rcv_transactions rct
122 WHERE rct.shipment_line_id = rsl.shipment_line_id
123 AND rsl.item_id = x_item_id
124 AND rsl.from_organization_id = x_from_org_id
125 AND rsl.to_organization_id = x_to_org_id
126 AND rct.transaction_type = 'DELIVER'
127 AND rct.transaction_date between x_start_date and x_end_date;
128
129 int_rec internal_details_c%rowtype;
130
131 cursor child_details_c is
132 SELECT rct.transaction_type trans_type,
133 rct.transaction_id trans_id,
134 rct.parent_transaction_id parent_trans_id,
135 rct.primary_quantity trans_qty
136 FROM rcv_transactions rct
137 WHERE rct.parent_transaction_id = int_rec.trans_id;
138
139 child_rec child_details_c%rowtype;
140
141 begin
142
143 x_delivered_qty := 0;
144
145 open internal_details_c;
146
147 x_progress := '001';
148
149 loop
150 fetch internal_details_c into int_rec;
151 exit when internal_details_c%notfound;
152
153 x_delivered_qty := x_delivered_qty + int_rec.trans_qty;
154
155 open child_details_c;
156
157 x_progress := '002';
158
159 loop
160 fetch child_details_c into child_rec;
161 exit when child_details_c%notfound;
162
163 if (child_rec.trans_type = 'CORRECT') then
164 x_delivered_qty := x_delivered_qty + child_rec.trans_qty;
165 end if;
166 end loop;
167
168 close child_details_c;
169
170 end loop;
171
172 close internal_details_c;
173
174 exception
175 when others then
176 po_message_s.sql_error('GET_INTERNAL_DETAILS',x_progress,sqlcode);
177 raise;
178 end GET_INTERNAL_DETAILS;
179
180 PROCEDURE GET_INTRANSIT_DETAILS ( x_from_org_id in number,
181 x_to_org_id in number,
182 x_rec_not_del_qty out NOCOPY number ) IS
183
184 x_progress VARCHAR2(3) := '000';
185 begin
186
187 SELECT count(*)
188 INTO x_rec_not_del_qty
189 FROM rcv_shipment_lines rsl
190 , mtl_supply ms
191 WHERE rsl.SHIPMENT_LINE_ID = ms.SHIPMENT_LINE_ID
192 AND rsl.SHIPMENT_HEADER_ID = ms.SHIPMENT_HEADER_ID
193 AND rsl.FROM_ORGANIZATION_ID = x_from_org_id
194 AND rsl.TO_ORGANIZATION_ID = x_to_org_id
195 AND ms.FROM_ORGANIZATION_ID = x_from_org_id
196 AND ms.TO_ORGANIZATION_ID = x_to_org_id
197 AND ms.SUPPLY_TYPE_CODE in ('RECEIVING', 'SHIPMENT');
198
199 exception
200 when others then
201 po_message_s.sql_error('GET_INTRANSIT_DETAILS',x_progress,sqlcode);
202 raise;
203 end GET_INTRANSIT_DETAILS;
204
205
206 END RCV_GET_DELIVERED_QTY;