|
發表者 |
討論內容 |
冷日 (冷日) |
發表時間:2018/8/9 14:32 |
- Webmaster

- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]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博客
|
|
|
冷日 (冷日) |
發表時間:2018/8/9 14:34 |
- Webmaster

- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]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博客
|
|
冷日 (冷日) |
發表時間:2018/8/9 14:37 |
- Webmaster

- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]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博客
|
|
|
冷日 (冷日) |
發表時間:2018/8/18 13:03 |
- Webmaster

- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]Oracle Purchase Order Single Insert Script Process
Oracle Purchase Order Single Insert Script Process 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
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 |
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 |
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. |
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
|
|
冷日 (冷日) |
發表時間:2018/8/18 13:08 |
- Webmaster

- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]PL/SQL APIs for Concurrent Processing
PL/SQL APIs for Concurrent Processing OverviewThis 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 PackageThis package is used for submitting sub-requests from PL/SQL concurrent programs. FND_CONC_GLOBAL.REQUEST_DATA
Summary | function FND_CONC_GLOBAL.REQUEST_DATA return varchar2; |
Description | FND_CONC_GLOBAL.REQUEST_DATA retrieves the value of the REQUEST_DATA global. | FND_CONC_GLOBAL.SET_REQ_GLOBALS 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); |
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 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 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) 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. |
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. |
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: 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)
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. |
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. |
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) 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. |
status | The status to set the concurrent program to. Either NORMAL, WARNING, or ERROR. | message | An optional message. | FND_FILE: PL/SQL File I/OThe 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 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. |
which | Log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT. | buff | Text to write. | FND_FILE.PUT_LINE 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. |
which | Log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT. | buff | Text to write. | ExampleUsing 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
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. |
which | Log file or output file. Use either FND_FILE.LOG or FND_FILE.OUTPUT. | lines | Number of line terminators to write. | ExampleTo write two new line characters:
fnd_file.new_line(FND_FILE.LOG,2);
FND_FILE.PUT_NAMES
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;
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
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 LoadersThe 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 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 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. |
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 Summary |
procedure FND_PROGRAM.DELETE_EXECUTABLE (executable IN varchar2, application IN varchar2);
|
Description | Use this procedure to delete a concurrent program executable. An executable that is assigned to a concurrent program cannot be deleted. |
executable | The short name of the executable to delete. | application | The short name of the executable's application, for example 'FND'. | FND_PROGRAM.REGISTER
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. |
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
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. |
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 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. 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 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. |
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 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. |
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 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. |
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 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. |
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
Summary |
procedure FND_PROGRAM.DELETE_GROUP (group IN VARCHAR2, application IN VARCHAR2);
|
Description | Use this procedure to delete a request group. |
request_group | The name of the request group to delete. | application | The application that owns the request group. | FND_PROGRAM.ADD_TO_GROUP 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. |
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
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. |
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
Summary |
function FND_PROGRAM.PROGRAM_EXISTS (program IN VARCHAR2, application IN VARCHAR2) return boolean;
|
Description | Returns TRUE if a concurrent program exists. |
program | The short name of the program | application | Application short name of the program. | FND_PROGRAM.PARAMETER_EXISTS Summary |
function FND_PROGRAM.PARAMETER_EXISTS (program_short_name IN VARCHAR2, application IN VARCHAR2, parameteR IN VARCHAR2) return boolean;
|
Description | Returns TRUE if a program parameter exists. |
program | The short name of the program | application | Application short name of the program. | parameter | Name of the parameter. | FND_PROGRAM.INCOMPATIBILITY_EXISTS 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. |
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 Summary |
function FND_PROGRAM.EXECUTABLE_EXISTS (executable_short_name IN VARCHAR2, application IN VARCHAR2) return boolean;
|
Description | Returns TRUE if program executable exists. |
program | The name of the executable. | application | Application short name of the executable. | FND_PROGRAM.REQUEST_GROUP_EXISTS Summary |
function FND_PROGRAM.REQUEST_GROUP_EXISTS (request_group IN VARCHAR2, application IN VARCHAR2) return boolean;
|
Description | Returns TRUE if request group exists. |
program | The name of the executable. | application | Application short name of the request group. | FND_PROGRAM.PROGRAM_IN_GROUP 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. |
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 Syntax |
procedure FND_PROGRAM_ENABLE_PROGRAM (short_name IN VARCHAR2, application IN VARCHAR2, ENABLED IN VARCHAR2);
|
Description | Use this procedure to enable or disable a concurrent program. |
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)
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. |
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) 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. |
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) 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. 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) 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. 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) 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. | FND_REQUEST.SET_MODE (Server) 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. 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 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. |
name | The name of the parameter of the request's concurrent program. | FND_REQUEST_INFO.GET_PARAM_INFO 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. |
param_num | The number of the parameter of the request's concurrent program. | FND_REQUEST_INFO.GET_PROGRAM 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. |
prog_name | The name of the concurrent program. | prog_app_name | The concurrent program's application short name. | FND_REQUEST_INFO.GET_PARAMETER 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. |
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 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 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. |
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 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. |
request_set | The short name of the request set to delete. | application | The application that owns the request set. | FND_SET.ADD_PROGRAM 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. |
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 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. |
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
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. |
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
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. |
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 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. |
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 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. |
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 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. 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 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. |
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 Set X is incompatible with program Y:
fnd_set.incompatibility(request_set=>'X' application=>'APPX' inc_prog_short_name=>'Y', inc_prog_application=>'APPY');
Set X is incompatible with set Y:
fnd_set.incompatibility(request_set=>'X', application=>'APPX', inc_request_set=>'Y', inc_set_application=>'APPY');
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);
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 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. |
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 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. |
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
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. |
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 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. |
db_trigger | Set to TRUE if the request set is submitted from a database trigger. | FND_SUBMIT.SET_REL_CLASS_OPTIONS 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. |
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 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. |
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 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. |
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
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. |
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 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. |
printer | Printer name where the request output should be sent. | copies | Number of copies to print. | FND_SUBMIT.ADD_NOTIFICATION 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. | FND_SUBMIT.SET_NLS_OPTIONS 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. |
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
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. |
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 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. |
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
|
|
冷日 (冷日) |
發表時間:2018/8/18 13:12 |
- Webmaster

- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]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.
First Timer, know these first
Before you start, you need to understand the database objects which play critical role. 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 |
Steps by Steps Know what is getting ining Data into Purchase Order Interface Tables - 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
- 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. 
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 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 |
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.
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.
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
|
|
冷日 (冷日) |
發表時間:2018/8/18 13:18 |
- Webmaster

- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]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.
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 - 博客园
|
|
|
冷日 (冷日) |
發表時間:2018/8/18 13:21 |
- Webmaster

- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]Oracle Tables and Views for Oracle Procurement Cloud : PO_HEADERS_INTERFACE
PO_HEADERS_INTERFACEPO_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 Primary KeyDetails of the primary key for the table.Name | Columns |
---|
PO_HEADERS_INTERFACE_PK | INTERFACE_HEADER_ID |
---|
ColumnsColumns that are associated with the table.Name | Database | Length | Precision | Not Null | Comments | Flexfield 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 | | | Yes |
Who 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 | VARCHAR2 |
25 | | | 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_ID |
NUMBER | | | | 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_ADDRESS |
VARCHAR2 | 240 | | | AGENT_EMAIL_ADDRESS | |
---|
Foreign KeysForeign keys that are associated with the table.Table | Foreign Table | Foreign Key Column |
---|
POR_DOC_UPLOAD_JOBS | PO_HEADERS_INTERFACE | INTERFACE_HEADER_ID |
---|
IndexesIndexes that are associated with the table.Index | Uniqueness | Tablespace | Column |
---|
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 | Default |
VENDOR_DOC_NUM |
---|
PO_HEADERS_INTERFACE_N4 | Non Unique | Default | PO_HEADER_ID |
---|
PO_HEADERS_INTERFACE_U1 | Unique | Default | INTERFACE_HEADER_ID |
---|
原文出處:PO_HEADERS_INTERFACE
|
|
冷日 (冷日) |
發表時間:2018/8/18 13:24 |
- Webmaster

- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]利用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博客
|
|
|