茫茫網海中的冷日
         
茫茫網海中的冷日
發生過的事,不可能遺忘,只是想不起來而已!
 恭喜您是本站第 1535616 位訪客!  登入  | 註冊
主選單

Google 自訂搜尋

Goole 廣告

隨機相片
F09_306.jpg

授權條款

使用者登入
使用者名稱:

密碼:


忘了密碼?

現在就註冊!

DB研討會 : [轉貼]EBS 接口開發

發表者 討論內容
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15763
[轉貼]EBS 接口開發
EBS 接口開發
2012年02月21日 15:01:42



1 根據數據源創建臨時表
2 將數據源導入臨時表
3 將臨時表的數據導入標準接口表
4 創建採購員
5 提交Import Standard Purchase Orders請求

1 根據數據源創建臨時表
     /* Formatted on 2012/02/20 10:24 (Formatter Plus v4.8.7) */
CREATE TABLE po_headers_interface_beke(
      org_id NUMBER,
      process_code VARCHAR2(25),
      action VARCHAR2(25),
      document_type_code VARCHAR2(25),
      document_subtype VARCHAR2(25),
      document_num VARCHAR2(20),
      approval_status VARCHAR2(25),
      agent_id NUMBER,
      vendor_id NUMBER,
      vendor_site_id NUMBER,
      interface_source_code VARCHAR2(25),
      batch_id NUMBER,
      interface_header_id NUMBER NOT NULL
)

/* Formatted on 2012/02/20 10:38 (Formatter Plus v4.8.7) */
CREATE TABLE po_lines_interface_beke(
       process_code VARCHAR2(25),
       action VARCHAR2(25),
       line_num NUMBER,
       item_id NUMBER,
       unit_price NUMBER,
       interface_header_id NUMBER NOT NULL
)

2 將數據源導入臨時表,可以用sql loader,也可以在PL/SQL Developer中複製粘貼,由於數據不多,在這裡用複製粘貼的方式
/* Formatted on 2012/02/21 11:31 (Formatter Plus v4.8.7) */
SELECT *
  FROM po_headers_interface_beke for update
/* Formatted on 2012/02/21 11:32 (Formatter Plus v4.8.7) */
SELECT *
  FROM po_lines_interface_beke for update

3 創建存儲過程,將臨時表的數據導入標準接口表,在此創建了一個包,包兩個存儲過程
3.1 package
                /* Formatted on 2012/02/21 13:34 (Formatter Plus v4.8.7) */
CREATE OR REPLACE PACKAGE po_interface_beke_pkg
IS
   PROCEDURE po_interface_add_bpa_beke (
      p_user_id        IN   NUMBER := 0,
      p_resp_id        IN   NUMBER := 52054,
      p_resp_appl_id   IN   NUMBER := 201
   );
   PROCEDURE po_interface_add_bpa_beke2 (
      p_user_id        IN   NUMBER := 0,
      p_resp_id        IN   NUMBER := 52054,
      p_resp_appl_id   IN   NUMBER := 201
   );
END po_interface_beke_pkg;

3.2 package body
                /* Formatted on 2012/02/21 13:37 (Formatter Plus v4.8.7) */
CREATE OR REPLACE PACKAGE BODY po_interface_beke_pkg
IS
   PROCEDURE po_interface_add_bpa_beke (
      p_user_id        IN   NUMBER := 0,
      p_resp_id        IN   NUMBER := 52054,
      p_resp_appl_id   IN   NUMBER := 201
   )
   IS
      l_iface_rec         po_headers_interface%ROWTYPE;
      l_iface_lines_rec   po_lines_interface%ROWTYPE;
   BEGIN
      SELECT po_headers_interface_s.NEXTVAL
        INTO l_iface_rec.interface_header_id
        FROM DUAL;

      SELECT phib.org_id, phib.process_code,
             phib.action, phib.document_type_code,
             phib.document_subtype, phib.document_num,
             phib.approval_status, phib.agent_id,
             phib.vendor_id, phib.vendor_site_id,
             phib.interface_source_code, phib.batch_id
        INTO l_iface_rec.org_id, l_iface_rec.process_code,
             l_iface_rec.action, l_iface_rec.document_type_code,
             l_iface_rec.document_subtype, l_iface_rec.document_num,
             l_iface_rec.approval_status, l_iface_rec.agent_id,
             l_iface_rec.vendor_id, l_iface_rec.vendor_site_id,
             l_iface_rec.interface_source_code, l_iface_rec.batch_id
        FROM po_headers_interface_beke phib;

      INSERT INTO po_headers_interface
           VALUES l_iface_rec;

      SELECT po_lines_interface_s.NEXTVAL
        INTO l_iface_lines_rec.interface_line_id
        FROM DUAL;

      l_iface_lines_rec.interface_header_id := l_iface_rec.interface_header_id;

      SELECT plib.process_code, plib.action,
             plib.line_num, plib.item_id,
             plib.unit_price
        INTO l_iface_lines_rec.process_code, l_iface_lines_rec.action,
             l_iface_lines_rec.line_num, l_iface_lines_rec.item_id,
             l_iface_lines_rec.unit_price
        FROM po_lines_interface_beke plib;

      INSERT INTO po_lines_interface
           VALUES l_iface_lines_rec;

      --Should initialize before po_docs_interface_sv5.process_po_header_interface
      fnd_global.apps_initialize (user_id           => p_user_id,
                                  resp_id           => p_resp_id,
                                  resp_appl_id      => p_resp_appl_id
                                 );
      po_docs_interface_sv5.process_po_headers_interface
                          (x_selected_batch_id               => l_iface_rec.batch_id,
                           x_buyer_id                        => NULL,
                           x_document_type                   => l_iface_rec.document_type_code,
                           x_document_subtype                => l_iface_rec.document_subtype,
                           x_create_items                    => 'N',
                           x_create_sourcing_rules_flag      => NULL,
                           x_rel_gen_method                  => NULL,
                           x_approved_status                 => l_iface_rec.approval_status,
                           x_commit_interval                 => 1,
                           x_process_code                    => l_iface_lines_rec.process_code,
                           x_interface_header_id             => NULL,
                           x_org_id_param                    => NULL,
                           x_ga_flag                         => NULL
                          );
   END po_interface_add_bpa_beke;

   PROCEDURE po_interface_add_bpa_beke2 (
      p_user_id        IN   NUMBER := 0,
      p_resp_id        IN   NUMBER := 52054,
      p_resp_appl_id   IN   NUMBER := 201
   )
   IS
      l_iface_rec         po_headers_interface%ROWTYPE;
      l_iface_lines_rec   po_lines_interface%ROWTYPE;

      CURSOR cur_headers
      IS
         SELECT phib.org_id, phib.process_code, phib.action,
                phib.document_type_code, phib.document_subtype,
                phib.document_num, phib.approval_status, phib.agent_id,
                phib.vendor_id, phib.vendor_site_id,
                phib.interface_source_code, phib.batch_id,
                phib.interface_header_id
           FROM po_headers_interface_beke phib;

      CURSOR cur_lines
      IS
         SELECT plib.process_code, plib.action, plib.line_num, plib.item_id,
                plib.unit_price, plib.interface_header_id
           FROM po_lines_interface_beke plib;
   BEGIN
      FOR rec_headers IN cur_headers
      LOOP
         SELECT po_headers_interface_s.NEXTVAL
           INTO l_iface_rec.interface_header_id
           FROM DUAL;

         l_iface_rec.org_id := rec_headers.org_id;
         l_iface_rec.process_code := rec_headers.process_code;
         l_iface_rec.action := rec_headers.action;
         l_iface_rec.document_type_code := rec_headers.document_type_code;
         l_iface_rec.document_subtype := rec_headers.document_subtype;
         l_iface_rec.document_num := rec_headers.document_num;
         l_iface_rec.approval_status := rec_headers.approval_status;
         l_iface_rec.agent_id := rec_headers.agent_id;
         l_iface_rec.vendor_id := rec_headers.vendor_id;
         l_iface_rec.vendor_site_id := rec_headers.vendor_site_id;
         l_iface_rec.interface_source_code :=rec_headers.interface_source_code;
         l_iface_rec.batch_id := rec_headers.batch_id;

         INSERT INTO po_headers_interface
              VALUES l_iface_rec;

         FOR rec_lines IN cur_lines
         LOOP
            IF rec_lines.interface_header_id = rec_headers.interface_header_id
            THEN
               SELECT po_lines_interface_s.NEXTVAL
                 INTO l_iface_lines_rec.interface_line_id
                 FROM DUAL;

               l_iface_lines_rec.interface_header_id := l_iface_rec.interface_header_id;
               l_iface_lines_rec.process_code := rec_lines.process_code;
               l_iface_lines_rec.action := rec_lines.action;
               l_iface_lines_rec.line_num := rec_lines.line_num;
               l_iface_lines_rec.item_id := rec_lines.item_id;
               l_iface_lines_rec.unit_price := rec_lines.unit_price;

               INSERT INTO po_lines_interface
                    VALUES l_iface_lines_rec;

               --Should initialize before po_docs_interface_sv5.process_po_header_interface
               fnd_global.apps_initialize (user_id           => p_user_id,
                                           resp_id           => p_resp_id,
                                           resp_appl_id      => p_resp_appl_id
                                          );
               po_docs_interface_sv5.process_po_headers_interface
                          (x_selected_batch_id               => l_iface_rec.batch_id,
                           x_buyer_id                        => NULL,
                           x_document_type                   => l_iface_rec.document_type_code,
                           x_document_subtype                => l_iface_rec.document_subtype,
                           x_create_items                    => 'N',
                           x_create_sourcing_rules_flag      => NULL,
                           x_rel_gen_method                  => NULL,
                           x_approved_status                 => l_iface_rec.approval_status,
                           x_commit_interval                 => 1,
                           x_process_code                    => l_iface_lines_rec.process_code,
                           x_interface_header_id             => NULL,
                           x_org_id_param                    => NULL,
                           x_ga_flag                         => NULL
                          );
            END IF;
         END LOOP;
      END LOOP;
   END po_interface_add_bpa_beke2;
END po_interface_beke_pkg;

4 創建採購員
4.1 在Human Resources模塊裡創建employee


4.2 在System Adminstrator裡面,把剛創建的employee添加到Person項裡


4.3 在Purchasing Super User裡,在Setup->Personnel->Buyers表單裡把剛才創建的employee設置為Buyer採購員


5 提交Import Standard Purchase Orders請求,根據Batch Id可以看出是剛才導入的數據


That's all.

原文出處:EBS 接口开发 - CSDN博客
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15763
[轉貼]Oracle EBS 採購訂單導入(API)
採購訂單導入(API)
2014年12月24日 10:49:27
DECLARE
  l_iface_rec       po.po_headers_interface%ROWTYPE;
  l_iface_lines_rec po.po_lines_interface%ROWTYPE;
  l_iface_dis_rec   po.po_distributions_interface%ROWTYPE;
  --l_org_id          NUMBER := 81; --OU ID l_user_id NUMBER := 0; --User ID?Sysadmin
  l_batch_id NUMBER;
  l_line_num NUMBER;
  CURSOR headers IS
    SELECT DISTINCT old_po_number,
                    ou_name,
                    org_id,
                    vendor_name,
                    vendor_id,
                    vendor_site_code,
                    vendor_site_id,
                    agent_name,
                    agent_id,
                    currency_code,
                    terms_name,
                    terms_id,
                    rate,
                    rate_type

      FROM cux_po_inport;

  CURSOR lines(p_old_po_number  VARCHAR2,
               p_org_id         NUMBER,
               p_vendor_id      NUMBER,
               p_vendor_site_id NUMBER,
               p_agent_id       NUMBER,
               p_currency_code  VARCHAR2,
               p_terms_id       NUMBER) IS
    SELECT item_code,
           item_id,
           quantity,
           uom,
           unit_price,
           promised_date,
           need_by_date,
           destination_subinventory
      FROM cux_po_inport
     WHERE old_po_number = p_old_po_number
       AND org_id = p_org_id
       AND vendor_id = p_vendor_id
       AND vendor_site_id = p_vendor_site_id
       AND agent_id = p_agent_id
       AND currency_code = p_currency_code
       AND terms_id = p_terms_id;
BEGIN
  SELECT po_headers_interface_s.NEXTVAL INTO l_batch_id FROM dual;

  FOR h IN headers LOOP
    --l_iface_rec.org_id := l_org_id;
    SELECT po_headers_interface_s.NEXTVAL
      INTO l_iface_rec.interface_header_id
      FROM dual;
    l_iface_rec.process_code       := 'PENDING';
    l_iface_rec.action             := 'ORIGINAL';
    l_iface_rec.document_type_code := 'STANDARD';
    l_iface_rec.document_subtype   := NULL;
    --l_iface_rec.quote_warning_delay   := 10;
    l_iface_rec.document_num    := NULL;
    l_iface_rec.approval_status := 'APPROVED';
    l_iface_rec.agent_id        := h.agent_id;
    l_iface_rec.vendor_id       := h.vendor_id;
    l_iface_rec.vendor_site_id  := h.vendor_site_id;
    l_iface_rec.currency_code   := h.currency_code;
    l_iface_rec.terms_id        := h.terms_id;
    l_iface_rec.attribute5      := h.old_po_number;
    IF h.rate_type IS NULL THEN
      l_iface_rec.rate_date := NULL;
    ELSE
      l_iface_rec.rate_date := TO_DATE('2012-12-06', 'YYYY-MM-DD') /*TRUNC(SYSDATE)*/
       ;
    END IF;

    l_iface_rec.RATE_TYPE := h.rate_type;
    l_iface_rec.rate      := h.rate;

    l_iface_rec.interface_source_code := 'CUX_PO_INPORT';
    l_iface_rec.batch_id              := l_batch_id;

    INSERT INTO po.po_headers_interface VALUES l_iface_rec;
    l_line_num := 0;
    FOR l IN lines(h.old_po_number,
                   h.org_id,
                   h.vendor_id,
                   h.vendor_site_id,
                   h.agent_id,
                   h.currency_code,
                   h.terms_id) LOOP
      l_line_num                            := l_line_num + 1;
      l_iface_lines_rec.interface_header_id := l_iface_rec.interface_header_id;
      l_iface_lines_rec.process_code        := 'PENDING';
      l_iface_lines_rec.action              := 'ORIGINAL';
      l_iface_lines_rec.line_num            := l_line_num;
      l_iface_lines_rec.item_id             := l.item_id;
      --l_iface_lines_rec.promised_date       := SYSDATE;
      --Shipment 1
      --l_iface_lines_rec.shipment_num  := 1;
      l_iface_lines_rec.quantity      := l.quantity;
      l_iface_lines_rec.unit_price    := l.unit_price;
      l_iface_lines_rec.promised_date := l.promised_date; --承諾日期
      l_iface_lines_rec.need_by_date  := l.need_by_date; --需求日期

      SELECT po_lines_interface_s.NEXTVAL
        INTO l_iface_lines_rec.interface_line_id
        FROM dual;
      INSERT INTO po_lines_interface VALUES l_iface_lines_rec;
      l_iface_dis_rec.interface_header_id := l_iface_lines_rec.interface_header_id;
      l_iface_dis_rec.distribution_num    := 1;
      --l_iface_dis_rec.quantity_delivered  := 0;
      --l_iface_dis_rec.quantity_billed     := 0;
      --l_iface_dis_rec.quantity_cancelled  := 0; --Must Be 0
      --l_iface_dis_rec.charge_account_id   := NULL;
      l_iface_dis_rec.interface_line_id        := l_iface_lines_rec.interface_line_id;
      l_iface_dis_rec.quantity_ordered         := l_iface_lines_rec.quantity;
      l_iface_dis_rec.destination_subinventory := l.destination_subinventory; --子庫
      SELECT po.po_distributions_interface_s.NEXTVAL
        INTO l_iface_dis_rec.interface_distribution_id
        FROM dual;
      INSERT INTO po.po_distributions_interface VALUES l_iface_dis_rec;
    END LOOP;
  END LOOP;
  /* --Shipment 2
  l_iface_lines_rec.shipment_num := 2;
  l_iface_lines_rec.quantity     := 300;
  --l_iface_lines_rec.unit_price   := 1;
  SELECT po_lines_interface_s.NEXTVAL
    INTO l_iface_lines_rec.interface_line_id
    FROM dual;
  INSERT INTO po_lines_interface VALUES l_iface_lines_rec;

  l_iface_dis_rec.interface_line_id        := l_iface_lines_rec.interface_line_id;
  l_iface_dis_rec.quantity_ordered         := l_iface_lines_rec.quantity;
  l_iface_dis_rec.destination_subinventory := 'W009'; --子庫
  SELECT po.po_distributions_interface_s.NEXTVAL
    INTO l_iface_dis_rec.interface_distribution_id
    FROM dual;
  INSERT INTO po.po_distributions_interface VALUES l_iface_dis_rec;*/
  --Should initialize before po_docs_interface_sv5.process_po_headers_interface
  apps.fnd_global.apps_initialize(user_id      => 0,
                                  resp_id      => 50819,
                                  resp_appl_id => 201);
  mo_global.set_policy_context('S', 84 /*fnd_profile.VALUE('ORG_ID')*/);
  po_docs_interface_sv5.process_po_headers_interface(x_selected_batch_id          => l_iface_rec.batch_id,
                                                     x_buyer_id                   => NULL,
                                                     x_document_type              => l_iface_rec.document_type_code,
                                                     x_document_subtype           => l_iface_rec.document_subtype,
                                                     x_create_items               => 'N',
                                                     x_create_sourcing_rules_flag => NULL,
                                                     x_rel_gen_method             => NULL,
                                                     x_approved_status            => l_iface_rec.approval_status,
                                                     x_commit_interval            => 1,
                                                     x_process_code               => 'PENDING',
                                                     x_interface_header_id        => NULL,
                                                     x_org_id_param               => NULL,
                                                     x_ga_flag                    => NULL);
END;


原文出處:采购订单导入(API) - CSDN博客
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15763
[轉貼]Oracle PO 採購訂單接口導入 分拆發運行
Oracle PO 採購訂單接口導入 分拆發運行
2016年07月29日 11:36:42

現象:
在oracle ebs po 導入標準採購訂單的時候,分拆採購訂單,發現不能創建分配中的賬戶等信息

測試環境:
Oracle EBS R12.1.3

解決方法:
po接口行表字段修改
l_iface_lines_rec.line_loc_populated_flag := 'Y'; --手工創建發運行    『N』 --自動創建發運行

1.進行發運行的分拆
2.修改接口代碼
select * from po_lines_interface.action := 'ADD' --經測試傳add時候 會創建分配中的賬戶
--標準採購訂單傳 ORIGINAL   的時候不能生成分配中的賬戶信息等
--ORIGINAL  新增/REPLACE  替換整個訂單/UPDATE  修改

調用 po_docs_interface_sv5.process_po_headers_interface 創建採購訂單

原文出處:Oracle PO 采购订单接口导入 分拆发运行 - CSDN博客
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15763
[轉貼]Oracle Purchase Order Single Insert Script Process

Oracle Purchase Order Single Insert Script Process

Overview

            This document focuses on creating single insert scripts for importing standard purchase orders using interface approach. It also mentions the prerequisites for importing standard purchase orders.

This Document provides the guidance to the user with the necessary information for importing the purchase orders through interface approach.
          The Interface tables used here are:
           1. PO_HEADERS_INTERFACE
           2. PO_LINES_INTERFACE

           3. PO_DISTRIBUTIONS_INTERFACE



Mandatory Columns

COLUMN_NAME
DATATYPE
REQUIRED
COMMENTS
INTERFACE_HEADER_ID
NUMBER
Y
A sequential value
BATCH_ID
NUMBER
Y
To uniquely identify the records inserted
ACTION
VARCHAR2
Y
'ORIGINAL','UPDATE','ADD'
DOCUMENT_TYPE_CODE
VARCHAR2
Y
'STANDARD','BLANKET','QUOTATION'
VENDOR_ID
NUMBER
Y
Vendor Id
VENDOR_SITE_ID
NUMBER
Y
Vendor Site for the vendor
ORG_ID

NUMBER
OPTIONAL
Operating unit
CURRENCY_CODE
VARCHAR2
Y
Base Currency for the PO
AGENT_ID
NUMBER
Y
The buyer defined for the PO
SHIP_TO_LOCATION_ID
NUMBER
Y
Ship To Location for the purchase order
BILL_TO_LOCATION_ID
NUMBER
Y
Billing location
APPROVED_DATE
DATE
OPTIONAL
The date PO approved
CREATION_DATE
DATE
Y

CREATED_BY
NUMBER
Y
Who Column
EFFECTIVE_DATE
DATE
OPTIONAL





COLUMN_NAME

DATATYPE
REQUIRED
COMMENTS
INTERFACE_LINE_ID
NUMBER
Y
A sequential value
INTERFACE_HEADER_ID
NUMBER
Y
A sequential value matching the header value
LINE_TYPE
VARCHAR2
Y
'GOODS'
LINE_NUM
NUMBER
Y
Line number
UNIT_OF_MEASURE
VARCHAR2
Y
Unit of measure for the item
QUANTITY
NUMBER
Y
Quantity required
UNIT_PRICE
NUMBER
Y
The unit price for the item being ordered
ITEM_ID
NUMBER
OPTIONAL
The item id
ITEM_DESCRIPTION
VARCHAR2
Y
The description of the item
SHIP_TO_LOCATION_ID
NUMBER
OPTIONAL
Ship To Location for the purchase order
SHIP_TO_ORGANIZATION_ID
NUMBER
OPTIONAL
The organization corresponding to ship_to_location_id
CREATION_DATE
DATE
Y
Who column
CREATED_BY
NUMBER
Y
Who Column
SHIPMENT_NUMBER
NUMBER
OPTIONAL
Shipment number
CATEGORY_ID
NUMBER
OPTIONAL
The purchasing category assigned to this item.
PROMISED_DATE
DATE
Y
The promised date of deliverable

 


                        PO_DISTRIBUTIONS_INTERFACE

COLUMN_NAME
DATATYPE

REQUIRED
COMMENTS
INTERFACE_DISTRIBUTION_ID
NUMBER
Y
A sequential value
INTERFACE_LINE_ID
NUMBER
Y
A sequential value matching the line value
INTERFACE_HEADER_ID
VARCHAR2
Y
A sequential value matching the header value
DISTRIBUTION_NUMBER
NUMBER
Y
The distribution number

QUANTITY_ORDERED
VARCHAR2
Y
Quantity ordered in the PO
DESTINATION_TYPE_CODE
NUMBER
Y
'EXPENSE','INVENTORY'
ACRUE_ON_RECEIPT_FLAG
NUMBER
Y

CREATION_DATE
NUMBER
Y
Who column
CREATED_BY
VARCHAR2
Y
Who column
ORG_ID

NUMBER
OPTIONAL
Operating Unit

 


Insertion steps

Following are the steps that must be followed, in order, to perform the Single insertions for purchase orders in 12.1.3
Execution Step
Description
Step 1
Identify the vendor id and his corresponding vendor site id.

select * from ap_suppliers
where 1 = 1
and vendor_name like 'Star Gate Ltd'
and vendor_id = 5

With the above vendor_id get any one of the vendor_site_id which have purchasing_site_flag enabled. The vendor_site_id is  specific to the operating unit. Choose the vendor_site_id under the operating unit you are performing the import.

select * from ap_supplier_sites_all
where 1 = 1
and vendor_id = 5
AND purchasing_site_flag = 'Y'
and vendor_site_id = 6

Step 2
Get the agent_id i.e. buyer_id. A agent has to be defined for viewing any purchase order related forms. These agents are the employees of the organizations who are set up as buyers, so that they can raise purchase orders and requisitions.

SELECT full_name , agent_id
FROM po_agents pa , per_all_people_f ppl
WHERE 1 = 1
and pa . agent_id = ppl . person_id
AND
UPPER ( ppl . full_name ) LIKE UPPER ( 'Stock, Ms. Pat' )
and agent_id  = 25

Step 3
Identify the ship_to_location_id and bill_to_location_id defined for the vendor_sites using the Step 1 of vendor sites.

SELECT
vendor_site_id
, ship_to_location_id
, bill_to_location_id
FROM po_vendor_sites_all
WHERE 1 = 1
and vendor_id = 5
AND purchasing_site_flag = 'Y'
AND org_id = 204 ;


If there is no data for the location columns then derive the values from the below query. It returns the default ship_to and bill_to location values for a org id.

SELECT
ship_to_location_id
, bill_to_location_id
FROM financials_system_params_all
WHERE 1 = 1
AND
org_id = 204

Step 4
Choose one of the values for the action column
'ORIGINAL' → A New record
'UPDATE' → To update an existing record
'ADD' → To add lines to an existing purchase order.

Choose the following values for the document_type_code column
STANDARD → One time purchase orders
BLANKET  →  Cover a specific date range and used when not sure of the exact quantity is not known prior.
QUOTATIONS → Quotation in response to the RFQ generated by the organization.


Step 5
INSERT INTO po_headers_interface
          ( interface_header_id ,
         batch_id ,
         action ,
         document_type_code ,
         vendor_id ,
         vendor_site_id ,
         org_id ,

         currency_code ,
         agent_id ,
         ship_to_location_id ,
         bill_to_location_id ,
         approved_date ,
         creation_date ,
         created_by ,
         effective_date )
     VALUES
( po_headers_interface_s . NEXTVAL , --interface_header_id,
             4321 ,                   -- batch_id
             'ORIGINAL' ,               -- action
             'STANDARD' ,   -- document_type_code
             5
,                     -- vendor_id
             6 ,                -- vendor_site_id
             204 ,                      -- org_id
             'USD' ,             -- currency_code

             25 ,                     -- agent_id
             207 ,         -- ship_to_location_id
             204 ,         -- bill_to_location_id
             SYSDATE ,           -- approved_date

             SYSDATE ,           -- creation_date
             1318 ,                 -- created_by
             SYSDATE            --effective_date
                    );

Step 6

Get an item id from mtl_system_items_b such that both the flags 'Purchasing_Enabled_Flag' and 'Purchasing Item Flag' are set to 'Y'. Also get the relevant unit_of_measure for the item.

SELECT
primary_unit_of_measure
, inventory_item_id
, description
, organization_id
FROM mtl_system_items_b
WHERE 1 = 1
AND segment1 = 'AS54999'
AND purchasing_enabled_flag = 'Y'
AND purchasing_item_flag =
'Y'

Step 7
Based on the ship_to_location_id column value given in the Po_Headers_Interface derive the corresponding ship_to_organization_id.
SELECT hu .NAME, hu . organization_id
FROM hr_organization_information hi , hr_all_organization_units hu
WHERE 1 = 1
AND hi . organization_id = hu . organization_id
AND hu . location_id = 207 --(as from the Step 3 )
AND hi . org_information1 = 'INV'

Step 8
There's an exclusive category set to be used for purchasing  which is 'Purchasing Category Set'.
The item being considered for purchase orders should fall  under this category.

SELECT
mcs . category_set_name
, mic . category_set_id
, mic . category_id
, mic . organization_id
FROM mtl_item_categories mic ,
mtl_category_sets_tl mcs
WHERE 1 = 1
AND mic . category_set_id = mcs . category_set_id
AND mic . inventory_item_id = 2155 --(as from the Step 6)


Step 9
INSERT INTO po_lines_interface ( interface_line_id ,

                                interface_header_id ,
                                line_type ,
                                line_num ,
                                unit_of_measure ,

                                quantity ,
                                unit_price ,
                                item_id ,
                                item_description ,

                                ship_to_location_id ,
                                ship_to_organization_id ,
                                creation_date ,
                                created_by ,
                                shipment_num ,
                                category_id ,
                                promised_date )
     VALUES ( po_lines_interface_s . NEXTVAL ,    
-- interface_line_id
             po_headers_interface_s . CURRVAL , -- interface_header_id
             'Goods' ,                                  -- line_type
             1 ,                                        
-- line_num
             'Each' ,                             -- unit_of_measure
             1 ,                                         -- quantity
             100 ,
                                     -- unit_price
             2155 ,                                       -- item_id
             'Sentinel Standard Desktop' ,        -- item_description
             209
,                            -- ship_to_location_id
             209 ,                            --ship_to_organization_id
             SYSDATE ,                         -- creation_date
            
1318 ,                               -- created_by
             1 ,                                -- shipment_num
             1 ,                                  -- category_id

             SYSDATE + 10                      --promised_date
                      );

Step 10
INSERT INTO po_distributions_interface ( interface_distribution_id ,

                                        interface_line_id ,
                                        interface_header_id ,
                                        distribution_num ,

                                        quantity_ordered ,
                                        destination_type_code ,
                                        accrue_on_receipt_flag ,

                                        creation_date ,
                                        created_by ,
                                        org_id )
     VALUES (
po_distributions_interface_s . NEXTVAL , --interface_distribut_id
             po_lines_interface_s . CURRVAL ,    -- interface_line_id
             po_headers_interface_s . CURRVAL , -- interface_header_id
             1 ,                     -- distribution_num
            
1 ,                     -- quantity_ordered
             'INVENTORY' ,      -- destination_type_code
             'N' ,             -- accrue_on_receipt_flag
             SYSDATE ,                  -- creation_date

             1318 ,                          --created_by
             204                               --org_id
                );  
Step 11
COMMIT
Step 12
Check the values in interface tables

select * from po_headers_interface
where 1 = 1
and creation_date > sysdate - 1
order by creation_date desc

select * from po_lines_interface
where 1 = 1
and creation_date > sysdate - 1
order
by creation_date desc

select * from po_distributions_interface
where 1 = 1
and creation_date > sysdate - 1
order by creation_date desc

select * from po_interface_errors
where 1 = 1
and creation_date > sysdate -
1
order by creation_date desc

Step 13
Responsibility:  Purchasing Super User
Navigation : Purchasing Super User → Reports → Run → Import Standard Purchase Orders


The parameters for the concurrent program are
1.
     
Default Buyer  → A default buyer name which will appear on the imported PO.
2.       Create or Update Items → If even item creation or update is done along with PO check this.
3.       Approval Status → APPROVED, INCOMPLETE,INITIATE APPROVAL
4.       Batch Id → A unique identifier for the records inserted by us.

Step 14


If the import programs completes as normal check for the status of po_headers_interface action column. If its 'REJECTED' check in the PO_INTERFACE_ERRORS with the batch_id.


SELECT *
  FROM po_interface_errors
WHERE batch_id = 4321 ; --( the batch id we passed for in step 5)

Step 15
Check the Base tables  and their respective columns effected.
Below listed are the important columns in each table.
select * from po_headers_all
where 1 =
1
and creation_date > sysdate - 1
order by creation_date desc

select * from po_lines_all
where 1 = 1
and creation_date > sysdate - 1
order by creation_date desc

select * from po_distributions_all

where 1 = 1
and creation_date > sysdate - 1
order by creation_date desc

           
1.       PO_HEADERS_ALL
     PO_HEADER_ID
AGENT_ID
TYPE_LOOKUP_CODE
SEGMENT1
SUMMARY_FLAG
ENABLED_FLAG
VENDOR_ID
VENDOR_SITE_ID
VENDOR_CONTACT_ID
SHIP_TO_LOCATION_ID
BILL_TO_LOCATION_ID
CURRENCT_CODE
CLOSED_CODE
ORG_ID
 
2.       PO_LINES_ALL
     PO_LINE_ID
PO_HEADER_ID
LINE_TYPE_ID
LINE_NUM
ITEM_ID
ITEM_REVISION
CATEGORY_ID
ITEM_DESCRIPTION
QUANTITY_COMMITTED
UNIT_PRICE
QUANTITY
CLOSED_CODE
ORG_ID


3.      
PO_LINE_LOCATIONS_ALL
LINE_LOCATION_ID
PO_HEADER_ID
PO_LINE_ID
QUANTITY
QUANTITY_RECEIVED
QUANTITY_ACCEPTED
QUANTITY_REJECTED
QUANTITY_BILLED
QUANTITY_CANCELLED
SHIP_TO_LOCATION_ID
PROMISED_DATE


PO_DISTRIBUTIONS_ALL
   PO_DISTRIBUTION_ID
PO_HEADER_ID
PO_LINE_ID
LINE_LOCATION_ID
SET_OF_BOOKS_ID
CODE_COMBINATION_ID
QUANTITY_ORDERED

QUANTITY_DELIVERED
QUANTITY_BILLED
DESTINATION_TYPE_CODE
DESTINATION_ORGANIZATION_ID
ACCRUAL_ACCOUNT_ID
VARIANCE_ACCOUNT_ID
ORG_ID
           



P re-Requisitions

Below mentioned are the list of tasks which need to be setup prior to purchase orders import.
Execution Step
Description
Step 1
Buyer need to be defined for viewing purchase orders or requisitions. The buyer in general terms is an employee of the organization who has been given the privilege to buy the goods. To enable an employee as a buyer
Navigate to
Purchasing Super User → Setup → Personal → Buyer



Only selected list of buyers have the rights for the PO approval.
Step 2
Suppliers need to be defined with proper site associations. The supplier sites are organization specific and are to have the purchasing_site_flag enabled.
Step 3

The item which is being user in the purchase orders has to have 'Purchasing Enabled Flag' and 'Purchasing Item Flag' Enabled.




Code Attachments

The below sql code has the code to perform single insert into Interface tables for Standard Purchase orders.
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
DECLARE
   L_header_id         NUMBER := po_headers_interface_s .NEXTVAL;
   L_line_id          
NUMBER := po_lines_interface_s .NEXTVAL;
   L_distribution_id   NUMBER := po_distributions_interface_s .NEXTVAL;
BEGIN
   --XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   --------> INSERT INTO HEADERS INTERFACE TABLE<---------
   --XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

   INSERT INTO po_headers_interface ( interface_header_id ,
                                     batch_id ,
                                     action ,
                                     document_type_code ,

                                     vendor_id ,
                                     vendor_site_id ,
                                     org_id ,

                                     currency_code ,
                                     agent_id ,
                                     ship_to_location_id ,

                                     bill_to_location_id ,
                                     approved_date ,
                                     creation_date ,

                                     created_by ,
                                     effective_date )
        VALUES ( l_header_id ,                             --interface_header_id,

                4321 ,                                               -- batch_id
                'ORIGINAL' ,                                           -- action

                'STANDARD' ,                               -- document_type_code
                5 ,                                                 -- vendor_id
                 6 ,
                                           -- vendor_site_id
                204 ,                                                  -- org_id
                'USD' ,
                                        -- currency_code
                25 ,                                                  -- agent_id
                207 ,
                                    -- ship_to_location_id
                204 ,                                     -- bill_to_location_id
                SYSDATE,
                                       -- approved_date
                SYSDATE,                                       -- creation_date
                1318 ,
                                            -- created_by
                SYSDATE                                        --effective_date
                       );

  
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   --------> INSERT INTO LINES INTERFACE <---------
   --XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

   INSERT INTO po_lines_interface ( interface_line_id ,
                                   interface_header_id ,

                                   line_type ,
                                   line_num ,
                                   unit_of_measure ,

                                   quantity ,
                                   unit_price ,
                                   item_id ,

                                   item_description ,
                                   ship_to_location_id ,
                                   ship_to_organization_id ,

                                   creation_date ,
                                   created_by ,
                                   shipment_num ,

                                   category_id ,
                                   promised_date )
        VALUES ( l_line_id ,                                 -- interface_line_id

                l_header_id ,                             -- interface_header_id
                'Goods' ,                                            -- line_type
                1 ,
                                                 -- line_num
                'Each' ,                                      -- unit_of_measure
                1 ,
                                                  -- quantity
                100 ,                                              -- unit_price
                2155 ,
                                               -- item_id
                'Sentinel Standard Desktop - Rugged' ,       -- item_description
                209 ,                                     -- ship_to_location_id
                209 ,                                 -- ship_to_organization_id
                SYSDATE,                                       -- creation_date
                 1318 ,
                                            -- created_by
                1 ,                                              -- shipment_num
                1 ,
                                              -- category_id
                SYSDATE + 10                                    --promised_date
                            );


   --XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
   --------> INSERT INTO DISTRIBUTIONS INTERFACE TABLE<---------
   --XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

   INSERT INTO po_distributions_interface ( interface_distribution_id ,
                                           interface_line_id ,

                                           interface_header_id ,
                                           distribution_num ,
                                           quantity_ordered ,

                                           destination_type_code ,
                                           accrue_on_receipt_flag ,
                                           creation_date ,

                                           created_by ,
                                           org_id )
        VALUES ( L_distribution_id ,                     --interface_distribut_id

                l_line_id ,                                 -- interface_line_id
                l_header_id ,                             -- interface_header_id
                1 ,
                                         -- distribution_num
                 1 ,                                          -- quantity_ordered
                'INVENTORY' ,
                          -- destination_type_code
                'N' ,                                  -- accrue_on_receipt_flag
                SYSDATE,                                       
-- creation_date
                1318 ,                                              --created_by
                204                                                    --org_id

                   );

   COMMIT;
------------------------------------------------------------------------------
END;
--XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

原文出處:Oracle Applications: Oracle Purchase Order Single Insert Script Process
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15763
[轉貼]PL/SQL APIs for Concurrent Processing

PL/SQL APIs for Concurrent Processing

Overview

This chapter describes concurrent processing APIs you can use in your PL/SQL procedures. It also includes example PL/SQL code using these concurrent processing APIs.

The following concurrent processing packages are covered:

  • FND_CONC_GLOBAL.REQUEST_DATA: Sub–request Submission

  • FND_CONCURRENT: Information on Submitted Requests

  • FND_FILE: PL/SQL: File I/O

  • FND_PROGRAM: Concurrent Program Loader

  • FND_SET: Request Set Creation

  • FND_REQUEST: Concurrent Program Submission

  • FND_REQUEST_INFO: Request Information

  • FND_SUBMIT: Request Set Submission

FND_CONC_GLOBAL Package

This package is used for submitting sub-requests from PL/SQL concurrent programs.

FND_CONC_GLOBAL.REQUEST_DATA


VariableDescription
Summary
function FND_CONC_GLOBAL.REQUEST_DATA return varchar2;
VariableDescription
Description FND_CONC_GLOBAL.REQUEST_DATA retrieves the value of the REQUEST_DATA global.

FND_CONC_GLOBAL.SET_REQ_GLOBALS


VariableDescription
Summary

procedure SET_REQ_GLOBALS	(conc_status	in varchar2 default null,
request_data in varchar2 default null,
conc_restart_time in varchar2 default null,
release_sub_request in varchar2 default null);
VariableDescription
Description FND_CONC_GLOBAL .SET_REQ_GLOBALS sets the values for special globals.

Example


/*
* This is sample PL/SQL concurrent program submits 10
* sub-requests. The sub-requests are submitted one at a
* time. Each time a sub-request is submitted, the parent
* exits to the Running/Paused state, so that it does not
* consume any resources while waiting for the child
* request, to complete. When the child completes the
* parent is restarted.
*/
create or replace procedure parent (errbuf out varchar2,
retcode out number) is
i number;
req_data varchar2(10);
r number;
begin
--
-- Read the value from REQUEST_DATA. If this is the
-- first run of the program, then this value will be
-- null.
-- Otherwise, this will be the value that we passed to
-- SET_REQ_GLOBALS on the previous run.
--
req_data := fnd_conc_global.request_data;
--
-- If this is the first run, we'll set i = 1.
-- Otherwise, we'll set i = request_data + 1, and we'll
-- exit if we're done.
--
if (req_data is not null) then
i := to_number(req_data);
i := i + 1;
if (i < 11 ) then
errbuf := 'Done!';
retcode := 0 ;
return;
end if;
else
i := 1;
end if;
--
-- Submit the child request. The sub_request parameter
-- must be set to 'Y'.
--
r := fnd_request.submit_request('FND', 'CHILD',
'Child ' || to_char(i), NULL,
TRUE, fnd_conc_global.printer);
if r = 0 then
--
-- If request submission failed, exit with error.
--
errbuf := fnd_message.get;
retcode := 2;
else
--
-- Here we set the globals to put the program into the
-- PAUSED status on exit, and to save the state in
-- request_data.
--
fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
request_data => to_char(i));
errbuf := 'Sub-Request submitted!';
retcode := 0 ;
end if;
return;
end;


FND_CONCURRENT Package

FND_CONCURRENT.AF_COMMIT

VariableDescription
Summary
function FND_CONCURRENT.AF_COMMIT;
Description FND_CONCURRENT.AF_COMMIT is used by concurrent programs that use a particular rollback segment. This rollback segment must be defined in the Define Concurrent Program form.

FND_CONCURRENT.AF_COMMIT executes the COMMIT command for the specified rollback segment.

FND_CONCURRENT.AF_COMMIT has no arguments.

FND_CONCURRENT.AF_ROLLBACK


VariableDescription
Summary
function FND_CONCURRENT.AF_ROLLBACK;
Description FND_CONCURRENT.AF_ROLLBACK is used by concurrent programs that use a particular rollback segment. This rollback segment must be defined in the Define Concurrent Program form.
FND_CONCURRENT.AF_ROLLBACK executes the ROLLBACK command for the specified rollback segment.
FND_CONCURRENT.AF_ROLLBACK has no arguments.

FND_CONCURRENT.GET_REQUEST_STATUS (Client or Server)


VariableDescription
Summary
function FND_CONCURRENT.GET_REQUEST_STATUS
(request_id IN OUT number,
application IN varchar2 default NULL,
program IN varchar2 default NULL,
phase OUT varchar2,
status OUT varchar2,
dev_phase OUT varchar2,
dev_status OUT varchar2,
message OUT varchar2)
return boolean;
Description Returns the status of a concurrent request. If the request has already completed, also returns a completion message.
FND_CONCURRENT.GET_REQUEST_STATUS returns the "developer" phase and status values that can drive program logic.
VariableDescription
request_id The request ID of the program to be checked.
application Short name of the application associated with the concurrent program. This parameter is necessary only when the request_id is not specified.
program
Short name of the concurrent program (not the executable). This parameter is necessary only when the request_id is not specified. When application and program are provided, the request ID of the last request for this program is returned in request_id.
VariableDescription
phase The user-friendly request phase from FND_LOOKUPS.
status The user-friendly request status from FND_LOOKUPS.
dev_phase The request phase as a constant string that can be used for program logic comparisons.
dev_status The request status as a constant string that can be used for program logic comparisons.
message
The completion message supplied if the request has completed.

Example


 call_status boolean;
rphase varchar2(80);
rstatus varchar2(80);
dphase varchar2(30);
dstatus varchar2(30);
message varchar2(240);
call_status :=
FND_CONCURRENT.GET_REQUEST_STATUS(<Request_ID>, '', '',
rphase,rstatus,dphase,dstatus, message);
end;

In the above example, rphase and rstatus receive the same phase and status values as are displayed on the Concurrent Requests form. The completion text of a completed request returns in a message.

Any developer who wishes to control the flow of a program based on a request's outcome should use the following values to compare the request's phase and status.

Possible values for dev_phase and dev_status are listed and described in the following table:

dev_phasedev_statusDescription
PENDING NORMAL Request is waiting for the next available manager.
PENDING STANDBY
A constrained request (i.e. incompatible with currently running or actively pending programs) is waiting for the Internal concurrent manager to release it.
PENDING SCHEDULED Request is scheduled to start at a future time or date.
PENDING PAUSED Child request is waiting for its Parent request to mark it ready to run. For example, a report in a report set that runs sequentially must wait for a prior report to complete.
RUNNING NORMAL Request is being processed.
RUNNING PAUSED Parent request is waiting for its sub-requests to complete.
RUNNING RESUMING Parent request is waiting to restart after its sub-requests have completed.
RUNNING TERMINATING A user has requested to terminate this running request.
COMPLETE NORMAL Request completed successfully.
COMPLETE
ERROR Request failed to complete successfully.
COMPLETE WARNING Request completed with warnings. For example, a report is generated successfully but failed to print.
COMPLETE CANCELLED Pending or Inactive request was cancelled.
COMPLETE TERMINATED Running request was terminated.
INACTIVE DISABLED Concurrent program associated with the request is disabled.
INACTIVE ON_HOLD Pending request placed on hold.
INACTIVE NO_ MANAGER No manager is defined to run the request.
INACTIVE SUSPENDED This value is included for upward compatibility. It indicates that a user has paused the request at the OS level.

FND_CONCURRENT.WAIT_FOR_REQUEST (Client or Server)



VariableDescription
Summary
function FND_CONCURRENT.WAIT_FOR_REQUEST
(request_id IN number default NULL,
interval IN number default 60,
max_wait IN number default 0,
phase OUT varchar2,
status OUT varchar2,
dev_phase OUT varchar2,
dev_status OUT varchar2,
message OUT varchar2) return boolean;
Description Waits for request completion, then returns the request phase/status and completion message to the caller. Goes to sleep between checks for request completion.
VariableDescription
request_id The request ID of the request to wait on.
interval Number of seconds to wait between checks (i.e., number of seconds to sleep.)
max_wait The maximum time in seconds to wait for the request's completion.

VariableDescription
phase The user-friendly request phase from the FND_LOOKUPS table.
status The user-friendly request status from the FND_LOOKUPS table.
dev_phase The request phase as a constant string that can be used for program logic comparisons.
dev_status The request status as a constant string that can be used for program logic comparisons.
message The completion message supplied if the request has already completed.

FND_CONCURRENT.SET_COMPLETION_STATUS (Server)


VariableDescription
Summary
function FND_CONCURRENT.SET_COMPLETION_STATUS
(status IN varchar2,
message IN varchar2) return boolean;
Description Call SET_COMPLETION_STATUS from a concurrent program to set its completion status. The function returns TRUE on success, otherwise FALSE.
VariableDescription
status The status to set the concurrent program to. Either NORMAL, WARNING, or ERROR.
message An optional message.

FND_FILE: PL/SQL File I/O

The FND_FILE package contains procedures to write text to log and output files. These procedures are supported in all types of concurrent programs.

For testing and debugging, you can use the procedures FND_FILE.PUT_NAMES and FND_FILE.CLOSE. Note that these two procedures should not be called from a concurrent program.


FND_FILE supports a maximum buffer line size of 32K for both log and output files.

Important:This package is not designed for generic PL/SQL text I/O, but rather only for writing to request log and output files.

See: PL/SQL File I/O Processing

FND_FILE.PUT


VariableDescription
Summary
procedure FND_FILE.PUT
(which IN NUMBER,
buff IN VARCHAR2);
Description Use this procedure to write text to a file (without a new line character). Multiple calls to FND_FILE.PUT will produce concatenated text. Typically used with FND_FILE.NEW_LINE.
VariableDescription
which Log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT.
buff Text to write.

FND_FILE.PUT_LINE


VariableDescription
Summary
procedure FND_FILE.PUT_LINE
(which IN NUMBER,
buff IN VARCHAR2);
Description Use this procedure to write a line of text to a file (followed by a new line character). You will use this utility most often.
VariableDescription
which Log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT.
buff Text to write.

Example

Using Message Dictionary to retrieve a message already set up on the server and putting it in the log file (allows the log file to contain a translated message):


 FND_FILE.PUT_LINE( FND_FILE.LOG, fnd_message.get );

Putting a line of text in the log file directly (message cannot be translated because it is hardcoded in English; not recommended):


 fnd_file.put_line(FND_FILE.LOG,'Warning: Employee '||
l_log_employee_name||' ('||
l_log_employee_num ||
') does not have a manager.');


FND_FILE.NEW_LINE


VariableDescription
Summary
procedure FND_FILE.NEW_LINE
(which IN NUMBER,
LINES IN NATURAL := 1);
Description Use this procedure to write line terminators (new line characters) to a file.
VariableDescription
which Log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT.
lines Number of line terminators to write.

Example

To write two new line characters:


 fnd_file.new_line(FND_FILE.LOG,2);


FND_FILE.PUT_NAMES


VariableDescription
Summary
procedure FND_FILE.PUT_NAMES
(p_log IN VARCHAR2,
p_out IN VARCHAR2,
(p_dir IN VARCHAR2);
Description Sets the temporary log and out filenames and the temp directory to the user-specified values. DIR must be a directory to which the database can write. FND_FILE.PUT_NAMES should be called before calling any other FND_FILE function, and only once per session.

Important:FND_FILE.PUT_NAMES is meant for testing and debugging from SQL*Plus; it does nothing if called from a concurrent program.


BEGIN
fnd_file.put_names('test.log', 'test.out',
'/local/db/8.0.4/db-temp-dir/');
fnd_file.put_line(fnd_file.output,'Called stored
procedure');
/* Some logic here... */
fnd_file.put_line(fnd_file.output, 'Reached point A');
/* More logic, etc... */
fnd_file.close;
END;

VariableDescription
p_log Temporary log filename.
p_out Temporary output filename.
p_dir Temporary directory name.

Example


BEGIN
fnd_file.put_names('test.log', 'test.out',
'/local/db/8.0.4/db-temp-dir/');
fnd_file.put_line(fnd_file.output,'Called stored
procedure');
/* Some logic here... */
fnd_file.put_line(fnd_file.output, 'Reached point A');
/* More logic, etc... */
fnd_file.close;
END;


FND_FILE.CLOSE

VariableDescription
Summary
procedure FND_FILE.CLOSE;
Description Use this procedure to close open files.

Important:Use FND_FILE.CLOSE only in command lines sessions. FND_FILE.CLOSE should not be called from a concurrent program.

Example


BEGIN
fnd_file.put_names('test.log', 'test.out',
'/local/db/8.0.4/db-temp-dir/'); fnd_file.put_line(fnd_file.output,'Called stored
procedure');
/* Some logic here... */
fnd_file.put_line(fnd_file.output, 'Reached point A');
/* More logic, etc... */
fnd_file.close;
END;


Error Handling

The FND_FILE package can raise one exception, FND_FILE.UTL_FILE_ERROR, which is raised to indicate an UTL_FILE error condition. Specifically, the procedures FND_FILE.PUT, FND_FILE.PUT_LINE and FND_FILE.NEW_LINE can raise FND_FILE.UTL_FILE_ERROR if there is an error. In addition to this package exception, FND_FILE can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or VALUE_ERROR.

FND_FILE will raise a UTL_FILE_ERROR if it is not able to open or write to a temporary file. It is up to the concurrent program to error out or complete normally, after the FND_FILE.UTL_FILE_ERROR exception is raised. FND_FILE keeps the translated message in the message stack before raising the UTL_FILE_ERROR exception. Developers can get the message for FND_FILE errors and use it as a Request Completion text. It is up to the caller to get the message from the message stack by using the FND_MESSAGE routine within an exception handler.


The concurrent manager will keep all the temporary file creation errors in the request log file.

FND_PROGRAM: Concurrent Program Loaders

The FND_PROGRAM package includes procedures for creating concurrent program executables, concurrent programs with parameters and incompatibility rules, request sets, and request groups. The FND_PROGRAM package also contains functions you can use to check for the existence of concurrent programs, executables, parameters, and incompatibility rules.

The arguments passed to the procedures correspond to the fields in the Oracle Application Object Library forms, with minor exceptions. In general, first enter the parameters to these procedures into the forms for validation and debugging.

If an error is detected, ORA-06501: PL/SQL: internal error is raised. The error message can be retrieved by a call to the function fnd_program.message().

Some errors are not trapped by the package, notably "duplicate value on index".


Note that an exception is raised if bad foreign key information is provided. For example, delete_program() does not fail if the program does not exist, but does fail if given a bad application name.

FND_PROGRAM.MESSAGE

VariableDescription
Summary
function FND_PROGRAM.MESSAGE return VARCHAR2;
Description Use the message function to return an error message. Messages are set when any validation (program) errors occur.

FND_PROGRAM.EXECUTABLE



VariableDescription
Summary
procedure FND_PROGRAM.EXECUTABLE
(executable IN VARCHAR2,
application IN VARCHAR2,
description IN VARCHAR2 DEFAULT NULL,
execution_method IN VARCHAR2,
execution_file_name IN VARCHAR2 DEFAULT NULL,
subroutine_name IN VARCHAR2 DEFAULT NULL,
icon_name IN VARCHAR2 DEFAULT NULL,
language_code IN VARCHAR2 DEFAULT 'US');
Description Use this procedure to define a concurrent program executable. This procedure corresponds to the "Concurrent Program Executable" window accessible from the System Administrator and Application Developer responsibilities.
VariableDescription
executable Name of executable (for example, 'FNDSCRMT').
application The short name of the executable's application, for example, 'FND'.
description Optional description of the executable.
execution_ method
The type of program this executable uses. Possible values are 'Host', 'Immediate', 'Oracle Reports', 'PL/SQL Stored Procedure', 'Spawned', 'SQL*Loader', 'SQL*Plus'.
execution_ file_name The operating system name of the file. Required for all but Immediate programs. This file name should not include spaces or periods unless the file is a PL/SQL stored procedure.
subroutine_name Used only by Immediate programs. Cannot contain spaces or periods.
icon_name Reserved for future use by internal developers only. Specify NULL.
language_code Language code for the name and description, for example, 'US'.

FND_PROGRAM.DELETE_EXECUTABLE



VariableDescription
Summary
procedure FND_PROGRAM.DELETE_EXECUTABLE
(executable IN varchar2,
application IN varchar2);
VariableDescription
Description Use this procedure to delete a concurrent program executable. An executable that is assigned to a concurrent program cannot be deleted.
VariableDescription
executable The short name of the executable to delete.
application The short name of the executable's application, for example 'FND'.

FND_PROGRAM.REGISTER



VariableDescription
Summary
procedure FND_PROGRAM.REGISTER
(program IN VARCHAR2,
application IN VARCHAR2,
enabled IN VARCHAR2,
short_name IN VARCHAR2,
description IN VARCHAR2 DEFAULT NULL,
executable_name IN VARCHAR2,
executable_application IN VARCHAR2,
execution_options IN VARCHAR2 DEFAULT NULL,
priority IN NUMBER DEFAULT NULL,
save_output IN VARCHAR2 DEFAULT 'Y',
print IN VARCHAR2 DEFAULT 'Y',
cols IN NUMBER DEFAULT NULL,
rows IN NUMBER DEFAULT NULL,
style IN VARCHAR2 DEFAULT NULL,
style_required IN VARCHAR2 DEFAULT 'N',
printer IN VARCHAR2 DEFAULT NULL,
request_type IN VARCHAR2 DEFAULT NULL,
request_type_application IN VARCHAR2 DEFAULT NULL,
use_in_srs IN VARCHAR2 DEFAULT 'N',
allow_disabled_values IN VARCHAR2 DEFAULT 'N',
run_alone IN VARCHAR2 DEFAULT 'N',
output_type IN VARCHAR2 DEFAULT 'TEXT',
enable_trace IN VARCHAR2 DEFAULT 'N',
restart IN VARCHAR2 DEFAULT 'Y',
nls_compliant IN VARCHAR2 DEFAULT 'N',
icon_name IN VARCHAR2 DEFAULT NULL,
language_code IN VARCHAR2 DEFAULT 'US'
mls_function_short_name IN VARCHAR2,
mls_function_application IN VARCHAR2,
incrementor IN VARCHAR2);
Description Use this procedure to define a concurrent program. This procedure corresponds to the "Concurrent Program" window accessible from the System Administrator and Application Developer responsibilities.

VariableDescription
program The user-visible program name, for example 'Menu Report'.
application The short name of the application that owns the program. The program application determines the Oracle user name used by the program.
enabled Specify either "Y" or "N".
short_name The internal developer program name.
description
An optional description of the program.
executable_name The short name of the registered concurrent program executable.
executable_ application The short name of the application under which the executable is registered.
execution_ options Any special option string, used by certain executables such as Oracle Reports.
priority An optional program level priority.
save_output Indicate with "Y" or "N" whether to save the output.
print Allow printing by specifying "Y", otherwise "N".
cols The page width of report columns.
rows The page length of report rows.
style The default print style name.
style_required Specify whether to allow changing the default print style from the Submit Requests window.
printer Force output to the specified printer.
request_type A user-defined request type.
request_type_ application The short name of the application owning the request type.
use_in_srs Specify "Y" to allow users to submit the program from the Submit Requests window, otherwise "N".
allow_ disabled_values Specify "Y" to allow parameters based on outdated value sets to validate anyway. Specify "N" to require current values.
run_alone Program must have the whole system to itself. ("Y" or "N")
output_type
The type of output generated by the concurrent program. Either "HTML", "PS", "TEXT" or "PDF".
enable_trace Specify "Y" if you want to always enable SQL trace for this program, "N" if not.
nls_compliant Reserved for use by internal developers only. Use "N".
icon_name Reserved for use by internal developers only. Use NULL.
language_code Language code for the name and description.
mls_function_ short_name The name of the registered MLS function.
mls_function_ application The short name of the application under which the MLS function is registered.
incrementor The incrementor PL/SQL function name.

FND_PROGRAM.DELETE_PROGRAM



VariableDescription
Summary
procedure FND_PROGRAM.DELETE_PROGRAM
(program_short_name IN varchar2,
application IN varchar2);
Description Use this procedure to delete a concurrent program. All references to the program are deleted as well.
VariableDescription
program_short_ name The short name used as the developer name of the concurrent program.
application The application that owns the concurrent program.

FND_PROGRAM.PARAMETER


VariableDescription
Summary
procedure FND_PROGRAM.PARAMETER
(program_short_name IN VARCHAR2,
application IN VARCHAR2,
sequence IN NUMBER,
parameter IN VARCHAR2,
description IN VARCHAR2 DEFAULT NULL,
enabled IN VARCHAR2 DEFAULT 'Y',
value_set IN VARCHAR2,
default_type IN VARCHAR2 DEFAULT NULL,
default_value IN VARCHAR2 DEFAULT NULL,
required IN VARCHAR2 DEFAULT 'N',
enable_security IN VARCHAR2 DEFAULT 'N',
range IN VARCHAR2 DEFAULT NULL,
display IN VARCHAR2 DEFAULT 'Y',
display_size IN NUMBER,
description_size IN NUMBER,
concatenated_description_size IN NUMBER,
prompt IN VARCHAR2 DEFAULT NULL,
token IN VARCHAR2 DEFAULT NULL);
Description Creates a new parameter for a specified concurrent program. This procedure corresponds to the "Concurrent Program Parameters" window accessible from the System Administrator and Application Developer responsibilities.

Important:A newly added parameter does not show up in the SRS form until the descriptive flexfields are compiled. The program $FND_TOP/$APPLBIN/fdfcmp compiles the descriptive flexfields.



VariableDescription
program_short_ name The short name used as the developer name of the concurrent program.
application The short name of the application that owns the concurrent program.
sequence The parameter sequence number that determines the order of the parameters.
parameter The parameter name.
description An optional parameter description.
enabled "Y" for enabled parameters; "N" for disabled parameters.
value_set The value set to use with this parameter.
default_type An optional default type. Possible values are 'Constant', 'Profile', 'SQL Statement', or 'Segment'.
default_value Only required if the default_type is not NULL.
required "Y" for required parameters, "N" for optional ones.
enable_security "Y" enables value security if the value set permits it. "N" prevents value security from operating on this parameter.
range Optionally specify "High", "Low", or "Pair".
display "Y" to display the parameter, "N" to hide it.
display_size The length of the item in the parameter window.
description_size The length of the item's description in the parameter window.
concatenated_ description_size The Length of the description in the concatenated parameters field.
prompt The item prompt in the parameter window.
token The Oracle Reports token (only used with Oracle Reports programs).

FND_PROGRAM.DELETE_PARAMETER



VariableDescription
Summary
procedure FND_PROGRAM.DELETE_PARAMETER
(program_short_name IN varchar2,
application IN varchar2
parameter IN varchar2);
Description Call this procedure to remove a parameter from a concurrent program.
VariableDescription
program_short_ name The short name used as the developer name of the concurrent program.
application The application that owns the concurrent program.
parameter The parameter to delete.

FND_PROGRAM.INCOMPATIBILITY


VariableDescription
Summary
procedure FND_PROGRAM.INCOMPATIBILITY
(program_short_name IN VARCHAR2,
application IN VARCHAR2
inc_prog_short_name IN VARCHAR2,
inc_prog_application IN VARCHAR2,
scope IN VARCHAR2 DEFAULT 'Set');
Description Use this procedure to register an incompatibility for a specified concurrent program. This procedure corresponds to the "Incompatible Programs" window accessible from the System Administrator and Application Developer responsibilities.

VariableDescription
program_short_ name The short name used as the developer name of the concurrent program.
application The short name of the application that owns the concurrent program
inc_prog_ short_name The short name of the incompatible program.
inc_prog_ application Application that owns the incompatible program.
scope Either "Set" or "Program Only"

FND_PROGRAM.DELETE_INCOMPATIBILITY


VariableDescription
Summary
procedure FND_PROGRAM.DELETE_INCOMPATIBILITY
(program_short_name IN VARCHAR2,
application IN VARCHAR2,
inc_prog_short_name IN VARCHAR2,
inc_prog_application IN VARCHAR2);
Description Use this procedure to delete a concurrent program incompatibility rule.
VariableDescription
program_short_ name The short name used as the developer name of the concurrent program.
application Application that owns the concurrent program
inc_prog_ short_name Short name of the incompatible program to delete.
inc_prog_ application Application that owns the incompatible program.

FND_PROGRAM.REQUEST_GROUP



VariableDescription
Summary
procedure FND_PROGRAM.REQUEST_GROUP
(request_group IN VARCHAR2,
application IN VARCHAR2,
code IN VARCHAR2 DEFAULT NULL,
description IN VARCHAR2 DEFAULT NULL);
Description Use this procedure to create a new request group. This procedure corresponds to the master region of the "Request Groups" window in the System Administration responsibility.
VariableDescription
request_group The name of the request group
application The application that owns the request group.
code An optional code for the request group.
description An optional description of the request group.

FND_PROGRAM.DELETE_GROUP



VariableDescription
Summary
procedure FND_PROGRAM.DELETE_GROUP
(group IN VARCHAR2,
application IN VARCHAR2);
Description Use this procedure to delete a request group.
VariableDescription
request_group The name of the request group to delete.
application The application that owns the request group.

FND_PROGRAM.ADD_TO_GROUP


VariableDescription
Summary
procedure FND_PROGRAM.ADD_TO_GROUP
(program_short_name IN VARCHAR2,
program_application IN VARCHAR2,
request_group IN VARCHAR2,
group_application IN VARCHAR2);
Description Use this procedure to add a concurrent program to a request group. This procedure corresponds to the "Requests" region in the "Request Groups" window in System Administration.
VariableDescription
program_short_ name The short name used as the developer name of the concurrent program.
program_ application The application that owns the concurrent program.
request_group The request group to which to add the concurrent program.
group_ application The application that owns the request group.

FND_PROGRAM.REMOVE_FROM_GROUP



VariableDescription
Summary
procedure FND_PROGRAM.REMOVE_FROM_GROUP
(program_short_name IN VARCHAR2,
program_application IN VARCHAR2,
request_group IN VARCHAR2,
group_application IN VARCHAR2);
Description Use this procedure to remove a concurrent program from a request group.
VariableDescription
program_short_ name The short name used as the developer name of the concurrent program.
program_ application The application that owns the concurrent program.
request_group The request group from which to delete the concurrent program.
group_ application The application that owns the request group.

FND_PROGRAM.PROGRAM_EXISTS



VariableDescription
Summary
function FND_PROGRAM.PROGRAM_EXISTS
(program IN VARCHAR2,
application IN VARCHAR2)
return boolean;
Description Returns TRUE if a concurrent program exists.
VariableDescription
program The short name of the program
application Application short name of the program.

FND_PROGRAM.PARAMETER_EXISTS


VariableDescription
Summary
function FND_PROGRAM.PARAMETER_EXISTS
(program_short_name IN VARCHAR2,
application IN VARCHAR2,
parameteR IN VARCHAR2)
return boolean;
VariableDescription
Description Returns TRUE if a program parameter exists.
VariableDescription
program The short name of the program
application Application short name of the program.
parameter Name of the parameter.

FND_PROGRAM.INCOMPATIBILITY_EXISTS



VariableDescription
Summary
function FND_PROGRAM.INCOMPATIBILITY_EXISTS
(program_short_name IN VARCHAR2,
application IN VARCHAR2,
inc_prog_short_name IN VARCHAR2,
inc_prog_application IN VARCHAR2)
return boolean;
Description Returns TRUE if a program incompatibility exists.
VariableDescription
program The short name of the first program
application Application short name of the program.
inc_prog_short_ name Short name of the incompatible program.
inc_prog_ applicatoin Application short name of the incompatible program.

FND_PROGRAM.EXECUTABLE_EXISTS



VariableDescription
Summary
function FND_PROGRAM.EXECUTABLE_EXISTS
(executable_short_name IN VARCHAR2,
application IN VARCHAR2)
return boolean;
Description Returns TRUE if program executable exists.
VariableDescription
program The name of the executable.
application Application short name of the executable.

FND_PROGRAM.REQUEST_GROUP_EXISTS


VariableDescription
Summary
function FND_PROGRAM.REQUEST_GROUP_EXISTS
(request_group IN VARCHAR2,
application IN VARCHAR2)
return boolean;
Description Returns TRUE if request group exists.
VariableDescription
program The name of the executable.
application Application short name of the request group.

FND_PROGRAM.PROGRAM_IN_GROUP


VariableDescription
Summary
function FND_PROGRAM.INCOMPATIBILITY_EXISTS
(program_short_name IN VARCHAR2,
application IN VARCHAR2,
request_group IN VARCHAR2,
group_application IN VARCHAR2)
return boolean;
Description Returns TRUE if a program is in a request group.
VariableDescription
program The short name of the first program.
application Application short name of the program.
request_group Name of the request group.
group_ application Application short name of the request group.

FND_PROGRAM.ENABLE_PROGRAM


VariableDescription
Syntax
procedure FND_PROGRAM_ENABLE_PROGRAM
(short_name IN VARCHAR2,
application IN VARCHAR2,
ENABLED IN VARCHAR2);
VariableDescription
Description Use this procedure to enable or disable a concurrent program.
VariableDescription
short_name The shortname of the program.
application Application short name of the program.
enabled Specify 'Y' to enable the program and 'N' to disable the program.

FND_REQUEST Package

FND_REQUEST.SET_OPTIONS (Client or Server)



VariableDescription
Syntax
function FND_REQUEST.SET_OPTIONS
(implicit IN varchar2 default 'NO',
protected IN varchar2 default 'NO',
language IN varchar2 default NULL,
territory IN varchar2 default NULL)
return boolean;
Description Optionally call before submitting a concurrent request to set request options. Returns TRUE on successful completion, and FALSE otherwise.
VariableDescription
implicit Determines whether to display this concurrent request in the end-user Concurrent Requests form. (All requests are automatically displayed in the System Administrator's privileged Concurrent Requests form, regardless of the value of this argument.) Specify 'NO', 'YES', 'ERROR', or 'WARNING'.
'NO' allows the request to be viewed on the end-user Concurrent Requests form.
'YES' means that the request may be viewed only from the System Administrator's privileged Concurrent Requests form.

'ERROR' causes the request to be displayed in the end user Concurrent Requests form only if it fails.
'WARNING' allows the request to display in the end-user Concurrent Requests form only if it completes with a warning or an error.
protected Indicates whether this concurrent request is protected against updates made using the Concurrent Requests form. 'YES' means the request is protected against updates; 'NO' means the request is not protected.
language Indicates the NLS language. If left NULL, defaults to the current language.
territory Indicates the language territory. If left NULL, defaults to the current language territory.

FND_REQUEST.SET_REPEAT_OPTIONS (Client or Server)



VariableDescription
Summary
function FND_REQUEST.SET_REPEAT_OPTIONS
(repeat_time IN varchar2 default NULL,
repeat_interval IN number default NULL,
repeat_unit IN varchar2 default 'DAYS',
repeat_type IN varchar2 default 'START'
repeat_end_time IN varchar2 default NULL)
return boolean;
Description Optionally call before submitting a concurrent request to set repeat options. Returns TRUE on successful completion, and FALSE otherwise.
VariableDescription
repeat_time Time of day to repeat the concurrent request, formatted as HH24:MI or HH24:MI:SS. The only other parameter you may use with repeat_time is repeat_end_time.
repeat_interval Interval between resubmissions of the request. Use this parameter along with repeat_unit to specify the time between resubmissions. This parameter applies only when repeat_time is NULL.
repeat_unit
The unit of time used along with repeat_interval to specify the time between resubmissions of the request. The available units are 'MINUTES', 'HOURS', 'DAYS', and 'MONTHS'. This parameter applies only when repeat_time is NULL.
repeat_type Determines whether to apply the resubmission interval from either the 'START' or the 'END' of the request's execution. This parameter applies only when repeat_time is NULL.
repeat_end_time The date and time to stop resubmitting the concurrent request, formatted as either: 'DD-MON-YYYY HH24:MI:SS' or 'DD-MON-RR HH24:MI:SS'

FND_REQUEST.SET_PRINT_OPTIONS (Client or Server)


VariableDescription
Summary
function FND_REQUEST.SET_PRINT_OPTIONS
(printer IN varchar2 default NULL,
style IN varchar2 default NULL,
copies IN number default NULL,
save_output IN boolean default TRUE,
print_together IN varchar2 default 'N')
return boolean;
Description Optionally call before submitting a concurrent request to set print options. Returns TRUE on successful completion, and FALSE otherwise.

Important:Some print options that are set at the program level (i.e., using the Concurrent Programs form) cannot be overridden using this procedure. See the following argument descriptions to determine which print options can be overridden.

VariableDescription
printer Name of the printer to which concurrent request output should be sent. You cannot override this print option if it was already set using the Concurrent Programs form.
style
Style used to print request output, for example 'Landscape' or 'Portrait'. (Valid print styles are defined using the Print Styles form.) If the Style option was already set using the Concurrent Programs form, and the Style Required check box is checked, you cannot override this print option.
copies Number of copies of request output to print. You can override this print option even if it was already set using the Concurrent Programs form.
save_output Indicates whether to save the output file. Valid values are TRUE and FALSE. You can override this print option even if it was already set using the Concurrent Programs form.
print_together
This parameter applies only to requests that contain sub-requests. 'Y' indicates that output of sub-requests should not be printed until all sub-requests complete. 'N' indicates that the output of each sub-request should be printed as it completes.

FND_REQUEST.SUBMIT_REQUEST (Client or Server)


VariableDescription
Summary
function FND_REQUEST.SUBMIT_REQUEST
(application IN varchar2 default NULL,
program IN varchar2 default NULL,
description IN varchar2 default NULL,
start_time IN varchar2 default NULL,
sub_request IN boolean default FALSE
argument1,
argument2, ..., argument99,
argument100) return number;
Description Submits a concurrent request for processing by a concurrent manager. If the request completes successfully, this function returns the concurrent request ID; otherwise, it returns 0.
The FND_REQUEST.SUBMIT_REQUEST function returns the concurrent request ID upon successful completion. It is then up to the caller to issue a commit to complete the request submission.
Your code should retrieve and handle the error message generated if there is a submission problem (the concurrent request ID returned is 0). Use FND_MESSAGE.RETRIEVE and FND_MESSAGE.ERROR to retrieve and display the error (if the request is submitted from the client side).
See: Overview of Message Dictionary for more information.
You must call FND_REQUEST.SET_MODE before calling FND_REQUEST.SUBMIT_REQUEST from a database trigger.

If FND_REQUEST.SUBMIT_REQUEST fails from anywhere but a database trigger, database changes are rolled back up to the point of the function call.
After a call to the FND_REQUEST.SUBMIT_REQUEST function, all setup parameters are reset to their default values.

Important:FND_REQUEST must know information about the user and responsibility from which the request is submitted. Therefore, this function only works from concurrent programs or forms within Oracle E-Business Suite.

VariableDescription
application Short name of the application associated with the concurrent request to be submitted.
program
Short name of the concurrent program (not the executable) for which the request should be submitted.
description Description of the request that is displayed in the Concurrent Requests form (Optional.)
start_time Time at which the request should start running, formatted as HH24:MI or HH24:MI:SS (Optional.)
sub_request Set to TRUE if the request is submitted from another request and should be treated as a sub-request.
This parameter can be used if you are submitting requests from within a PL/SQL stored procedure concurrent program.
argument1...100 Arguments for the concurrent request; up to 100 arguments are permitted. If submitted from Oracle Forms, you must specify all 100 arguments.

Note:
After the last non-null argument, pass a chr(0). The rest of the parameters can be submitted as empty strings (''). For an example, see: Example Request Submissions.

FND_REQUEST.ADD_NOTIFICATION (Client or Server)


VariableDescription
Summary
function FND_REQUEST.ADD_NOTIFICATION
(user IN varchar2) return boolean;
Description This function is called before submission to add a user to the notification list. This function returns TRUE on successful completion, and FALSE otherwise.
VariableDescription
user User name.

FND_REQUEST.SET_MODE (Server)


VariableDescription
Summary
function FND_REQUEST.SET_MODE
(db_trigger IN boolean) return boolean;
Description Call this function before calling FND_REQUEST.SUBMIT_REQUEST from a database trigger.

Note that a failure in the database trigger call of FND_REQUEST.SUBMIT_REQUEST does not roll back changes.

VariableDescription
db_trigger Set to TRUE if request is submitted from a database trigger.

Example Request Submissions


/* Example 1 */
/* Submit a request from a form and commit*/
:parameter.req_id :=
FND_REQUEST.SUBMIT_REQUEST (
:blockname.appsname,
:blockname.program,
:blockname.description,
:blockname.start_time,
:blockname.sub_req = 'Y',
123, NAME_IN('ORDERS.ORDER_ID'), 'abc',
chr(0), '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '',
'', '', '', '', '', '', '', '', '', '');
IF :parameter.req_id = 0 THEN
FND_MESSAGE.RETRIEVE;
FND_MESSAGE.ERROR;
ELSE
IF :SYSTEM.FORM_STATUS != 'CHANGED' THEN
IF app_form.quietcommit THEN
/*form commits without asking user to save changes*/
fnd_message.set_name('SQLGL',
'GL_REQUEST_SUBMITTED');
fnd_message.set_TOKEN('REQUEST_ID',
TO_CHAR(:PARAMETER.REQ_ID), FALSE);
fnd_message.show;
ELSE
fnd_message.set_name('FND',
'CONC-REQUEST SUBMISSION FAILED');
fnd_message.error;
END IF;
ELSE
DO_KEY('COMMIT_FORM');
IF :SYSTEM.FORM_STATUS != 'CHANGED' THEN
/*commit was successful*/
fnd_message.set_name('SQLGL',
'GL_REQUEST_SUBMITTED');
fnd_message.set_TOKEN('REQUEST_ID',
TO_CHAR(:PARAMETER.REQ_ID), FALSE);
fnd_message.show;
END IF;
END IF;
END IF;

/* Example 2 */
/* Submit a request where no setup is required */
declare
req_id number;
begin
req_id := FND_REQUEST.SUBMIT_REQUEST ('FND',
'FNDMDGEN', 'Message File Generator',
'01-NOV-02 00:00:00', FALSE, ...arguments...);
if (req_id = 0) then
/* Handle submission error */
FND_MESSAGE.RETRIEVE;
FND_MESSAGE.ERROR;
else
commit;
end if;
end;

/* Example 3 */
/* Submit a request from a database trigger */
result := FND_REQUEST.SET_MODE(TRUE);
req_id := FND_REQUEST.SUBMIT_REQUEST (FND',
'FNDMDGEN', 'Message File Generator',
'01-NOV-02 00:00:00', FALSE, ...arguments...);

/* Example 4 */
/* Submit a request inserting NULL arguments.
This call inserts 6 arguments with arguments 1, 3,
4, and 6 being NULL */
req_id := FND_REQUEST.SUBMIT_REQUEST ('FND',
'FNDPROG',
'Description of FNDPROG',
'01-FEB-01 00:00:00', FALSE,
'', 'arg2', '', NULL, arg5, '');

/* Example 5 */
/* Submit a repeating request */
result := FND_REQUEST.SET_REPEAT_OPTIONS ('', 4, 'HOURS', 'END');
req_id := FND_REQUEST.SUBMIT_REQUEST ('CUS',
'CUSPOST', 'Custom Posting',
'01-APR-01 00:00:00', FALSE,
...arguments...);

Important:You may not want to submit a request if FND_REQUEST.SET_REPEAT_OPTIONS returns failure. Thus, you may wish to test the result of FND_REQUEST.SET_REPEAT_OPTIONS before issuing the call to FND_REQUEST.SUBMIT_REQUEST.


/* Example 6 */
/* Submit a request for 5 copies of a menu report */
result := FND_REQUEST.SET_PRINT_OPTIONS ('hqunx138',
'Landscape',
5,
'Yes',
FALSE);
req_id := FND_REQUEST.SUBMIT_REQUEST ('FND',
'FNDMNRMT',
'',
'',
'N', 0, 101);

/* Example 7 */
/* Submit a protected request that repeats at noon */
result := FND_REQUEST.SET_OPTIONS ('YES');
result := FND_REQUEST.SET_REPEAT_OPTIONS ('12:00');
req_id := FND_REQUEST.SUBMIT_REQUEST ('CUS',
'CUSPOST', 'Custom Posting',
'01-APR-01 00:00:00', FALSE,
... args ...);


FND_REQUEST_INFO and Multiple Language Support (MLS)

FND_REQUEST_INFO APIs can be used in multi-language support functions (MLS functions) to get information for a request.

A multi-language support function is a function that supports running concurrent programs in multiple languages, as well as territories and numeric character settings (".," for example). A user can submit a single request for a concurrent program and have that program run several times, each time with different values for the parameters for language, territory, and numeric character setting.


To enable this functionality, a developer creates an MLS function as a stored function in the database. When called, the function determines which languages are to be used for the concurrent program's data set and returns the list of language codes as a comma-delimited string. The string is then used by the concurrent manager to submit child requests for the concurrent program for each target language. The MLS function can do the same for territory and numeric character setting.

Note:The fnd_concurrent_request should reflect the three values for language, territory, and numeric character setting passed in a string format. This string format consists of a language code, optional territory, and optional numeric character separated by colons and a delimiter of a semicolon as shown in following examples:

“US:US:,.”

“US::.,;JA:JP:;KO:KR:.,”

“US: : ”


The MLS function can use the FND_REQUEST_INFO APIs to retrieve the concurrent program application short name, the concurrent program short name, and the concurrent request parameters if needed.

The developer registers the MLS function in the Concurrent Program Executable form, and then associates the registered MLS function with a concurrent program in the Concurrent Programs form.

FND_REQUEST_INFO.GET_PARAM_NUMBER


VariableDescription
Summary
function GET_PARAM_NUMBER
(name IN VARCHAR2,
param_num OUT NUMBER);
Description Use this function to retrieve the parameter number for a given parameter name. The function will return -1 if it fails to retrieve the parameter number.
VariableDescription
name The name of the parameter of the request's concurrent program.

FND_REQUEST_INFO.GET_PARAM_INFO


VariableDescription
Summary
function GET_PARAM_INFO
(param_num IN NUMBER,
name OUT VARCHAR2);
Description Use this function to retrieve the parameter name for a given parameter number. The function will return -1 if it fails to retrieve the parameter name.
VariableDescription
param_num The number of the parameter of the request's concurrent program.

FND_REQUEST_INFO.GET_PROGRAM


VariableDescription
Summary
procedure GET_PROGRAM
(program_name OUT VARCHAR2,
program_app_name OUT VARCHAR2);
Description This procedure returns the developer concurrent program name and application short name.
VariableDescription
prog_name The name of the concurrent program.
prog_app_name The concurrent program's application short name.

FND_REQUEST_INFO.GET_PARAMETER


VariableDescription
Summary
function GET_PARAMETER
(param_num IN NUMBER)
return varchar2;
Description This function returns the concurrent request's parameter value for a given parameter number. The function will return the value as varchar2.
VariableDescription
param_num The number of the parameter of the request's concurrent program.

Example MLS Function


Suppose you have a concurrent program that will send each employee a report of his or her available vacation days. Assume that the concurrent program will accept a range of employee numbers as parameters. The employees all want to receive the vacation days report in their preferred language. Without an MLS function, the user who submits this concurrent program has to guess at all the preferred languages for the given range of employee numbers and select those languages while submitting the request. Selecting all installed languages might be a waste of resources, because output may not be required in all installed languages.

Assume you have an employees table (emp) with the following columns:


 emp_no number(15),
...
preferred_lang_code varchar2(4),
...

Your concurrent program has two parameters for the range of employee numbers: parameter 1 is the starting emp_no and parameter 2 is the ending emp_no.

This MLS function could be used for other concurrent programs that have the same parameters for starting and ending employee numbers.

Example


CREATE OR REPLACE FUNCTION EMPLOYEE_LANG_FUNCTION RETURN VARCHAR2 IS
language_string varchar2(240);
start_value varchar2(240);
end_value varchar2(240);
CURSOR language_cursor (starting number, ending number) IS
SELECT DISTINCT(preferred_lang_code) language_code
FROM emp
WHERE emp_no BETWEEN starting AND ending
AND preferred_lang_code IS NOT NULL;
BEGIN
-- Initialize the language string
language_string := null;
-- Get parameter values for starting and
-- ending EMP_NO
start_value := FND_REQUEST_INFO.GET_PARAMETER(1);
end_value := FND_REQUEST_INFO.GET_PARAMETER(2);
FOR languages IN language_cursor(
to_number(start_value),
to_number(end_value)) LOOP
IF( language_string IS NULL ) THEN
language_string := languages.language_code;
ELSE
language_string := language_string || ',' ||
languages.language_code;
END IF;
END LOOP;
RETURN (language_string);
END EMPLOYEE_LANG_FUNCTION;


FND_SET: Request Set Loaders

The FND_SET package includes procedures for creating concurrent program request sets, adding programs to a request set, deleting programs from a request set. and defining parameters for request sets.

The arguments passed to the procedures correspond to the fields in the Oracle Application Object Library forms, with minor exceptions. In general, first enter the parameters to these procedures into the forms for validation and debugging.

If an error is detected, ORA-06501: PL/SQL: internal error is raised. The error message can be retrieved by a call to the function fnd_program.message().

Some errors are not trapped by the package, notably "duplicate value on index".

Note that an exception is raised if bad foreign key information is provided. For example, delete_program() does not fail if the program does not exist, but does fail if given a bad application name.

See:
Overview of Request Sets

FND_SET.MESSAGE

VariableDescription
Summary function FND_SET.MESSAGE return VARCHAR2;
Description Use the message function to return an error message. Messages are set when any validation (program) errors occur.

FND_SET.CREATE_SET


VariableDescription
Summary
procedure FND_SET.CREATE_SET
(name IN VARCHAR2,
short_name IN VARCHAR2,
application IN VARCHAR2,
description IN VARCHAR2 DEFAULT NULL,
owner IN VARCHAR2 DEFAULT NULL,
start_date IN DATE DEFAULT SYSDATE,
end_date IN DATE DEFAULT NULL,
print_together IN VARCHAR2 DEFAULT 'N',
incompatibilities_allowed IN VARCHAR2 DEFAULT 'N',
language_code IN VARCHAR2 DEFAULT 'US');
Description Use this procedure to register a Request Set. This procedure corresponds to the master region of the "Request Set" window.

VariableDescription
name The name of the new request set.
short_name The short name for the request set.
application The application that owns the request set.
description An optional description of the set.
owner An optional Oracle E-Business Suite user ID identifying the set owner, for example SYSADMIN.
start_date The date the set becomes effective.
end_date An optional date on which the set becomes outdated.
print_together Specify "Y" or "N" to indication whether all the reports in a set should print at the same time.
incompatibilities _allowed Specify "Y" or "N" to indicate whether to allow incompatibilities for this set.
language_code Language code for the above data, for example "US".

FND_SET.DELETE_SET


VariableDescription
Summary
procedure FND_SET.DELETE_SET
(request_set IN VARCHAR2,
application IN VARCHAR2);
Description Use this procedure to delete a request set and references to that set.
VariableDescription
request_set The short name of the request set to delete.
application The application that owns the request set.

FND_SET.ADD_PROGRAM


VariableDescription
Summary
procedure FND_SET.ADD_PROGRAM
(program IN VARCHAR2,
program_application IN VARCHAR2,
request_set IN VARCHAR2,
set_application IN VARCHAR2,
stage IN VARCHAR2,
program_sequence IN NUMBER,
critical IN VARCHAR2 DEFAULT 'Y',
number_of_copies IN NUMBER DEFAULT 0,
save_output IN VARCHAR2 DEFAULT 'Y',
style IN VARCHAR2 DEFAULT NULL,
printer IN VARCHAR2 DEFAULT NULL);
Description Use this procedure to add a concurrent program to a request set stage. This procedure corresponds to the "Programs" region in the "Stage Requests" window of the "Request Set" form.
VariableDescription
program_short_ name The short name used as the developer name of the concurrent program, for example 'FNDSCRMT'.
program_ application The short name of the application that owns the concurrent program.
request_set The short name of the request set.
set_application The application that owns the request set.
stage
The short name of the stage.
program_ sequence The sequence number of this program in the stage. All programs in a stage require a unique sequence number.
critical Specify 'Y' if this program can affect the stage's outcome, and 'N' if not.
number_of_ copies An optional default for the number of copies to print.
save_output Specify 'Y' to allow users to save output, or 'N' if the default is not to save the output.
style Optionally provide a default print style.
printer Optionally provide a default printer.

FND_SET.REMOVE_PROGRAM



VariableDescription
Summary
procedure FND_SET.REMOVE_PROGRAM
(program_short_name IN VARCHAR2,
program_application IN VARCHAR2,
request_set IN VARCHAR2,
set_application IN VARCHAR2,
stage IN VARCHAR2,
program_sequence IN NUMBER);
Description Use this procedure to remove a concurrent program from a request set.
VariableDescription
program_short_ name The short name used as the developer name of the concurrent program.
program_ application The short name of the application that owns the concurrent program.
request_set The short name of the request set.
set_application The short name of the application that owns the request set.
program_ sequence The sequence number of this program in the stage. All programs in a stage require a unique sequence number.


FND_SET.PROGRAM_PARAMETER


VariableDescription
Summary
procedure FND_SET.PROGRAM_PARAMETER
(program IN VARCHAR2,
program_application IN VARCHAR2,
request_set IN VARCHAR2,
set_application IN VARCHAR2,
stage IN VARCHAR2.
program_sequence IN NUMBER,
parameter IN VARCHAR2,
display IN VARCHAR2 DEFAULT 'Y',
modify IN VARCHAR2 DEFAULT 'Y',
shared_parameter IN VARCHAR2 DEFAULT NULL,
default_type IN VARCHAR2 DEFAULT NULL,
default_value IN VARCHAR2 DEFAULT NULL);
Description This procedure registers shared parameter information and the request set level overrides of program parameter attributes. This procedure corresponds to the "Request Parameters" window of the "Request Sets" form.
VariableDescription
program The short name used as the developer name of the concurrent program.
program_ application The short name of the application that owns the concurrent program.
request_set The short name of the request set.
set_application The short name of the application that owns the request set.
program_ sequence
The sequence number of this program in the stage.
parameter The name of the program parameter.
display "Y" to display the parameter, "N" to hide it.
modify "Y" to allow users to modify the parameter value, "N" to prevent it.
shared_parameter If the parameter uses a shared parameter, enter the shared parameter name here.
default_type If the parameter uses a default, enter the type here. Valid types are 'Constant', 'Profile', 'SQL Statement', or 'Segment'.
default_value If the parameter uses a default, enter a value appropriate for the default type here. This argument is required if default_type is not null.

FND_SET.DELETE_PROGRAM_PARAMETER



VariableDescription
Summary
procedure FND_SET.DELETE_SET_PARAMETER
program IN VARCHAR2,
program_application IN VARCHAR2,
request_set IN VARCHAR2 DEFAULT NULL,
stage IN VARCHAR2,
set_application IN VARCHAR2,
program_sequence IN NUMBER,
parameter IN VARCHAR2);
Description This procedure removes a concurrent program request set parameter from a request set definition.

VariableDescription
program The short name used as the developer name of the concurrent program.
program_ application The short name of the application that owns the concurrent program.
request_set The short name of the request set.
set_application The short name of the application that owns the request set.
program_ sequence The sequence number of this program in the set. All programs in a stage require a unique sequence number.
parameter The name of the program parameter to delete.

FND_SET.ADD_STAGE


VariableDescription
Summary
procedure FND_SET.ADD_STAGE
(name IN VARCHAR2,
request_set IN VARCHAR2,
set_application IN VARCHAR2,
short_name IN VARCHAR2,
description IN VARCHAR2 DEFAULT NULL,
display_sequence IN NUMBER,
function_short_name IN VARCHAR2 DEFAULT 'FNDRSSTE'
function_application IN VARCHAR2 DEFAULT 'FND',
critical IN VARCHAR2 DEFAULT 'N',
incompatibilities_allowed IN VARCHAR2 DEFAULT 'N',
start_stage IN VARCHAR2 DEFAULT 'N',
language_code IN VARCHAR2 DEFAULT 'US');
Description Adds a stage to a request set. This procedure corresponds to the "Stages" window of the "Request Sets" window.

VariableDescription
name The name of the stage.
request_set The short name of the request set.
set_application The application that owns the request set.
short_name The stage short (non-translated) name.
description Description of the stage.
function_ short_name Accept the default, "FNDRSSTE", the Standard Stage Evaluation function.
function_ application The short name of the application owning the function. The default is "FND".
function_ application Accept the default, "FND".
critical Specify "Y" if the return value of this stage affects the completion status of the request set, and "N" if it does not.
start_stage Specify "Y" or "N" to indicate whether this stage is the start stage for the set.
incompatibilities _allowed Specify "Y" or "N" to indicate whether to allow incompatibilities for this stage.
language_code The language code for the above data.

FND_SET.REMOVE_STAGE



VariableDescription
Summary
procedure FND_SET.REMOVE_STAGE
(request_set IN VARCHAR2,
set_application IN VARCHAR2,
stage IN VARCHAR2);
Description Use this procedure to delete a stage from a request set.
VariableDescription
request_set The short name of the request set.
set_application The short name of the application that owns the request set.
stage The short name of the stage to be removed.

FND_SET.LINK_STAGES


VariableDescription
Summary
procedure FND_SET.LINK_STAGES
(request_set IN VARCHAR2,
set_application IN VARCHAR2,
from_stage IN VARCHAR2,
to_stage IN VARCHAR2 DEFAULT NULL,
success IN VARCHAR2 DEFAULT 'N',
warning IN VARCHAR2 DEFAULT 'N',
error IN VARCHAR2 DEFAULT 'N');
Description Use this procedure to link two stages.

Important:This procedure updates the specified links. Sets created by FND_SET.CREATE_SET have null links between stages by default.

VariableDescription
request_set The short name of the request set.
set_application The application that owns the request set.
from_stage The short name of the "from" stage.
to_stage The short name of the "to" stage.
success Create success link. Specify 'Y' or 'N'.
warning
Create warning link. Specify 'Y' or 'N'.
error Create error link. Specify 'Y' or 'N'.

FND_SET.INCOMPATIBILITY


VariableDescription
Summary
 procedure FND_SET.INCOMPATIBILITY
(request_set IN VARCHAR2,
application IN VARCHAR2,
stage IN VARCHAR2 DEFAULT NULL,
inc_prog IN VARCHAR2 DEFAULT NULL
inc_prog_application IN VARCHAR2 DEFAULT NULL,
inc_request_set IN VARCHAR2 DEFAULT NULL,
inc_set_application IN VARCHAR2 DEFAULT NULL,
inc_stage IN VARCHAR2 DEFAULT NULL);
Description Use this procedure to register an incompatibility for a set or stage. Examples are given below.

VariableDescription
request_set The short name of the request set.
application The short name of the application that owns the request set.
stage The short name of the stage (for stage incompatibility).
inc_prog Short name of the incompatible program.
inc_prog_ application Application that owns the incompatible program.
inc_request_set Short name of the incompatible request set.
inc_set_ application The short name of the application that owns the incompatible request set.
inc_stage Short name of the incompatible stage.

Examples

  1. Set X is incompatible with program Y:


    fnd_set.incompatibility(request_set=>'X'
    application=>'APPX'
    inc_prog_short_name=>'Y', inc_prog_application=>'APPY');

  2. Set X is incompatible with set Y:


    fnd_set.incompatibility(request_set=>'X',
    application=>'APPX',
    inc_request_set=>'Y',
    inc_set_application=>'APPY');

  3. Set X is incompatible with stage 2 of set Y:


    fnd_set.incompatibility(request_set=>'X',
    application=>'APPX',
    inc_request_set=>'Y',
    inc_set_application=>'APPY',
    inc_stage_number=>2);

  4. Stage 3 of set X is incompatible with program Y:


    fnd_set.incompatibility(request_set=>'X',
    application=>'APPX',
    stage_number=>3,
    inc_prog_short_name=>'Y',
    inc_prog_application=>'APPY');

FND_SET.DELETE_INCOMPATIBILITY


VariableDescription
Summary
procedure FND_SET.DELETE_INCOMPATIBILITY
(request_set IN VARCHAR2,
application IN VARCHAR2,
stage IN VARCHAR2 DEFAULT NULL,
inc_prog IN VARCHAR2 DEFAULT NULL
inc_prog_application IN VARCHAR2 DEFAULT NULL,
inc_request_set IN VARCHAR2 DEFAULT NULL,
inc_set_application IN VARCHAR2 DEFAULT NULL,
inc_stage IN VARCHAR2 DEFAULT NULL);
Description Use this procedure to delete a request set incompatibility rule.

VariableDescription
request_set The short name of the request set.
application The short name of the application that owns the request set.
stage The short name of the stage (for stage incompatibility).
inc_prog Short name of the incompatible program.
inc_prog_ application Application that owns the incompatible program.
inc_request_set Short name of the incompatible request set.
inc_set_ application The short name of the application that owns the incompatible request set.
inc_stage Short name of the incompatible stage.

FND_SET.ADD_SET_TO_GROUP


VariableDescription
Summary
procedure FND_SET.ADD_SET_TO_GROUP
(request_set IN VARCHAR2,
set_application IN VARCHAR2,
request_group IN VARCHAR2,
group_application IN VARCHAR2);
Description Use this procedure to add a request set to a request group. This procedure corresponds to the "Requests" region in the "Request Groups" window in System Administration.
VariableDescription
request_set The short name of the request set to add to the request group.
set_application The application that owns the request set.
request_group The request group to which to add the request set.
group_ application The application that owns the request group.

FND_SET.REMOVE_SET_FROM_GROUP



VariableDescription
Summary
procedure FND_SET.REMOVE_SET_FROM_GROUP
(request_set IN VARCHAR2,
set_application IN VARCHAR2,
request_group IN VARCHAR2,
group_application IN VARCHAR2);
Description Use this procedure to remove a request set from a request group.
VariableDescription
request_set The short name of the request set to remove from the request group.
set_application The application that owns the request set.
request_group The request group from which to remove the request set.
group_ application The application that owns the request group.

FND_SUBMIT: Request Set Submission


This document describes the FND_SUBMIT APIs for request set submission. The APIs are described in the order that they would be called. Some of these APIs are optional.

FND_SUBMIT.SET_MODE

VariableDescription
Summary
function FND_SUBMIT.SET_MODE (db_trigger IN boolean) return boolean;
Description Use this optional procedure to set the mode if the request set is submitted from a database trigger. Call this function before calling FND_SUBMIT.SET_REQUEST_SET from a database trigger. Note that a failure in the database trigger call of FND_SUBMIT.SUBMIT_SET does not rollback changes.

VariableDescription
db_trigger Set to TRUE if the request set is submitted from a database trigger.

FND_SUBMIT.SET_REL_CLASS_OPTIONS


VariableDescription
Summary
function FND_SUBMIT.SET_REL_CLASS_OPTIONS
(application IN varchar2 default NULL,
class_name IN varchar2 default NULL,
cancel_or_hold IN varchar2 default 'H',
stale_date IN varchar2 default NULL) return boolean;
Description Call this function before calling FND_SUBMIT.SET_REQUEST_SET to use the advanced scheduling feature. If both FND_SUBMIT.SET_REL_CLASS_OPTIONS and FND_SUBMIT.SET_REPEAT_OPTIONS are set then FND_SUBMIT.SET_REL_CLASS_OPTIONS will take precedence. This function returns TRUE on successful completion, and FALSE otherwise.
VariableDescription
application The short name of the application associated with the release class.
class_name Developer name of the release class.
cancel_or_hold Cancel or Hold flag.
stale_date
Cancel this request on or after this date if the request has not yet run.

FND_SUBMIT.SET_REPEAT_OPTIONS


VariableDescription
Summary
function FND_SUBMIT.SET_REPEAT_OPTIONS
(repeat_time IN varchar2 default NULL,
repeat_interval IN number default NULL,
repeat_unit IN varchar2 default 'DAYS',
repeat_type IN varchar2 default 'START',
repeat_end_time IN varchar2 default NULL) return boolean;
Description Optionally call this function to set the repeat options for the request set before submitting a concurrent request set. If both FND_SUBMIT.SET_REL_CLASS_OPTIONS and FND_SUBMIT.SET_REPEAT_OPTIONS were set then FND_SUBMIT.SET_REL_CLASS_OPTIONS will take the percedence. Returns TRUE on successful completion, and FALSE otherwise.

VariableDescription
repeat_time Time of day at which the request set is to be repeated.
repeat_interval Frequency at which the request set is to be repeated.
repeat_unit Unit for the repeat interval. The default is DAYS. Valid values are MONTHS, DAYS, HOURS, and MINUTES.
repeat_type The repeat type specifies whether the repeat interval should apply from the start or end of the previous request. Valid values are START or END. Default value is START.
repeat_end_time Time at which the repetitions should end.

FND_SUBMIT_SET.REQUEST_SET


VariableDescription
Summary
function FND_SUBMIT.SET_REQUEST_SET
(application IN VARCHAR2,
request_set IN VARCHAR2) return boolean;
Description This function sets the request set context. Call this function at the very beginning of the submission of a concurrent request set transaction. Call this function after calling the optional functions FND_SUBMIT.SET_MODE, FND_SUBMIT.SET_REL_CLASS, FND_SUBMIT.SET_REPEAT_OPTIONS. It returns TRUE on successful completion, and FALSE otherwise.
VariableDescription
request_set The short name of request set (the developer name of the request set).
application The short name of the application that owns the request set.

FND_SUBMIT.SET_PRINT_OPTIONS



VariableDescription
Summary
function FND_SUBMIT.SET_PRINT_OPTIONS
(printer IN varchar2 default NULL,
style IN varchar2 default NULL,
copies IN number default NULL,
save_output IN boolean default
print_together IN varchar2 default 'N') return boolean;
Description Call this function before submitting the request if the printing of output has to be controlled with specific printer/style/copies, etc. Optionally call for each program in the request set. Returns TRUE on successful completion, and FALSE otherwise.
VariableDescription
printer Printer name for the output.
style Print style to be used for printing.
copies Number of copies to print.
save_output Specify TRUE if the output should be saved after printing, otherwise FALSE. The default is TRUE.
print_together
This argument applies only for subrequests. If 'Y', then output will not be printed untill all the subrequests are completed. The default is 'N'.

FND_SUBMIT.ADD_PRINTER


VariableDescription
Summary
function FND_SUBMIT.SET.ADD_PRINTER
(printer IN varchar2 default null,
copies IN number default null) return boolean;
Description Call this function after you set print options to add a printer to the printer list. Optionally call for each program in the request set. Returns TRUE on successful completion, and FALSE otherwise.
VariableDescription
printer Printer name where the request output should be sent.
copies Number of copies to print.

FND_SUBMIT.ADD_NOTIFICATION


VariableDescription
Summary
function FND_SUBMIT.ADD_NOTIFICATION
(user IN varchar2) return boolean;
Description This function is called before submission to add a user to the notification list. Optionally call for each program in the request set. This function returns TRUE on successful completion, and FALSE otherwise.
VariableDescription
user User name.

FND_SUBMIT.SET_NLS_OPTIONS


VariableDescription
Summary
function FND_SUBMIT.SET_NLS_OPTIONS
(language IN varchar2 default NULL,
territory IN varchar2 default NULL) return boolean;
Description Call this function before submitting request. This function sets request attributes. Optionally call for each program in the request set. This function returns TRUE on successful completion, and FALSE otherwise.
VariableDescription
implicit Nature of the request to be submitted.
protected Whether the request is protected against updates.
language The NLS language.
territory The language territory.

FND_SUBMIT.SUBMIT_PROGRAM



VariableDescription
Summary
function FND_SUBMIT.SUBMIT_PROGRAM
(application IN varchar2,
program IN varchar2,
stage IN varchar2,
argument1,...argument100) return boolean;
Description Call FND_SUBMIT.SET_REQUEST_SET function before calling this function to set the context for the report set submission. Before calling this function you may want to call the optional functions SET_PRINT_OPTIONS, ADD_PRINTER, ADD_NOTIFICATION, SET_NLS_OPTIONS. Call this function for each program (report) in the request set. You must call fnd_submits.set_request_set before calling this function. You have to call fnd_submit.set_request_set only once for all the submit_program calls for that request set.

VariableDescription
application Short name of the application associated with the program within a report set.
program Name of the program with the report set.
stage Developer name of the request set stage that the program belongs to.
argument1...100 Arguments for the program

FND_SUBMIT.SUBMIT_SET


VariableDescription
Summary
function FND_SUBMIT.SUBMIT_SET
(start_time IN varchar2 default null,
sub_request IN boolean default FALSE) return integer;
Description Call this function to submit the request set which is set by using the SET_REQUEST_SET. If the request set submission is successful, this function returns the concurrent request ID; otherwise; it returns 0.
VariableDescription
start_time Time at which the request should start running, formated as HH24:MI or HH24:MI:SS.
sub_request Set to TRUE if the request is submitted from another request and should be treated as a sub-request.

Examples of Request Set Submission


/* Example 1 */
/* To submit a Request set which is having STAGE1 and
STAGE2. STAGE1 is having 'FNDSCARU' and 'FNDPRNEV'
programs. STAGE2 is having 'FNDSCURS'. */
/* set the context for the request set FNDRSTEST */
success := fnd_submit.set_request_set('FND', 'FNDRSTEST');
if ( success ) then
/* submit program FNDSCARU which is in stage STAGE1 */
success := fnd_submit.submit_program('FND','FNDSCARU',
'STAGE1', CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* submit program FNDPRNEV which is in stage STAGE1 */
success := fnd_submit.submit_program('FND','FNDPRNEV',
'STAGE1','','','','','','','','','','',
CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* submit program FNDSCURS which is in stage STAGE2 */
success := fnd_submit.submit_program('FND','FNDSCURS',
'STAGE2', CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* Submit the Request Set */
req_id := fnd_submit.submit_set(null,FALSE);
end if;

/* Example 2 */
/* To submit a request set FNDRSTEST as a repeating request set.
Request set FNDRSTEST has STAGE1 and STAGE2.
STAGE1 contains 'FNDSCARU' and 'FNDPRNEV' programs.
STAGE2 has 'FNDSCURS'. */
/* set the repeating options for the request set before
calling the set_request_set */
success := fnd_submit.set_repeat_options( '', 4, 'HOURS', 'END');
/* set the context for the request set FNDRSTEST */
success := fnd_submit.set_request_set('FND',
'FNDRSTEST');
if ( success ) then
/* submit program FNDSCARU which is in stage STAGE1 */
success := fnd_submit.submit_program('FND','FNDSCARU',
'STAGE1', CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* submit program FNDPRNEV which is in stage STAGE1 */
success := fnd_submit.submit_program('FND','FNDPRNEV',
'STAGE1','','','','','','','','','','',
CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* submit program FNDSCURS which is in stage STAGE2 */
success := fnd_submit.submit_program('FND','FNDSCURS',
'STAGE2', CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* Submit the Request set */
req_id := fnd_submit.submit_set(null,FALSE);
end if;

/* Example 3 */
/* To submit a Request set FNDRSTEST with 5 copies of the Print environment variables report. Request set FNDRSTEST has STAGE1 and STAGE2. STAGE1 has 'FNDSCARU' and 'FNDPRNEV' programs. STAGE2 has 'FNDSCURS'. */
/* set the context for the request set FNDRSTEST */
success := fnd_submit.set_request_set('FND', 'FNDRSTEST');
if ( success ) then
/* submit program FNDSCARU which is in stage STAGE1 */
success := fnd_submit.submit_program('FND','FNDSCARU',
'STAGE1', CHR(0),'','','','','','','','','', ...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* set the print options for the program */
success := fnd_submit.set_print_options( 'hqunx138', 'Landscape', 5,
'Yes', FALSE);
/* submit program FNDPRNEV which is in stage STAGE1 */
success:= fnd_submit.submit_program('FND','FNDPRNEV',
'STAGE1','','','','','','','','','','',
CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* submit program FNDSCURS which is in stage STAGE2 */
success := fnd_submit.submit_program('FND','FNDSCURS', 'STAGE2', CHR(0),'','','','','','','','','',
...arguments...);
if ( not success ) then
raise submit_failed;
end if;
/* Submit the Request set */
req_id := fnd_submit.submit_set(null,FALSE);
end if;

原文出處:Oracle E-Business Suite Developer's Guide
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15763
[轉貼]What is In “Purchase Order import”

What is In “Purchase Order import”

Posted on December 24th, 2007 by Sanjit Anand |

You can use Purchase Document Open Interface which allows you to quickly import a large volume of Standard Purchase Orders into Oracle Purchasing.

The Import process involves populating the PO interface tables with the document information to be imported and then running the Import
Standard Purchase Orders concurrent program which will validate the data and create the PO in the application and return an error message if something fail.


dgreybarrow First Timer, know these first

Before you start, you need to understand the database objects which play critical role.

Table NameDescriptionType
PO_HEADERS_INTERFACE This is the table where to insert PO headers data in interface table. Interface table
PO_LINES_INTERFACE This is where we insert PO lines information to be imported ( it is used also for Shipments details ) Interface table
PO_DISTRIBUTIONS_INTERFACE This is where we insert PO distribution details before import
Interface table
PO_INTERFACE_ERRORS Stores all errors resulted from import process. Errors table
PO_HEADERS_ALL Stores document headers for purchase orders, purchase agreements,quotations, and RFQs PO Base table
PO_LINES_ALL Stores purchase document lines for purchase orders, purchase agreements, quotations, and RFQs PO Base table
PO_LINE_LOCATIONS_ALL Stores document shipment schedules for purchase orders, purchase agreements, quotations, and RFQs PO Base table
PO_DISTRIBUTIONS_ALL
Stores purchase order distributions PO Base table

dgreybarrow Steps by Steps

Know what is getting ining Data into Purchase Order Interface Tables

  1. Load PO header, lines, shipments and distributions data from your source system into the following interface tables
    • PO_HEADERS_INTERFACE
    • PO_LINES_INTERFACE
    • PO_DISTRIBUTIONS_INTERFACE
  2. Once the data has been inserted into the interface tables, a queries like the following can be used to review the information before running the import program :
    • Select * from PO_HEADERS_INTERFACE where INTERFACE_HEADER_ID=<headerid>
    • Select * from PO_LINES_INTERFACE where INTERFACE_HEADER_ID=&headerid

    • Select * from PO_DISTRIBUTIONS_INTERFACE where INTERFACE_HEADER_ID=&headerid

Review data before calling Import Standard Purchase Orders program.

when you submit the import program, third parameter is approval status , which altogether have different logic, which you need to understand the impact.

Purchase Order import

dgreybarrow Understanding approval status in parameter

Significant impact is there on Approval Status parameter and have import logic which is as below:

 



Status in
Interface Table
Imporft Program Approval Status ParameterResulting Document Status
NULL Incomplete Incomplete
NULL Approved Approved
NULL Initiate Approval Initiate Approval
Incomplete Incomplete Incomplete
Incomplete Approved Incomplete
Incomplete Initiate Approval Initiate Approval
Approved Incomplete Approved
Approved Approved Approved
Approved Initiate Approval Approved

dgreybarrow Take Away

If the records got imported successfully without issues, the records will stay in the interface tables.

  • You can notice , successful records get PROCESS_CODE as “ACCEPTED”
  • It is good practice and important to check the Purchasing Interface Errors report always.
  • This Error report you can submit after your import completed.

  • Because the Purchasing Documents Open Interface saves or errors out line by line, it can accept partial documents. So that you may find a document has been accepted although some lines from it has been rejected . Therefore, to see which document lines were not submitted because of errors, you must check the Purchasing Interface Errors report.

dgreybarrow What happen with IMPORT

This seems sound intresting to you.

  • The Purchasing Documents Open Interface (PDOI) programs first process a record from the PO_HEADERS_INTERFACE table.
  • Then, the program processes the child records in the PO_LINES_INTERFACE table then process the PO_DISTRIBUTIONS_INTERFACE table, before going on to the next PO represented by a record in PO_HEADERS_INTERFACE. Make sense.

  • In between , If the program gets an error while processing a record, the program writes the error details to the PO_INTERFACE_ERRORS table and increments the record's error counter.
  • Therefore, the Purchasing Documents Open Interface saves or errors out on a line-byline basis.
  • This means that if an error is found in a document line, only that line is rolled back (not submitted to Purchasing), and we will be able to find the error in the PO_INTERFACE_ERRORS table.
  • You should be aware , because the Purchasing Documents Open Interface can accept partial documents as it saves or errors out line by line.
  • If an error is found in a header, none of its lines are processed.
  • The Purchasing Documents Open Interface rolls back the header, does not process its lines, and does the following:
    • Sets the PROCESS_CODE column value to REJECTED in the PO_HEADERS_INTERFACE table.

    • Writes out the record identification number and the details of the error to the PO_INTERFACE_ERRORS table.
    • Begins processing the next header record.
  • If no processing errors are found during processing, the header record and all successfully submitted child records are loaded into Purchasing, and then flagged as processed by setting the PROCESS_CODE column to ACCEPTED.

As mention earlier, To check for records in error, the Purchasing Interface Errors Report can be run to provide information as to the cause of the error.

dgreybarrow Other tools

This is most acceptable interface and widly used every where, therefore Oracle have Diagnostics tool for this keeping developer in mind.

Oracle Diagnostics tool name is Oracle Purchasing Documents Open Interface Data Collection Test.

This diagnostic test will verify the data in the interface tables used by the purchasing documents open interface (PDOI) so that it can be used proactively or reactively to resolve or prevent issues in the purchasing documents open interface (PDOI).

Hope you find this is very useful and productive tool.should you need any input send me offline.


原文出處:What is In "Purchase Order import" | OracleApps Epicenter
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15763
[轉貼]Purchase Document Open Interface(PDOI)

Purchase Document Open Interface(PDOI)

PO模塊也有自己的接口表,多用於把其他業務系統在Oracle EBS系統生成採購訂單記錄。


Table Name Description Type
PO_HEADERS_INTERFACE This is the table where to insert PO headers data in interface table. Interface table
PO_LINES_INTERFACE This is where we insert PO lines information to be imported ( it is used also for Shipments details ) Interface table
PO_DISTRIBUTIONS_INTERFACE This is where we insert PO distribution details before import
Interface table
PO_INTERFACE_ERRORS Stores all errors resulted from import process. Errors table
PO_HEADERS_ALL Stores document headers for purchase orders, purchase agreements,quotations, and RFQs PO Base table
PO_LINES_ALL Stores purchase document lines for purchase orders, purchase agreements, quotations, and RFQs PO Base table
PO_LINE_LOCATIONS_ALL Stores document shipment schedules for purchase orders, purchase agreements, quotations, and RFQs PO Base table
PO_DISTRIBUTIONS_ALL
Stores purchase order distributions
PO Base table
下邊是主要的三張PO接口表,用於記錄訂單頭,行,Shipments以及Distributions信息
  •     PO_HEADERS_INTERFACE
  •     PO_LINES_INTERFACE
  •     PO_DISTRIBUTIONS_INTERFACE
接口表的查詢語句
  • Select * from PO_HEADERS_INTERFACE where INTERFACE_HEADER_ID=&headerid;
  • Select * from PO_LINES_INTERFACE where INTERFACE_HEADER_ID=&headerid;
  • Select * from PO_DISTRIBUTIONS_INTERFACE where INTERFACE_HEADER_ID=&headerid;
R12還引入了另外一張Interface表,PO_LINE_LOCATIONS_INTERFACE,這張表其實暫時還用不到,還只是Oracle標準產品內部使用,但因為名稱上很容易讓人誤解,所以這裡提一下。

Table Name

Feature Area

PO_LINE_LOCATIONS_INTERFACE

This table is intended for internal use only.
This is the interface table for price breaks and standard PO shipments. You should not populate these tables rather PO_LINES_INTERFACE as before. PDOI internally pushes records to this interface table for processing.


數據插入到接口表後,需要運行Concurrent Program: Import Standard Purchase Orders,用於把接口表導入到採購訂單相關表。

Concurrent Program參數:
Default Buyer(Optional):
Enter the default buyer name to be associated with the incoming documents that are not assigned to a buyer..

If a default buyer is not specified and the imported purchase order also does not have buyer information, the program will use the normal Purchasing defaulting mechanism to generate buyer information. If the buyer name provided in the interface is not valid, then the record will error out and the default buyer name will not be supplemented.

Create or Update Item (Required)
The valid response is Yes or No. If Yes, the program will also create items in the item master from those buyer part numbers in the interface data where there is no corresponding item master entry. If No, the program will not create new item master entries.

Approval Status
有三個可選值,APPROVED,INCOMPLETE,INITIATE APPROVAL.

關於這個字段
Purchasing User Guide中有清楚的說明,接口表中Status字段 + 這個Concurrent Program的Approval Status共同決定最終PO的狀態。
Document Status in Interface Table Concurrent Program Approval Status Parameter Resulting Document Status
Null INCOMPLETE INCOMPLETE
Null APPROVED APPROVED
Null INITIATE APPROVAL INITIATE APPROVAL
INCOMPLETE INCOMPLETE INCOMPLETE
INCOMPLETE
APPROVED INCOMPLETE
INCOMPLETE INITIATE APPROVAL INITIATE APPROVAL
APPROVED INCOMPLETE APPROVED
APPROVED APPROVED APPROVED
APPROVED INITIATE APPROVAL APPROVED

You can import Standard purchase orders with an INCOMPLETE status and then automatically initiate the PO Approval Workflow for these imported documents. Specifying a status of Incomplete or leaving the status Null in the interface data, and specifying a value of Initiate Approval for the Approval Status runtime parameter can achieve this.


Standard purchase orders can also be imported with an APPROVED status. Specifying a status of Approved in the interface data or leaving the status Null in the interface data, and specifying a value of Approved for the Approval Status runtime parameter can achieve this. The PO Approval Workflow is not initiated.

Batch ID (Optional)
Enter a valid batch ID, that is a unique identifier for the group of documents in this submission. If you don't enter a Batch ID, one is generated for you when the process runs.

Import Standard Purchase Orders對應的後台程序為SQL*Plus類型的POXPDOI


訂單Import Program的後台邏輯
1.首先從PO_HEADERS_INTERFACE處理一條記錄
2.根據INTERFACE_HEADER_ID,再處理行接口記錄PO_LINES_INTERFACE,以及 PO_DISTRIBUTIONS_INTERFACE。都處理完之後,又從PO_HEADERS_INTERFACE表中Pick下一個訂單頭數據。
  • In between , If the program gets an error while processing a record, the program writes the error details to the PO_INTERFACE_ERRORS table and increments the record's error counter.
  • Therefore, the Purchasing Documents Open Interface saves or errors out on a line-byline basis.
  • This means that if an error is found in a document line, only that line is rolled back (not submitted to Purchasing), and we will be able to find the error in the PO_INTERFACE_ERRORS table.
  • You should be aware , because the Purchasing Documents Open Interface can accept partial documents as it saves or errors out line by line.
  • If an error is found in a header, none of its lines are processed.
  • The Purchasing Documents Open Interface rolls back the header, does not process its lines, and does the following:

    • Sets the PROCESS_CODE column value to REJECTED in the PO_HEADERS_INTERFACE table.
    • Writes out the record identification number and the details of the error to the PO_INTERFACE_ERRORS table.
    • Begins processing the next header record.
  • If no processing errors are found during processing, the header record and all successfully submitted child records are loaded into Purchasing, and then flagged as processed by setting the PROCESS_CODE column to ACCEPTED.

As mention earlier, To check for records in error, the  Purchasing Interface Errors Report  can be run to provide information as to the cause of the error.

Diagnose Problems With Importing Standard Purchase Orders

如果Import Standard Purchase Orders運行後報錯,可使用Purchasing Interface Errors Report來查錯,這個Report實際上就是PO_INTERFACE_ERRORS裡的內容。

如果訂單沒有導入成功,可以使用下邊的SQL查看錯誤信息,
Select * from PO_INTERFACE_ERRORS where interface_type = 'PO_DOCS_OPEN_INTERFACE' order by creation_date desc;


TRACING
To trace the Import Standard Purchase Orders program from 11.5.10 onwards, do the following :
1 - Navigate to System Administrator responsibility
2 - Navigate to Profiles->System
3 - Query the Profile Option Concurrent: Allow Debugging and set it to Yes at User level
4 - Navigate to Purchasing responsibility
5 - From the Requests form, choose the Import Standard Purchase Orders program and set the required Parameters
6 - Click the Debug button
7 - Check the SQL Trace checkbox and specify Trace with Binds and Waits
8 - Submit the Concurrent program
9 - Retrieve the trace file created.

LOGGING
In order to create a FND Debug Log for the Import Standard Purchase Orders program, the following steps need to be followed :
1 - Using a SQL client run the following query and Note down this log_seq. :
select max(log_sequence) from fnd_log_messages;
2 - Set the following profiles at the user level:
FND: Debug Log Enabled = YES
FND: Debug Log Filename = NULL
FND: Debug Log Level = STATEMENT
FND: Debug Log Module = %
3 - Run Import Standard Purchase Orders program
4. Using a SQL client run the following query :
    Select * from fnd_log_messages
    where log_sequence > &log_seq_noted_above
    order by log_sequence
The output of this query can indicate errors that were encountered during the Import Standard Purchase Orders program.
 
Key Fields in Interface Tables
PO_HEADERS_INTERFACE
PROCESS_CODE: Set it to PENDING. After processing it shows as ACCEPTED, REJECTED or NOTIFIED.
如果訂單頭出錯,那麼Program不會處理訂單行中的內容,訂單頭接口表中 PROCESS_CODE字段會被設置為 REJECTED,如果沒錯則 PROCESS_CODE字段為ACCEPTED
For blanket purchase agreements and standard purchase orders only, when the supplier sends an updated price/sales catalog, the Purchasing Documents Open Interface sets the PROCESS_CODE column to NOTIFIED for those lines with prices that exceed your price tolerance. For these price updates only, the Purchasing Documents Open Interface waits for you to accept or reject the price increase in the Exceeded Price Tolerances window before completing or rejecting the price change. 


ACTION: ORIGINAL, REPLACE or UPDATE.
      ORIGINAL: Original is one in which all information is new in your system.
      REPLACE: Replaces already-created blanket purchase agreements or catalog quotations with new documents containing the new information. First, it looks for documents that have same document type
                    (DOCUMENT_TYPE_CODE), supplier (VENDOR_ID)  and supplier document number (VENDOR_DOC_ NUM) with the existing supplier document number (VENDOR_ORDER _NUM) as the replacement document
      UPDATE: Updates Unit Price, Item Description, UOM, Price Breaks, Expiration Date
For More Fields Descrption,Check This   Blog

DOCUMENT_TYPE_CODE:  STANDARD, BLANKET , QUOTATION

Interface Scripts Examples
a - PO with a single line :

INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
1,
'PENDING',
'ORIGINAL', -- Indicates this is a new document
204, -- Org id for operating unit Vision Operations
'STANDARD', -- Indicates a standard PO is being imported
'USD', -- The currency to be used in the PO
57, -- The ID of the buyer
'Office Supplies, Inc.', -- Supplier name
'OFFICESUPPLIES', -- Supplier Site
'V1- New York City', -- Ship to location name
'V1- New York City', -- Bill to location name
'TestPO'); -- Reference that can be tied to the imported PO
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713', -- Item to imported on the PO line
'Ea', -- Unit of Measure
1,
50, -- Line price in specified currency
'V1', -- Inventory Organization which will receive the shipment
'V1- New York City' ); -- Ship to location name
commit;

b - PO with one line and one distribution :

INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
2,
'PENDING',
'ORIGINAL',
204,
'STANDARD',
'USD',
57, -- Your buyer id
'Office Supplies, Inc.',
'OFFICESUPPLIES',
'V1- New York City',
'V1- New York City',
'TestPO');
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',
'Ea',
1,
50,
'V1',
'V1- New York City' );
INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered,
charge_account_id)
VALUES
(po_headers_interface_s.currval,
po.po_lines_interface_s.CURRVAL,
po.po_distributions_interface_s.NEXTVAL,
1,
1,
12975); -- Code Combination ID for the Charge Account to be used on the Distribution
commit;

c - PO with two lines :

INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
3,
'PENDING',
'ORIGINAL',
204,
'STANDARD',
'USD',
57,
'Office Supplies, Inc.',
'OFFICESUPPLIES',
'V1- New York City',
'V1- New York City',
'TestPO');
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',
'Ea',
1,
50,
'V1',
'V1- New York City' );
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
2,
1,
'Goods',
'CM96713',
'Ea',
1,
80,
'V1',
'V1- New York City' );
commit;

d - PO with one line and two shipments :

INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
4,
'PENDING',
'ORIGINAL',
204,
'STANDARD',
'USD',
57,
'Office Supplies, Inc.',
'OFFICESUPPLIES',
'V1- New York City',
'V1- New York City',
'TestPO');
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location,
promised_date)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',
'Ea',
1,
50,
'V1',
'V1- New York City',
sysdate+1);
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location,
promised_date)
VALUES
(apps.po_lines_interface_s.NEXTVAL,
apps.po_headers_interface_s.CURRVAL,
1,
2,
'Goods',
'CM96713',
'Ea',
2,
50,
'V1',
'V1- New York City',
sysdate+5);
commit;

e - PO with one line, one shipment and two distributions :

INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
5,
'PENDING',
'ORIGINAL',
204,
'STANDARD',
'USD',
57,
'Office Supplies, Inc.',
'OFFICESUPPLIES',
'V1- New York City',
'V1- New York City',
'TestPO');
INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location,
promised_date)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',
'Ea',
5,
50,
'V1',
'V1- New York City',
sysdate+1);
INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered,
charge_account_id)
VALUES
(po_headers_interface_s.currval,
po.po_lines_interface_s.CURRVAL,
po.po_distributions_interface_s.NEXTVAL,
1,
1,
12975);
INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered,
charge_account_id)
VALUES
(po_headers_interface_s.currval,
po.po_lines_interface_s.CURRVAL,
po.po_distributions_interface_s.NEXTVAL,
2,
4,
12976);
commit



原文出處: Purchase Document Open Interface(PDOI) - you Richer - 博客园
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15763
[轉貼]Oracle Tables and Views for Oracle Procurement Cloud : PO_HEADERS_INTERFACE

PO_HEADERS_INTERFACE

PO_HEADERS_INTERFACE is the interface table that holds header information for the PDOI program to create standard/blanket purchase orders and catalog quotations. The PDOI program validates your data, derives or defaults additional information and writes an error message for every validation that fails into the PO_INTERFACE_ERRORS table. Scope: public Product: PO Display Name: PO Headers Interface Category: BUSINESS_ENTITY PO_BLANKET_PURCHASE_AGREEMENT Category: BUSINESS_ENTITY PO_CONTRACT_PURCHASE_AGREEMENT Category: BUSINESS_ENTITY PO_GLOBAL_BLANKET_AGREEMENT Category: BUSINESS_ENTITY PO_GLOBAL_CONTRACT_AGREEMENT Category: BUSINESS_ENTITY PO_PRICE_BREAKS Category: BUSINESS_ENTITY PO_STANDARD_PURCHASE_ORDER

Details

  • Schema: FUSION

  • Object owner: PO

  • Object type: TABLE

  • Tablespace: APPS_TS_TX_DATA


Primary Key

Details of the primary key for the table.
NameColumns
PO_HEADERS_INTERFACE_PK INTERFACE_HEADER_ID

Columns









































Columns that are associated with the table.
NameDatabaseLengthPrecisionNot NullCommentsFlexfield Mapping
INTERFACE_HEADER_ID NUMBER 18 Yes Interface header unique identifier
BILLTO_BU_NAME VARCHAR2 240 Bill-to BU Name
SOLDTO_LE_NAME VARCHAR2 240 Sold-to Le Name
INTERFACE_HEADER_KEY VARCHAR2 50 Header record identifier, unique within LOAD_RE QUEST_ID. Reserved for data uploaded through cloud interface only.
PRC_BU_NAME VARCHAR2 240 Procurement BU Name
REQ_BU_NAME VARCHAR2 240 Requisitioning BU Name
LOAD_REQUEST_ID NUMBER 18 LOAD_REQUEST_ID
BILLTO_BU_ID NUMBER 18 Bill-to Business Unit unique identifier. Indicates the business unit which vouches the supplier invoices received for this purchase order.
SOLDTO_LE_ID NUMBER 18 Sold to Legal Entity unique identifier. Indicates the party which is financially responsible for the purchases on this purchase order.
DEFAULT_PO_TRADE_ORG_ID NUMBER 18 Unique identifier for Purchasing Trade Organization. Purchasing Trade Organization is the inventory organization reporting to the Sold-to Legal Entity identified in a Purchase Order. This organization is used for cost accounting the transactions in the Sold-to Legal Entity.
ORCHESTRATION_ORDER_FLAG VARCHAR2 1 Indicates whether this purchase order is an orchestrated purchase order. Valid values are Y/N. A value of 'Y' implies that this is an orchestrated order. A value of 'N' implies that this is not an orchestrated order.
AGING_PERIOD_DAYS NUMBER 5 Indicates the maximum number of days the material may be on consignment. It defaults a value for the same term on the document line. Only positive values are allowed. Applicable only to BPA.
AGING_ONSET_POINT VARCHAR2 30 Indicates the mutually agreed event point at which consigned material begins to age. The possible values are None / Receipt / Shipment. Values from Lookups. Applicable only to BPA.
CONSUMPTION_ADVICE_FREQUENCY VARCHAR2 30 Indicates the default value of the frequency at which the 'Create Consumption Advice' program will be run for all consumption transactions for consigned inventory purchased under this agreement. It defaults a value for the same term on the document line. The possible values are Daily / Weekly / Monthly. Values from Lookups. Applicable only to BPA.
CONSUMPTION_ADVICE_SUMMARY VARCHAR2 30 Indicates the granularity at which consumption advices will be generated. The possible values are Inventory Organization / Item and Transaction Date / Inventory Organization and Item / Inventory Organization / All Inventory Organizations. Values from Lookups. Applicable only to BPA.
BILLING_CYCLE_CLOSING_DATE DATE Field to capture when the current billing period for an agreement ends. This is applicable only for a BPA with consignment terms enabled.
DEFAULT_CONSIGNMENT_LINE_FLAG VARCHAR2 1 Indicates the default value for the Consignment line attribute on the agreement line. Values: Y/N. Applicable only to BPA.
PAY_ON_USE_FLAG VARCHAR2 1 This checkbox will indicate whether self-billing is enabled for consumption of consigned material. Values: Y/N. Applicable only to BPA.
PRICE_UPDATE_TOLERANCE NUMBER PRICE_UPDATE_TOLERANCE
APPROVAL_ACTION VARCHAR2 25 APPROVAL_ACTION
CAT_ADMIN_AUTH_ENABLED_FLAG VARCHAR2 1 CAT_ADMIN_AUTH_ENABLED_FLAG
SUPPLIER_NOTIF_METHOD VARCHAR2 25 SUPPLIER_NOTIF_METHOD
FAX VARCHAR2 60 FAX
EMAIL_ADDRESS VARCHAR2 2000 EMAIL_ADDRESS
GROUP_REQUISITIONS VARCHAR2 1 GROUP_REQUISITIONS
GROUP_REQUISITION_LINES VARCHAR2 1 GROUP_REQUISITION_LINES
USE_NEED_BY_DATE VARCHAR2 1 USE_NEED_BY_DATE
USE_SHIP_TO VARCHAR2 1 USE_SHIP_TO
RETRO_PRICE_COMM_UPDATES_FLAG VARCHAR2 1 Communicate price updates flag
RETRO_PRICE_APPLY_UPDATES_FLAG VARCHAR2 1 Retroactively apply price updates to existing POs flag.
GENERATE_ORDERS_AUTOMATIC VARCHAR2 1 Update sourcing rules flag
SUBMIT_APPROVAL_AUTOMATIC VARCHAR2 1 Enable automatic sourcing flag
ACCEPTANCE_WITHIN_DAYS NUMBER Used for Acknowledge within days
APPROVAL_STATUS VARCHAR2 25 APPROVAL_STATUS
PRC_BU_ID NUMBER 18 PRC_BU_ID
REQ_BU_ID NUMBER 18 REQ_BU_ID
BATCH_ID NUMBER Batch unique identifier
INTERFACE_SOURCE_CODE VARCHAR2 25 Identifier for upstream integrating module. Reserved for internal use.
GROUP_CODE VARCHAR2 25 Requisition line grouping
DOCUMENT_TYPE_CODE VARCHAR2 25 Document type to be created: PO or RFQ
DOCUMENT_SUBTYPE VARCHAR2 25 Document subtype
SOLDTO_BU_ID NUMBER 18 Sold to Business Unit Unique Identifier
CARRIER_ID NUMBER 18 Carrier ID (Party ID)
CO_NUM VARCHAR2 80 Change Order Number
CHANGE_ORDER_DESC VARCHAR2 2000 Change Order Description
ORIGINATOR_ROLE VARCHAR2 25 Change Order Initiator Role (Buyer/Supplier)
INITIATE_APPROVAL VARCHAR2 25 Initiate approval process (NULL/Yes/No)
DOCUMENT_NUM VARCHAR2 30 Document number
PO_HEADER_ID NUMBER 18 Document header unique identifier
RELEASE_DATE DATE Release date
CURRENCY_CODE VARCHAR2 15 Currency code
RATE_TYPE_DISP VARCHAR2 30 RATE_TYPE_DISP
RATE_TYPE VARCHAR2 30 Rate type
RATE_DATE DATE Rate date
RATE NUMBER Rate
AGENT_NAME VARCHAR2 2000 Buyer name
AGENT_ID NUMBER 18 Buyer unique identifier
VENDOR_NAME VARCHAR2 360 Supplier name
VENDOR_ID NUMBER 18 Supplier unique identifier
VENDOR_SITE_CODE VARCHAR2 15 Supplier site code
VENDOR_SITE_ID NUMBER 18 Supplier site unique identifier
VENDOR_CONTACT VARCHAR2 360 Supplier contact name
VENDOR_CONTACT_ID NUMBER 18 Supplier contact unique identifier
SHIP_TO_LOCATION VARCHAR2 60 Ship-to location code
SHIP_TO_LOCATION_ID NUMBER 18 Ship-to location unique identifier
BILL_TO_LOCATION VARCHAR2 60 Bill-to location code
BILL_TO_LOCATION_ID NUMBER 18 Bill-to location unique identifier
PAYMENT_TERMS VARCHAR2 50 Payment terms name
TERMS_ID NUMBER 18 Payment terms unique identifier
FREIGHT_CARRIER VARCHAR2 360 Freight carrier
FOB VARCHAR2 30 Type of free-on-board terms for the document
FREIGHT_TERMS VARCHAR2 30 Freight terms
APPROVED_DATE DATE Approval date
REVISED_DATE DATE Revised date
REVISION_NUM NUMBER Document revision number
NOTE_TO_VENDOR VARCHAR2 1000 Note to supplier
NOTE_TO_RECEIVER VARCHAR2 1000 Note to receiver
CONFIRMING_ORDER_FLAG VARCHAR2 1 Indicates whether purchase order is a confirming order
COMMENTS VARCHAR2 240 Comments
ACCEPTANCE_REQUIRED_FLAG VARCHAR2 1 Indicates whether acceptance from the supplier is required or not
ACCEPTANCE_DUE_DATE DATE Date by which the supplier should accept the purchase order
AMOUNT_AGREED NUMBER Amount agreed for the planned or blanket purchase order
AMOUNT_LIMIT NUMBER Maximum amount that can be released against the purchase order (blanket, contract, or planned)
MIN_RELEASE_AMOUNT NUMBER Minimum amount that can be released against a blanket or planned purchase order
START_DATE DATE START_DATE
FROZEN_FLAG VARCHAR2 1 Indicator of whether the document is frozen
CLOSED_CODE VARCHAR2 25 Closed status
CLOSED_DATE DATE Closed date
FROM_HEADER_ID NUMBER Unique identifier of the RFQ used to autocreate a quotation
FROM_TYPE_LOOKUP_CODE VARCHAR2 25 Document type used to autocreate from
USSGL_TRANSACTION_CODE VARCHAR2 30 United States standard general ledger transaction code
ATTRIBUTE_CATEGORY VARCHAR2 30 Descriptive Flexfield: structure definition of the user descriptive flexfield.
ATTRIBUTE1 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE2 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE3 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE4 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE5 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE6 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE7 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE8 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE9 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE10 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE11 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE12 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE13 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE14 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE15 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE16 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE17 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE18 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE19 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE20 VARCHAR2 150 Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER1 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER2 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER3 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER4 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER5 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER6 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER7 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER8 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER9 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_NUMBER10 NUMBER Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE1 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE2 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE3 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE4 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE5 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE6 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE7 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE8 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE9 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_DATE10 DATE Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_TIMESTAMP1 TIMESTAMP Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_TIMESTAMP2 TIMESTAMP Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_TIMESTAMP3 TIMESTAMP Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_TIMESTAMP4 TIMESTAMP Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_TIMESTAMP5 TIMESTAMP Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_TIMESTAMP6 TIMESTAMP Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_TIMESTAMP7 TIMESTAMP Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_TIMESTAMP8 TIMESTAMP Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_TIMESTAMP9 TIMESTAMP Descriptive Flexfield: segment of the user descriptive flexfield.
ATTRIBUTE_TIMESTAMP10 TIMESTAMP Descriptive Flexfield: segment of the user descriptive flexfield.
CREATION_DATE TIMESTAMP YesWho column: indicates the date and time of the creation of the row.
CREATED_BY VARCHAR2 64 Yes Who column: indicates the user who created the row.
LAST_UPDATE_DATE TIMESTAMP Yes Who column: indicates the date and time of the last update of the row.
LAST_UPDATED_BY VARCHAR2 64 Yes Who column: indicates the user who last updated the row.
LAST_UPDATE_LOGIN VARCHAR2 32 Who column: indicates the session login associated to the user who last updated the row.
REQUEST_ID NUMBER 18 Enterprise Service Scheduler: indicates the request ID of the job that created or last updated the row.
JOB_DEFINITION_NAME VARCHAR2 100 Enterprise Service Scheduler: indicates the name of the job that created or last updated the row.
JOB_DEFINITION_PACKAGE VARCHAR2 900 Enterprise Service Scheduler: indicates the package name of the job that created or last updated the row.
PROGRAM_NAME VARCHAR2 30 PROGRAM_NAME
PROGRAM_APP_NAME VARCHAR2 50 PROGRAM_APP_NAME
PROCESS_CODE VARCHAR2 25 Interface record status
ACTION VARCHAR2 25 Interface action: ADD, NEW
RATE_TYPE_CODE VARCHAR2 30 Rate type code
EFFECTIVE_DATE DATE Date document becomes effective
EXPIRATION_DATE DATE Date document expires
FIRM_FLAG VARCHAR2 25 Indicates whether you want to prevent this purchase order from being automatically rescheduled by your manufacturing application
VENDOR_DOC_NUM VARCHAR2 25 Supplier document number
REFERENCE_NUM VARCHAR2 120 Needed for 832 EDI transaction
LOAD_SOURCING_RULES_FLAG VARCHAR2 1 Needed for 832 EDI transaction
VENDOR_NUM VARCHAR2 30 Needed for 832 EDI transaction
WF_GROUP_ID NUMBER Identifies the records that were created during one run of the PO Create Documents workflow
PCARD_ID NUMBER 18 Unique identifer for the procurement card used for the order
PAY_ON_CODE VARCHAR2 25 Indicator of whether the purchase order will be paid upon receipt
CONSUME_REQ_DEMAND_FLAG VARCHAR2 1 Used by Oracle Sourcing- Tells whether user wanted to consume the req demand when creating a blanket
SHIPPING_CONTROL VARCHAR2 30 Indicator of who is responsible for arranging transportation
ENCUMBRANCE_REQUIRED_FLAG VARCHAR2 1 -- FPJ Encumbrance Rewrite project
AMOUNT_TO_ENCUMBER NUMBER -- FPJ Encumbrance Rewrite project
CHANGE_SUMMARY VARCHAR2 2000 Description of changes made. This information is entered by the Buyer when submitting the document for approval and may be used in generating an amendment document.
BUDGET_ACCOUNT_SEGMENT1 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT2 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT3 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT4 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT5 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT6 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT7 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT8 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT9 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT10 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT11 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT12 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT13 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT14
VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT15 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT16 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT17 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT18 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT19 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT20 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT21 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT22 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT23 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT24 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT25 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT26 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT27 VARCHAR225 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT28 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT29 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_SEGMENT30 VARCHAR2 25 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT VARCHAR2 2000 FPJ Encumbrance Rewrite.
BUDGET_ACCOUNT_ID NUMBER 18 FPJ Encumbrance Rewrite.
GL_ENCUMBERED_DATE DATE FPJ Encumbrance Rewrite.
GL_ENCUMBERED_PERIOD_NAME VARCHAR2 15 FPJ Encumbrance Rewrite.
CREATED_LANGUAGE VARCHAR2 4 Language code in which the GBPA was created.
CPA_REFERENCE NUMBER 18 This is to support the iP catalog upgrade for items that had a CPA reference prior to Release 12. This column is just for information purpose.
PROCESSING_ID NUMBER Identifier for records to be processed by a certain PDOI run
PROCESSING_ROUND_NUM NUMBER The round number within each PDOI process
ORIGINAL_PO_HEADER_IDNUMBER Document to be replaced. Used only when action='REPLACE'
STYLE_ID NUMBER 18 Determines the Purchasing Style of the document.
STYLE_DISPLAY_NAME VARCHAR2 240 Style Display Name
OBJECT_VERSION_NUMBER NUMBER 9 Yes Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried.
DOCUMENT_STATUS VARCHAR2 25 DOCUMENT_STATUS
DEFAULT_TAXATION_COUNTRY VARCHAR2 2 The country for taxation for tax calculation purposes.
TAX_DOCUMENT_SUBTYPE VARCHAR2 240 Country specific classification of the document used for tax calculation and reporting purposes.
MERGE_REQUEST_ID NUMBER 18 Merge Request Id Unique Identifier(References POZ_SUPPLIER_MERGE_REQUESTS.merge_request_id)
MODE_OF_TRANSPORT VARCHAR2 80 Type of transportation used to ship the product, such as truck, air or boat. This column stores the displayed value.
MODE_OF_TRANSPORT_CODE VARCHAR2 30 Type of transportation used to ship the product, such as truck, air or boat. It is mapped to the column MODE_OF_TRANSPORT on other PO_HEADERS_% table
SERVICE_LEVEL VARCHAR2 80 Priority of transportation that affects how quickly goods are transported. This column stores the displayed value.
SERVICE_LEVEL_CODE VARCHAR2 30 Priority of transportation that affects how quickly goods are transported. It is mapped to the column SERVICE_LEVEL on other PO_HEADERS_% table
FIRST_PTY_REG_ID NUMBER 18 First Party Tax Registration ID for the transaction.
FIRST_PTY_REG_NUM VARCHAR2 50 First Party Tax Registration Number for the transaction.
THIRD_PTY_REG_ID NUMBER 18 Third Party Tax Registration ID for the transaction.
THIRD_PTY_REG_NUM VARCHAR2 50 Third Party Tax Registration Number for the transaction.
DISABLE_AUTOSOURCING_FLAG VARCHAR2 1 Disable Automatic Sourcing Flag
PUNCHOUT_SOURCING_ONLY_FLAG VARCHAR2 1 Automatic Sourcing of Punchout Requests Only
CONSIGNMENT_TERMS_ENABLED_FLAG VARCHAR2 1 Consignment Terms Enabled Flag
AGENT_EMAIL_ADDRESSVARCHAR2 240 AGENT_EMAIL_ADDRESS

Foreign Keys

Foreign keys that are associated with the table.
TableForeign TableForeign Key Column
POR_DOC_UPLOAD_JOBS PO_HEADERS_INTERFACE INTERFACE_HEADER_ID

Indexes


Indexes that are associated with the table.
IndexUniquenessTablespaceColumn
PO_HEADERS_INTERFACE_N1 Non Unique Default PROCESSING_ID, PROCESSING_ROUND_NUM
PO_HEADERS_INTERFACE_N2 Non Unique Default BATCH_ID
PO_HEADERS_INTERFACE_N3 Non Unique DefaultVENDOR_DOC_NUM
PO_HEADERS_INTERFACE_N4 Non Unique Default PO_HEADER_ID
PO_HEADERS_INTERFACE_U1 Unique Default INTERFACE_HEADER_ID

原文出處:PO_HEADERS_INTERFACE
冷日
(冷日)
Webmaster
  • 註冊日: 2008/2/19
  • 來自:
  • 發表數: 15763
[轉貼]利用API產生PO,已測試成功
利用API產生PO,已測試成功
2014年10月06日 17:48:34
/* Formatted on 2014-10-6 16:33:22 (QP5 v5.115.810.9015) */
DECLARE
   -- Local variables here
   I              INTEGER;
   L_HEADER_REC   PO.PO_HEADERS_INTERFACE%ROWTYPE;
   L_LINE_REC     PO.PO_LINES_INTERFACE%ROWTYPE;
   L_DIST_REC     PO.PO_DISTRIBUTIONS_INTERFACE%ROWTYPE;
   L_ERROR_MESSAGE VARCHAR2(4000);
BEGIN
   -- Test statements here
   MO_GLOBAL.INIT('PO');
   MO_GLOBAL.SET_POLICY_CONTEXT('S', 85);
   FND_GLOBAL.APPS_INITIALIZE(USER_ID     => 11236
                             ,RESP_ID     => 50623
                             ,RESP_APPL_ID => 201
                             );
   --PO頭
   L_HEADER_REC.ORG_ID := 85;

   SELECT PO.PO_HEADERS_INTERFACE_S.NEXTVAL
     INTO L_HEADER_REC.INTERFACE_HEADER_ID
     FROM DUAL;

   dbms_output.PUT_LINE('HEADER_ID:' || L_HEADER_REC.INTERFACE_HEADER_ID);
   L_HEADER_REC.BATCH_ID := L_HEADER_REC.INTERFACE_HEADER_ID;
   L_HEADER_REC.PROCESS_CODE := 'PENDING';
   L_HEADER_REC.ACTION := 'ORIGINAL';
   L_HEADER_REC.DOCUMENT_TYPE_CODE := 'STANDARD';                 --'BLANKET';
   --l_iface_rec.document_subtype   := NULL;
   L_HEADER_REC.APPROVAL_STATUS := 'APPROVED';
   L_HEADER_REC.VENDOR_ID := 253;
   L_HEADER_REC.VENDOR_SITE_ID := 2708;
   L_HEADER_REC.AGENT_ID := 10750;
   L_HEADER_REC.CURRENCY_CODE := 'CNY';                          --USD是本位幣
   L_HEADER_REC.RATE_TYPE_CODE := NULL;
   L_HEADER_REC.RATE_DATE :=NULL;
   L_HEADER_REC.RATE := NULL;

   INSERT INTO PO.PO_HEADERS_INTERFACE
       VALUES L_HEADER_REC;

   --PO行
   L_LINE_REC.INTERFACE_HEADER_ID := L_HEADER_REC.INTERFACE_HEADER_ID;

   SELECT PO.PO_LINES_INTERFACE_S.NEXTVAL
     INTO L_LINE_REC.INTERFACE_LINE_ID
     FROM DUAL;

   L_LINE_REC.LINE_NUM := 1;
  -- L_LINE_REC.LINE_TYPE := 'GOODS';--'貨物';
   L_LINE_REC.ACTION := 'ORIGINAL';
   L_LINE_REC.PROCESS_CODE := 'PENDING';
   L_LINE_REC.ITEM_ID := 51277;
   L_LINE_REC.UOM_CODE := 'EA';
   L_LINE_REC.UNIT_PRICE := 60;
   L_LINE_REC.QUANTITY := 10;
   L_LINE_REC.NEED_BY_DATE := SYSDATE;
   L_LINE_REC.SHIP_TO_ORGANIZATION_ID := 104;
   L_LINE_REC.SHIP_TO_LOCATION_ID := 147;

   INSERT INTO PO.PO_LINES_INTERFACE
       VALUES L_LINE_REC;

   -- PO分配
   L_DIST_REC.INTERFACE_HEADER_ID := L_HEADER_REC.INTERFACE_HEADER_ID;
   L_DIST_REC.INTERFACE_LINE_ID := L_LINE_REC.INTERFACE_LINE_ID;

   SELECT PO.PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL
     INTO L_DIST_REC.INTERFACE_DISTRIBUTION_ID
     FROM DUAL;

   L_DIST_REC.QUANTITY_ORDERED := 10;

   INSERT INTO PO.PO_DISTRIBUTIONS_INTERFACE
       VALUES L_DIST_REC;

   PO_DOCS_INTERFACE_SV5.PROCESS_PO_HEADERS_INTERFACE(
      X_SELECTED_BATCH_ID => L_HEADER_REC.BATCH_ID
     ,X_BUYER_ID  => NULL
     ,X_DOCUMENT_TYPE => L_HEADER_REC.DOCUMENT_TYPE_CODE
     ,X_DOCUMENT_SUBTYPE => L_HEADER_REC.DOCUMENT_SUBTYPE
     ,X_CREATE_ITEMS => 'N'
     ,X_CREATE_SOURCING_RULES_FLAG => NULL
     ,X_REL_GEN_METHOD => NULL
     ,X_APPROVED_STATUS => L_HEADER_REC.APPROVAL_STATUS
     ,X_COMMIT_INTERVAL => 1
     ,X_PROCESS_CODE => 'PENDING'
     ,X_INTERFACE_HEADER_ID => NULL
     ,X_ORG_ID_PARAM => NULL
     ,X_GA_FLAG   => NULL
   );

   SELECT MAX(PIE.ERROR_MESSAGE)
     INTO L_ERROR_MESSAGE
     FROM PO_INTERFACE_ERRORS PIE
    WHERE PIE.INTERFACE_HEADER_ID = L_HEADER_REC.INTERFACE_HEADER_ID;

   dbms_output.PUT_LINE('頭錯誤信息:' || L_ERROR_MESSAGE);
END;



--
select * FROM PO_HEADERS_INTERFACE
WHERE PO_HEADER_ID =26074620

select * FROM PO_HEADERS_ALL
WHERE CREATED_BY = 11236


原文出處:利用API产生PO,已测试成功 - CSDN博客
前一個主題 | 下一個主題 | 頁首 | | |



Powered by XOOPS 2.0 © 2001-2008 The XOOPS Project|