Oracle 的DML命令的详细处理过程

程序员文章站 2022-05-13 09:30:51

...

近来，分公司上线系统，团队一直非常忙碌，忙中出乱是正常的。发生的问题: 有一张销售订单已发运确认，发运事务处理中状态

近来，分公司上线系统，团队一直非常忙碌，忙中出乱是正常的。发生的问题:

有一张销售订单已发运确认，发运事务处理中状态是“已发运”和下一步“不适应”,为什么应收接口表中没有对应的记录？

分析原因:

1.可能用户不小心删除了。但是，不会这巧只删除这一张销售订单。

2.可能后台工作流没有运行完成。但是，找到对应的工作流也没有提示什么警告和错误。

现在首要问题是要产生出应收，解决方案是根据销售订单和发运事务处理，通过应收API接口产生数据到接口表中。但是，网上没有关于AR接口表的资料,原因：不同AR和OM设置有产生不同的接口数据。只能自己根据现在产生在接口表的销售订单反推出需求哪些数据。

得出结果如下:

/*BEGIN
MO_GLOBAL.init('ONT');
END;*/

Insert Into RA_INTERFACE_LINES_ALL

Select Null INTERFACE_LINE_ID,
'ORDER ENTRY' INTERFACE_LINE_CONTEXT, --常量
WDV.SOURCE_HEADER_NUMBER INTERFACE_LINE_ATTRIBUTE1, --销售订单编号
OEH.order_type INTERFACE_LINE_ATTRIBUTE2, --订单类型
WNV.NAME INTERFACE_LINE_ATTRIBUTE3, --发运编号
0 INTERFACE_LINE_ATTRIBUTE4,
0 INTERFACE_LINE_ATTRIBUTE5,
WDV.source_line_id INTERFACE_LINE_ATTRIBUTE6, --订单行ID
0 INTERFACE_LINE_ATTRIBUTE7,
0 INTERFACE_LINE_ATTRIBUTE8,
'OM导入发票' BATCH_SOURCE_NAME, ---AR发票的事务处理来源
2304 SET_OF_BOOKS_ID, ---帐本ID
'LINE' LINE_TYPE, ---行类，若有发运运费，则比较复杂
(Case
When OEL.ORDERED_ITEM = MSI.SEGMENT1 Then
MSI.DESCRIPTION
Else
CIX.CUSTOMER_ITEM_DESC
End) DESCRIPTION, ---若订单项目是用客户的项目号，则说明就在用客户的物料说明
OEH.TRANSACTIONAL_CURR_CODE CURRENCY_CODE, --订单的货币
Round(WDV.shipped_quantity * OEL.UNIT_SELLING_PRICE,2) AMOUNT, --金额
Null CUST_TRX_TYPE_NAME,
OTT.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID, --订单事务处理类型ID
Null TERM_NAME,
OEH.payment_term_id TERM_ID, --付款条件ID
Null ORIG_SYSTEM_BATCH_NAME,
Null ORIG_SYSTEM_BILL_CUSTOMER_REF,
OEH.sold_to_org_id ORIG_SYSTEM_BILL_CUSTOMER_ID, --收单客户ID
Null ORIG_SYSTEM_BILL_ADDRESS_REF,
BHCASA.CUST_ACCT_SITE_ID ORIG_SYSTEM_BILL_ADDRESS_ID, --收单地点ID
Null ORIG_SYSTEM_BILL_CONTACT_REF,
Null ORIG_SYSTEM_BILL_CONTACT_ID,
Null ORIG_SYSTEM_SHIP_CUSTOMER_REF,
OEH.sold_to_org_id ORIG_SYSTEM_SHIP_CUSTOMER_ID, --收货客户ID
Null ORIG_SYSTEM_SHIP_ADDRESS_REF,
SHCASA.CUST_ACCT_SITE_ID ORIG_SYSTEM_SHIP_ADDRESS_ID, --收货地点ID
Null ORIG_SYSTEM_SHIP_CONTACT_REF,
Null ORIG_SYSTEM_SHIP_CONTACT_ID,
Null ORIG_SYSTEM_SOLD_CUSTOMER_REF,
OEH.sold_to_org_id ORIG_SYSTEM_SOLD_CUSTOMER_ID, --订单客户ID
Null LINK_TO_LINE_ID,
Null LINK_TO_LINE_CONTEXT,
Null LINK_TO_LINE_ATTRIBUTE1,
Null LINK_TO_LINE_ATTRIBUTE2,
Null LINK_TO_LINE_ATTRIBUTE3,
Null LINK_TO_LINE_ATTRIBUTE4,
Null LINK_TO_LINE_ATTRIBUTE5,
Null LINK_TO_LINE_ATTRIBUTE6,
Null LINK_TO_LINE_ATTRIBUTE7,
Null RECEIPT_METHOD_NAME,
Null RECEIPT_METHOD_ID,
'User' CONVERSION_TYPE, ---OEH.conversion_type_code 注意要添加
Null CONVERSION_DATE,
1 CONVERSION_RATE, --OEH.conversion_rate 注意要添加
Null CUSTOMER_TRX_ID,
Null TRX_DATE,
Null GL_DATE,
Null DOCUMENT_NUMBER,
Null TRX_NUMBER,
Null LINE_NUMBER,
WDV.shipped_quantity QUANTITY, --数量
WDV.shipped_quantity QUANTITY_ORDERED, --订单数量
OEL.UNIT_SELLING_PRICE UNIT_SELLING_PRICE, --销售单价
OEL.UNIT_SELLING_PRICE UNIT_STANDARD_PRICE, ---标准单价
Null PRINTING_OPTION,
Null INTERFACE_STATUS,
Null REQUEST_ID,
Null RELATED_BATCH_SOURCE_NAME,
Null RELATED_TRX_NUMBER,
Null RELATED_CUSTOMER_TRX_ID,
Null PREVIOUS_CUSTOMER_TRX_ID,
Null CREDIT_METHOD_FOR_ACCT_RULE,
Null CREDIT_METHOD_FOR_INSTALLMENTS,
Null REASON_CODE,
Null TAX_RATE,
OEL.TAX_CODE TAX_CODE, --税率代码
Null TAX_PRECEDENCE,
Null EXCEPTION_ID,
Null EXEMPTION_ID,
WNV.Confirm_Date SHIP_DATE_ACTUAL, --发运确认日期
WDV.fob_code FOB_POINT,
OTP.DESCRIPTION SHIP_VIA, ---
0 WAYBILL_NUMBER, ---
Null INVOICING_RULE_NAME,
Null INVOICING_RULE_ID,
Null ACCOUNTING_RULE_NAME,
Null ACCOUNTING_RULE_ID,
Null ACCOUNTING_RULE_DURATION,
Null RULE_START_DATE,
Null PRIMARY_SALESREP_NUMBER,
OEH.salesrep_id PRIMARY_SALESREP_ID, --销售员ID
OEH.order_number SALES_ORDER, ---订单编号
OEL.LINE_NUMBER SALES_ORDER_LINE, --订单行编号
SYSDATE SALES_ORDER_DATE, --订单日期
'ORDER ENTRY' SALES_ORDER_SOURCE, ---销售来源
Null SALES_ORDER_REVISION,
Null PURCHASE_ORDER,
Null PURCHASE_ORDER_REVISION,
Null PURCHASE_ORDER_DATE,
Null AGREEMENT_NAME,
Null AGREEMENT_ID,
Null MEMO_LINE_NAME,
Null MEMO_LINE_ID,
WDV.INVENTORY_ITEM_ID, --物料ID
Null MTL_SYSTEM_ITEMS_SEG1,
Null MTL_SYSTEM_ITEMS_SEG2,
Null MTL_SYSTEM_ITEMS_SEG3,
Null MTL_SYSTEM_ITEMS_SEG4,
Null MTL_SYSTEM_ITEMS_SEG5,
Null MTL_SYSTEM_ITEMS_SEG6,
Null MTL_SYSTEM_ITEMS_SEG7,
Null MTL_SYSTEM_ITEMS_SEG8,
Null MTL_SYSTEM_ITEMS_SEG9,
Null MTL_SYSTEM_ITEMS_SEG10,
Null MTL_SYSTEM_ITEMS_SEG11,
Null MTL_SYSTEM_ITEMS_SEG12,
Null MTL_SYSTEM_ITEMS_SEG13,
Null MTL_SYSTEM_ITEMS_SEG14,
Null MTL_SYSTEM_ITEMS_SEG15,
Null MTL_SYSTEM_ITEMS_SEG16,
Null MTL_SYSTEM_ITEMS_SEG17,
Null MTL_SYSTEM_ITEMS_SEG18,
Null MTL_SYSTEM_ITEMS_SEG19,
Null MTL_SYSTEM_ITEMS_SEG20,
Null REFERENCE_LINE_ID,
Null REFERENCE_LINE_CONTEXT,
Null REFERENCE_LINE_ATTRIBUTE1,
Null REFERENCE_LINE_ATTRIBUTE2,
Null REFERENCE_LINE_ATTRIBUTE3,
Null REFERENCE_LINE_ATTRIBUTE4,
Null REFERENCE_LINE_ATTRIBUTE5,
Null REFERENCE_LINE_ATTRIBUTE6,
Null REFERENCE_LINE_ATTRIBUTE7,
Null TERRITORY_ID,
Null TERRITORY_SEGMENT1,
Null TERRITORY_SEGMENT2,
Null TERRITORY_SEGMENT3,
Null TERRITORY_SEGMENT4,
Null TERRITORY_SEGMENT5,
Null TERRITORY_SEGMENT6,
Null TERRITORY_SEGMENT7,
Null TERRITORY_SEGMENT8,
Null TERRITORY_SEGMENT9,
Null TERRITORY_SEGMENT10,
Null TERRITORY_SEGMENT11,
Null TERRITORY_SEGMENT12,
Null TERRITORY_SEGMENT13,
Null TERRITORY_SEGMENT14,
Null TERRITORY_SEGMENT15,
Null TERRITORY_SEGMENT16,
Null TERRITORY_SEGMENT17,
Null TERRITORY_SEGMENT18,
Null TERRITORY_SEGMENT19,
Null TERRITORY_SEGMENT20,
Null ATTRIBUTE_CATEGORY,
Null ATTRIBUTE1,
Null ATTRIBUTE2,
Null ATTRIBUTE3,
Null ATTRIBUTE4,
Null ATTRIBUTE5,
Null ATTRIBUTE6,
Null ATTRIBUTE7,
Null ATTRIBUTE8,
Null ATTRIBUTE9,
Null ATTRIBUTE10,
Null ATTRIBUTE11,
Null ATTRIBUTE12,
Null ATTRIBUTE13,
Null ATTRIBUTE14,
Null ATTRIBUTE15,
OEH.context HEADER_ATTRIBUTE_CATEGORY, ---订单主题弹性域上下文
SUBSTRB(OEH.attribute1,1,150) HEADER_ATTRIBUTE1, --对订单主题弹性域attribute1
SUBSTRB(OEH.attribute2,1,150) HEADER_ATTRIBUTE2,
SUBSTRB(OEH.attribute3,1,150) HEADER_ATTRIBUTE3,
SUBSTRB(OEH.attribute4,1,150) HEADER_ATTRIBUTE4,
SUBSTRB(OEH.attribute5,1,150) HEADER_ATTRIBUTE5,
SUBSTRB(OEH.attribute6,1,150) HEADER_ATTRIBUTE6,
SUBSTRB(OEH.attribute7,1,150) HEADER_ATTRIBUTE7,
SUBSTRB(OEH.attribute8,1,150) HEADER_ATTRIBUTE8,
SUBSTRB(OEH.attribute9,1,150) HEADER_ATTRIBUTE9,
SUBSTRB(OEH.attribute10,1,150) HEADER_ATTRIBUTE10,
SUBSTRB(OEH.attribute11,1,150) HEADER_ATTRIBUTE11,
SUBSTRB(OEH.attribute12,1,150) HEADER_ATTRIBUTE12,
SUBSTRB(OEH.attribute13,1,150) HEADER_ATTRIBUTE13,
SUBSTRB(OEH.attribute14,1,150) HEADER_ATTRIBUTE14,
SUBSTRB(OEH.attribute15,1,150) HEADER_ATTRIBUTE15,
Null COMMENTS,
Null INTERNAL_NOTES,
Null INITIAL_CUSTOMER_TRX_ID,
Null USSGL_TRANSACTION_CODE_CONTEXT,
Null USSGL_TRANSACTION_CODE,
Null ACCTD_AMOUNT,
Null CUSTOMER_BANK_ACCOUNT_ID,
Null CUSTOMER_BANK_ACCOUNT_NAME,
WDV.requested_quantity_uom UOM_CODE, --发运单位代码
Null UOM_NAME,
Null DOCUMENT_NUMBER_SEQUENCE_ID,
Null LINK_TO_LINE_ATTRIBUTE10,
Null LINK_TO_LINE_ATTRIBUTE11,
Null LINK_TO_LINE_ATTRIBUTE12,
Null LINK_TO_LINE_ATTRIBUTE13,
Null LINK_TO_LINE_ATTRIBUTE14,
Null LINK_TO_LINE_ATTRIBUTE15,
Null LINK_TO_LINE_ATTRIBUTE8,
Null LINK_TO_LINE_ATTRIBUTE9,
Null REFERENCE_LINE_ATTRIBUTE10,
Null REFERENCE_LINE_ATTRIBUTE11,
Null REFERENCE_LINE_ATTRIBUTE12,
Null REFERENCE_LINE_ATTRIBUTE13,
Null REFERENCE_LINE_ATTRIBUTE14,
Null REFERENCE_LINE_ATTRIBUTE15,
Null REFERENCE_LINE_ATTRIBUTE8,
Null REFERENCE_LINE_ATTRIBUTE9,
WDV.organization_id INTERFACE_LINE_ATTRIBUTE10, --发运库存组织ID
0 INTERFACE_LINE_ATTRIBUTE11, ---------
1 INTERFACE_LINE_ATTRIBUTE12, ---------
0 INTERFACE_LINE_ATTRIBUTE13, ---------
0 INTERFACE_LINE_ATTRIBUTE14, ---------
Null INTERFACE_LINE_ATTRIBUTE15,
(Case
When OEL.ORDERED_ITEM = MSI.SEGMENT1 Then
'0'
Else
OEL.ORDERED_ITEM
End) INTERFACE_LINE_ATTRIBUTE9, ----
Null VAT_TAX_ID,
Null REASON_CODE_MEANING,
Null LAST_PERIOD_TO_CREDIT,
Null PAYING_CUSTOMER_ID,
Null PAYING_SITE_USE_ID,
'S' TAX_EXEMPT_FLAG, ---------
Null TAX_EXEMPT_REASON_CODE,
Null TAX_EXEMPT_REASON_CODE_MEANING,
Null TAX_EXEMPT_NUMBER,
Null SALES_TAX_ID,
0 CREATED_BY,
SYSDATE CREATION_DATE,
0 LAST_UPDATED_BY,
SYSDATE LAST_UPDATE_DATE,
0 LAST_UPDATE_LOGIN,
Null LOCATION_SEGMENT_ID,
Null MOVEMENT_ID,
OEH.org_id ORG_ID, ---OU
Null AMOUNT_INCLUDES_TAX_FLAG,
Null HEADER_GDF_ATTR_CATEGORY,
Null HEADER_GDF_ATTRIBUTE1,
Null HEADER_GDF_ATTRIBUTE2,
Null HEADER_GDF_ATTRIBUTE3,
Null HEADER_GDF_ATTRIBUTE4,
Null HEADER_GDF_ATTRIBUTE5,
Null HEADER_GDF_ATTRIBUTE6,
Null HEADER_GDF_ATTRIBUTE7,
Null HEADER_GDF_ATTRIBUTE8,
Null HEADER_GDF_ATTRIBUTE9,
Null HEADER_GDF_ATTRIBUTE10,
Null HEADER_GDF_ATTRIBUTE11,
Null HEADER_GDF_ATTRIBUTE12,
Null HEADER_GDF_ATTRIBUTE13,
Null HEADER_GDF_ATTRIBUTE14,
Null HEADER_GDF_ATTRIBUTE15,
Null HEADER_GDF_ATTRIBUTE16,
Null HEADER_GDF_ATTRIBUTE17,
Null HEADER_GDF_ATTRIBUTE18,
Null HEADER_GDF_ATTRIBUTE19,
Null HEADER_GDF_ATTRIBUTE20,
Null HEADER_GDF_ATTRIBUTE21,
Null HEADER_GDF_ATTRIBUTE22,
Null HEADER_GDF_ATTRIBUTE23,
Null HEADER_GDF_ATTRIBUTE24,
Null HEADER_GDF_ATTRIBUTE25,
Null HEADER_GDF_ATTRIBUTE26,
Null HEADER_GDF_ATTRIBUTE27,
Null HEADER_GDF_ATTRIBUTE28,
Null HEADER_GDF_ATTRIBUTE29,
Null HEADER_GDF_ATTRIBUTE30,
Null LINE_GDF_ATTR_CATEGORY,
Null LINE_GDF_ATTRIBUTE1,
Null LINE_GDF_ATTRIBUTE2,
Null LINE_GDF_ATTRIBUTE3,
Null LINE_GDF_ATTRIBUTE4,
Null LINE_GDF_ATTRIBUTE5,
Null LINE_GDF_ATTRIBUTE6,
Null LINE_GDF_ATTRIBUTE7,
Null LINE_GDF_ATTRIBUTE8,
Null LINE_GDF_ATTRIBUTE9,
Null LINE_GDF_ATTRIBUTE10,
Null LINE_GDF_ATTRIBUTE11,
Null LINE_GDF_ATTRIBUTE12,
Null LINE_GDF_ATTRIBUTE13,
Null LINE_GDF_ATTRIBUTE14,
Null LINE_GDF_ATTRIBUTE15,
Null LINE_GDF_ATTRIBUTE16,
Null LINE_GDF_ATTRIBUTE17,
Null LINE_GDF_ATTRIBUTE18,
Null LINE_GDF_ATTRIBUTE19,
Null LINE_GDF_ATTRIBUTE20,
Null RESET_TRX_DATE_FLAG,
Null PAYMENT_SERVER_ORDER_NUM,
Null APPROVAL_CODE,
Null ADDRESS_VERIFICATION_CODE,
WDV.organization_id WAREHOUSE_ID, ---发运组织ID
'0' TRANSLATED_DESCRIPTION,
Null CONS_BILLING_NUMBER,
Null PROMISED_COMMITMENT_AMOUNT,
Null PAYMENT_SET_ID,
Null ORIGINAL_GL_DATE,
Null CONTRACT_LINE_ID,
Null CONTRACT_ID,
Null SOURCE_DATA_KEY1,
Null SOURCE_DATA_KEY2,
Null SOURCE_DATA_KEY3,
Null SOURCE_DATA_KEY4,
Null SOURCE_DATA_KEY5,
Null INVOICED_LINE_ACCTG_LEVEL,
Null OVERRIDE_AUTO_ACCOUNTING_FLAG,
Null SOURCE_APPLICATION_ID,
Null SOURCE_EVENT_CLASS_CODE,
Null SOURCE_ENTITY_CODE,
Null SOURCE_TRX_ID,
Null SOURCE_TRX_LINE_ID,
Null SOURCE_TRX_LINE_TYPE,
Null SOURCE_TRX_DETAIL_TAX_LINE_ID,
Null HISTORICAL_FLAG,
Null TAX_REGIME_CODE,
Null TAX,
Null TAX_STATUS_CODE,
Null TAX_RATE_CODE,
Null TAX_JURISDICTION_CODE,
Null TAXABLE_AMOUNT,
Null TAXABLE_FLAG,
Null LEGAL_ENTITY_ID,
Null PARENT_LINE_ID,
Null DEFERRAL_EXCLUSION_FLAG,
Null PAYMENT_TRXN_EXTENSION_ID,
Null RULE_END_DATE,
Null PAYMENT_ATTRIBUTES,
Null APPLICATION_ID,
Null BILLING_DATE
From WSH_DELIVERABLES_V WDV,
OE_ORDER_HEADERS_V OEH,
OE_TRANSACTION_TYPES_ALL OTT,
OE_ORDER_LINES_V OEL,
MTL_SYSTEM_ITEMS_B MSI,
MTL_CUSTOMER_ITEM_XREFS_V CIX,
WSH_NEW_DELIVERIES WNV,
HZ_CUST_ACCT_SITES_ALL BHCASA,
HZ_CUST_SITE_USES_ALL BILL,
HZ_CUST_SITE_USES_ALL SHIP,
HZ_CUST_ACCT_SITES_ALL SHCASA,
(SELECT FV.FLEX_VALUE, FV.FLEX_VALUE_MEANING, FV.DESCRIPTION
FROM FND_FLEX_VALUES_VL FV, FND_FLEX_VALUE_SETS FS
WHERE FV.FLEX_VALUE_SET_ID = FS.FLEX_VALUE_SET_ID
And FS.FLEX_VALUE_SET_NAME = 'XYG_ORDER_TYPE') OTP
Where WDV.source_header_id = OEH.header_id
And WDV.source_line_id = OEL.LINE_ID
And WDV.delivery_id = WNV.Delivery_Id
And WDV.organization_id = MSI.ORGANIZATION_ID
And WDV.inventory_item_id = MSI.INVENTORY_ITEM_ID
And OEH.sold_to_org_id = CIX.CUSTOMER_ID
And OEL.INVENTORY_ITEM_ID = CIX.INVENTORY_ITEM_ID
And OEH.order_type_id = OTT.TRANSACTION_TYPE_ID
And OTT.ATTRIBUTE1 = OTP.FLEX_VALUE
And OEH.invoice_to_org_id = BILL.SITE_USE_ID
And BILL.SITE_USE_CODE = 'BILL_TO'
And BILL.CUST_ACCT_SITE_ID = BHCASA.CUST_ACCT_SITE_ID
AND BILL.ORG_ID = BHCASA.ORG_ID
AND BHCASA.CUST_ACCOUNT_ID = OEH.sold_to_org_id
And OEH.ship_to_org_id = SHIP.SITE_USE_ID
And SHIP.SITE_USE_CODE = 'SHIP_TO'
And SHIP.CUST_ACCT_SITE_ID = SHCASA.CUST_ACCT_SITE_ID
And SHIP.ORG_ID = SHCASA.ORG_ID
And SHCASA.CUST_ACCOUNT_ID = OEH.sold_to_org_id
And WDV.SOURCE_HEADER_NUMBER = '221111030001'
And WDV.released_status = 'C' --已发运或已连接
And WDV.INV_INTERFACED_FLAG = 'Y' --表示发运事务处理下一步“不适应”
And WDV.shipped_quantity > 0 --发运数量一定大于0才产生应收
And Not Exists ---不存在接口表中
(Select 'x'
From RA_INTERFACE_LINES_ALL RIL
Where RIL.Org_Id = OEH.org_id
And RIL.INTERFACE_LINE_ATTRIBUTE6 = WDV.source_line_id
And RIL.INTERFACE_LINE_ATTRIBUTE3 = WNV.Name)
And Not Exists ---未产生应收事务处理
(Select 'X'
From RA_CUSTOMER_TRX_LINES_ALL RAL
Where RAL.ORG_ID = OEH.ORG_ID
And RAL.INTERFACE_LINE_ATTRIBUTE1 = WDV.SOURCE_HEADER_NUMBER
And RAL.INTERFACE_LINE_ATTRIBUTE6 = WDV.source_line_id);