Receive Demand Schedule Service

Database Scripts

This service uses the following database scripts:

For more information about using database scripts, see Database Scripts.

Supporting Transaction Definitions

This service was built from the configured services defined in the following transaction definitions:

• setDemandScheduleTxn1227.txp
• DemandScheduleTxn1227.txp

For information about using the transaction definition files to customize this service, see Transaction Definitions.

Flow Control

This service uses the following flow steps to import demand schedules:

• specifyDefaultSettings specifies the default parameter settings required for service execution. You should change these settings accordingly.
• specifyConcProgParams specifies the default parameter settings required for concurrent program execution. You should change these settings accordingly.
• bizDocMapping maps the incoming business document structure to the required Oracle Applications data structures (the interface tables).
  • getAddressId takes ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, POSTAL_CODE, STATE, PROVINCE, COUNTY, and COUNTRY as input, and queries the table HZ_CUST_ACCT_SITES_ALL to get ADDRESS_ID.
  • getAgreementId takes AGREEMENT_NAME as input, and queries the table OE_AGREEMENTS_B to get AGREEMENT_ID.
  • getDateTypeCode takes DATE_TYPE as input, and queries the table FND_LOOKUPS to get DATE_TYPE_CODE.
  • getInvoiceOrgId takes BILL_TO_ADDRESS1, BILL_TO_ADDRESS2, BILL_TO_ADDRESS3, BILL_TO_ADDRESS4, BILL_TO_CITY, BILL_TO_POSTAL_CODE, BILL_TO_STATE, BILL_TO_PROVINCE, BILL_TO_COUNTY, BILL_TO_COUNTRY, and INVOICE_TO_ORGANIZATION as input, and queries the tables HZ_CUST_SITE_USES_ALL and HZ_CUST_ACCT_SITES_ALL to get INVOICE_TO_ORG_ID.
  • getOrderHeaderId takes ORDER_NUMBER and ORG_NAME as input, and queries the tables OE_ORDER_HEADERS_ALL and HR_ORGANIZATION_UNITS to get ORDER_HEADER_ID.
  • getPriceListId takes PRICE_LIST_NAME as input, and queries the table QP_LIST_HEADERS_V to get LIST_HEADER_ID.
  • getShipFromOrgId takes ORG_NAME, SHIP_FROM_ADDRESS1, SHIP_FROM_ADDRESS2, SHIP_FROM_ADDRESS3, SHIP_FROM_CITY, SHIP_FROM_COUNTY, SHIP_FROM_STATE, SHIP_FROM_PROVINCE, SHIP_FROM_POSTAL_CODE, and SHIP_FROM_COUNTRY as input, and queries the tables HR_LOCATIONS and HR_ALL_ORGANIZATION_UNITS to get ORGANIZATION_ID.
  • getShipToOrgId takes SHIP_TO_ORGANIZATION, SHIP_TO_ADDRESS1, SHIP_TO_ADDRESS2, SHIP_TO_ADDRESS3, SHIP_TO_ADDRESS4, SHIP_TO_CITY, SHIP_TO_POSTAL_CODE, SHIP_TO_STATE, SHIP_TO_PROVINCE, SHIP_TO_COUNTY, and SHIP_TO_COUNTRY as input, and queries the tables HZ_CUST_SITE_USES_ALL and HZ_CUST_ACCT_SITES_ALL to get SHIP_TO_ORG_ID.
  • getCustomerId takes CUSTOMER_NAME as input, and queries the table HZ_CUST_ACCOUNTS to get CUSTOMER_ID.
  • getCustItemIdFromAddrTmp takes CUSTOMER_ITEM_NUMBER, PARTY_NAME, and ADDRESS_ID as input, and queries the tables MTL_CUSTOMER_ITEMS_ALL_V and HZ_CUST_ACCOUNTS to get CUSTOMER_ITEM_ID.
  • getCustItemIdFromAddr takes CUSTOMER_ITEM_NUMBER, PARTY_NAME, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, CITY, POSTAL_CODE, STATE, PROVINCE, COUNTY, and COUNTRY as input, and queries the HZ_CUST_SITE_USES_ALL table to get CUST_ACC_SITE_ID.
• setDemandScheduleTxn inserts data into the interface table. It extracts data from the IData structure that results from the bizDocMapping service, and puts the data into the interface table.
• importDemandSchedule imports data to the production table from the interface table. It calls the services execDemandScheduleConcProg, checkDemandScheduleImportStatus, and getDemandScheduleImport_ERR to execute the corresponding concurrent program that inserts data into the production table, and to generate the error/acknowledgement message. If the status of the execution is SUCCESS (returned by the service execDemandScheduleConcProg), it checks for the record in the interface table. If records are found, it then indicates an error during import. In this case, this service calls getDemandScheduleImport_ERR to retrieve the errors. If no records are found, it comes out of the flow, indicating success of the data import process. If the status of the execution is FAILED, it comes out of execution. There can be two types of action on the record to be imported: update and insert. This flow checks for the mode of action and accordingly calls the import program.
  • execDemandScheduleConcProg invokes the stored procedure WM_RLM_DEMAND_IMP_HANDLER_PKG. WM_HANDLE_RLM_DEMAND. The procedure calls the corresponding concurrent subroutine to execute the data import process for Demand Schedule Transactions into Oracle Applications. This service returns Status ID, Request ID, Execution Status Message (for normal concurrent program completion), and database Stored Procedure error message (if an exception occurs in Stored Procedure execution).
  • checkDemandScheduleImportStatus checks the status of the execution by checking the Interface Table for any rejected records corresponding to the current REQUEST_ID. If the query does not return rows, it indicates a successful import. If the query returns rows, it indicates that the concurrent program could not import data successfully in the production tables of Oracle Applications.
  • getDemandScheduleImport_ERR gets the error message that occurs during the data import to the production table from the interface table. Based on the parameter REQUEST_ID, it scans tables RLM_DEMAND_EXCEPTIONS to get the corresponding message.

For more details on receive transactions, see Overview of Receive Service Transaction Processing.

Business Document Structure

This service uses the business document DemandScheduleBizDoc. Its structure is as follows:

• 1.0. RLM_HEADERS
  • 1.1. RLM_LINES

1.0. RLM_HEADERS(Maps to RLM_INTERFACE_HEADERS_ALL)

1.1. RLM_LINES (Maps to RLM_INTERFACE_LINES_ALL)