oracle ebs 采购订单导入 来源参考 Oracle metalink
来源:互联网 发布:javascript var json 编辑:程序博客网 时间:2024/03/29 05:30
/* ======================================================================================================= Name : Create Standard Purchase Order Purpose : This plsql program is used to insert data into PO interface table to create Standard Purchase Order through PDOI: Import Standard Purchase Order Concurrent Program History:Date Action By 15-FEB-2010 Created Supriya ======================================================================================================= */DECLARE--------------------------------------------------------------------------------------Define Mandatory column variables to insert into interface tables. Values to all-- Variables need to be intialized with required data before running the script.-----------------------------------------------------------------------------------------------------------------------------------------------------------------------Header level information-----------------------------------------------------------------------------------This provides batch id which is used to run particular set of datal_batch_id po_headers_interface.batch_id%TYPE := 100;--Contains Organization IDl_org_id po_headers_interface.org_id%TYPE := 204;--Contains Agent ID for which PO is createdl_agent_id po_headers_interface.agent_id%TYPE := 25;--Contains Vendor IDl_vendor_id po_headers_interface.vendor_id%TYPE := 21;--Contains Vendor Site ID l_vendor_site_id po_headers_interface.vendor_site_id%TYPE := 41;--Contains Ship to Location IDl_ship_to_location_id po_headers_interface.ship_to_location_id%TYPE := 204;--Contains Bill to Location IDl_bill_to_location_id po_headers_interface.bill_to_location_id%TYPE := 204;--Contains Ship to Organization IDl_ship_to_org_id po_line_locations_interface.ship_to_organization_id%TYPE := 204;-- Contains Attribute value which can be your username -- which can be used to check records inserted by the userl_attribute1 po_headers_interface.attribute1%TYPE := 'SBONTALA';-----------------------------------------------------------------------------------Line level information-----------------------------------------------------------------------------------Contains Line type l_line_type po_lines_interface.line_type%TYPE := 'Goods';--Contains Line informationl_item po_lines_interface.item%TYPE := 'AS10000';--Specifies UOM code l_uom_code po_lines_interface.uom_code%TYPE := 'Ea';--Contains Quantity required of the iteml_quantity po_lines_interface.quantity%TYPE := 100;--Contains Unit price of the Iteml_unit_price po_lines_interface.unit_price%TYPE:= 100;--Contains charge account ID for the item distributionl_charge_account_id po_distributions_interface.charge_account_id%TYPE := 13402;-- Specifies number of Purchase order to be createdl_header_count NUMBER := 1;--Specifies number of lines to be created per POl_line_count NUMBER := 2;--Specifies number of shipments to be created per linel_shipment_count NUMBER := 2;--Specifies number of distributions to be created per shipmentl_dist_count NUMBER := 2;--To track progess l_progress VARCHAR2(10) ;BEGIN--Header LoopFOR hdr_cnt IN 1..L_header_countLOOP ---- Inserting into header interface table l_progress := '001'; Insert into PO.PO_HEADERS_INTERFACE (INTERFACE_HEADER_ID, BATCH_ID, PROCESS_CODE, ACTION, ORG_ID, DOCUMENT_TYPE_CODE, CURRENCY_CODE, AGENT_ID, VENDOR_ID, VENDOR_SITE_ID, SHIP_TO_LOCATION_ID, BILL_TO_LOCATION_ID, ATTRIBUTE1, CREATION_DATE) VALUES ( po_headers_interface_s.NEXTVAL,--- INTERFACE_HEADER_ID, l_batch_id, 'PENDING',--- PROCESS_CODE, 'ORIGINAL', --- ACTION, l_org_id,--- ORG_ID, 'STANDARD',--- DOCUMENT_TYPE_CODE, 'USD', --- CURRENCY_CODE, l_agent_id, l_vendor_id, l_vendor_Site_id, l_ship_to_location_id, l_bill_to_location_id, l_attribute1, SYSDATE );--- CREATION_DATE, ---Line Loop FOR line_cnt IN 1..l_line_count LOOP --- Inserting into Lines interface table l_progress := '002';Insert into PO.PO_LINES_INTERFACE (INTERFACE_LINE_ID, INTERFACE_HEADER_ID, ACTION, LINE_NUM, LINE_TYPE, ITEM, UOM_CODE, QUANTITY, UNIT_PRICE, SHIP_TO_ORGANIZATION_ID, SHIP_TO_LOCATION_ID, NEED_BY_DATE, PROMISED_DATE, CREATION_DATE, LINE_LOC_POPULATED_FLAG) Values (po_lines_interface_s.nextval, --- INTERFACE_LINE_ID, po_headers_interface_s.currval,--- INTERFACE_HEADER_ID, 'ADD',--- ACTION, line_cnt,--- LINE_NUM, l_line_type, l_item, l_uom_code, l_quantity, l_unit_price, l_ship_to_org_id, l_ship_to_location_id, SYSDATE,--- NEED_BY_DATE, SYSDATE,--- PROMISED_DATE, SYSDATE,--- CREATION_DATE, 'Y');--- LINE_LOC_POPULATED_FLAG, ----Shipment Loop FOR ship_cnt IN 1..l_shipment_count LOOP ---Inserting into Line Locations Interface table l_progress := '003'; Insert into PO.PO_LINE_LOCATIONS_INTERFACE (INTERFACE_LINE_LOCATION_ID, INTERFACE_HEADER_ID, INTERFACE_LINE_ID, SHIPMENT_TYPE, SHIPMENT_NUM, SHIP_TO_ORGANIZATION_ID, SHIP_TO_LOCATION_ID, NEED_BY_DATE, PROMISED_DATE, QUANTITY, CREATION_DATE) Values (po_line_locations_interface_s.nextval,--- INTERFACE_LINE_LOCATION_ID, po_headers_interface_s.currval,--- INTERFACE_HEADER_ID, po_lines_interface_s.currval, --- INTERFACE_LINE_ID, 'STANDARD',--- SHIPMENT_TYPE, ship_cnt,--- SHIPMENT_NUM, l_ship_to_org_id,--- SHIP_TO_ORGANIZATION_ID, l_ship_to_location_id,--- SHIP_TO_LOCATION_ID, SYSDATE,--- NEED_BY_DATE, SYSDATE,--- PROMISED_DATE, l_quantity/l_shipment_count,--- QUANTITY, SYSDATE);--- CREATION_DATE, --Distribution Loop FOR dist_cnt IN 1..l_dist_count LOOP ---Inserting into Distribution Interface table l_progress := '004'; Insert into PO.PO_DISTRIBUTIONS_INTERFACE (INTERFACE_HEADER_ID, INTERFACE_LINE_ID, INTERFACE_LINE_LOCATION_ID, INTERFACE_DISTRIBUTION_ID, DISTRIBUTION_NUM, ORG_ID, QUANTITY_ORDERED, CHARGE_ACCOUNT_ID, CREATION_DATE) Values (po_headers_interface_s.currval, ---INTERFACE_HEADER_ID, po_lines_interface_s.currval, --- INTERFACE_LINE_ID, po_line_locations_interface_s.currval, ---INTERFACE_LINE_LOCATION_ID, po.po_distributions_interface_s.NEXTVAL,--- INTERFACE_DISTRIBUTION_ID, dist_cnt, --- DISTRIBUTION_NUM, l_org_id, l_quantity/(l_shipment_count*l_dist_count),--- QUANTITY_ORDERED, l_charge_account_id, SYSDATE); END LOOP; ---End of Distribution Loop END LOOP;---End of shipment Loop END LOOP;---End of Line Loop END LOOP;---End of Header Loop COMMIT;EXCEPTIONWHEN OTHERS THENdbms_output.put_line('Error while inserting data at :'||l_progress||'---'||SQLCODE||SQLERRM);END;
/* ======================================================================================================= Name : Update Standard Purchase Order -- Adding Lines to the Purchase Order Purpose : This plsql program is used to Add lines to existing standard purchase order through PDOI : Import standard Purchase Order concurrent program ======================================================================================================= */DECLARE--------------------------------------------------------------------------------------Define Mandatory column variables to insert into interface tables. Values to all-- Variables need to be intialized with required data before running the script.-----------------------------------------------------------------------------------------------------------------------------------------------------------------------Header level information-----------------------------------------------------------------------------------This provides batch id which is used to run particular set of datal_batch_id po_headers_interface.batch_id%TYPE := 100;--Contains Organization IDl_org_id po_headers_interface.org_id%TYPE := 204;--Contains Ship to Location IDl_ship_to_location_id po_headers_interface.ship_to_location_id%TYPE := 204;--Contains Bill to Location IDl_bill_to_location_id po_headers_interface.bill_to_location_id%TYPE := 204;--Contains Ship to Organization IDl_ship_to_org_id po_line_locations_interface.ship_to_organization_id%TYPE := 204;-- Contains Attribute value which can be your username -- which can be used to check records inserted by the userl_attribute1 po_headers_interface.attribute1%TYPE := 'SBONTALA';--Specifies PO header Id to be updatedl_po_header_id po_headers_interface.po_header_id%TYPE := 198441;-----------------------------------------------------------------------------------Line level information-----------------------------------------------------------------------------------Contains Line type l_line_type po_lines_interface.line_type%TYPE := 'Goods';--Contains Item informationl_item po_lines_interface.item%TYPE := 'f12000';--Specifies UOM code l_uom_code po_lines_interface.uom_code%TYPE := 'Ea';--Contains Quantity required of the iteml_quantity po_lines_interface.quantity%TYPE := 100;--Contains Unit price of the Iteml_unit_price po_lines_interface.unit_price%TYPE:= 100;--Contains charge account ID for the item distributionl_charge_account_id po_distributions_interface.charge_account_id%TYPE := 13402;--Contains the line number from which lines are added to the purchase orderl_line_num NUMBER;--Specifies number of lines to be created per POl_line_count NUMBER := 1;--Specifies number of shipments to be created per linel_shipment_count NUMBER := 1;--Specifies number of distributions to be created per shipmentl_dist_count NUMBER := 1;--To track progess l_progress VARCHAR2(10) ;BEGIN ---- Inserting into header interface table l_progress := '001'; Insert into PO.PO_HEADERS_INTERFACE (INTERFACE_HEADER_ID, BATCH_ID, PROCESS_CODE, ACTION, po_header_id, ATTRIBUTE1, CREATION_DATE) VALUES ( po_headers_interface_s.NEXTVAL,---INTERFACE_HEADER_ID, l_batch_id, 'PENDING',---PROCESS_CODE, 'UPDATE', ---ACTION, l_po_header_id, l_attribute1, SYSDATE );---CREATION_DATE, --- Fetching the maximum line number from lines table for the given header id FOR i IN (SELECT MAX(line_num) cnt FROM po_lines_all WHERE po_header_id = l_po_header_id) LOOP l_line_num := i.cnt; END LOOP ; --Line loop FOR line_cnt IN 1..l_line_count LOOP --Incrementing the line number l_line_num := l_line_num + 1; --- Inserting into Lines interface table l_progress := '002';Insert into PO.PO_LINES_INTERFACE (INTERFACE_LINE_ID, INTERFACE_HEADER_ID, ACTION, LINE_NUM, LINE_TYPE, ITEM, UOM_CODE, QUANTITY, UNIT_PRICE, SHIP_TO_ORGANIZATION_ID, SHIP_TO_LOCATION_ID, NEED_BY_DATE, PROMISED_DATE, CREATION_DATE, LINE_LOC_POPULATED_FLAG) Values (po_lines_interface_s.nextval, ---INTERFACE_LINE_ID, po_headers_interface_s.currval, ---INTERFACE_HEADER_ID, 'ADD', ---ACTION, l_line_num, l_line_type, l_item, l_uom_code, l_quantity, l_unit_price, l_ship_to_org_id, l_ship_to_location_id, SYSDATE, --- NEED_BY_DATE, SYSDATE, --- PROMISED_DATE, SYSDATE, --- CREATION_DATE, 'Y'); --- LINE_LOC_POPULATED_FLAG, ----Shipment Loop FOR ship_cnt IN 1..l_shipment_count LOOP ---Inserting into Line Locations Interface table l_progress := '003'; Insert into PO.PO_LINE_LOCATIONS_INTERFACE (INTERFACE_LINE_LOCATION_ID, INTERFACE_HEADER_ID, INTERFACE_LINE_ID, SHIPMENT_TYPE, SHIPMENT_NUM, SHIP_TO_ORGANIZATION_ID, SHIP_TO_LOCATION_ID, NEED_BY_DATE, PROMISED_DATE, QUANTITY, CREATION_DATE) Values (po_line_locations_interface_s.nextval,---INTERFACE_LINE_LOCATION_ID, po_headers_interface_s.currval,--- INTERFACE_HEADER_ID, po_lines_interface_s.currval,--- INTERFACE_LINE_ID, 'STANDARD',--- SHIPMENT_TYPE, ship_cnt,--- SHIPMENT_NUM, l_ship_to_org_id,--- SHIP_TO_ORGANIZATION_ID, l_ship_to_location_id,--- SHIP_TO_LOCATION_ID, SYSDATE,--- NEED_BY_DATE, SYSDATE, --- PROMISED_DATE, l_quantity/l_shipment_count,--- QUANTITY, SYSDATE);--- CREATION_DATE, --Distribution Loop FOR dist_cnt IN 1..l_dist_count LOOP ---Inserting into Distribution Interface table l_progress := '004'; Insert into PO.PO_DISTRIBUTIONS_INTERFACE (INTERFACE_HEADER_ID, INTERFACE_LINE_ID, INTERFACE_LINE_LOCATION_ID, INTERFACE_DISTRIBUTION_ID, DISTRIBUTION_NUM, ORG_ID, QUANTITY_ORDERED, CHARGE_ACCOUNT_ID, CREATION_DATE) Values (po_headers_interface_s.currval, ---INTERFACE_HEADER_ID, po_lines_interface_s.currval, --- INTERFACE_LINE_ID, po_line_locations_interface_s.currval, --- INTERFACE_LINE_LOCATION_ID, po.po_distributions_interface_s.NEXTVAL, --- INTERFACE_DISTRIBUTION_ID, dist_cnt, --- DISTRIBUTION_NUM, l_org_id, --- ORG_ID, l_quantity/(l_shipment_count*l_dist_count), --- QUANTITY_ORDERED, l_charge_account_id, --- CHARGE_ACCOUNT_ID SYSDATE); END LOOP; ---End of Distribution Loop END LOOP;---End of shipment Loop END LOOP;---End of Line Loop COMMIT;EXCEPTIONWHEN OTHERS THENdbms_output.put_line('Error while inserting data at :'||l_progress||SQLCODE||SQLERRM);END;
/* ======================================================================================================= Name : Create Blanket Purchase Agreement Purpose : This plsql program is used to create BPA through PDOI : Import price catalog concurrent program ======================================================================================================= */DECLARE--------------------------------------------------------------------------------------Define Mandatory column variables to insert into interface tables. Values to all-- Variables need to be intialized with required data before running the script.-----------------------------------------------------------------------------------------------------------------------------------------------------------------------Header level information-----------------------------------------------------------------------------------This provides batch id which is used to run particular set of datal_batch_id po_headers_interface.batch_id%TYPE := 100;--Contains Organization IDl_org_id po_headers_interface.org_id%TYPE := 204;--Contains Agent ID for which PO is createdl_agent_id po_headers_interface.agent_id%TYPE := 25;--Contains Vendor IDl_vendor_id po_headers_interface.vendor_id%TYPE := 21;--Contains Vendor Site ID l_vendor_site_id po_headers_interface.vendor_site_id%TYPE := 41;--Contains Ship to Location IDl_ship_to_location_id po_headers_interface.ship_to_location_id%TYPE := 204;--Contains Bill to Location IDl_bill_to_location_id po_headers_interface.bill_to_location_id%TYPE := 204;--Contains Ship to Organization IDl_ship_to_org_id po_line_locations_interface.ship_to_organization_id%TYPE := 204;-- Contains Attribute value which can be your username -- which can be used to check records inserted by the userl_attribute1 po_headers_interface.attribute1%TYPE := 'SBONTALA';-----------------------------------------------------------------------------------Line level information-----------------------------------------------------------------------------------Contains Line type l_line_type po_lines_interface.line_type%TYPE := 'Goods';--Contains Item informationl_item po_lines_interface.item%TYPE := 'AS10000';--Specifies UOM code l_uom_code po_lines_interface.uom_code%TYPE := 'Ea';--Contains Unit price of the Iteml_unit_price po_lines_interface.unit_price%TYPE:= 100;-- Specifies number of Purchase order to be createdl_header_count NUMBER := 1;--Specifies number of lines to be created per POl_line_count NUMBER := 1;--To track progess l_progress VARCHAR2(10) ;BEGIN--Header LoopFOR hdr_cnt IN 1..L_header_countLOOP ---- Inserting into header interface table l_progress := '001'; Insert into PO.PO_HEADERS_INTERFACE (INTERFACE_HEADER_ID, BATCH_ID, PROCESS_CODE, ACTION, ORG_ID, DOCUMENT_TYPE_CODE, CURRENCY_CODE, AGENT_ID, VENDOR_ID, VENDOR_SITE_ID, SHIP_TO_LOCATION_ID, BILL_TO_LOCATION_ID, ATTRIBUTE1, CREATION_DATE) VALUES ( po_headers_interface_s.NEXTVAL,---INTERFACE_HEADER_ID, l_batch_id, 'PENDING',--- PROCESS_CODE, 'ORIGINAL', --- ACTION, l_org_id, 'BLANKET',--- DOCUMENT_TYPE_CODE, 'USD', --- CURRENCY_CODE, l_agent_id, l_vendor_id, l_vendor_Site_id, l_ship_to_location_id, l_bill_to_location_id, l_attribute1, SYSDATE );--- CREATION_DATE, ---Line Loop FOR line_cnt IN 1..l_line_count LOOP l_progress := '002'; --- Inserting into Lines interface tableInsert into PO.PO_LINES_INTERFACE (INTERFACE_LINE_ID, INTERFACE_HEADER_ID, ACTION, LINE_NUM, LINE_TYPE, ITEM, UOM_CODE, UNIT_PRICE, SHIP_TO_ORGANIZATION_ID, SHIP_TO_LOCATION_ID, NEED_BY_DATE, PROMISED_DATE, CREATION_DATE, LINE_LOC_POPULATED_FLAG) Values (po_lines_interface_s.nextval, ---INTERFACE_LINE_ID, po_headers_interface_s.currval, ---INTERFACE_HEADER_ID, 'ADD', --- ACTION, line_cnt, l_line_type, l_item, l_uom_code, l_unit_price, l_ship_to_org_id, --- SHIP_TO_ORGANIZATION_ID, l_ship_to_location_id, --- SHIP_TO_LOCATION_ID, SYSDATE, --- NEED_BY_DATE, SYSDATE, --- PROMISED_DATE, SYSDATE, --- CREATION_DATE, 'Y'); --- LINE_LOC_POPULATED_FLAG, END LOOP;---End of Line Loop END LOOP;---End of Header Loop COMMIT;EXCEPTIONWHEN OTHERS THENdbms_output.put_line('Error while inserting data at :'||l_progress||SQLCODE||SQLERRM);END;
/* ======================================================================================================= Name : Update Blanket Purchase Agreement -- Updating Line of existing BPA Purpose : This plsql program is used to update line in a BPA through PDOI : Import price catalog concurrent program ======================================================================================================= */DECLARE--------------------------------------------------------------------------------------Define Mandatory column variables to insert into interface tables. Values to all-- Variables need to be intialized with required data before running the script.-----------------------------------------------------------------------------------------------------------------------------------------------------------------------Header level information-----------------------------------------------------------------------------------This provides batch id which is used to run particular set of datal_batch_id po_headers_interface.batch_id%TYPE := 100;-- Contains Attribute value which can be your username -- which can be used to check records inserted by the userl_attribute1 po_headers_interface.attribute1%TYPE := 'SBONTALA';--Specifies PO header Id to be updatedl_po_header_id po_headers_interface.po_header_id%TYPE := 198445;-----------------------------------------------------------------------------------Line level information-----------------------------------------------------------------------------------Contains Unit price of the Iteml_unit_price po_lines_interface.unit_price%TYPE:= 400;--Contains the line number to be modifiedl_line_num NUMBER := 1;--To track Progressl_progress VARCHAR2(10) ;BEGIN l_progress := '001'; Insert into PO.PO_HEADERS_INTERFACE (INTERFACE_HEADER_ID, BATCH_ID, PROCESS_CODE, ACTION, PO_HEADER_ID, ATTRIBUTE1, CREATION_DATE) VALUES ( po_headers_interface_s.NEXTVAL,---INTERFACE_HEADER_ID, l_batch_id,--- BATCH_ID, 'PENDING',--- PROCESS_CODE, 'UPDATE', --- ACTION, l_po_header_id, l_attribute1,--- ATTRIBUTE1, SYSDATE );--- CREATION_DATE, l_progress := '002';Insert into PO.PO_LINES_INTERFACE (INTERFACE_LINE_ID, INTERFACE_HEADER_ID, ACTION, LINE_NUM, UNIT_PRICE, CREATION_DATE) Values (po_lines_interface_s.nextval, ---INTERFACE_LINE_ID, po_headers_interface_s.currval, ---INTERFACE_HEADER_ID, 'UPDATE', --- ACTION, l_line_num, --- LINE_NUM, l_unit_price,--- UNIT_PRICE, SYSDATE); --- CREATION_DATE COMMIT;EXCEPTIONWHEN OTHERS THENdbms_output.put_line('Error while inserting data at :'||l_progress||SQLCODE||SQLERRM);END;
l_request_id := fnd_request.submit_request('PO',
'POXPOPDOI',
'',
to_char(SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
FALSE,
NULL,
'STANDARD',
NULL,
'N',
NULL,
'INCOMPLETE', --'APPROVED',
NULL,
NULL,
lt_org_id,
NULL,
chr(0));
0 0
- oracle ebs 采购订单导入 来源参考 Oracle metalink
- EBS 采购订单接口导入
- oracle ebs 采购订单关闭之PL/SQL实现方法
- Oracle EBS 如何将申购单转换成采购订单
- oracle ebs 采购订单关闭之PL/SQL实现方法
- oracle EBS采购订单各表作用分析
- Oracle EBS:PO 采购订单类型(单据类型)
- Oracle EBS Interface/API(3)--采购订单审批
- oracle EBS采购订单各表作用分析
- oracle EBS采购订单各表作用分析
- oracle EBS采购订单各表作用分析
- Oracle PO 采购订单接口导入 分拆发运行
- ORACLE PO采购订单界面
- EBS --PO采购订单
- EBS 采购订单入库
- EBS创建采购订单
- EBS采购订单接收
- Oracle EBS发放销售订单
- 0.8.2kafka集群配置
- 最新 Android屏幕适配全攻略(官方权威适配方案)
- awakeFromNib方法中的注意
- 大型网站技术架构
- Java 集合:Collection,List,ArrayList,Vector,LinkedList(实现方式,对比)
- oracle ebs 采购订单导入 来源参考 Oracle metalink
- 投票操作管理系统
- iOS开发--NSDate与NSDateFormatter的相关用法【转】
- X86&&X64 汇编学习——内联基础
- [编程题]最大子方阵
- 常用正则表达式
- 【Struts 2】Struts2环境搭建
- 视频背景抠图(opencv)
- Canal--------阿里巴巴开源项目: 基于mysql数据库binlog的增量订阅&消费