Hi all,when i am trying to run the below procedure it works fine..But the same procedure i run with slight modifications i am getting errors as follows
1 CREATE OR REPLACE PROCEDURE xxc_lc_rcv_interface_prc IS
2 v_header_interface_id NUMBER;
3 v_interface_group_id NUMBER;
4 v_shipment_header_id NUMBER;
5 v_shipment_line_id NUMBER;
6 v_transaction_iface_id NUMBER;
7 CURSOR rcv_interface_cur (p_shipment_header_id in number)
8 IS
9 SELECT shipment_line_id
10 FROM RCV_SHIPMENT_LINES
11 WHERE SHIPMENT_HEADER_ID =p_shipment_header_id ;
12 BEGIN
13 SELECT rcv_headers_interface_s.nextval
14 INTO v_header_interface_id
15 FROM dual ;
16 SELECT rcv_interface_groups_s.nextval
17 INTO v_interface_group_id
18 FROM dual;
19 BEGIN
20 INSERT INTO rcv_headers_interface
21 (
22 HEADER_INTERFACE_ID,
23 GROUP_ID,
24 PROCESSING_STATUS_CODE,
25 RECEIPT_SOURCE_CODE,
26 TRANSACTION_TYPE,
27 AUTO_TRANSACT_CODE,
28 LAST_UPDATE_DATE,
29 LAST_UPDATE_LOGIN,
30 LAST_UPDATED_BY,
31 CREATION_DATE,
32 CREATED_BY,
33 VALIDATION_FLAG,
34 COMMENTS,
35 SHIPMENT_NUM,
36 FROM_ORGANIZATION_ID,
37 SHIP_TO_ORGANIZATION_ID,
38 EXPECTED_RECEIPT_DATE
39 --RECEIPT_HEADER_ID
40 )
41 VALUES
42 (v_header_interface_id , --Header Interface ID
43 v_interface_group_id, --Group ID
44 'PENDING', --Processing Status Code
45 'INVENTORY', --Receipt source Code
46 'RECEIVE', --Transaction Type
47 'DELIVER' , --AUT Transact Code
48 sysdate, --last update date
49 1053, --last updated by
50 1053, --Last Update Login
51 sysdate, --creation date
52 1053, --created by
53 'Y', --Validation Flag
54 'Receiving Through Interface', --Comments
55 'NOV1125' , --Shipment Number
56 81, --From Org
57 82, --To org
58 sysdate --Expected Receipt Date
59 );
60 END;
61 BEGIN
62 SELECT shipment_header_id
63 INTO v_shipment_header_id
64 FROM RCV_SHIPMENT_HEADERS
65 WHERE SHIPMENT_NUM = 'NOV1125';
66 FOR crec IN rcv_interface_cur(v_shipment_header_id) loop
67 SELECT rcv_transactions_interface_s.nextval
68 INTO v_transaction_iface_id
69 FROM dual;
70 v_shipment_line_id := crec.shipment_line_id;
71 INSERT INTO rcv_transactions_interface
72 (
73 HEADER_INTERFACE_ID,
74 GROUP_ID,
75 INTERFACE_TRANSACTION_ID,
76 TRANSACTION_TYPE,
77 TRANSACTION_DATE,
78 PROCESSING_STATUS_CODE,
79 PROCESSING_MODE_CODE,
80 TRANSACTION_STATUS_CODE,
81 CATEGORY_ID,
82 QUANTITY,
83 LAST_UPDATE_DATE,
84 LAST_UPDATED_BY,
85 CREATION_DATE,
86 CREATED_BY,
87 RECEIPT_SOURCE_CODE,
88 DESTINATION_TYPE_CODE,
89 AUTO_TRANSACT_CODE,
90 SOURCE_DOCUMENT_CODE,
91 UNIT_OF_MEASURE,
92 INTERFACE_SOURCE_CODE,
93 ITEM_ID,
94 ITEM_DESCRIPTION,
95 UOM_CODE,
96 EMPLOYEE_ID,
97 SHIPMENT_HEADER_ID,
98 SHIPMENT_LINE_ID,
99 TO_ORGANIZATION_ID,
100 SUBINVENTORY,
101 FROM_ORGANIZATION_ID,
102 FROM_SUBINVENTORY,
103 EXPECTED_RECEIPT_DATE,
104 SHIPPED_DATE,
105 VALIDATION_FLAG
106 )
107 VALUES
108 (v_header_interface_id, --Header Interface ID
109 v_interface_group_id, --Group ID
110 v_transaction_iface_id, --Interface_transaction_id
111 'RECEIVE', --Transaction Type
112 sysdate, --Transaction Date
113 'PENDING', --Processing Status Code
114 'BATCH', --Processing Mode Code
115 'PENDING', --Transaction Status Code
116 120, --Category ID
117 2, --Quantity
118 sysdate, --last update date
119 1053, --last updated by
120 sysdate, --creation date
121 1053, --created by
122 'INVENTORY', --Receipt source Code
123 'INVENTORY', --Destination Type Code
124 'DELIVER' , --AUTO Transact Code
125 'INVENTORY', --Source Document Code
126 'Each', --Unit Of Measure
127 'RCV', --Interface Source Code
128 2492, --Item ID
129 'ABBY KITCHEN CURTAIN SET BEIGE/BURGUNDY', --Item Description
130 'EA', --UOM COde
131 1053, --User
132 v_shipment_header_id, --Shipment Header ID
133 v_shipment_line_id, --SHipment Line ID
134 82, --To Organization ID
135 'Brooklyn', --Sub Inventory ID
136 81, --From Organization
137 'Vessel', --From Subinventory
138 sysdate, --Expected Receipt Date
139 sysdate, --Shipped Date
140 'Y' --Validation Flag
141 );
142 commit;
143 --END IF;
144 END LOOP;
145 END;
146* END xxc_lc_rcv_interface_prc;
147 /
Procedure created.
SQL> exec xxc_lc_rcv_interface_prc;
PL/SQL procedure successfully completed.
*Modified One*
1 --PROCEDURE xxc_lc_rcv_interface_prc(v_file_number IN VARCHAR2,m1 OUT NUMBER) IS
2 DECLARE
3 /**************************************************
4 This Procedure is used for generation of Receipt
5 ***************************************************/
6 v_header_interface_id NUMBER;
7 v_interface_group_id NUMBER;
8 v_shipment_header_id NUMBER;
9 v_shipment_line_id NUMBER;
10 v_transaction_iface_id NUMBER;
11 m1 NUMBER;
12 CURSOR rcv_interface_cur (p_shipment_header_id IN NUMBER)
13 IS
14 SELECT shipment_line_id
15 FROM RCV_SHIPMENT_LINES
16 WHERE SHIPMENT_HEADER_ID =p_shipment_header_id ;
17 BEGIN
18 SELECT rcv_headers_interface_s.nextval
19 INTO v_header_interface_id
20 FROM dual ;
21 SELECT rcv_interface_groups_s.nextval
22 INTO v_interface_group_id
23 FROM dual;
24 BEGIN
25 INSERT INTO rcv_headers_interface
26 (
27 HEADER_INTERFACE_ID,
28 GROUP_ID,
29 PROCESSING_STATUS_CODE,
30 RECEIPT_SOURCE_CODE,
31 TRANSACTION_TYPE,
32 AUTO_TRANSACT_CODE,
33 LAST_UPDATE_DATE,
34 LAST_UPDATED_BY,
35 --LAST_UPDATE_LOGIN,
36 CREATION_DATE,
37 CREATED_BY,
38 VALIDATION_FLAG,
39 COMMENTS,
40 SHIPMENT_NUM,
41 FROM_ORGANIZATION_ID,
42 SHIP_TO_ORGANIZATION_ID,
43 EXPECTED_RECEIPT_DATE
44 --RECEIPT_HEADER_ID
45 )
46 SELECT
47 v_header_interface_id , --Header Interface ID
48 v_interface_group_id, --Group ID
49 'PENDING', --Processing Status Code
50 'INVENTORY', --Receipt source Code
51 'RECEIVE', --Transaction Type
52 'DELIVER' , --AUT Transact Code
53 lc.last_update_date, --last update date
54 lc.last_updated_by, --last updated by
55 --1053, --Last Update Login
56 sysdate, --creation date
57 lc.created_by, --created by
58 'Y', --Validation Flag
59 'Receiving Through Interface', --Comments
60 lc.file_number , --Shipment Number
61 81, --From Org
62 82, --To org
63 sysdate --Expected Receipt Date
64 FROM regal.regal_inv_landed_cost_tab lc
65 WHERE lc.file_number = 'FEB800'
66 AND lc.selected_flag = 'Y';
67 --EXCEPTION
68 --WHEN OTHERS THEN --Exception Handling
69 --dbms_output.put_line('Receiving Transaction failed while loading data into rcv_headers_interf
70 END;
71 BEGIN
72 SELECT shipment_header_id
73 INTO v_shipment_header_id
74 FROM RCV_SHIPMENT_HEADERS
75 WHERE SHIPMENT_NUM = (select file_number from regal.regal_inv_landed_cost_tab);
76 FOR crec IN rcv_interface_cur(v_shipment_header_id) loop
77 SELECT rcv_transactions_interface_s.nextval
78 INTO v_transaction_iface_id
79 FROM dual;
80 v_shipment_line_id := crec.shipment_line_id;
81 INSERT INTO rcv_transactions_interface
82 (
83 HEADER_INTERFACE_ID,
84 GROUP_ID,
85 INTERFACE_TRANSACTION_ID,
86 TRANSACTION_TYPE,
87 TRANSACTION_DATE,
88 PROCESSING_STATUS_CODE,
89 PROCESSING_MODE_CODE,
90 TRANSACTION_STATUS_CODE,
91 CATEGORY_ID,
92 QUANTITY,
93 LAST_UPDATE_DATE,
94 LAST_UPDATED_BY,
95 CREATION_DATE,
96 CREATED_BY,
97 RECEIPT_SOURCE_CODE,
98 DESTINATION_TYPE_CODE,
99 AUTO_TRANSACT_CODE,
100 SOURCE_DOCUMENT_CODE,
101 UNIT_OF_MEASURE,
102 INTERFACE_SOURCE_CODE,
103 ITEM_ID,
104 ITEM_DESCRIPTION,
105 UOM_CODE,
106 --EMPLOYEE_ID,
107 SHIPMENT_HEADER_ID,
108 SHIPMENT_LINE_ID,
109 TO_ORGANIZATION_ID,
110 SUBINVENTORY,
111 FROM_ORGANIZATION_ID,
112 FROM_SUBINVENTORY,
113 EXPECTED_RECEIPT_DATE,
114 SHIPPED_DATE,
115 VALIDATION_FLAG
116 )
117 SELECT
118 v_header_interface_id, --Header Interface ID
119 v_interface_group_id, --Group ID
120 v_transaction_iface_id, --Interface_transaction_id
121 'RECEIVE', --Transaction Type
122 sysdate, --Transaction Date
123 'PENDING', --Processing Status Code
124 'BATCH', --Processing Mode Code
125 'PENDING', --Transaction Status Code
126 mic.category_id, --Category ID
127 lc.quantity_received , --Quantity
128 sysdate, --last update date
129 lc.last_updated_by, --last updated by
130 sysdate, --creation date
131 1053, --created by
132 'INVENTORY', --Receipt source Code
133 'INVENTORY', --Destination Type Code
134 'DELIVER' , --AUTO Transact Code
135 'INVENTORY', --Source Document Code
136 msi.primary_unit_of_measure, --Unit Of Measure
137 'RCV', --Interface Source Code
138 lc.inventory_item_id, --Item ID
139 lc.description, --Item Description
140 msi.primary_uom_code, --UOM COde
141 --1053, --User
142 v_shipment_header_id, --Shipment Header ID
143 v_shipment_line_id, --SHipment Line ID
144 82, --To Organization ID
145 'Brooklyn', --Sub Inventory ID
146 81, --From Organization
147 'Vessel', --From Subinventory
148 sysdate, --Expected Receipt Date
149 sysdate, --Shipped Date
150 'Y' --Validation Flag
151 FROM regal.regal_inv_landed_cost_tab lc,
152 mtl_item_categories mic,
153 mtl_system_items msi
154 WHERE lc.organization_id = mic.organization_id
155 AND lc.inventory_item_id = mic.inventory_item_id
156 AND lc.organization_id = msi.organization_id
157 AND lc.inventory_item_id = msi.inventory_item_id
158 AND lc.file_number = 'FEB800'
159 AND lc.selected_flag = 'Y';
160 END LOOP;
161 COMMIT;
162 m1 := SQL%ROWCOUNT;
163 dbms_output.put_line(m1 || 'Records inserted');
164 --EXCEPTION
165 --WHEN OTHERS THEN --Exception Handling
166 --dbms_output.put_line('Receiving Transaction failed while loading data into rcv_transactions_i
167 END;
168* END;
SQL> /
--PROCEDURE xxc_lc_rcv_interface_prc(v_file_number IN VARCHAR2,m1 OUT NUMBER) IS
*
ERROR at line 1:
ORA-00001: unique constraint (PO.RCV_HEADERS_INTERFACE_U1) violated
ORA-06512: at line 25
What could probably go wrong...i really don't understand where i am doing wrong,kindly suggest me where i am doing wrong.
Thanks in advance!!