5 Publication and Subscription API Designs

This chapter includes publication designs that describe, on a technical level, how ORFM publishes messages to the Oracle Retail Integration Bus (RIB).

The following topics are covered in this chapter:

• Secondary ASNOut Publication API

• POSchedule Publication API

• Shipment Information Subscription API

Secondary ASNOut Publication API

The business overview, functionality checklist, and package impact of the Secondary ASNOut Publication API is covered in the subsequent sections.

Business Overview

ORFM publishes this message while submitting a schedule based on PO to SIM for receiving or transfer to RWMS/SIM for receiving. RWMS/SIM subscribes to this message to do the receiving. This RIB message is triggered by submitting a schedule in ORFM. The output message is in hierarchical structure, with ASN information in the Description section, distro, item, and carton information in the detail sections. To facilitate the routing of data, the header level of routing information contains the to_phys_loc with the value of location ID and 'to_phys_loc_type' with the value W/S and from_phys_loc and from_phys_loc_type information. Detail level routing information contains the source_app with the value of 'ORFM'. This allows RIB to route the message from ORFM to RWMS/SIM.

Functionality Checklist

Table 5-1

Description	ORFM	RIB
ORFM must publish ASNOut information
Create new publisher	X	X

Form Impact

None

Business Object Records

None

Package Impact

This section covers the impact of the package – FM_SECONDARY_ASNOUT.

Business Object ID

The business object ID for ASNOut publisher is recv_no.

Package Name: FM_SECONDARY_ASNOUT

Spec File Name: fm_secondary_asnout_sql_s.pls

Body File Name: fm_secondary_asnout_sql_b.pls

Package Specification – Global Variables

L_FAMILY constant varchar2(25) := asnout

Function Level Description

ADDTOQ – The inbound Schedule for a transfer is published from ORFM as one ASNout message to SIM/RWMS and PO published to SIM.This function adds a record in the FM_RIB_RECEIVING_MFQUEUE table with asnoutmod message_type for transfer, asnoutcre for PO/Two-legged transfer/Repairing and ASNOut family for each inbound schedule/ASN. The published flag should be set to U.

GETNXT – The RIB calls GETNXT function to obtain ASNOut messages. It performs a cursor loop on the unpublished records with PUB_STATUS = U and FAMILY=asnout on the FM_RIB_RECEIVING_MFQUEUE table. For each record retrieved, GETNXT obtains the following:

• A lock on the queue FM_RIB_RECEIVING_MFQUEUE table for the records which ORFM is going to publish. The lock is obtained by calling the function LOCK_THE_BLOCK.

• The information from the FM_RIB_RECEIVING_MFQUEUE table is passed to PROCESS_QUEUE_RECORD function. This function will build the Oracle Object message for RIB.

  • If PROCESS_QUEUE_RECORD does not run successfully, GETNXT raises an exception call to HANDLE_ERRORS function that updates the PUB_STATUS to 'H'.

PUB_RETRY – It performs a cursor loop on the records with PUB_STATUS = H and FAMILY = asnoutmod. For each record retrieved, PUB_RETRY obtains the following:

• A lock on the queue FM_RIB_RECEIVING_MFQUEUE table for the records which ORFM is going to publish. The lock is obtained by calling the function LOCK_THE_BLOCK.

• The information from the FM_RIB_RECEIVING_MFQUEUE table is passed to PROCESS_QUEUE_RECORD function. This function will build the Oracle Object message for RIB.

  • If PROCESS_QUEUE_RECORD does not run successfully, PUB_RETRY raises an exception call to HANDLE_ERRORS function that updates the PUB_STATUS to 'H'.

PROCESS_QUEUE_RECORD (local) – This function is called from GETNXT and PUB_RETRY functions for those Inbound schedule records that are in PUB_STATUS = 'U' and 'H' respectively. This function calls API_LIBRARY.GET_RIB_SETTINGS to obtain the RIB settings for the ASNOut message family. It also calls BUILD_HEADER_OBJECT function to build the header and detail sections of secondary ASNOut message. Once the Oracle object is successfully formulated, this function deletes the current record from the queue (that is, FM_RIB_RECEIVING_MFQUEUE table) by calling DELETE_QUEUE_REC function.

BUILD_HEADER_OBJECT (local) – This function will take necessary data from FM_RIB_STG_RECEIVING_HEADER and FM_RIB_STG_RECEIVING_DETAIL tables for the current schedule_nbr/ASN and prepare the Oracle object for the ASNOut message. The structure of ASNOut is same as base payloads with an additional attribute containing schedule_nbr in the RIB_ASNOutDesc_REC section for RWMS.

The same function will build ASNOut message structure with an additional attribute of auto_receipt flag set to Y in the RIB_ASNOutDesc_REC section for SIM. This flag will otherwise be NULL in case of RWMS.

While publishing the secondary ASNOut to RWMS and SIM, ORFM will be sending three routing information through Oracle Object - RIB_ROUTINGINFO_REC.

1. Sending location

2. Receiving location

3. A hard-coded string depicting source application - ORFM

This is required to indicate the source of ASNOut message is ORFM and hence, it is meant only for RWMS and SIM applications to consume it. However, RMS also subscribes to ASNOut message, but in this case as the source is ORFM - RMS subscriber package will discard the message.

Call the BUILD_DETAIL_OBJECTS to obtain the details of the current schedule record. The container_qty is a required field on the RIB object. So, ORFM sends it as 1 instead of NULL.

This function will also be modified to publish the following additional fields in the publishing message to RWMS/SIM:

1. Schedule number (will be NULL for SIM)

2. Auto_receive flag (will be NULL for RWMS and Y for SIM)

BUILD_DETAIL_OBJECTS (local) – This function is responsible for building the detail section of ASNOut message. It builds as many detail Oracle Object as many items present in a transfer. It will fetch the detail records from FM_RIB_STG_RECEIVING_HEADER, FM_RIB_STG_RECEIVING_DETAIL for the given schedule number and will assign the above details into RIB_ASNOutItem_REC, RIB_ASNOutCtn_REC and RIB_ASNOutDistro_REC record groups.

This package will be modified to generate the ASN# and CTN# and will maintain the next up sequence number for it in case of PO to SIM and two-legged transfer/repairing publishing to RWMS/SIM.

LOCK_THE_BLOCK (local) – This function locks all queue records for the current business object. This is to ensure that GETNXT does not wait on any business processes that currently have the queue table locked and have not committed.

HANDLE_ERRORS (local) – HANDLE_ERRORS is called from GETNXT and PUB_RETRY when an exception is raised. If the error is a non-fatal error, GETNXT passes the sequence number of the driving FM_RIB_RECEIVING_MFQUEUE record back to the RIB in the ROUTING_INFO. It sends back a status of 'H'ospital to the RIB as well. It then updates the status of the queue record to 'H'ospital, so that it will not get picked up again by the driving cursor in GETNXT.

DELETE_QUEUE_REC (local) – DELETE_QUEUE_REC is called from PROCESS_QUEUE_RECORD once a queue record is formed from FM_RIB_RECEIVING_MFQUEUE, FM_RIB_STG_RECEIVING_DETAIL, and FM_RIB_STG_RECEIVING_HEADER tables. This is just to not pick the same record again.

Trigger Impact

Trigger Name

Not applicable

Trigger File Name

Not applicable

Table

Not applicable

Message XSD

The file name that corresponds with the message type. Refer the RIB documentation for this message type in order to obtain a detailed picture of the composition of the message.

Table 5-2

Message Types	Message Type Description	XML Schema Definition (XSD)
ASNOutCre	ASNOut Create message	ASNOutDesc.xsd
ASNOutMod	ASNOut Modify message	ASNOutDesc.xsd

Table Impact

Table 5-3

TABLE	SELECT	INSERT	UPDATE	DELETE
FM_RIB_RECEIVING_MFQUEUE	Y	Y	Y	Y
FM_RIB_STG_RECEIVING_HEADER	Y	Y	N	Y
FM_RIB_STG_RECEIVING_DETAIL	Y	Y	N	Y
FM_RECEIVING_HEADER	Y	N	N	N
FM_RECEIVING_DETAIL	Y	N	N	N

Design Assumptions

This message is applicable only if ORFM exists in case of Brazil countries. It is applicable for PO/Schedule publication to SIM and transfer/schedule publishing to RWMS/SIM.

POSchedule Publication API

The business overview, functionality checklist, and package impact of the POSchedule Publication API is covered in the subsequent sections.

Business Overview

ORFM publishes this message while submitting a schedule based on 'PO' to RWMS for receiving. RWMS subscribes to this message in order to create schedule based appointment and does the receiving.

This RIB message is triggered by submitting of PO based Schedule in ORFM. The output message is in hierarchical structure, with Schedule no in the Description section, PO information in the Header and Item details in the detail section.

To facilitate the routing of data, the header level of routing info contains the to_phys_loc with the value of location ID and to_phys_loc_type with the value W. Detail level routing info contains the source_app with the value of ORFM. This allows the RIB to route the message from ORFM to RWMS.

Functionality Checklist

Table 5-4

Description	ORFM	RIB
ORFM must publish POSchedule information
Create new publisher	X	X

Form Impact

None

Business Object Records

None

Package Impact

This section covers the impact of the package – FM_SCHED_SUBMIT.

Business Object ID

The business object ID for POSchedule publisher is recv_no that is, schedule no.

Package Name: FM_SCHED_SUBMIT

Spec File Name: fm_sched_submit_sql_s.pls

Body File Name: fm_sched_submit_sql_b.pls

Package Specification - Global Variables

FAMILY constant varchar2(25) := poschedule

LP_cre_type varchar2(15) := poschedulecre.

Function Level Description

ADDTOQ – This function adds the PO Schedule to the FM_RIB_RECEIVING_MFQUEUE table in 'U'npublished status. It stages the recv_no (schedule number) for publishing to the RIB.

GETNXT – This procedure is called from the RIB to obtain the next 'U'npublished schedule number from FM_RIB_RECEIVING_MFQUEUE table for publishing. Based on the seq_no on FM_RIB_RECEIVING_MFQUEUE, it calls the PROCESS_QUEUE_RECORD procedure.

GETNXT obtains the following:

• A lock on the queue FM_RIB_RECEIVING_MFQUEUE table for the records which ORFM is going to publish. The lock is obtained by calling the function LOCK_THE_BLOCK.

• The information from the FM_RIB_RECEIVING_MFQUEUE table is passed to PROCESS_QUEUE_RECORD function. This function will build the Oracle Object message for RIB.

  If PROCESS_QUEUE_RECORD does not run successfully, GETNXT raises an exception.

• If any exception is raised in GETNXT, including the exception raised by an unsuccessful call to PROCESS_QUEUE_RECORD, HANDLE_ERRORS is called.

PUB_RETRY – It will process the record for PUB_STATUS = 'H' and MESSAGE_TYPE = poschedcre. For each record retrieved, PUB_RETRY obtains the following:-

• A lock on the queue FM_RIB_RECEIVING_MFQUEUE table for the records which ORFM is going to publish. The lock is obtained by calling the function LOCK_THE_BLOCK.

• The information from the FM_RIB_RECEIVING_MFQUEUE table is passed to PROCESS_QUEUE_RECORD function. This function will build the Oracle Object message for RIB.

• If PROCESS_QUEUE_RECORD does not run successfully, PUB_RETRY raises an exception. Call to HANDLE_ERRORS function that updates the PUB_STATUS to 'H'.

• If any exception is raised in GETNXT, including the exception raised by an unsuccessful call to PROCESS_QUEUE_RECORD, HANDLE_ERRORS is called.

PROCESS_QUEUE_RECORD (local) – This function is called from GETNXT and PUB_RETRY functions for those Inbound schedule records that are in PUB_STATUS = U and H respectively. This function calls API_LIBRARY.GET_RIB_SETTINGS to obtain the RIB settings for its family (posched). It also calls BUILD_HEADER_OBJECT function to build header and detail sections of the PO Schedule Oracle objects. Once the Oracle object is successfully formulated, this function deletes the current record from the queue (that is, FM_RIB_RECEIVING_MFQUEUE table) by calling DELETE_QUEUE_REC function.

BUILD_HEADER_OBJECT (local) – Take all necessary data from FM_RIB_STG_RECEIVING_HEADER and FM_RIB_STG_RECEIVING_DETAIL tables for the current schedule and put it into a RIB_POScheduleDesc_REC object. Two routing information (source Application that is, ORFM and location that is, Warehouse) has to be sent to RIB through RIB_ROUTINGINFO_REC for PO.

This function publishes the following fields in the header section of the POSchedule message to RWMS:

• schedule_nbr

• receiving_location_id

This function will give a call to the BUILD_DETAIL_OBJECTS function.

BUILD_DETAIL_OBJECTS (local) – The function is responsible for building detail level Oracle Objects. It fetches the detail records from FM_RIB_STG_RECEIVING_DETAIL for the given schedule number and formulates the message structures: RIB_POSchedule_REC and RIB_POScheduleDtl_REC.

This function publishes the following fields to the above Oracle objects:

• requisition_nbr

• requisition_type

• item_id

• consolidate_qty

LOCK_THE_BLOCK (local) – This function locks all queue records for the current schedule_nbr. This is to ensure that GETNXT does not wait on any business processes that currently have the queue table locked and have not committed.

HANDLE_ERRORS (local) – HANDLE_ERRORS is called from GETNXT and PUB_RETRY when an exception is raised. If the error is a non-fatal error, GETNXT passes the sequence number of the driving FM_RIB_RECEIVING_MFQUEUE record back to the RIB in the ROUTING_INFO. It sends back a status of 'H'ospital to the RIB as well. It then updates the status of the queue record to 'H'ospital, so that it will not get picked up again by the driving cursor in GETNXT.

If the error is a fatal error, a status of 'E'rror is returned to the RIB.

DELETE_QUEUE_REC (local) – DELETE_QUEUE_REC is called from PROCESS_QUEUE_RECORD once a queue record is formed from FM_RIB_RECEIVING_MFQUEUE, FM_RIB_STG_RECEIVING_DETAIL, FM_RIB_STG_RECEIVING_HEADER tables. This is just to not pick the same record again.

Trigger Impact

Trigger Name

Not applicable

Trigger File Name

Not applicable

Table

Not applicable

Message XSD

The file name that corresponds with the message type. Refer to the RIB documentation for this message type in order to obtain a detailed picture of the composition of the message.

Table 5-5

Message Types	Message Type Description	XML Schema Definition (XSD)
POScheduleCre	POSchedule Create message	POScheduleDesc.xsd

Table Impact

Table 5-6

TABLE	SELECT	INSERT	UPDATE	DELETE
FM_RIB_RECEIVING_MFQUEUE	Y	Y	Y	Y
FM_RIB_STG_RECEIVING_HEADER	Y	Y	N	Y
FM_RIB_STG_RECEIVING_DETAIL	Y	Y	N	Y
FM_RECEIVING_HEADER	Y	N	N	N
FM_RECEIVING_DETAIL	Y	N	N	N

Design Assumptions

This message is applicable only if ORFM exists for Brazil localization. It is applicable for PO/Schedule publication to RWMS only.

Shipment Information Subscription API

The business overview, functionality checklist, and package impact of the Shipment Information Subscription API is covered in the subsequent sections.

Business Overview

ORFM subscribes this message in order to be able to generate and approve the NF-e.

This RIB message is triggered by RWMS and SIM for the following scenarios:

• Customer Order Fulfillment (direct customer delivery)

• Customer Order Fulfillment (multi-site fulfillment)

• Stock Orders (regular transfers between locations)

• RTVs*

Assumptions

RWMS does not support NF Request for RTVs. Only SIM can publish ShipInfo message for RTV transactions. Because in SIM the RTV and RTW are handled in the same context. ORFM will be able to consume ShipInfo for both, however only for RTW the message will trigger NF creation. For RTV the ShipInfo message will be ignored and NF will be created based on physical shipment and ASNOut message.

To facilitate the routing of data, RFM must be able to identify the transaction type that is being performed for each NF Request message. Some fields in the message structure will be leveraged to address this need.

Table 5-7 contains all valid combinations and the fields to be used to differentiate the transactions:

Table 5-7

distro_doc_type	Consumer_direct	to_location	Transaction Type for RFM
T	Y	Non-stockholding	Direct delivery CO transfer (NF request created at WH) Flow link
C	Y	Null	*Customer pick up at store (NF request created at store) Flow link
T	Y	Stockholding	Multi-site CO transfer Flow Link WH ST
T	N	Stockholding	Regular location to location transfer Flow Link WH ST
S	N	Stockholding	Regular location to location transferFlow Link WH ST
A	N	Stockholding	Regular location to location transferFlow Link WH ST
Null for RTV and ’T' for RTW	N	Vendor ID or WH ID	Returns from store to Vendor or WH Flow Link ST

Invalid combinations will result in error while consuming the ShipInfoDesc message which will be rejected in ORFM.

Functionality Checklist

None

Form Impact

None

Business Object Records

None

Package Impact

This section covers the impact of the package – FM_SHIPINFO_SQL.

Package Name: FM_SHIPINFO_SQL

Spec File Name: fm_shipinfo_sql_s.pls

Body File Name: fm_shipinfo_sql_b.pls

Package Specification - Global Variables

LP_shipinfo_type varchar2(15) := ’shipinfocre'

Function/Procedure Level Description

CONSUME – The RIB calls CONSUME procedure in order to be able to generate and approve the NF-e.

This procedure call function CONSUME_SHIPINFO responsible to make a bridge of process expected.

The function will be calling FM_STG_SHIPINFO_SQL.CONSUME add records into FM_STG_ASNOUT_DESC, FM_STG_ASNOUT_DISTRO, FM_STG_ASNOUT_CTN and FM_STG_ASNOUT_ITEM, validating message received based in the combination of values mentioned previously.

When everything is performed, CONSUME_SHIPINFO will be calling FM_EXIT_NF_CREATION_SQL to generate the NF-e.

Trigger Impact

Trigger Name

Not applicable

Trigger File Name

Not applicable

Table

Not applicable

Message XSD

The file name that corresponds with the message type. Refer the RIB documentation for this message type in order to obtain a detailed picture of the composition of the message.

Table 5-8

Message Types	Message Type Description	XML Schema Definition (XSD)
ShipInfoCre	ShipInfo Create message	ShipInfoDesc.xsd

Table Impact

Table 5-9

TABLE	SELECT	INSERT	UPDATE	DELETE
FM_STG_ASNOUT_DISTRO	Y	Y	Y	Y
FM_STG_ASNOUT_DESC	Y	Y	Y	Y
FM_STG_ASNOUT_CTN	Y	Y	Y	Y
FM_STG_ASNOUT_ITEM	Y	Y	Y	Y

Design Assumptions

This message is applicable only if ORFM exists for Brazil localization. It is applicable to generate and approve the NF-e subscribed by RWMS/SIM only.