PO*创建标准采购订单-CSDN博客

-- l_iface_rec po_headers_interface%ROWTYPE;

校验头相关信息

  1 SELECT COUNT(1)
  2         INTO l_po_count
  3         FROM po_headers_all ph
  4        WHERE 1 = 1
  5          AND ph.segment1 = rec.po_number;
  6       IF (l_po_count > 0) THEN
  7         l_message := l_message || ' Po_number: ' || rec.po_number || ' Is Error';
  8         log('Po_number: ' || rec.po_number || ' Is Error');
  9       END IF;
 10     
 11       BEGIN
 12         SELECT hou.organization_id
 13           INTO l_org_id
 14           FROM hr_operating_units hou
 15          WHERE 1 = 1
 16            AND hou.name = rec.ou;
 17       EXCEPTION
 18         WHEN OTHERS THEN
 19           l_message := l_message || ' OU: ' || rec.ou || ' Is Error';
 20           log('OU: ' || rec.ou || ' Is Error');
 21       END;
 22     
 23       BEGIN
 24         SELECT podb.document_subtype
 25           INTO l_type_code
 26           FROM po_document_types_all_b  podb,
 27                po_document_types_all_tl podt
 28          WHERE 1 = 1
 29            AND podb.document_type_code = podt.document_type_code
 30            AND podb.document_subtype = podt.document_subtype
 31            AND podb.org_id = podt.org_id
 32            AND podt.language = 'US'
 33            AND podb.org_id = l_org_id
 34            AND podb.document_type_code IN ('PO', 'PA')
 35            AND podt.type_name = rec.type;
 36       EXCEPTION
 37         WHEN OTHERS THEN
 38           l_message := l_message || ' Type: ' || rec.type || ' Is Error';
 39           log('Type: ' || rec.type || ' Is Error');
 40       END;
 41     
 42       BEGIN
 43         SELECT pv.vendor_id
 44           INTO l_vendor_id
 45           FROM po_vendors pv
 46          WHERE 1 = 1
 47            AND pv.vendor_name = rec.suppiler;
 48       EXCEPTION
 49         WHEN OTHERS THEN
 50           l_message := l_message || ' Suppiler: ' || rec.suppiler || ' Is Error';
 51           log('Suppiler: ' || rec.suppiler || ' Is Error');
 52       END;
 53     
 54       BEGIN
 55         SELECT vs.vendor_site_id
 56           INTO l_vendor_site_id
 57           FROM po_vendor_sites_all vs
 58          WHERE 1 = 1
 59            AND vs.vendor_id = l_vendor_id
 60            AND vs.vendor_site_code = rec.site;
 61       EXCEPTION
 62         WHEN OTHERS THEN
 63           l_message := l_message || ' Site: ' || rec.site || ' Is Error';
 64           log('Site: ' || rec.site || ' Is Error');
 65       END;
 66     
 67       BEGIN
 68         SELECT hl.location_id
 69           INTO l_ship_to_location_id
 70           FROM hr_locations_all_tl hl
 71          WHERE 1 = 1
 72            AND hl.location_code = rec.ship_to
 73            AND hl.language = userenv('LANG');
 74       EXCEPTION
 75         WHEN OTHERS THEN
 76           l_message := l_message || ' Ship_to: ' || rec.ship_to || ' Is Error';
 77           log('Ship_to: ' || rec.ship_to || ' Is Error');
 78       END;
 79     
 80       BEGIN
 81         SELECT hl.location_id
 82           INTO l_bill_to_location_id
 83           FROM hr_locations_all_tl hl
 84          WHERE 1 = 1
 85            AND hl.location_code = rec.bill_to
 86            AND hl.language = userenv('LANG');
 87       EXCEPTION
 88         WHEN OTHERS THEN
 89           l_message := l_message || ' Bill_to: ' || rec.bill_to || ' Is Error';
 90           log('Bill_to: ' || rec.bill_to || ' Is Error');
 91       END;
 92     
 93       BEGIN
 94         SELECT p.person_id
 95           INTO l_agent_id
 96           FROM per_people_f p
 97          WHERE 1 = 1
 98            AND p.full_name = rec.buyer;
 99       EXCEPTION
100         WHEN OTHERS THEN
101           l_message := l_message || ' Buyer: ' || rec.buyer || ' Is Error';
102           log('Buyer: ' || rec.buyer || ' Is Error');
103       END;
104     
105       IF (l_message IS NOT NULL) THEN
106         dbms_output.put_line('Error l_message: ' || l_message);
107         RAISE fnd_api.g_exc_error;
108       END IF;
109

--插入头接口表

 1       l_iface_rec.interface_header_id   := po_headers_interface_s.nextval;
 2       l_iface_rec.interface_source_code := 'XXPO_ONHAND_HEADER';
 3       l_iface_rec.batch_id              := g_session_id;
 4       l_iface_rec.process_code          := 'PENDING'; --PENDING, ACCEPTED, REJECTED, or NOTIFIED
 5       l_iface_rec.action                := 'ORIGINAL'; --ORIGINAL,REPLACE, or UPDATE
 6       l_iface_rec.org_id                := l_org_id;
 7       l_iface_rec.document_num          := rec.po_number;
 8       l_iface_rec.document_type_code    := l_type_code; --STANDARD, BLANKET, or QUOTATION
 9       l_iface_rec.vendor_id             := l_vendor_id;
10       l_iface_rec.vendor_site_id        := l_vendor_site_id;
11       l_iface_rec.ship_to_location_id   := l_ship_to_location_id;
12       l_iface_rec.bill_to_location_id   := l_bill_to_location_id;
13       l_iface_rec.agent_id              := l_agent_id;
14       l_iface_rec.approval_status       := rec.status; --'INCOMPLETE'
15       l_iface_rec.currency_code         := rec.currency;
16       --l_iface_rec.rate_date              := to_date('22-MAR-2017', 'DD-MON-YYYY');
17       --l_iface_rec.rate_type              := 'Corporate';
18       l_iface_rec.last_update_date       := g_last_update_date;
19       l_iface_rec.last_updated_by        := g_last_updated_by;
20       l_iface_rec.creation_date          := rec.created_date;
21       l_iface_rec.created_by             := g_created_by;
22       l_iface_rec.last_update_login      := g_last_update_login;
23       l_iface_rec.program_application_id := g_program_appl_id;
24       l_iface_rec.program_id             := g_program_id;
25       l_iface_rec.program_update_date    := g_program_upd_date;
26       INSERT INTO po_headers_interface
27       VALUES l_iface_rec;

--l_iface_lines_rec po_lines_interface%ROWTYPE;

---校验行相关信息

 1 BEGIN
 2           SELECT msi.inventory_item_id,
 3                  msi.description
 4             INTO l_item_id,
 5                  l_item_description
 6             FROM mtl_system_items_b           msi,
 7                  financials_system_params_all fsp
 8            WHERE 1 = 1
 9              AND nvl(msi.organization_id, fsp.inventory_organization_id) = fsp.inventory_organization_id
10              AND fsp.org_id = l_org_id
11              AND msi.segment1 = rec_po_line.item_number;
12         EXCEPTION
13           WHEN OTHERS THEN
14             l_message := l_message || ' Item_number: ' || rec_po_line.item_number || ' Is Error';
15             log('Item_number: ' || rec_po_line.item_number || ' Is Error');
16         END;
17       
18         BEGIN
19           SELECT t.category_id
20             INTO l_category_id
21             FROM mtl_categories_kfv t
22            WHERE t.concatenated_segments = 'DEFAULT|DEFAULT';
23         EXCEPTION
24           WHEN OTHERS THEN
25             l_message := l_message || ' Category Is Error';
26             log('Category Is Error');
27         END;
28       
29         IF (l_message IS NOT NULL) THEN
30           dbms_output.put_line('Error l_message: ' || l_message);
31           RAISE fnd_api.g_exc_error;
32         END IF;

--插入行接口表

 1         l_iface_lines_rec                        := NULL;
 2         l_iface_lines_rec.interface_header_id    := l_iface_rec.interface_header_id;
 3         l_iface_lines_rec.interface_line_id      := po.po_lines_interface_s.nextval;
 4         l_iface_lines_rec.process_code           := 'PENDING';
 5         l_iface_lines_rec.action                 := 'ORIGINAL';
 6         l_iface_lines_rec.line_num               := rec_po_line.line_num;
 7         l_iface_lines_rec.shipment_num           := 1;
 8         l_iface_lines_rec.line_type_id           := 1;
 9         l_iface_lines_rec.line_type              := rec_po_line.type;
10         l_iface_lines_rec.item_id                := l_item_id;
11         l_iface_lines_rec.category_id            := l_category_id;
12         l_iface_lines_rec.item_description       := l_item_description;
13         l_iface_lines_rec.uom_code               := rec_po_line.uom;
14         l_iface_lines_rec.quantity               := rec_po_line.qty;
15         l_iface_lines_rec.unit_price             := rec_po_line.price;
16         l_iface_lines_rec.need_by_date           := rec_po_line.need_by;
17         l_iface_lines_rec.last_update_date       := g_last_update_date;
18         l_iface_lines_rec.last_updated_by        := g_last_updated_by;
19         l_iface_lines_rec.creation_date          := g_creation_date;
20         l_iface_lines_rec.created_by             := g_created_by;
21         l_iface_lines_rec.last_update_login      := g_last_update_login;
22         l_iface_lines_rec.program_application_id := g_program_appl_id;
23         l_iface_lines_rec.program_id             := g_program_id;
24         l_iface_lines_rec.program_update_date    := g_program_upd_date;
25         INSERT INTO po_lines_interface
26         VALUES l_iface_lines_rec;
27

--l_iface_dis_rec po_distributions_interface%ROWTYPE;

 1   l_iface_dis_rec.interface_header_id       := po_headers_interface_s.currval; --;  
 2         l_iface_dis_rec.interface_line_id         := po_lines_interface_s.currval;
 3         l_iface_dis_rec.interface_distribution_id := po.po_distributions_interface_s.nextval; -- NULL;  
 4         l_iface_dis_rec.distribution_num          := 1;
 5         l_iface_dis_rec.org_id                    := l_org_id;
 6         l_iface_dis_rec.quantity_ordered          := rec_po_line.qty;
 7         l_iface_dis_rec.destination_type_code     := 'INVENTORY'; --类型（费用/库存）  
 8         l_iface_dis_rec.rate_date                 := SYSDATE;
 9         l_iface_dis_rec.last_update_date          := g_last_update_date;
10         l_iface_dis_rec.last_updated_by           := g_last_updated_by;
11         l_iface_dis_rec.creation_date             := g_creation_date;
12         l_iface_dis_rec.created_by                := g_created_by;
13         l_iface_dis_rec.last_update_login         := g_last_update_login;
14         l_iface_dis_rec.program_application_id    := g_program_appl_id;
15         l_iface_dis_rec.program_id                := g_program_id;
16         l_iface_dis_rec.program_update_date       := g_program_upd_date;
17         INSERT INTO po_distributions_interface
18         VALUES l_iface_dis_rec;

--导入PO

 1       po_docs_interface_sv5.process_po_headers_interface(x_selected_batch_id          => l_iface_rec.batch_id,
 2                                                          x_buyer_id                   => NULL,
 3                                                          x_document_type              => l_iface_rec.document_type_code,
 4                                                          x_document_subtype           => NULL,
 5                                                          x_create_items               => 'N',
 6                                                          x_create_sourcing_rules_flag => NULL,
 7                                                          x_rel_gen_method             => NULL,
 8                                                          x_approved_status            => l_iface_rec.approval_status,
 9                                                          x_commit_interval            => 1,
10                                                          x_process_code               => 'PENDING',
11                                                          x_interface_header_id        => NULL,
12                                                          x_org_id_param               => NULL,
13                                                          x_ga_flag                    => NULL)

--查看错误信息

ln_err_count := 0;
    FOR c IN (SELECT t.document_num,
                     l.line_num,
                     d.distribution_num,
                     a.error_message,
                     l.interface_line_id,
                     d.interface_distribution_id,
                     a.interface_header_id
                FROM po_interface_errors        a,
                     po_headers_interface       t,
                     po_lines_interface         l,
                     po_distributions_interface d
               WHERE a.interface_header_id = t.interface_header_id
                 AND l.interface_line_id(+) = a.interface_line_id
                 AND d.interface_distribution_id(+) = a.interface_distribution_id
                 AND t.batch_id = l_iface_rec.batch_id)
    LOOP
      ln_err_count := ln_err_count + 1;
      log('Error: ' || c.error_message);
      dbms_output.put_line('Error: ' || c.error_message);
    
      DELETE po_headers_interface ph
       WHERE ph.interface_header_id = l_iface_rec.interface_header_id;
    
      DELETE po_lines_interface pl
       WHERE pl.interface_header_id = l_iface_rec.interface_header_id;
    
      DELETE po_distributions_interface pd
       WHERE pd.interface_header_id = l_iface_rec.interface_header_id;
    
    END LOOP;
    IF (ln_err_count > 0) THEN
      RAISE fnd_api.g_exc_error;
    END IF;