Hi all,when i am trying to declare a cursor in a procedure i am getting errors.Kindly correct me where i am going wrong
1 CREATE OR REPLACE PROCEDURE xxc_lc_rcv_interface_prc IS
2 v_a NUMBER;
3 v_b NUMBER;
4 v_c NUMBER;
5 v_d NUMBER;
6 v_e NUMBER;
7 v_f NUMBER;
8 v_g NUMBER;
9 CURSOR rcv_interface_cur
10 IS
11 SELECT shipment_header_id INTO v_c
12 FROM RCV_SHIPMENT_HEADERS
13 WHERE SHIPMENT_NUM = 'NOV1124';
14 SELECT shipment_line_id INTO v_d
15 FROM RCV_SHIPMENT_LINES
16 WHERE SHIPMENT_HEADER_ID = v_c;
17 BEGIN
18 SELECT rcv_headers_interface_s.nextval INTO v_a FROM dual ;
19 SELECT rcv_interface_group_s.nextval INTO v_b FROM dual;
20 SELECT rcv_headers_interface_s.currval INTO v_e FROM dual ;
21 SELECT rcv_interface_groups_s.currval INTO v_f FROM dual;
22 SELECT rcv_transactions_interface_s.nextval INTO v_g FROM dual;
23 BEGIN
24 INSERT INTO rcv_headers_interface
25 (
26 HEADER_INTERFACE_ID,
27 GROUP_ID,
28 PROCESSING_STATUS_CODE,
29 RECEIPT_SOURCE_CODE,
30 TRANSACTION_TYPE,
31 AUTO_TRANSACT_CODE,
32 LAST_UPDATE_DATE,
33 LAST_UPDATE_LOGIN,
34 LAST_UPDATED_BY,
35 CREATION_DATE,
36 CREATED_BY,
37 VALIDATION_FLAG,
38 COMMENTS,
39 SHIPMENT_NUM,
40 FROM_ORGANIZATION_ID,
41 SHIP_TO_ORGANIZATION_ID,
42 EXPECTED_RECEIPT_DATE
43 --RECEIPT_HEADER_ID
44 )
45 VALUES
46 (v_a, --Header Interface ID
47 v_b, --Group ID
48 'PENDING', --Processing Status Code
49 'INVENTORY', --Receipt source Code
50 'RECEIVE', --Transaction Type
51 'DELIVER' , --AUT Transact Code
52 sysdate, --last update date
53 1053, --last updated by
54 1053, --Last Update Login
55 sysdate, --creation date
56 1053, --created by
57 'Y', --Validation Flag
58 'Receiving Through Interface', --Comments
59 'NOV1124' , --Shipment Number
60 81, --From Org
61 82, --To org
62 sysdate --Expected Receipt Date
63 );
64 END;
65 BEGIN
66 FOR crec IN rcv_interface_cur loop
67 INSERT INTO rcv_transactions_interface
68 (
69 HEADER_INTERFACE_ID,
70 GROUP_ID,
71 INTERFACE_TRANSACTION_ID,
72 TRANSACTION_TYPE,
73 TRANSACTION_DATE,
74 PROCESSING_STATUS_CODE,
75 PROCESSING_MODE_CODE,
76 TRANSACTION_STATUS_CODE,
77 CATEGORY_ID,
78 QUANTITY,
79 LAST_UPDATE_DATE,
80 LAST_UPDATED_BY,
81 CREATION_DATE,
82 CREATED_BY,
83 RECEIPT_SOURCE_CODE,
84 DESTINATION_TYPE_CODE,
85 AUTO_TRANSACT_CODE,
86 SOURCE_DOCUMENT_CODE,
87 UNIT_OF_MEASURE,
88 INTERFACE_SOURCE_CODE,
89 ITEM_ID,
90 --ITEM_DESCRIPTION,
91 UOM_CODE,
92 EMPLOYEE_ID,
93 SHIPMENT_HEADER_ID,
94 TO_ORGANIZATION_ID,
95 SUBINVENTORY,
96 FROM_ORGANIZATION_ID,
97 FROM_SUBINVENTORY,
98 EXPECTED_RECEIPT_DATE,
99 SHIPPED_DATE,
100 VALIDATION_FLAG
101 )
102 VALUES
103 (v_e, --Header Interface ID
104 v_f, --Group ID
105 v_g, --Interface_transaction_id
106 'RECEIVE', --Transaction Type
107 sysdate, --Transaction Date
108 'PENDING', --Processing Status Code
109 'BATCH', --Processing Mode Code
110 'PENDING', --Transaction Status Code
111 120, --Category ID
112 2, --Quantity
113 sysdate, --last update date
114 1053, --last updated by
115 sysdate, --creation date
116 1053, --created by
117 'INVENTORY', --Receipt source Code
118 'INVENTORY', --Destination Type Code
119 'DELIVER' , --AUTO Transact Code
120 'INVENTORY', --Source Document Code
121 'Each', --Unit Of Measure
122 'RCV', --Interface Source Code
123 2492, --Item ID
124 --'ABBY KITCHEN CURTAIN SET BEIGE/BURGUNDY', --Item Description
125 'EA', --UOM COde
126 1053, --User
127 v_c, --Shipment Header ID
128 v_d, --SHipment Line ID
129 82, --To Organization ID
130 'Brooklyn', --Sub Inventory ID
131 81, --From Organization
132 'Vessel', --From Subinventory
133 sysdate, --Expected Receipt Date
134 sysdate, --Shipped Date
135 'Y' --Validation Flag
136 );
137 --END IF;
138 END LOOP;
139 END;
140* END xxc_lc_rcv_interface_prc;
SQL> /
Warning: Procedure created with compilation errors.
SQL> sho err
Errors for PROCEDURE XXC_LC_RCV_INTERFACE_PRC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
14/2 PLS-00103: Encountered the symbol "SELECT" when expecting one of
the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
The symbol "begin" was substituted for "SELECT" to continue.
141/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
( begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << continue close current delete fetch lock
insert open rollback savepoint set sql execute commit forall
merge pipe purge