MD070-并发程序参考

XX项目技术说明文档 文档作者:汉得创建日期:六月 18, 2010更新日期:六月 18, 2010文档编号: 当前版本:1.0Note: Title, Subject, Last Updated Date, Reference Number, and Version are marked by a Word Bookmark so that they can be easily reproduced in the header and footer of documents. When you change any of these values, be careful not to accidentally delete the bookmark. You can make bookmarks visible by selecting Tools-OptionsView and checking the Bookmarks option in the Show region.审批签字:XX项目经理汉得项目经理Note: To add additional approval lines, press Tab from the last cell in the table above.Note: You can delete any elements of this cover page that you do not need for your document. For example, Copy Number is only required if this is a controlled document and you need to track each copy that you distribute.文档控制变更记录3日期作者版本变更说明09/9/05hand1.0初版审阅姓名职位中纺项目经理张凌明汉得项目经理分发拷贝编号姓名位置/岗位1资料室主管项目资料室2项目组成员34Note: The copy numbers referenced above should be written into the Copy Number space on the cover of each distributed copy. If the document is not controlled, you can delete this table, the Note To Holders, and the Copy Number label from the cover page.目录文档控制ii概述4程序开发列表4订单自动发放程序5函数及过程5全局变量5程序输出5程序逻辑：MAIN6程序逻辑：RELEASE_ORDER9更改历史15Note: To update the table of contents, put the cursor anywhere in the table and press F9. To change the number of levels displayed, select the menu option InsertIndex and Tables, make sure the Table of Contents tab is active, and change the Number of Levels to a new value.66fe1666a1efdd28a0998e55d6d98535.pdf第15 页共 15页 概述本文主要描述订单自动发放程序的技术设计。业务需求自动发货是天狮销售业务中的一个特殊业务模型，即先创建订单类型为“自动发货计划” 的订单，每月根据会员的自动发货计划订单，自动生成订单类型为“自动发货”订单。开发要求开发一个并发程序：TIENS: 订单自动发放程序。功能要求：1 搜索符合条件的会员自动发货计划，并生成自动发货发放订单；2 该程序实现只对组织内的会员进行自动发放；3 需要支持多语言；程序开发列表订单自动发放程序并发程序：订单自动发放程序。订单自动发放程序此程序实现自动发货订单的自动生成；。该程序为并发请求，使用ORACLE PL/SQL开发实现，以存储过程的形式保存在数据库中。程序名类型TIENS_ORDER_AUTO_RELEASE_PKGPACKAGE前提：1 为会员创建自动发货计划订单；函数及过程此PACKAGE中包含如下函数及过程：函数名/过程名类型说明MAINPROCEDURE并发程序主程序RELEASE_ORDERPROCEDURE生成自动发货订单全局变量此PACKAGE中包含如下全局变量：参数名说明G_AUTOSHIP_ORDER_TYPE_ID订单类型Autoship的order_type_idG_AUTOSHIP_LINE_TYPE_ID订单类型Autoship的默认行line_type_id程序输出-自动计划发放生成 会员清单自动发货发放生成日期：_分公司名称：_ 分公司代码：_ 会员名称会员购货卡号 自动计划订单 发放订单 消息- - - - - - - - - - - - -分公司名称：_ 分公司代码：_ X天内无自动发货要求分公司名称：_ 分公司代码：_ 会员名称会员购货卡号 自动计划订单 发放订单 消息- - - - - - - - - - - - -注意：请使用MESSAGE定义所有的中文输出；程序逻辑：MAINPROCEDURE:MAIN此PROCEDURE是会员编号申请的主程序。调用参数：参数名数据类型参数类型默认值说明ERRBUFVARCHAR2OUT返回信息（并发程序要求）RETCODEVARCHAR2OUT返回代码（并发程序要求）P_SUBCOMPANY_CODEVARCHAR2IN分公司代码P_LEADTIMENUMBERIN提前发放天数流程图：伪代码：DECLAREBEGIN设置成功标志 RETCODE = 0; 初始化全局变量G_AUTOSHIP_ORDER_TYPE_ID, G_AUTOSHIP_LINE_TYPE_ID从视图TIENS_ORDER_TYPES_V中取Autoship的ORDER_TYPE_ID, DEFAULT_OUTBOUND_LINE_TYPE_ID如果G_AUTOSHIP_ORDER_TYPE_ID, G_AUTOSHIP_LINE_TYPE_ID 有一个为空 则报错；退出；输出信息：自动发货发放生成日期：_根据用户权限和参数P_SUBCOMPANY_CODE获取需要自动发放的分公司；FOR 分公司 IN (需要自动发放的分公司) LOOP输出信息：（公司代码/名称）分公司名称：_ 分公司代码：_获取该分公司下需要自动发放的订单；FOR 订单 IN (需要自动发放的订单) LOOPIF 当前行为第一行 THEN输出信息：（行标题）会员名称会员购货卡号 自动计划订单 发放订单 消息END IF;自动释放该订单调用RELEASE_ORDER参数P_ORDER_HEADER_ID = 订单.ORDER_HEADER_ID；输出信息：（发放结果）会员名称，会员购货卡号，自动计划订单，发放订单，错误消息END LOOP;IF 该公司无需要自动发放的订单 THEN输出信息：X天内无自动发货要求X 为参数P_LEADTIME；END IF;END LOOP;EXCEPTION例外处理程序；设置并发程序返回错误状态；END;根据用户权限和参数P_SUBCOMPANY_CODE获取需要自动发放的分公司SELECT FLEX_VALUE SUBCOMPANY_CODEFROM TIENS_ACCESS_SUBCOMPANY_VWHERE FLEX_VALUE = P_SUBCOMPANY_CODE OR P_SUBCOMPANY_CODE IS NULL需要自动发放的订单条件：订单类型为Autoship Schedule，状态BOOKED，订单行有效，且在发放的时间范围。SELECT OH.ORDER_HEADER_ID, OH.ORDER_NUMBER, MN.CUSTOMER_NAME, MN.ORIG_SYSTEM_REFERENCE CUSTOMER_REF_NUMBERFROM TIENS_ORDER_HEADERS OH, TIENS_ORDER_TYPES_V OT, TIENS_ORDER_LINES OL, TIENS_MEMBER_NETWORK MNWHERE OL.ORDER_HEADER_ID = OH.ORDER_HEADER_ID AND MN.CUSTOMER_ID = OH.CUSTOMER_ID AND OT.ORDER_TYPE_ID = OH.ORDER_TYPE_ID AND OT.NAME = Autoship Schedule AND OH.ORDER_STATUS = BOOKED AND OL.ITEM_TYPE IN (STANDARD,KIT,PTO) AND NVL(OL.CANCELLED_FLAG,N) = N AND SYSDATE = NVL(OL.ACTIVE_START_DATE,SYSDATE) AND SYSDATE NVL(OL.ACTIVE_END_DATE, SYSDATE+1) AND OL.NEXT_SHIP_DATE BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + P_LEADTIMEGROUP BY OH.ORDER_HEADER_ID, OH.ORDER_NUMBER, MN.CUSTOMER_NAME, MN.ORIG_SYSTEM_REFERENCEORDER BY MN.ORIG_SYSTEM_REFERENCE设置并发程序警告状态：l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(WARNING,);设置并发程序错误状态：l_temp := FND_CONCURRENT.SET_COMPLETION_STATUS(ERROR,);程序逻辑：RELEASE_ORDERPROCEDURE:RELEASE_ORDER此PROCEDURE是会员编号申请的主程序。调用参数：参数名数据类型参数类型默认值说明P_ORDER_HEADER_IDNUMBERIN订单IDX_RELEASE_ORDER_NUMBERVARCHAR2OUT释放后的订单号X_RETURN_STATUSVARCHAR2OUT返回代码S 成功E 错误U 例外X_RETURN_MESSAGEVARCHAR2OUT错误信息流程图：伪代码：DECLARE游标：计划订单的头信息；游标：需要发放的行信息(ITEM_TYPE IN (STANDARD,KIT,PTO)；游标：计划订单行包含的组件；BEGIN设置成功标志 RETCODE = 0; SAVEPOINT 获取计划订单头信息；生成自动发货订单的头信息插入表tiens_order_headers字段：ORDER_HEADER_ID = TIENS_ORDER_HEADERS_S.NEXTVAL,ORDER_NUMBER = TIENS_ORDER_PUB.NEXT_ORDER_NUMBER(),ORDER_DATE = TRUNC(SYSDATE)ORDER_TYPE_ID = G_AUTOSHIP_ORDER_TYPE_ID(全局变量)PRICE_LIST_ID = 计划订单头. PRICE_LIST_IDORDER_STATUS = BOOKEDSOURCE_SITE_TYPE = 计划订单头. SOURCE_SITE_TYPESOURCE_SITE = 计划订单头. SOURCE_SITECUSTOMER_ID =计划订单头.CUSTOMER_IDEMAIL_ADDRESS =计划订单头.EMAIL_ADDRESSPHONE_NUMBER =计划订单头.PHONE_NUMBERSHIP_TO_ADDRESS1 =计划订单头.SHIP_TO_ADDRESS1SHIP_TO_ADDRESS2 =计划订单头.SHIP_TO_ADDRESS2SHIP_TO_ADDRESS3 =计划订单头.SHIP_TO_ADDRESS3SHIP_TO_ADDRESS4 =计划订单头.SHIP_TO_ADDRESS4SHIP_TO_POSTAL_CODE =计划订单头.SHIP_TO_POSTAL_CODESHIP_TO_ADDRESSEE =计划订单头.SHIP_TO_ADDRESSEEBILL_TO_ADDRESS1 =计划订单头.BILL_TO_ADDRESS1BILL_TO_ADDRESS2 =计划订单头.BILL_TO_ADDRESS2BILL_TO_ADDRESS3 =计划订单头.BILL_TO_ADDRESS3BILL_TO_ADDRESS4 =计划订单头.BILL_TO_ADDRESS4BILL_TO_POSTAL_CODE =计划订单头.BILL_TO_POSTAL_CODEBILL_TO_ADDRESSEE =计划订单头.BILL_TO_ADDRESSEESHIPPING_SITE_TYPE =计划订单头.SHIPPING_SITE_TYPESHIPPING_SITE =计划订单头.SHIPPING_SITESHIPPING_METHOD =计划订单头.SHIPPING_METHODCURRENCY_CODE =计划订单头.CURRENCY_CODESOURCE_SYSTEM = CRMSOURCE_ID =计划订单头.ORDER_HEADER_IDOWNER_ORG_LEVEL =计划订单头. OWNER_ORG_LEVELOWNER_ORG_CODE =计划订单头. OWNER_ORG_CODETAX_CODE =计划订单头. TAX_CODETAX_RATE =计划订单头. TAX_RATEWHO字段变量L_当前行 = 0;FOR 发放行 IN (需要发放的行) LOOPL_当前行 + 1;生成自动发货订单的行信息插入表TIENS_ORDER_LINES字段：ORDER_LINE_ID = TIENS_ORDER_LINES_S.NEXTVALORDER_HEADER_ID = 自动发货订单. ORDER_HEADER_IDLINE_NUMBER = L_当前行SHIPMENT_NUMBER = 0OPTION_NUMBER = NULLLINE_TYPE_ID = G_AUTOSHIP_LINE_TYPE_IDPRICE_LIST_ID =发放行.PRICE_LIST_IDINVENTORY_ITEM_ID =发放行.INVENTORY_ITEM_IDORGANIZATION_ID =发放行.ORGANIZATION_IDITEM_TYPE =发放行.ITEM_TYPEQUANTITY_UOM =发放行.QUANTITY_UOMQUANTITY =发放行.QUANTITYLIST_PRICE =发放行.LIST_PRICEUNIT_PRICE =发放行.UNIT_PRICEPV_UNIT_PRICE =发放行.PV_UNIT_PRICEPV_PERCENT =发放行.PV_PERCENTBV_UNIT_PRICE =发放行.BV_UNIT_PRICEBV_PERCENT =发放行.BV_PERCENTREQUEST_DATE = TRUNC(SYSDATE)SCHEDULE_SHIP_DATE =发放行.NEXT_SHIP_DATESHIPPED_FLAG = NCANCELLED_FLAG = NINV_INTERFACE_FLAG = NDESCRIPTION =发放行.DESCRIPTIONSOURCE_SYSTEM = CRMSOURCE_ID =计划订单头.ORDER_HEADER_IDSOURCE_LINE_ID =发放行. ORDER_LINE_IDSHIPPABLE_FLAG =发放行.TAX_CODE =发放行.TAX_RATE =发放行.WHO字段IF发放行.ITEM_TYPE = KIT OR 发放行.ITEM_TYPE = PTO THENFOR 组件 IN (计划订单行包含的组件) LOOP生成自动发货订单的行信息插入表TIENS_ORDER_LINES字段：ORDER_LINE_ID = TIENS_ORDER_LINES_S.NEXTVALORDER_HEADER_ID = 自动发货订单. ORDER_HEADER_IDLINE_NUMBER = L_当前行SHIPMENT_NUMBER = 0OPTION_NUMBER =组件. OPTION_NUMBERLINE_TYPE_ID = G_AUTOSHIP_LINE_TYPE_IDPRICE_LIST_ID =组件.PRICE_LIST_IDINVENTORY_ITEM_ID =组件.INVENTORY_ITEM_IDORGANIZATION_ID =组件.ORGANIZATION_IDITEM_TYPE =组件.ITEM_TYPEQUANTITY_UOM