Detailed process of Oracle DML commands

Recently, the Branch has launched the system, and the Team has been very busy. It is normal to go out of the box. Problem: A sales order has been shipped for confirmation, and the status of the shipping transaction is being processed.

Recently, the Branch has launched the system, and the Team has been very busy. It is normal to go out of the box. Problem: A sales order has been shipped for confirmation, and the status of the shipping transaction is being processed.

Recently, the Branch has launched the system, and the Team has been very busy. It is normal to go out of the box. Problems:

There is a sales order that has been confirmed by shipment. The status of the shipping transaction is "shipped" and "unsuitable" in the next step. Why is there no corresponding record in the receivables interface table?

Cause analysis:

1. The user may accidentally delete it. However, it is not a coincidence that only this sales order is deleted.

2. The background workflow may not be completed. However, no warning or error is prompted when the corresponding workflow is found.

At present, the primary problem is to generate receivables. The solution is to generate data to the interface table through the receivables API interface based on the sales orders and shipping transactions. However, there is no information about the AR interface table on the Internet. The reason is that different AR and OM settings generate different interface data. Only the data based on the reverse release requirements of the sales orders generated in the interface table.

The result is as follows:

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

Insert Into RA_INTERFACE_LINES_ALL

Select Null INTERFACE_LINE_ID,
'Order ENTRY 'INTERFACE_LINE_CONTEXT, -- constant
WDV. SOURCE_HEADER_NUMBER INTERFACE_LINE_ATTRIBUTE1, -- sales order number
OEH. order_type INTERFACE_LINE_ATTRIBUTE2, -- order type
WNV. NAME INTERFACE_LINE_ATTRIBUTE3, -- shipping number
0 INTERFACE_LINE_ATTRIBUTE4,
0 INTERFACE_LINE_ATTRIBUTE5,
WDV. source_line_id INTERFACE_LINE_ATTRIBUTE6, -- order line ID
0 INTERFACE_LINE_ATTRIBUTE7,
0 INTERFACE_LINE_ATTRIBUTE8,
'Om import invoiced 'BATCH_SOURCE_NAME, --- AR invoice transaction processing source
2304 SET_OF_BOOKS_ID, --- account ID
'Line' LINE_TYPE, --- LINE class. complicated if shipping freight is available
(Case
When OEL. ORDERED_ITEM = MSI. SEGMENT1 Then
MSI. DESCRIPTION
Else
CIX. CUSTOMER_ITEM_DESC
End) DESCRIPTION, --- if the order project uses the customer's project number, the customer's material DESCRIPTION is used.
OEH. TRANSACTIONAL_CURR_CODE CURRENCY_CODE, -- order currency
Round (WDV. shipped_quantity * OEL. UNIT_SELLING_PRICE, 2) AMOUNT, -- AMOUNT
Null CUST_TRX_TYPE_NAME,
OTT. CUST_TRX_TYPE_ID, -- order transaction processing type ID
Null TERM_NAME,
OEH. payment_term_id TERM_ID, -- payment condition ID
Null ORIG_SYSTEM_BATCH_NAME,
Null ORIG_SYSTEM_BILL_CUSTOMER_REF,
OEH. sold_to_org_id orig_system_bill_mermer_id, -- Client ID
Null ORIG_SYSTEM_BILL_ADDRESS_REF,
BHCASA. CUST_ACCT_SITE_ID ORIG_SYSTEM_BILL_ADDRESS_ID, -- Recipient location 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_mermer_id, -- recipient ID
Null ORIG_SYSTEM_SHIP_ADDRESS_REF,
SHCASA. CUST_ACCT_SITE_ID ORIG_SYSTEM_SHIP_ADDRESS_ID, -- ship location 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, -- order customer 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 Note: add
Null CONVERSION_DATE,
1 CONVERSION_RATE, -- OEH. conversion_rate must be added
Null CUSTOMER_TRX_ID,
Null TRX_DATE,
Null GL_DATE,
Null DOCUMENT_NUMBER,
Null TRX_NUMBER,
Null LINE_NUMBER,
WDV. shipped_quantity QUANTITY, -- QUANTITY
WDV. shipped_quantity QUANTITY_ORDERED, -- order quantity
OEL. UNIT_SELLING_PRICE, -- Sales Unit Price
OEL. UNIT_SELLING_PRICE UNIT_STANDARD_PRICE, --- standard unit 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 rate code
Null TAX_PRECEDENCE,
Null prediction_id,
Null EXEMPTION_ID,
WNV. Confirm_Date SHIP_DATE_ACTUAL, -- date of shipment confirmation
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, -- salesperson ID
OEH. order_number SALES_ORDER, --- Order Number
OEL. LINE_NUMBER SALES_ORDER_LINE, -- order line number
SYSDATE SALES_ORDER_DATE, -- order date
'Order ENTRY 'SALES_ORDER_SOURCE, --- sales 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, -- Item 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, --- order topic elastic domain context
SUBSTRB (OEH. attribute1, 1,150) HEADER_ATTRIBUTE1, -- the order subject elastic domain 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, -- shipping unit 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 of the shipping inventory Organization
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, --- Shipping Organization 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 _'
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 _'
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 = '201312'
And WDV. released_status = 'C' -- shipped or connected
And WDV. INV_INTERFACED_FLAG = 'y' -- indicates that the shipping transaction is not adaptive to the next step"
And WDV. shipped_quantity> 0 -- the shipping quantity must be greater than 0 to generate receivables
And Not Exists --- interface table does Not exist
(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 --- no handling of receivables
(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 );

Interface table insert required data.Note: The preceding SQL statements are only used for non-standard parameters.

The following receivables --> interfaces --> automatic invoicing: submit the "Automatic invoicing main program" and enter the corresponding parameters. However, the request is not submitted smoothly. Many errors have occurred.

1. Set the "enable invoice" flag for this inventory item to "no". Set the "enable invoice" flag for this inventory item to "yes" in the "project" form"

2. If the automatic invoicing settlement account is not used, the provided amount must match the amount calculated by multiplying the sales unit price by the quantity. The entered amount does not have the correct currency accuracy.

3. Error description:

Submit the "Automatic invoicing main program" again, and then import it to the receivables transaction.

Finally, I finally understood why the shipment confirmation of the sales order did not generate data in the receivables interface table. It turns out that the materials in the order were not set to "enable invoicing" in the materials of the main organization.