1 PACKAGE RCV_ROI_PREPROCESSOR as
2 /* $Header: RCVPREPS.pls 120.4.12010000.2 2008/10/09 18:11:41 vthevark ship $*/
3
4 -- Global vars cached for performance or convenience
5 g_is_edi_installed varchar2(1) := NULL;
6 g_strict VARCHAR2(1) := 'N';
7
8 -- Custom types used in the transactions cursor
9 uom_class po_line_locations.unit_of_measure_class%type;
10 ship_to_org_id po_line_locations.ship_to_organization_id%type;
11 receipt_code po_line_locations.receipt_days_exception_code%type;
12 value_basis po_lines.order_type_lookup_code%type;
13 purchase_basis po_lines.purchase_basis%type;
14 matching_basis po_lines.matching_basis%type;
15 error_code varchar2(1);
16 error_text varchar2(255);
17 derive_values Varchar2(1);
18 cascaded_table_index number;
19
20 -- Our main loop cursors
21 -- This is for picking up the RECEIVE and SHIP txns, which
22 -- will have headers in rcv_headers_interface
23 cursor headers_cur (x_request_id number, x_group_id NUMBER, x_header_interface_id NUMBER) is
24 select * from rcv_headers_interface
25 where NVL(asn_type,'STD') in ('ASN','ASBN','STD','WC','LCM') /* lcm changes */
26 and processing_status_code in ('RUNNING', 'SUCCESS')
27 and ( nvl(validation_flag,'N') = 'Y' OR
28 processing_status_code = 'SUCCESS') -- include sucess rows for multi-line asn
29 and (processing_request_id = x_request_id)
30 and (group_id = x_group_id or x_group_id = 0)
31 and header_interface_id = decode(x_header_interface_id, 0, group_id, x_header_interface_id)
32 -- order by transaction_type,shipment_num
33 for update of processing_status_code nowait;
34
35 -- This is for picking up the all the transaction lines
36 -- existing in rti for the given request and group ids
37 -- including the child / headerless transactions
38 -- such as corrections, transfers, inspections, returns, and deliveries.
39 cursor txns_cur (x_request_id NUMBER, x_group_id NUMBER) is
40 select rcv_transactions_interface.*,
41 rowid row_id,
42 uom_class unit_of_measure_class,
43 ship_to_org_id ship_to_organization_id,
44 receipt_code receipt_days_exception_code,
45 value_basis,
46 purchase_basis,
47 error_code error_status,
48 error_text error_message,
49 derive_values derive,
50 cascaded_table_index derive_index
51 from rcv_transactions_interface
52 where (processing_request_id = x_request_id)
53 and (group_id = x_group_id or x_group_id = 0)
54 and processing_status_code in ('RUNNING')
55 and nvl(validation_flag,'N') = 'Y'
56 order by order_transaction_id,interface_transaction_id asc;
57 -- order by document_line_num;
58
59 -- Custom record types used for derivation and defaulting
60 TYPE error_rec_type IS RECORD
61 (error_status varchar2(1),
62 error_message varchar2(255));
63
64 TYPE cascaded_trans_rec_type is RECORD
65 (transaction_record RCV_ROI_PREPROCESSOR.txns_cur%rowtype,
66 error_record RCV_ROI_PREPROCESSOR.error_rec_type);
67
68 TYPE cascaded_trans_tab_type IS TABLE OF
69 RCV_ROI_PREPROCESSOR.txns_cur%rowtype
70 INDEX BY BINARY_INTEGER;
71
72 TYPE organization_id_record_type IS RECORD
73 (organization_name org_organization_definitions.organization_name%type,
74 organization_code org_organization_definitions.organization_code%type,
75 organization_id org_organization_definitions.organization_id%type,
76 error_record RCV_ROI_PREPROCESSOR.error_rec_type);
77
78 TYPE location_id_record_type IS RECORD
79 (location_id hr_locations.location_id%type,
80 location_code hr_locations.location_code%type,
81 organization_id org_organization_definitions.organization_id%type,
82 error_record RCV_ROI_PREPROCESSOR.error_rec_type);
83
84 TYPE employee_id_record_type IS RECORD
85 (employee_name hr_employees.full_name%type,
86 employee_id hr_employees.employee_id%type,
87 error_record RCV_ROI_PREPROCESSOR.error_rec_type);
88
89 TYPE header_rec_type IS RECORD
90 (header_record RCV_ROI_PREPROCESSOR.headers_cur%rowtype,
91 error_record RCV_ROI_PREPROCESSOR.error_rec_type);
92
93
94 /*===========================================================================
95 PROCEDURE NAME: Preprocessor()
96
97 DESCRIPTION: Derives, defaults, and validates entries in the
98 Receiving Open Interface tables,
99 rcv_headers_interface and rcv_transactions_interface.
100 The preprocessor also handles ASN/ASBN imports,
101 creating the appropriate lines in rcv_shipment_headers
102 and rcv_shipment_lines.
103
104 PARAMETERS:
105
106 x_request_id: The group of rows that this submission of the
107 processor should work on.
108
109 X_group_id: The secondary grouping of rows, added for FPI
110 parallel-processing project.
111
112 DESIGN REFERENCES:
113
114 ALGORITHM:
115
116 NOTES:
117
118 OPEN ISSUES:
119
120 CLOSED ISSUES:
121
122 CHANGE HISTORY: DAWILLIA 03/10/03 Created, based on
123 Rcv_shipment_object_sv.create_object
124 ===========================================================================*/
125
126 PROCEDURE Preprocessor (x_request_id NUMBER , x_group_id NUMBER DEFAULT 0);
127
128 PROCEDURE process_line(
129 x_cascaded_table IN OUT NOCOPY RCV_ROI_PREPROCESSOR.cascaded_trans_tab_type,
130 n IN OUT NOCOPY BINARY_INTEGER,
131 x_header_id IN rcv_headers_interface.header_interface_id%TYPE,
132 x_asn_type IN rcv_headers_interface.asn_type%TYPE,
133 v_header_record IN OUT NOCOPY RCV_ROI_PREPROCESSOR.header_rec_type
134 );
135
136 PROCEDURE explode_lpn_failed(
137 x_interface_txn_id IN OUT NOCOPY rcv_transactions_interface.interface_transaction_id%type,
138 x_group_id NUMBER,
139 x_lpn_group_id NUMBER
140 );
141
142 PROCEDURE update_rti_error
143 (p_group_id IN rcv_transactions_interface.group_id%type,
144 p_interface_id IN rcv_transactions_interface.interface_transaction_id%type,
145 p_header_interface_id IN rcv_transactions_interface.header_interface_id%type,
146 p_lpn_group_id IN rcv_transactions_interface.lpn_group_id%type);
147
148 --Shikyu project
149 FUNCTION get_oe_osa_flag(
150 p_oe_order_line_id NUMBER
151 ) RETURN NUMBER;
152
153 END RCV_ROI_PREPROCESSOR;
154
[Home] [Help]