How to solve this Error

A message sent to adapter "FILE" on send port "sp_856_EDI_Out_FILE" 
with URI "e:\Messages\MyApp\Process\856EDI_Temp\%SourceFileName%" is suspended. 
 Error details: Unable to read the stream produced by the pipeline. 
 Details: Error: 1 (Field level error)
	SegmentID: N4
	Position in TS: 9
	Data Element ID: N401
	Position in Segment: 1
	Data Value: 
	6: Leading or trailing space found  
 MessageId:  {A29AF353-8E1D-49AA-BEDE-D6318C0F3F79}
 InstanceID: {980FBB92-21B7-4B86-AA0C-54CA810AA850}

Solution 1 – Fix the bad data

The N401 refers to the first element of the N4 segment, which is the city of the city, state, address.
In our case, the client sent us this in the EDI 850 message.

N4^CLOVIS    ^CA^93612^US~


They should have trimmed the spaces after the city name of “CLOVIS”. Oddly enough, the error did not occur on the 850, but on the 856. So we stored the data in our database with the trailing blanks. We extract data to an XML file and saved to disk, so I was able to edit the XML and resubmit it. If you can do this or not depends on how you have your set up your send/receive port structure (or your orchestration flow).

Solution 2 – Turn off EDI validation

Validation can be changed as follows:
1) From BizTalk Admin console, expand the “Parties”
2) Find the Profile, then the Agreement for the specific party. Open the agreement.
3) Click on the top tab that indicates message sent to that party.
4) Click on the left option called “Validation” and you will see something like the screen below.

EDI Validation Options

EDI Validation Options

 

	a:ActionNotSupported
	The message with Action '<BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Operation Name="Method1" Action="http://tempuri.org/Ib2b/Method1" />
  <Operation Name="Method2" Action="http://tempuri.org/Ib2b/Method2" />
  <...' cannot be processed at the receiver, due to a ContractFilter mismatch at the EndpointDispatcher. This may be because of either a contract mismatch (mismatched Actions between sender and receiver) or a binding/security mismatch between the sender and the receiver.  Check that sender and receiver have the same contract and the same binding (including security requirements, e.g. Message, Transport, None).

To further complicate matters, the orchestration that I inherited took the above message and loaded into XML, which failed with this message: {“‘<‘, hexadecimal value 0x3C, is an invalid attribute character. Line 8, position 501.”} (The line/position numbers may be different, because I changed the sample above). The basic issue is that BizTalk, when building the message, built some invalid XML in the faultstring. In my case, I had a lot more methods, and they were truncated in the middle of an attribute. So the XML parser never found the close of the attribute “Action=”, and when it found the next < sign, it decided that was an invalid character in an attribute.

One quick possibility is that you might be pointing to the wrong service (i.e. your orchestration is bound to the wrong endpoint). Or the web service you are calling has a method removed or renamed (and perhaps they didn’t communicate that with you).

When you call a web service method (also known as “Operation”), you specify the operation name in the second box of the logical send port; for example “Method3” in the Port_Demo below. In the main part of the orchestration, there is a send and receive shape tied to this port.

Orchestration_Logical_SendPort_Operation
(Note: when you create a new logical port in the orchestration, the method name will default to “Operation_1”, and you will need to change that to one of your method (operation) names.

I’m going to mention some basic here in case you are new to BizTalk:
1) The orchestration is deployed and the you find it under it’s Application in BizTalk Admin Console.
2) In BizTalk Admin, the orchestration logical (internal) ports need to be bound to SendPorts defined in BizTalk Administration. One of those SendPorts will be a webservice for this example.
3) In that web service SendPort, if you click Configure, then on the “General” tab there will be a section called “Action”. The value will look something like this, depending on your method names and namespaces.

 

SendPort_BtsActionMapping

So if you try to call Method3, which is non-existent in the webservice specified in the SendPort, you will get this error (or a similar one). It could be that someone changed the webservice, and you need to rebuild you schemas and your binding file to pickup the new or changed methods. So in other words, you could be passing a request message that doesn’t match one of the operation names.

It’s also possible that you have a new Method3 created on the development/test system, but perhaps you are pointing to a QA or Production URL for the webservice.  You either have to point back to the development/test system, or have someone promote the web service code up to the next environment of the URL that you are using.

Another “obvious” thing to check is the orchestration binding.  Make sure the internal port names (called “Outbound Logical Ports” in the binding), match the proper “Send Port”.  If you pick the wrong SendPort, that will cause this error.

Originally when you ran the ‘Add Generated Items”, and selected “Consume WCF Service”, then Visual Studio built a schema file for you, containing all the request/responses messages for each of the methods. I believe, but have not verified for 100% of the scenarios, that the root element of the request message will match the operation (method) name. Then the response message schema will be the same with the letters “Response” suffixed on the end.

For further information: StackOverflow

I got this error today in a BizTalk orchestration: “Identifier “abc” does not exist in ‘global_NS.AppName”; are you missing an assembly reference?” Basically, the auto-complete (Intellisense) wasn’t giving my .BizTalkComponents namespace after I typed in “AppName.”. So I typed it in anyway, and pressed CNTL-Space-Bar, and that’s when the error occured.

I had typed in AppName.BizTalkComponents, which was a valid namespace in C# program, to which I had made a project reference.

Normally, I follow naming conventions that match the BizTalk Deployment Framework, but in this case, I has an old application, written by a predecessor. I use a Find/Replace Utility to replace the namespace from NewAppname to Appname.
So, the namespace of the orchestration itself was Appname. I should have named it Appname.BizTalk or something similar.

So to fix the issue, I overrode the namespace of the orchestration to Appname to Appname.BizTalk (as shown below):

BizTalk_Orchestration_Namespace

This blog explains how to pass userid/password using Basic Authentication to a SOAP 1.1 web service. It took me about 2 1/2 days of trying different things to get it working. SOAP-UI was working, and I couldn’t get any of the Basic-HTTP, WS-HTTP, or WCF-Custom SendPorts to work, even after creating a “behaviorEnhancement” plug-in.

Since SOAP-UI was working, I finally followed advice of blogs and coworkers to use Fiddler to capture the request with SOAP Headers. Then it only took an hour or two to properly format the request for a message assignment shape in an orchestration.

This was the error I was getting when it wasn’t working, i.e. trying to configure the user/pass in the ports. I knew from SOAP-UI that if I passed a bad password, I would get “Not Authenticated” instead of “Unauthorized”.

<soap:Fault xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <faultcode>soap:Server</faultcode>
  <faultstring>Unauthorized</faultstring>
</soap:Fault>

Fiddler provided the following capture:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:typ="http://somesite.com/serviceOrder/service/types">
   <soapenv:Header>
   <wsse:Security soapenv:mustUnderstand="1" xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd" xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
   <wsse:UsernameToken wsu:Id="UsernameToken-E4E3CB1F68472DCF2914369675811859">
	   <wsse:Username>myuser</wsse:Username>
	   <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">mypass</wsse:Password>
	   <wsse:Nonce EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">NgLGMIejoGGLznG/+Qma5g==</wsse:Nonce>
	   <wsu:Created>2015-07-15T13:39:41.185Z</wsu:Created>
   </wsse:UsernameToken>
   </wsse:Security>
   </soapenv:Header>
   <soapenv:Body>
      <typ:serviceJobListRequest>
         <!--Various Parms Here:-->
      </typ:serviceJobListRequest>
   </soapenv:Body>
</soapenv:Envelope>

The code below worked for me. It goes in a Message Assignment shape (usually after your map) before the Send shape to the web service (as shown in the red box below):

MessageAssignment_Set_Custom_SoapHeaders_Example

xmlDocSoapHeader.LoadXml(
   "<headers>" + 
   "<wsse:Security mustUnderstand=\"1\" xmlns:wsse=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd\" xmlns:wsu=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd\">" + 
   "<wsse:UsernameToken wsu:Id=\"UsernameToken-E4E3CB1F68472DCF2914369675811859\">" +   
   "  <wsse:Username>" + strUserFromSSO + "</wsse:Username>" + 
   "  <wsse:Password Type=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText\">" + strPasswordFromSSO + "</wsse:Password>" + 
   "  <wsse:Nonce EncodingType=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary\">NgLGMIejoGGLznG/+Qma5g==</wsse:Nonce>" +
   "  <wsu:Created>" + System.DateTime.UtcNow.ToString("s") + "Z" + "</wsu:Created>" + 
   "</wsse:UsernameToken>" +
   "</wsse:Security>" + 
   "</headers>"  
);

msgListNewCallRequest(WCF.OutboundCustomHeaders) = xmlDocSoapHeader.OuterXml;

This also has a big advantage for our specific application. We might need to pass a different user/password for different regions. For example, the same app might run for USA and for Europe; so we can dynamically switch out the user based on some region or country code.

Here’s another trick. To test the XML Load in C#, rather than having to run the orchestration, I created a small console program and tested like this:


        static void Main(string[] args)
        {
            testSyntax(); 
            Console.WriteLine("Press enter to end:");
            Console.ReadLine();
        }

        public static void testSyntax()
        {
            System.Xml.XmlDocument xmlDocSoapHeader = new System.Xml.XmlDocument(); 
            string strUserFromSSO = "test"; 
            string strPasswordFromSSO = "test"; 
            Console.WriteLine("TestDate=" + System.DateTime.UtcNow.ToString("s") + "Z"); 
            // 02/10/2015 12:21 with Richard - remove soapenv:Header   THIS ONE WORKS!  
xmlDocSoapHeader.LoadXml(
   "<Header xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\">" + 
   "<wsse:Security soapenv:mustUnderstand=\"1\" xmlns:wsse=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd\" xmlns:wsu=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd\">" + 
   "<wsse:UsernameToken wsu:Id=\"UsernameToken-E4E3CB1F68472DCF2914369675811859\">" +   
   "  <wsse:Username>" + strUserFromSSO + "</wsse:Username>" + 
   "  <wsse:Password Type=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText\">" + strPasswordFromSSO + "</wsse:Password>" + 
   "  <wsse:Nonce EncodingType=\"http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary\">NgLGMIejoGGLznG/+Qma5g==</wsse:Nonce>" +
   "  <wsu:Created>" + System.DateTime.UtcNow.ToString("s") + "Z" + "</wsu:Created>" + 
   "</wsse:UsernameToken>" +
   "</wsse:Security>" + 
   "</Header>" 
    );

//msgListNewCallRequest(WCF.OutboundCustomHeaders) = xmlDocSoapHeader.OuterXml;
        }

This was my original MSDN social forum post on this issue. Most of the answers were of little value, which why I posted a full blog here.

I found the following links useful in my research of this issue:
1) Showed how to wrap or not wrap the soap headers: Forum Post
2) Similar: Forum Post
3) Similar problem, but from C# not BizTalk: Blog Post
4) Good overview and discussion (but from C# not BizTalk): StackOverflow
5) MSDN – over simplified way to add SoapHeaders: MSDN SoapHeaders
6) Related: Blog Post

Now, below are the blogs that lead me down the path of a custom WCF behavior extension, which still get me the “Unauthorized” result – so that was a loss of almost one day. I’m not saying it would never work, but I didn’t get it working. It required writing a C# program, putting it in the GAC, registering it to BizTalk, then magically it appears in the Behavior tab of the WCF-Custom SendPort. However, it was an interesting learning experience, but rather tedious.

1) Forum Post
2) Blog Post
3) Blog Post
4) How to Register Extensions: Blog Post

What do you when you cannot connect an element on the left to one on the right in a Biztalk map?  Today, I had a case where I was trying to update a map created by another developer.  I thought the values on the right side were missing, but later I noticed they had a value in the property.  At first, I thought these values were defaulted in the schema, but a closer look at the schema showed no default values.

So this comes to show that you can set the value of an element by just typing in the value in the “Value” property.  Once you have set that property, you can no longer drag and drop any value into that element on the GUI part of the map.

Here’s an example with a small schema with just two elements:

BizTalk Map - Setting Value Property

BizTalk Map – Setting Value Property

Below is an example what happens when you try to connect an element (after the value is set).  The icon will change to the “no drop” (circle with a slash through it) symbol.

Cannot Map to Target Element

Cannot Map to Target Element

So this goes to show that after 15 years or more of BizTalk, you can still learn new tricks and find new issues.

 

Today I needed to do a mass string replace in all files in a directory.  Years ago, I used to use BKReplacem.  Today I found FNR – Find and Replace, a free utility that worked great the first tiem I ran it.

At my current client, someone had created a BizTalk project a year ago, and called it NewABCApp. Now that I took it over, I wanted it to be called just ABCApp. With BizTalk, the namespaces get embedded deep everywhere, in schema, maps, orchestrations, etc…  It’s a real chore to change the code by hand.

I found this great tool: FNR.exe on CodePlex.  It downloads a .exe, you run it, enter your directory, old text, new text, various options and press run.  (Make sure you have checked out your code first; if it’s locked down by TFS or your source code control system, that might prevent the update.)

FNR.exe - Find and Replace Utility

FNR.exe – Find and Replace Utility

The tool can also be run from the command-line by adding various parms.

The source code is also available; it was written in C# by of EN Tech Solutions and coordinated by Eric Popivker.  It’s had over 32,000 downloads, and a rating of 4.5 out of 5.  It hasn’t been updated since May 2014, but that’s likely because it just works.

 

This blog shows how to write to MSMQ from C#. In an earlier blog, I showed how to write to MSMQ from Powershell.

There are two tricks:
1) With Transactional Queues you have to include the Transaction, and also specify the second parm on the .Send method.
2) MessagePriority is only supported in non-transactional queues.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Messaging;

namespace MSMQCSharpConsoleDemo
{
    class Program
    {
        static void Main(string[] args)
        {

            string queueName = @"FormatName:DIRECT=OS:ServerName\Private$\biztalknontrans";
            string message = "<helloWorld><MyMessage language='English' /></helloWorld>";
            // a good label with a key and date/time helps to see what's in the queue if the queue gets backed up  
            string label = "Order=12345 Date=" + System.DateTime.Now.ToString("yyyyMMdd hh:mm:ss");
            //label = null; 
            MessagePriority msgPriority = MessagePriority.Highest;
            WriteMSMQNoTrans(queueName, message, label, msgPriority);

            //queueName = @"FormatName:DIRECT=OS:ServerName\Private$\biztalktrans";
            //WriteMSMQTrans(queueName, message, label);  // Transactional queues do not support priority 

            Console.WriteLine("\n\nPress enter to end:");
            Console.ReadLine(); 
        }

        public static void WriteMSMQNoTrans(string queueName, string messageText, string label, MessagePriority msgPriority)
        {
            MessageQueue mq;
            try 
            {
                mq = new System.Messaging.MessageQueue(queueName);
                UTF8Encoding utf8 = new System.Text.UTF8Encoding(); 

                byte[] msgBytes = utf8.GetBytes(messageText);
                System.IO.MemoryStream msgStream = new System.IO.MemoryStream(); 
                msgStream.Write(msgBytes, 0, msgBytes.Length); 

                Message mm = new Message(); 
                mm.BodyStream = msgStream; 
                if (label != null) 
                {
                   mm.Label = label;
                }
                mm.Priority = msgPriority;
                mq.Send(mm);
                Console.WriteLine("Message sent (NonTrans)"); 
            }
            catch (Exception ex) 
            {
                string errMessage = "QueueName: " + queueName + " Error:" + ex.Message; 
                Console.WriteLine(errMessage); 
                System.Diagnostics.EventLog.WriteEntry("Logger",errMessage); 
            }
        }


        public static void WriteMSMQTrans(string queueName, string messageText, string label)
        {
            try
            {
                MessageQueue mq;
                MessageQueueTransaction tran = new MessageQueueTransaction();
                tran.Begin();
                mq = new System.Messaging.MessageQueue(queueName);
                UTF8Encoding utf8 = new System.Text.UTF8Encoding();

                byte[] msgBytes = utf8.GetBytes(messageText);
                System.IO.MemoryStream msgStream = new System.IO.MemoryStream();
                msgStream.Write(msgBytes, 0, msgBytes.Length);
                
                Message mm = new Message();
                mm.BodyStream = msgStream; 
                if (label != null)
                {
                    mm.Label = label;
                }
                //mq.Send(mm);  // wow with transactioanl queue this will run, not give an error, but no data will be written 
                                // You must include second parm below. 
                mq.Send(mm, MessageQueueTransactionType.Single);  
                 

                Console.WriteLine("Message sent (Trans)");
                tran.Commit();
                tran.Dispose(); 
            }
            catch (Exception ex)
            {
                string errMessage = "QueueName: " + queueName + " Error:" + ex.Message;
                Console.WriteLine(errMessage);
                System.Diagnostics.EventLog.WriteEntry("Logger", errMessage);
            }

            
        }

    }  // end class 
}

For further examples: https://msdn.microsoft.com/en-us/library/ms978430.aspx and https://support.microsoft.com/en-us/kb/815811

I had to spend a few hours today to dig into the batching features of BT2010 (I don’t think 2013 is much different, but don’t know about earlier releases).

The MSDN walkthrough can be found here:  2010 or 2013.  Finding the MSDN doc for BT2010 itself was a bit of a challenge. In my case, I was needing to debatch 855 and 856 EDI/X12 messages.

Built-In Convoy-Style Orchestration

So basically, there is a sequential convoy style orchestration that can be started, runs continuously, waiting on a receive to feed it more messages, and can spit out a batch occasionally (we will discuss what causes the batch to be created shortly).

Orchestration included with the BizTalk.EDI.Application

Four Orchestrations included with the BizTalk.EDI.Application

The following shows the batching orchestration running in the Group Hub / Running Instances.

Running Orchestration to do Batching

Running Orchestration to do Batching

Now, how do we start this orchestration, how do we stop it, and what causes the batched EDI files to be emitted?

Setting up the Agreement

I’m not going to cover BizTalk EDI from the very beginning, that would be another blog and a much longer one.  So I’m assuming you have already created some EDI files, and now you need to batch them, and thus you are already familiar with the Party/Agreements.

In the agreement, there is a “link” or item on the left called “Batch Configuration”.  From there, you can click “New Batch” and fill out the screen.  At first, I didn’t realize the screen was scrollable, so I didn’t see that about 70% of the options were not visible until one scrolls down.  So here is a screen shot of the “Batch Configuration” full expanded in length.  (I already clicked “New Batch” and entered some basic dummy data.)

Party/Agreement/Batch Configuration

Party/Agreement/Batch Configuration

 

You set up the Filter to some Receive Location that you have created.  You will drop the files you want to batch there.

When you click the “Start” button at the top, an orchestration will start running within a few seconds (the length of time might be dependent on your SQL polling interval BatchControlMessageRecvLoc, I’m still a little fuzzy on that).

Where do the batched EDI message go?

Per the walkthrough (link at the top), you create a SendPort with the following filters:

 

  • On the first line of the grid, select EDI.DestinationPartyName for Property, == for Operator, the name you selected for the party to send the batch to for Value, and And for Group by.
  • On the second line, select EDI.ToBeBatched for Property, == for Operator, and False for Value, and And for Group by.
  • On the third line, select EDI.BatchName for Property, == for Operator, and the name of the batch for Value. (Batch1 in my screen shot above).

 

When do the batch file get released (created)?

Back in the “Batch Configuration” (the tall screen above), there is a section called “Release”.  You can select four options:
1. Schedule
2. Maximum number of transaction sets in group or interchange
3. Maximum number of characterrs in interchange
4. External release trigger.

(You could have different batches, with different release mechanism.)

Use the schedule to release a file every 30 minute, every hour, every day, etc…
Here are samples of the schedule options:

Batch Schedule - Weekly

Batch Schedule – Weekly

 

Batch Schedule - Daily

Batch Schedule – Daily

 

Batch Schedule - Hourly

Batch Schedule – Hourly

I think the other option, the max number of transactions and characters, is fairly self explanatory.

The last option, “External release trigger”, is a little more interesting.  It is explained in detail on MDSN here.  The concept might be that you want to send a batch of EDI files based on some other factor.  Imagine for example that someone clicks a “Send” button on a web page.  That web page you would then be the “external trigger” that would cause a batched EDI file to be emitted.

In summary, the “external release trigger” involves creating a ReceiveLocation with the BatchControlMessageRecvPipeline Pipeline, then dropping a file like the one below in that Receive Location.  In the above example I made up, the “Send” button from the web page would create this XML and write the file in the BizTalk pickup directory tied to the appropriate Receive Location.

EDI Batching - External Release Trigger XML Message

EDI Batching – External Release Trigger XML Message

I hope this is a good summary.  I believe that if you have read this, then reading the MSDN articles and walk throughs will make a lot more sense, and you will get your EDI batching up and working faster.  Please comment below if you found this helpful, or if you see any corrections or improvements.

Remaining Questions

So the “Start” button at the top of the Batch Configuration starts the orchestration.  What if you want to quiesce your BizTalk system?  As usual, just stopping the host instances should to that.  If you need to redeploy your application, you would have to stop the orchestration; the question then is whether the last messages in the orchestration all get batched into one final file.  What if your trading partner just wanted one file per day?  Might be some logic you have to add here.  If I find answers, I will update the blog.

 

You can an “Invalid Pipeline Component” error when you browse to a .DLL in the “Choose Toolbox Items” dialog box.

Error

“You have selected an invalid pipeline component assembly. Please check security settings for the assembly if you are loading it from an UNC path.”

Pipeline Component Error

Pipeline Component Error

Solution

This Patric Wellink blog put on the right path to solving, but it was not very clear exactly what to do.

The following steps solved for me:
1. Find your namespace in your code, and copy it:

PipelineComponent_Namespace

2. Right click on the Pipeline Components Project in Visual Studio Solution Explorer.
Paste the namespace above into the “Default namespace” below.

See below, how the value in the red box is different from the namespace in the red box above.

Change the Default Namespace to Match

Change the Default Namespace to Match

I used the BizTalk Pipeline Component Wizard available for free download here: https://btsplcw.codeplex.com.  It automatically creates the .resx (resource) file. But then I changed the namespace in the code, but not in the project properties.  I believe the resource file inherits the namespace from the project properties.  And if your code has a different namespace, that is the cause of the problem.

You can also work around by not using the resource manager.  You can hard-code some of the names that are in the resource, such as: ComponentDescription, ComponentName, ComponentVersion.

ResourceFile_Sample

Example Resource File for BizTalk Pipeline Component

At first I started in that direction, but hit a road block on how to load an icon properly.

You can also get the error if you don’t have the appropriate attributes “mark up”, such as:
[ComponentCategory(CategoryTypes.CATID_PipelineComponent)]  (see http://bencops.blogspot.com/2006/02/you-have-selected-invalid-pipeline.html).

 

Sample SQL

select 
    to_char(sysdate,'D') as NumDayOfWeek, 
    to_char(sysdate,'DY') as DayOfWeek, 
    to_char(sysdate,'Dy') as DayOfWeekLower, 
    case when to_char(sysdate,'D') = 3 then 3 else 1 end as DaysBack 
    from dual; 

Result of Query

Oracle_Day_Of_Week

Explanation

to_char(sysdate,’D’) returns 1 for Sunday, 2 for Monday etc…

“dual” is of course the dummy table used to run a select statement when you don’t have any other table you want to use.
I needed to create an extract so on Tuesday morning, it could include data three days back (Saturday, Sunday, and Monday), and on all other days of the week, it would include data from the prior day back.