API for create Purchase order using packing_list in oracle apps r12

PROCEDURE xxuflx_po_api_with_packing (
      errbuff                 OUT   VARCHAR2,
      retcode                 OUT   VARCHAR2,
      p_packing_list_number         VARCHAR2 DEFAULT NULL
   )
   IS
      --
      lv_po_number             po_headers_all.segment1%TYPE;
      lv_seq                   NUMBER                         := 0;
      lv_interface_header_id   NUMBER;
      lv_count                 NUMBER;
      lv_interface_lines_id    NUMBER;
      lv_distributions_id      NUMBER;
      x_return_status          VARCHAR2 (2000);
      x_return_msg             VARCHAR2 (2000);
      lv_line_num              NUMBER                         := 0;
      lv_rec_count             NUMBER                         := 0;
      xx_so_not_created        EXCEPTION;
      xx_po_created            EXCEPTION;
      lv_trx_number            VARCHAR2 (1000);
      lv_trx_date              VARCHAR2 (1000);
      l_api_version            NUMBER                         := 1.0;
      l_init_msg_list          VARCHAR2 (2)                := fnd_api.g_false;
      l_commit                 VARCHAR2 (2)                := fnd_api.g_false;
      lv_return_status         VARCHAR2 (2);
      lv_msg_count             NUMBER                         := 0;
      lv_group_id              NUMBER;
      lv_request_id            NUMBER           := fnd_global.conc_request_id;
      lv_resp_id               NUMBER;
      v_trd_oe_header_id       NUMBER;
      lv_po_header_id          NUMBER;

      --
      CURSOR cur_po_header
      IS
         SELECT   trd_oe_header_id, org_id, po_organization_code,
                  po_for_supplier_name, vendor_id, vendor_site_id,
                  bill_to_location_id, ship_to_location_id,
                  transactional_curr_code, ship_to_org_id,
                  order_quantity_uom, ship_location_line_int,
                  ship_org_line_int, created_by, packing_list_number,
                  resp_org
             FROM (SELECT DISTINCT xtl.trd_oe_header_id, xtl.trd_oe_line_id,
                                   xtl.org_id,
                                   xpv.roll_weight shipped_quantity,
                                   ROUND
                                      (xtl.trd_unit_selling_price
                                      ) trd_unit_price,
                                   xpo.po_organization_code,
                                   xpo.po_for_supplier_name, aps.vendor_id,
                                   apt.vendor_site_id,
                                   apt.bill_to_location_id,
                                   apt.ship_to_location_id,
                                   ooh.transactional_curr_code,
                                   ool.ship_to_org_id, ool.inventory_item_id,
                                   ool.order_quantity_uom,
                                   xsv.ship_to_location_id
                                                       ship_location_line_int,
                                   organization_id ship_org_line_int,
                                   xtl.created_by, xpv.packing_list_number,
                                   xpo.mgf_location resp_org,
                                   xtl.mgf_oe_line_id, xpv.lot_sublot_number
                              FROM xxuflx_trd_mst_maping_po_v xpo,
                                   xxulx_trade_order_lines_int xtl,
                                   ap_suppliers aps,
                                   ap_supplier_sites_all apt,
                                   oe_order_headers_all ooh,
                                   oe_order_lines_all ool,
                                   xxufl_ship_org_id_v xsv,
                                   xxuflx_packlist_fil_v xpv
                             WHERE xpo.mgf_org_id = xtl.org_id
                               AND aps.vendor_id = xpo.po_for_supplier_id
                               AND aps.vendor_id = apt.vendor_id
                               AND apt.org_id = xtl.org_id
                               AND xtl.oe_header_id = ooh.header_id
                               AND ool.header_id = ooh.header_id
                               AND ooh.org_id = xtl.org_id
                               AND xsv.operating_unit = xtl.org_id
                               AND xpo.po_organization_code =
                                                         xsv.organization_code
                               --    AND xtl.mgf_oe_header_id IS NOT NULL
                                --   AND xtl.process_flag = 'Y'
                               AND xtl.mgf_oe_header_id = xpv.order_header_id
                               AND xtl.mgf_org_id = xpv.org_id
                               AND xpv.line_id = xtl.mgf_oe_line_id
                               AND xpv.packing_list_number =
                                                         p_packing_list_number
                               AND xpv.lot_sublot_number NOT IN (
                                                  SELECT NVL (attribute1, 'N')
                                                    FROM po_lines_all
                                                   WHERE org_id = xtl.org_id))
         GROUP BY trd_oe_header_id,
                  org_id,
                  po_organization_code,
                  po_for_supplier_name,
                  vendor_id,
                  vendor_site_id,
                  bill_to_location_id,
                  ship_to_location_id,
                  transactional_curr_code,
                  ship_to_org_id,
                  order_quantity_uom,
                  ship_location_line_int,
                  ship_org_line_int,
                  created_by,
                  packing_list_number,
                  resp_org;

      CURSOR cur_po_line
      IS
         SELECT DISTINCT xtl.trd_oe_line_id, xtl.org_id,
                         xpv.roll_weight shipped_quantity,
                         ROUND (xtl.trd_unit_selling_price) trd_unit_price,
                         xpo.po_organization_code, xpo.po_for_supplier_name,
                         aps.vendor_id, apt.vendor_site_id,
                         apt.bill_to_location_id, apt.ship_to_location_id,
                         ooh.transactional_curr_code, ool.ship_to_org_id,
                         ool.inventory_item_id, ool.order_quantity_uom,
                         xsv.ship_to_location_id ship_location_line_int,
                         organization_id ship_org_line_int, xtl.created_by,
                         xpv.packing_list_number, xpo.mgf_location resp_org,
                         xtl.mgf_oe_line_id, xpv.lot_sublot_number
                    FROM xxuflx_trd_mst_maping_po_v xpo,
                         xxulx_trade_order_lines_int xtl,
                         ap_suppliers aps,
                         ap_supplier_sites_all apt,
                         oe_order_headers_all ooh,
                         oe_order_lines_all ool,
                         xxufl_ship_org_id_v xsv,
                         xxuflx_packlist_fil_v xpv
                   WHERE xpo.mgf_org_id = xtl.org_id
                     AND aps.vendor_id = xpo.po_for_supplier_id
                     AND aps.vendor_id = apt.vendor_id
                     AND apt.org_id = xtl.org_id
                     AND xtl.oe_header_id = ooh.header_id
                     AND ool.header_id = ooh.header_id
                     AND ooh.org_id = xtl.org_id
                     AND xsv.operating_unit = xtl.org_id
                     AND xpo.po_organization_code = xsv.organization_code
                     -- AND xtl.mgf_oe_header_id IS NOT NULL
                     -- AND xtl.process_flag = 'Y'
                     AND xtl.mgf_oe_header_id = xpv.order_header_id
                     AND xtl.mgf_org_id = xpv.org_id
                     AND xpv.line_id = xtl.mgf_oe_line_id
                     AND xpv.packing_list_number = p_packing_list_number
                     AND xpv.item_code = ool.ordered_item
                     AND xpv.lot_sublot_number NOT IN (
                                                  SELECT NVL (attribute1, 'N')
                                                    FROM po_lines_all
                                                   WHERE org_id = xtl.org_id);

   BEGIN
      SELECT COUNT (*)
        INTO lv_count
        FROM po_headers_all
       WHERE attribute15 = p_packing_list_number;

      IF lv_count <> 0
      THEN
         retcode := 1;
         x_return_msg :=
            'PO already created for packing list--Please Create New packing list';
         fnd_file.put_line (fnd_file.LOG,
                               TO_CHAR (SYSDATE, 'YYYYMMDD_HH24:MI:SS')
                            || ' - '
                            || x_return_msg
                           );
      ELSE
         FOR i IN cur_po_header
         LOOP
            lv_line_num := 0;
            v_trd_oe_header_id := i.trd_oe_header_id;

            SELECT po_headers_interface_s.NEXTVAL
              INTO lv_interface_header_id
              FROM DUAL;

            INSERT INTO po_headers_interface
                        (action, interface_header_id, interface_source_code,
                         org_id, document_type_code, currency_code,
                         agent_id, vendor_id, vendor_site_id,
                         ship_to_location_id, bill_to_location_id,
                         approval_status, approved_date,
                         confirming_order_flag, acceptance_required_flag,
                         frozen_flag, attribute_category, attribute1,
                         attribute2, attribute3, attribute7, attribute15,
                         batch_id, creation_date, created_by, attribute10,
                         attribute11, attribute14
                        )
                 VALUES ('ORIGINAL', lv_interface_header_id, NULL,
                         i.org_id, 'STANDARD', i.transactional_curr_code,
                         1435, i.vendor_id, i.vendor_site_id,
                         i.ship_to_location_id, i.bill_to_location_id,
                         'APPROVED', SYSDATE,
                         'N',                         --CONFIRMING_ORDER_FLAG,
                             'N',                  --ACCEPTANCE_REQUIRED_FLAG,
                         'N',                                   --FROZEN_FLAG,
                             'USA',                    --PO.ATTRIBUTE_CATEGORY
                                   'IMP.',
                         'OTHERS', 'LINE 12', 'OTHER', i.packing_list_number,
                         1, SYSDATE, i.created_by, 'N',
                         'N', i.trd_oe_header_id
                        );

            FOR l IN cur_po_line
            LOOP
               lv_line_num := lv_line_num + 1;

               SELECT po_lines_interface_s.NEXTVAL
                 INTO lv_interface_lines_id
                 FROM DUAL;

               INSERT INTO po_lines_interface
                           (interface_line_id, interface_header_id,
                            line_num, line_type_id, item_id,
                            quantity, unit_price,
                            uom_code, ship_to_organization_code,
                            line_type, need_by_date, promised_date,
                            creation_date, created_by,
                            ship_to_organization_id, ship_to_location_id,
                            line_attribute13, line_attribute14,
                            line_attribute1
                           )
                    VALUES (lv_interface_lines_id, lv_interface_header_id,
                            lv_line_num, 1, l.inventory_item_id,
                            l.shipped_quantity, l.trd_unit_price,
                            l.order_quantity_uom, l.po_organization_code,
                            'Goods', SYSDATE, SYSDATE,
                            SYSDATE, i.created_by,
                            l.ship_org_line_int, l.ship_location_line_int,
                            v_trd_oe_header_id, l.trd_oe_line_id,
                            l.lot_sublot_number
                           );

               SELECT po_distributions_interface_s.NEXTVAL
                 INTO lv_distributions_id
                 FROM DUAL;

               INSERT INTO po_distributions_interface
                           (interface_header_id, interface_line_id,
                            interface_distribution_id, distribution_num,
                            quantity_ordered, creation_date, created_by
                           )
                    VALUES (lv_interface_header_id, lv_interface_lines_id,
                            lv_distributions_id, lv_line_num,
                            l.shipped_quantity, SYSDATE, l.created_by
                           );
            END LOOP;

            COMMIT;
            -- IF lv_count = 0
            -- THEN
               -- fnd_file.put_line ('calling po import to process iface data');
            po_pdoi_grp.start_process
               (p_api_version                     => 1.0,
                p_init_msg_list                   => fnd_api.g_true,
                p_validation_level                => fnd_api.g_valid_level_full,
                p_commit                          => fnd_api.g_true,
                x_return_status                   => lv_return_status,
                p_gather_intf_tbl_stat            => fnd_api.g_false,
                p_calling_module                  => po_pdoi_constants.g_call_mod_concurrent_prgm,
                p_selected_batch_id               => 1,
                p_batch_size                      => po_pdoi_constants.g_def_batch_size,
                p_buyer_id                        => NULL,
                p_document_type                   => 'STANDARD',
                p_document_subtype                => NULL,
                p_create_items                    => 'N',
                p_create_sourcing_rules_flag      => NULL,
                p_rel_gen_method                  => NULL,
                p_sourcing_level                  => NULL,
                p_sourcing_inv_org_id             => NULL,
                p_approved_status                 => 'APPROVED',
                p_process_code                    => po_pdoi_constants.g_process_code_pending,
                p_interface_header_id             => NULL,
                p_org_id                          => i.org_id,
                p_ga_flag                         => NULL
               );

            -- lv_po_number := NULL;
            BEGIN
               SELECT MAX (segment1)
                 INTO lv_po_number
                 FROM po_headers_all
                WHERE org_id = i.org_id
                      AND attribute15 = p_packing_list_number;

               SELECT po_header_id
                 INTO lv_po_header_id
                 FROM po_headers_all
                WHERE org_id = i.org_id AND segment1 = lv_po_number;
            EXCEPTION
               WHEN OTHERS
               THEN
                  lv_po_number := NULL;
            END;

            --
            IF lv_po_number IS NULL
            THEN
               x_return_status := 'E';
               x_return_msg :=
                  'Error while creating PO for packing list. See PO Interface Errors for details';
               --
               -- print errors from interface
               lv_interface_header_id := apps.po_headers_interface_s.CURRVAL;
               fnd_file.put_line (fnd_file.LOG,
                                     TO_CHAR (SYSDATE, 'YYYYMMDD_HH24:MI:SS')
                                  || ' - '
                                  || x_return_msg
                                  || '-'
                                  || lv_interface_header_id
                                 );
            ELSE
               x_return_status := 'S';
               x_return_msg :=
                           'PO Created Successfully. PO# is ' || lv_po_number;
               --fnd_file.put_line (x_return_msg);
               fnd_file.put_line (fnd_file.LOG,
                                     TO_CHAR (SYSDATE, 'YYYYMMDD_HH24:MI:SS')
                                  || ' - '
                                  || x_return_msg
                                 );

               UPDATE po_line_locations_all
                  SET qty_rcv_exception_code = 'NONE'
                WHERE po_header_id = lv_po_header_id;

               xxuflx_insert_mrn_v (lv_po_number,
                                    i.org_id,
                                    p_packing_list_number
                                   );
            END IF;
         END LOOP;
      END IF;

      COMMIT;
   EXCEPTION
      WHEN xx_so_not_created
      THEN
         x_return_status := 'E';
         x_return_msg :=
            'Sales Order not created in US org. Packing list can not be processed.';
         fnd_file.put_line (fnd_file.LOG,
                               TO_CHAR (SYSDATE, 'YYYYMMDD_HH24:MI:SS')
                            || ' - '
                            || x_return_msg
                           );
      WHEN xx_po_created
      THEN
         x_return_status := 'S';
         x_return_msg :=
               'PO already created for packing list. PO# is ' || lv_po_number;
         fnd_file.put_line (fnd_file.LOG,
                               TO_CHAR (SYSDATE, 'YYYYMMDD_HH24:MI:SS')
                            || ' - '
                            || x_return_msg
                           );
      WHEN OTHERS
      THEN
         x_return_status := 'E';
         x_return_msg :=
            'Unexpected error while creating PO. Error Message - ' || SQLERRM;
         fnd_file.put_line (fnd_file.LOG,
                               TO_CHAR (SYSDATE, 'YYYYMMDD_HH24:MI:SS')
                            || ' - '
                            || x_return_msg
                           );
   --  END LOOP;
   END;