[Home] [Help]
PACKAGE: APPS.RCV_ROI_RETURN
Source
1 PACKAGE RCV_ROI_RETURN
2 /* $Header: RCVPRERS.pls 120.1 2006/04/19 00:38:34 szaveri noship $*/
3 AS
4 CURSOR default_return (v_parent_trx_id NUMBER)
5 IS
6 SELECT rt.po_revision_num, rt.primary_unit_of_measure,
7 rt.po_header_id, rt.po_release_id, rt.location_id,
8 rt.organization_id OID, rsh.receipt_source_code,
9 rt.source_document_code, rt.wip_entity_id, rt.wip_line_id,
10 rt.wip_repetitive_schedule_id, rt.wip_operation_seq_num,
11 rt.wip_resource_seq_num, rt.department_code,
12 rt.bom_resource_id, rt.oe_order_header_id,
13 rt.oe_order_line_id, rt.destination_context,
14 rt.inspection_status_code, rt.currency_code,
15 rt.currency_conversion_type, rt.currency_conversion_rate,
16 rt.currency_conversion_date, rt.shipment_header_id,
17 rt.shipment_line_id, rsl.category_id, rt.vendor_id,
18 rt.vendor_site_id, rt.customer_id, rt.customer_site_id,
19 rt.po_unit_price, rt.movement_id, rt.po_line_id,
20 rt.po_line_location_id, rt.deliver_to_person_id,
21 rt.deliver_to_location_id, rt.po_distribution_id,
22 rt.locator_id, rsl.item_description, rt.subinventory,
23 rt.reason_id, rt.transfer_lpn_id, rt.lpn_id
24 FROM rcv_transactions rt,
25 rcv_shipment_lines rsl,
26 rcv_shipment_headers rsh
27 WHERE rt.transaction_id = v_parent_trx_id
28 AND rt.shipment_line_id = rsl.shipment_line_id
29 AND rt.shipment_header_id = rsh.shipment_header_id
30 AND ( ( ( (rt.transaction_type IN
31 ('RECEIVE',
32 'TRANSFER',
33 'ACCEPT',
34 'REJECT',
35 'MATCH'
36 )
37 )
38 OR ( rt.transaction_type = 'UNORDERED'
39 AND NOT EXISTS (
40 SELECT 'PROCESSED MATCH ROWS'
41 FROM rcv_transactions rt2
42 WHERE rt2.parent_transaction_id =
43 rt.transaction_id
44 AND rt2.transaction_type = 'MATCH')
45 AND NOT EXISTS (
46 SELECT 'UNPROCESSED MATCH ROWS'
47 FROM rcv_transactions_interface rti
48 WHERE rti.parent_transaction_id =
49 rt.transaction_id
50 AND rti.transaction_type = 'MATCH')
51 )
52 )
53 AND EXISTS (
54 SELECT 'POSTIVE RCV SUPPLY'
55 FROM rcv_supply rs
56 WHERE rs.rcv_transaction_id = rt.transaction_id
57 AND rs.to_org_primary_quantity >
58 (SELECT NVL (SUM (rti.primary_quantity),
59 0
60 )
61 FROM rcv_transactions_interface rti
62 WHERE rti.parent_transaction_id =
63 rt.transaction_id
64 AND rti.transaction_status_code =
65 'PENDING'))
66 )
67 OR ( rt.transaction_type = 'DELIVER'
68 AND rt.source_document_code <> 'RMA'
69 )
70 )
71 AND rt.source_document_code IN ('PO', 'RMA')
72 ORDER BY rt.transaction_id;
73
74 CURSOR default_return_rti (v_parent_inter_trx_id NUMBER)
75 IS
76 SELECT rti.po_revision_num, rti.primary_unit_of_measure,
77 rti.po_header_id, rti.po_release_id, rti.location_id,
78 rti.to_organization_id OID, rti.receipt_source_code,
79 rti.source_document_code, rti.wip_entity_id, rti.wip_line_id,
80 rti.wip_repetitive_schedule_id, rti.wip_operation_seq_num,
81 rti.wip_resource_seq_num, rti.department_code,
82 rti.bom_resource_id, rti.oe_order_header_id,
83 rti.oe_order_line_id, rti.destination_context,
84 rti.inspection_status_code, rti.currency_code,
85 rti.currency_conversion_rate, rti.currency_conversion_type,
86 rti.currency_conversion_date, rti.shipment_header_id,
87 rti.shipment_line_id, rti.category_id, rti.vendor_id,
88 rti.vendor_site_id, rti.customer_id, rti.customer_site_id,
89 rti.po_unit_price, rti.movement_id, rti.po_line_id,
90 rti.po_line_location_id, rti.deliver_to_person_id,
91 rti.deliver_to_location_id, rti.po_distribution_id,
92 rti.locator_id, rti.item_description, rti.subinventory,
93 rti.reason_id, rti.transfer_lpn_id,rti.lpn_id
94 FROM rcv_transactions_interface rti
95 WHERE interface_transaction_id = v_parent_inter_trx_id;
96
97 PROCEDURE derive_return_line (
98 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
99 n IN OUT NOCOPY BINARY_INTEGER,
100 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
101 x_header_record IN rcv_roi_preprocessor.header_rec_type
102 );
103
104 PROCEDURE default_return_line (
105 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
106 n IN BINARY_INTEGER
107 );
108
109 PROCEDURE derive_return_line_qty (
110 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
111 n IN OUT NOCOPY BINARY_INTEGER,
112 temp_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type
113 );
114
115 PROCEDURE derive_reason_info (
116 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
117 n IN OUT NOCOPY BINARY_INTEGER
118 );
119
120 PROCEDURE derive_ship_to_org_info (
121 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
122 n IN OUT NOCOPY BINARY_INTEGER,
123 x_header_record IN rcv_roi_preprocessor.header_rec_type
124 );
125
126 PROCEDURE default_common_lines (
127 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
128 n IN BINARY_INTEGER
129 );
130
131 PROCEDURE default_po_info (
132 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
133 n IN BINARY_INTEGER,
134 default_return_rec IN default_return%ROWTYPE
135 );
136
137 PROCEDURE default_shipment_info (
138 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
139 n IN BINARY_INTEGER,
140 default_return_rec IN default_return%ROWTYPE
141 );
142
143 PROCEDURE default_wip_info (
144 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
145 n IN BINARY_INTEGER,
146 default_return_rec IN default_return%ROWTYPE
147 );
148
149 PROCEDURE default_oe_info (
150 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
151 n IN BINARY_INTEGER,
152 default_return_rec IN default_return%ROWTYPE
153 );
154
155 PROCEDURE default_currency_info (
156 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
157 n IN BINARY_INTEGER,
158 default_return_rec IN default_return%ROWTYPE
159 );
160
161 PROCEDURE default_vendor_info (
162 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
163 n IN BINARY_INTEGER,
164 default_return_rec IN default_return%ROWTYPE
165 );
166
167 PROCEDURE default_customer_info (
168 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
169 n IN BINARY_INTEGER,
170 default_return_rec IN default_return%ROWTYPE
171 );
172
173 PROCEDURE default_deliver_to_info (
174 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
175 n IN BINARY_INTEGER,
176 default_return_rec IN default_return%ROWTYPE
177 );
178
179 PROCEDURE default_source_info (
180 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
181 n IN BINARY_INTEGER,
182 default_return_rec IN default_return%ROWTYPE
183 );
184
185 PROCEDURE default_item_info (
186 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
187 n IN BINARY_INTEGER,
188 default_return_rec IN default_return%ROWTYPE
189 );
190
191 PROCEDURE default_destination_info (
192 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
193 n IN BINARY_INTEGER,
194 default_return_rec IN default_return%ROWTYPE
195 );
196
197 PROCEDURE default_location_info (
198 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
199 n IN BINARY_INTEGER,
200 default_return_rec IN default_return%ROWTYPE
201 );
202
203 PROCEDURE default_movement_id (
204 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
205 n IN BINARY_INTEGER,
206 default_return_rec IN default_return%ROWTYPE
207 );
208
209 PROCEDURE default_bom_resource_id (
210 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
211 n IN BINARY_INTEGER,
212 default_return_rec IN default_return%ROWTYPE
213 );
214
215 PROCEDURE derive_inv_qty (
216 x_cascaded_table IN OUT NOCOPY rcv_roi_preprocessor.cascaded_trans_tab_type,
217 n IN BINARY_INTEGER
218 );
219
220 PROCEDURE derive_inv_qty_1(
221 p_destination_type_code IN rcv_transactions_interface.destination_type_code%type,
222 p_transaction_type IN rcv_transactions_interface.transaction_type%type,
223 p_quantity IN rcv_transactions_interface.quantity%type,
224 p_interface_transaction_id IN rcv_transactions_interface.interface_transaction_id%type,
225 p_to_organization_id IN rcv_transactions_interface.to_organization_id%type,
226 p_item_id IN rcv_transactions_interface.item_id%type,
227 p_item_revision IN rcv_transactions_interface.item_revision%type,
228 p_receipt_source_code IN rcv_transactions_interface.receipt_source_code%type,
229 p_po_header_id IN rcv_transactions_interface.po_header_id%type,
230 p_unit_of_measure IN rcv_transactions_interface.unit_of_measure%type,
231 p_primary_unit_of_measure IN rcv_transactions_interface.primary_unit_of_measure%type,
232 p_subinventory IN rcv_transactions_interface.subinventory%type,
233 p_locator_id IN rcv_transactions_interface.locator_id%type,
234 p_transfer_lpn_id IN rcv_transactions_interface.transfer_lpn_id%type,
235 p_lpn_id IN rcv_transactions_interface.lpn_id%type,
236 x_error_status IN OUT NOCOPY VARCHAR2,
237 x_error_message IN OUT NOCOPY VARCHAR2
238 );
239
240 END RCV_ROI_RETURN;