Saturday, August 15, 2015

PL/SQL Script to upload data from staging table to interface table

PL/SQL Script to upload data from staging table to interface table 


?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
CREATE OR REPLACE PROCEDURE xxx_po_api
AS
---To Import data from Interface to Base Tables - Import Standard Purchase Orders
-- please do the following: to see the errors
-- Run the program - Purchasing Interface Errors Report
-- choose parameter : PO_DOCS_OPEN_INTERFACE
-- po_interface_errors
l_currency_code fnd_currencies_vl.currency_code%type;
l_verify_flag Char(1);
l_error_message varchar2(5000);
l_vendor_id po_vendors.vendor_id%type;
l_vendor_site_id po_vendor_sites_all.vendor_site_id%type;
l_ship_to hr_locations.location_id%type;
l_bill_to hr_locations.location_id%type;
l_inventory_item_id mtl_system_items_b.inventory_item_id%type;
l_legacy_ponum number(20):=0;
l_batch_id number(3);
CURSOR C_PO_HEADER IS
            select distinct legacy_ponum,
            currency_code,
            vendor_name,
            vendor_site_code,
            ship_to,
            bill_to,
            status
            from xxx_purchase_order_stg;
 
CURSOR C_PO_LINES(l_legacy_ponum NUMBER) IS
            select *
            from xxx_purchase_order_stg
            where trim(legacy_ponum) = trim(l_legacy_ponum);
BEGIN
    FOR H1 IN C_PO_HEADER LOOP
        l_verify_flag := 'Y' ;
        l_error_message := NULL;
        BEGIN
            select currency_code
            into l_currency_code
            from fnd_currencies_vl
            where enabled_flag = 'Y'
            and currency_flag = 'Y'
            and upper(currency_code) = upper(trim(H1.currency_code));
            EXCEPTION
            WHEN OTHERS THEN
            l_verify_flag := 'N' ;
            l_error_message := l_error_message||'Currency Code is not Valid...';
        END;
        BEGIN
            select vendor_id
            into l_vendor_id
            from po_vendors
            where upper(vendor_name) = upper(trim(H1.vendor_name)) ;
            EXCEPTION
            WHEN OTHERS THEN
            l_verify_flag := 'N' ;
            l_error_message := l_error_message||'Vendor is not Existing...';
        END;
        BEGIN
            select vendor_site_id
            into l_vendor_site_id
            from po_vendor_sites_all
            where vendor_id = l_vendor_id
            and vendor_site_code = upper(trim(H1.vendor_site_code)) ;
            EXCEPTION
            WHEN OTHERS THEN
            l_verify_flag := 'N' ;
            l_error_message := l_error_message||'Vendor Site is not Existing...';
        END;
        BEGIN
            select location_id
            into l_ship_to
            from hr_locations
            where location_code = upper(trim(H1.ship_to));
            EXCEPTION
            WHEN OTHERS THEN
            l_verify_flag := 'N' ;
            l_error_message := l_error_message||'Ship To Location is not Existing...';
        END;
        BEGIN
            select location_id
            into l_bill_to
            from hr_locations
            where location_code = upper(trim(H1.bill_to));
            EXCEPTION
            WHEN OTHERS THEN
            l_verify_flag := 'N' ;
            l_error_message := l_error_message||'Bill To Location is not Existing...';
        END;
        If H1.status = 'Approved' then
        l_batch_id := 100 ;
        elsif H1.status = 'Incomplete' then
        l_batch_id := 101 ;
        else
        l_verify_flag := 'N' ;
        l_error_message := l_error_message||'Status is not valid...';
        end if;
l_legacy_ponum := trim(H1.legacy_ponum) ;
        IF l_verify_flag <> 'N' THEN
            insert into po_headers_interface
            (interface_header_id,
            batch_id,
            action,
            document_type_code,
            currency_code,
            agent_id,
            vendor_id,
            vendor_site_id,
            ship_to_location_id,
            bill_to_location_id
            )
            values
            (po_headers_interface_s.nextval,
            l_batch_id,
            'ORIGINAL',
            'STANDARD',
            l_currency_code,
            5479,
            l_vendor_id,
            l_vendor_site_id,
            l_ship_to,
            l_bill_to );
            update xxx_purchase_order_stg
            set h_verify_flag = 'Y'
            where legacy_ponum = l_legacy_ponum;
            COMMIT;
    FOR L1 IN C_PO_LINES(l_legacy_ponum) LOOP
        BEGIN
            select inventory_item_id
            into l_inventory_item_id
            from mtl_system_items_b
            where segment1||'.'||segment2||'.'||segment3||'.'||segment4=L1.item
            and organization_id = (select inventory_organization_id
            from hr_locations
            where location_id = l_ship_to ) ;
            EXCEPTION
            WHEN OTHERS THEN
            l_verify_flag := 'N' ;
            l_error_message := l_error_message ||'Inventory Item is not Existing...';
        END;
        IF L1.unit_price IS NULL THEN
        l_verify_flag := 'N' ;
        l_error_message := l_error_message ||'Unit Price is not Existing...';
        ELSIF L1.quantity IS NULL THEN
        l_verify_flag := 'N' ;
        l_error_message := l_error_message ||'Quantity is not Existing...';
        ELSIF L1.need_by_date IS NULL THEN
        l_verify_flag := 'N' ;
        l_error_message := l_error_message ||'Need By Date is not Existing...';
        END IF;
        IF l_verify_flag <> 'N' THEN
            insert into po_lines_interface
            (interface_line_id,
            interface_header_id,
            action,
            line_num,
            item_id,
            unit_price,
            quantity,
            Need_By_Date)
            values
            (po_lines_interface_s.nextval,
            po_headers_interface_s.currval,
            'ORIGINAL',
            L1.line_num,
            l_inventory_item_id,
            L1.unit_price,
            L1.quantity,
            L1.need_by_date);
            update xxx_purchase_order_stg
            set l_verify_flag = 'Y'
            where legacy_ponum = L1.legacy_ponum
            and line_num = L1.line_num;
ELSE
            update xxx_purchase_order_stg
            set l_error_message = l_error_message,
            l_verify_flag = 'N'
            where legacy_ponum = L1.legacy_ponum
            and line_num = L1.line_num;
END IF;
COMMIT;
END LOOP;
ELSE
            update xxx_purchase_order_stg
            set h_error_message = l_error_message,
            h_verify_flag = 'N'
            where legacy_ponum = H1.legacy_ponum;
END IF;
COMMIT;
END LOOP;
end xxx_po_api;
/
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)