iby_trxn_summaries_all.status

The table below contains the different statuses of transactions .Here is the meaning for them.

iby_trxn_summaries_all.status

SUCCESS 0

ERROR
1, Communication error
2, Duplicate request, order id
3, Duplicate batch id
4, Mandatory fields required or missing
5, Payment system specific error
6, Batch partially succeeded
7, Batch failed
8, Request action not supported
14, Request cancelled
15, Failed to schedule
16, Payment system failed
17, Unable to pay (insufficient funds)
19, Invalid Credit Card number
20, Declined
21, Failed (requires voice auth)
101, Trxn was sent in a batch that resulted in communication error
114, Trxn was cancelled in an open batch (void)
120, iPayment max number of batches exceeded for the day
-99, Invalid request

IBY_0002: Duplicate orderid (IBY_0002) Error Data Fix

IBY_0002: Duplicate orderid (IBY_0002)  Data Fix

1. ) Backup of the table iby_Trxn_summaries_all and run the following update for one particular line

update iby_Trxn_summaries_all summ set status = 14, last_update_date=sysdate,
object_version_number=-999, last_updated_by= 18765906
where summ.tangibleid = 'ONT8083' and trxntypeid = 2 and status = 0;
commit;

(The above update is corresponding to Lineid# 3819589, HeaderId# 1337983)

2) Now, try to process the line and see how it goes.

3) If this approach works and if you are able to process the line, you can go ahead and run the following generic fix

update iby_Trxn_summaries_all summ set status = 14, last_update_date=sysdate,
object_version_number=-999, last_updated_by= 18765906
where trxntypeid = 2 and status = 0 and initiator_extension_id is null and
not exists (select 'Y' from iby_fndcpt_tx_operations op where
summ.transactionid = op.transactionid);

Credit Card Sales Order Queries

/* -----List of Orders which are pick released on Sysdate ---------*/

SELECT oeh.header_id, ott.name Order_type,oeh.order_number,oeh.payment_type_code,rt.name payment_term,ool.line_number,

ool.ordered_item,ool.schedule_ship_date,ool.flow_status_code line_status,

ohd.name,ooh.order_hold_id,ooh.creation_date,ooh.released_flag

  FROM oe_order_headers_all oeh, oe_order_lines_all ool,oe_transaction_types_tl ott,

  ra_terms_tl rt,

  oe_order_holds_all ooh,

  oe_hold_sources_all ohs,

  oe_hold_definitions ohd

WHERE 1=1

 and oeh.header_id=ool.header_id

   AND oeh.flow_status_code != 'CLOSED'

   and ool.flow_status_code!='CANCELLED'

   and oeh.order_type_id=ott.transaction_type_id

   and ott.language='US'

   and oeh.payment_term_id=rt.term_id

   and rt.language='US'

   and oeh.header_id=ooh.header_id(+)

   and ooh.hold_source_id=ohs.hold_source_id(+)

   and ohs.hold_id=ohd.hold_id(+)

   and trunc(ool.schedule_ship_date)= to_date('30-APR-14','DD-MON-YY')

    and oeh.PAYMENT_TERM_ID in ( 1021 ,1143);

/*List of Standing Orders along with Credit Card Details on the first open line*/

   SELECT   OOHA.HEADER_ID,oola.line_number,oola.line_id,op.line_id,

  OOHA.ORDER_NUMBER,

  (SELECT hca.account_number

  FROM hz_cust_accounts hca

  WHERE hca.cust_account_id=ooha.sold_to_org_id

  ) CustomerNumber,

  OOHA.FLOW_STATUS_CODE "OrderStatus",

  OOHA.PAYMENT_TYPE_CODE,

  ooha.credit_card_number,

  OOLA.ORDERED_ITEM,

  OOLA.ORDERED_QUANTITY,

  OOLA.SHIPPED_QUANTITY,

  oola.invoiced_quantity,

  OOLA.TAX_VALUE,

  (OOLA.UNIT_SELLING_PRICE * OOLA.ORDERED_QUANTITY) ITEMTOTALAMOUNT,

  OOLA.FLOW_STATUS_CODE "LineStatus",

  IFTE.TRXN_EXTENSION_ID ,

  ic.ccnumber,

  ic.chname,

  ic.card_owner_id,

  ic.masked_cc_number,

  ic.card_issuer_code,

  ic.expirydate,

  ic.inactive_date

FROM OE_ORDER_HEADERS_ALL OOHA,

  OE_ORDER_LINES_ALL OOLA,

  OE_PAYMENTS OP,

  IBY_FNDCPT_TX_EXTENSIONS IFTE,

  IBY_PMT_INSTR_USES_ALL IPUA,

  IBY_CREDITCARD  IC

WHERE OOHA.HEADER_ID=OOLA.HEADER_ID

--AND OOHA.PAYMENT_TYPE_CODE LIKE 'CREDIT_CARD'

AND OP.line_ID(+)        =OOla.line_ID

AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)

  --AND IFTE.TRXN_EXTENSION_ID=ITSA.INITIATOR_EXTENSION_ID

AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)

AND IPUA.INSTRUMENT_ID      =IC.INSTRID(+)

AND ooha.flow_status_code != 'CLOSED'

 and ooha.PAYMENT_TERM_ID in ( 1021 ,1143)

 and ooha.order_type_id=1010

 and oola.line_id = (select min(l.line_id) from oe_order_lines_all l where l.header_id=ooha.header_id

 and l.flow_status_code not in ('CLOSED','CANCELLED'));

/List of Standing Orders for which credit card details are populated at header level/

SELECT   OOHA.HEADER_ID,  OOHA.ORDER_NUMBER,

  (SELECT hca.account_number

  FROM hz_cust_accounts hca

  WHERE hca.cust_account_id=ooha.sold_to_org_id

  ) CustomerNumber,

  OOHA.FLOW_STATUS_CODE "OrderStatus",

  OOHA.PAYMENT_TYPE_CODE,

  ooha.credit_card_number,

  IFTE.TRXN_EXTENSION_ID ,

  ic.ccnumber,

  ic.chname,

  ic.card_owner_id,

  ic.masked_cc_number,

  ic.card_issuer_code,

  ic.expirydate,

  ic.inactive_date

FROM OE_ORDER_HEADERS_ALL OOHA,

  OE_PAYMENTS OP,

  IBY_FNDCPT_TX_EXTENSIONS IFTE,

  IBY_PMT_INSTR_USES_ALL IPUA,

  IBY_CREDITCARD  IC

WHERE OP.header_id(+)        =OOha.header_id

and op.payment_level_code(+)='ORDER'

AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)

AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)

AND IPUA.INSTRUMENT_ID      =IC.INSTRID(+)

AND ooha.flow_status_code not in ('CLOSED' ,'CANCELLED')

 and ooha.PAYMENT_TERM_ID in ( 1021 ,1143)

 and ooha.order_type_id=1010;

XXXXXXXXXXXX0481

/**List of Standng order lines for with credit card details are shown******/

SELECT   OOHA.HEADER_ID,OOHA.ORDER_NUMBER,  OOHA.FLOW_STATUS_CODE "OrderStatus",oola.line_number,oola.flow_status_code,  oola.schedule_ship_date, 

  (SELECT hca.account_number

  FROM hz_cust_accounts hca

  WHERE hca.cust_account_id=ooha.sold_to_org_id

  ) CustomerNumber,

  OOHA.PAYMENT_TYPE_CODE,

  ooha.credit_card_number,

  OOLA.ORDERED_ITEM,

  OOLA.ORDERED_QUANTITY,

  OOLA.SHIPPED_QUANTITY,

  oola.invoiced_quantity,

  ic.ccnumber,

  ic.chname,

  ic.card_owner_id,

  ic.masked_cc_number,

  ic.card_issuer_code,

  ic.expirydate,

  ic.inactive_date

FROM OE_ORDER_HEADERS_ALL OOHA,

  OE_ORDER_LINES_ALL OOLA,

  OE_PAYMENTS OP,

  IBY_FNDCPT_TX_EXTENSIONS IFTE,

  IBY_PMT_INSTR_USES_ALL IPUA,

  IBY_CREDITCARD  IC

WHERE OOHA.HEADER_ID=OOLA.HEADER_ID

AND OP.line_ID(+)        =OOla.line_ID

AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)

AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)

AND IPUA.INSTRUMENT_ID      =IC.INSTRID(+)

AND ooha.flow_status_code not in ('CLOSED' ,'CANCELLED')

 and ooha.PAYMENT_TERM_ID in ( 1021 ,1143)

 and ooha.order_type_id=1010

 --and trunc(oola.schedule_ship_date)>trunc(sysdate)

--and trunc(oola.schedule_ship_date)= to_date('02-MAY-14','DD-MON-YY')

 and oola.line_id = (select min(l.line_id) from oe_order_lines_all l where l.header_id=ooha.header_id

 and l.flow_status_code not in ('CLOSED','CANCELLED'))

 order by oola.schedule_ship_date asc;

/**********query which we run daily to knows orders on hold***************/

SELECT ooh.header_id,oeh.header_id, ott.name Order_type,oeh.order_number,oeh.payment_type_code,rt.name payment_term,ool.line_number,

ool.ordered_item,ool.schedule_ship_date,ool.flow_status_code line_status,

ohd.name,ooh.order_hold_id,ooh.creation_date,ooh.released_flag

,ic.ccnumber,

  ic.chname,

  ic.card_owner_id,

  ic.masked_cc_number,

  ic.card_issuer_code,

  ic.expirydate,

  ic.inactive_date

  FROM oe_order_headers_all oeh, oe_order_lines_all ool,oe_transaction_types_tl ott,

  ra_terms_tl rt,

  oe_order_holds_all ooh,

  oe_hold_sources_all ohs,

  oe_hold_definitions ohd,

  OE_PAYMENTS OP,

  IBY_FNDCPT_TX_EXTENSIONS IFTE,

  IBY_PMT_INSTR_USES_ALL IPUA,

  IBY_CREDITCARD  IC 

WHERE 1=1

 and oeh.header_id=ool.header_id

   AND oeh.flow_status_code != 'CLOSED'

   and ool.flow_status_code!='CANCELLED'

   and oeh.order_type_id=ott.transaction_type_id

   and ott.language='US'

   and oeh.payment_term_id=rt.term_id

   and rt.language='US'

   and ool.header_id=ooh.header_id(+)

   and ool.line_id=nvl(ooh.line_id(+),ool.line_id)

 --  and ool.line_id=ooh.line_id(+)

   and ooh.hold_source_id=ohs.hold_source_id(+)

   and ohs.hold_id=ohd.hold_id(+)

   AND OP.line_ID(+)        =Ool.line_ID

AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)

--and op.payment_level_code(+)='ORDER'

  --AND IFTE.TRXN_EXTENSION_ID=ITSA.INITIATOR_EXTENSION_ID

AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)

AND IPUA.INSTRUMENT_ID      =IC.INSTRID(+)

   and trunc(ool.schedule_ship_date)= to_date('08-MAY-14','DD-MON-YY')

    and oeh.PAYMENT_TERM_ID in ( 1021 ,1143);

"Unsolicited Mail" Warning When Receiving Out-Of-Office Reply or Unable to Connect to Mail Store due to " javax.mail.internet.ParseException"

Symptoms

Case #1: Out-of-office Autoreply emails are being sent "Warning for Unsolicited Mail" messages, even though a mailer tag has been created to ignore "AutoReply:" messages.

The following email is received by the user who is Out of the Office:

Messages sent to this account are processed automatically by the Oracle Workflow Notification
Mailer. The message you sent did not appear to be in response to a notification. If you are
responding to a notification, please use the response template that was included with your
notification. Take care to include the 'NID' line of the template in your reply. If you are not
responding to a notification, please do not send mail to this account. Important: Some mail
clients, notably early releases of Microsoft Outlook Express, may not copy the 'NID' line properly
in your response. Please verify that the 'NID' line is included in full and contains the prefix
'NID' and all the details between the square brackets when responding.

When the Workflow Mailer encounters the TAG, it treats it as a Unsolicited Message:         EXCEPTION:[SVC-GSM-WFMLRSVC-63613-10006 : oracle.apps.fnd.wf.mailer.IMAPResponseHandler.processSingleMessage(Message)]: 
Processing Message SUBJECT[Out of Office AutoReply: Regional Buyer Assignment 
Message for Purchase Requisition 20103] UI 

oracle.apps.fnd.wf.mailer.IMAPResponseHandler.processSingleMessage(Message)]: 
Preparing to parse [Out of Office AutoReply: Regional Buyer Assignment Message 
for Purchase Requisition 20103] 

The Mailer log will reflect the following:        oracle.apps.fnd.wf.mailer.IMAPResponseHandler.handleUnsolicited(EmailParser)]: 
Successfully sent -> [Warning For Unsolicited Mail] [[Joe Shmoe  com>]] 
SVC-GSM-WFMLRSVC-63613-10006 : 
oracle.apps.fnd.wf.mailer.IMAPInboundProcessor.moveMessage(Message, String)]: 
Moving message to Discard

Case #2: In another case the Workflow Mailer stops working due to the following error:

:STATEMENT:[SVC-GSM-WFMLRSVC-1616026-10006 : oracle.apps.fnd.wf.mailer.EmailParser.parseEmail()]:<SUBJECT> SUBJECT[Autoreply-Re: FYI: Remittance Advice]
[Jul 27, 2010 8:28:45 PM EDT]:1280276925427:Thread[inboundThreadGroup1,5,inboundThreadGroup]:0:-1:auohscgna07:140.85.254.149:-1:-1:STATEMENT:[SVC-GSM-WFMLRSVC-1616026-10006 : oracle.apps.fnd.wf.mailer.EmailParser.parseEmail()]:
[Jul 27, 2010 8:28:45 PM EDT]:1280276925427:Thread[inboundThreadGroup1,5,inboundThreadGroup]:0:-1:auohscgna07:140.85.254.149:-1:-1:STATEMENT:[SVC-GSM-WFMLRSVC-1616026-10006 : oracle.apps.fnd.wf.mailer.EmailParser.parseEmail()]:
[Jul 27, 2010 8:28:45 PM EDT]:1280276925427:Thread[inboundThreadGroup1,5,inboundThreadGroup]:0:-1:auohscgna07:140.85.254.149:-1:-1:STATEMENT:[SVC-GSM-WFMLRSVC-1616026-10006 : oracle.apps.fnd.wf.mailer.EmailParser.parseEmail()]:
[Jul 27, 2010 8:28:45 PM EDT]:1280276925427:Thread[inboundThreadGroup1,5,inboundThreadGroup]:0:-1:auohscgna07:140.85.254.149:-1:-1:STATEMENT:[SVC-GSM-WFMLRSVC-1616026-10006 : oracle.apps.fnd.wf.mailer.EmailParser.parseEmail()]:
[Jul 27, 2010 8:28:45 PM EDT]:1280276925427:Thread[inboundThreadGroup1,5,inboundThreadGroup]:0:-1:auohscgna07:140.85.254.149:-1:-1:STATEMENT:[SVC-GSM-WFMLRSVC-1616026-10006 : oracle.apps.fnd.wf.mailer.EmailParser.parseEmail()]:Processing single -> text/plain; charset=us-ascii;
[Jul 27, 2010 8:28:45 PM EDT]:1280276925428:Thread[inboundThreadGroup1,5,inboundThreadGroup]:0:-1:auohscgna07:140.85.254.149:-1:-1:UNEXPECTED:[SVC-GSM-WFMLRSVC-1616026-10006 : oracle.apps.fnd.wf.mailer.IMAPResponseHandler.processSingleMessage(Message)]:Problem reaching content in step {Parsing email} -> javax.mail.internet.ParseException

After that, every 5 minutes I get the following error:

[Jul 27, 2010 3:04:34 PM EDT]:1280257474343:Thread[inboundThreadGroup1,5,inboundThreadGroup]:0:-1:auohscgna07:140.85.254.149:-1:-1:UNEXPECTED:[SVC-GSM-WFMLRSVC-1616026-10006 : oracle.apps.fnd.wf.mailer.IMAPInboundProcessor.open()]:Unable to connect to mail store

Changes

Case #1: SQL> select name, tag_id, action, pattern from wf_mailer_tags

-WF_DEFAULT- 1 UNDELVRD -- Unsent message follows --
-WF_DEFAULT- 2 UNDELVRD Host unknown
-WF_DEFAULT- 3 UNDELVRD The following addresses had delivery problems
-WF_DEFAULT- 4 UNDELVRD Transcript of session follows
-WF_DEFAULT- 5 UNAVAIL Auto-Reply: 
-WF_DEFAULT- 6 UNAVAIL Auto-reply: 
-WF_DEFAULT- 7 UNAVAIL Returned mail:
-WF_DEFAULT- 8 IGNORE SENDMAIL
-WF_DEFAULT- 9 IGNORE POSTMASTER
-WF_DEFAULT- 10 IGNORE Warning For Unsolicited Mail
-WF_DEFAULT- 21 IGNORE Warning: Your previous response to this message was 
invalid
-WF_DEFAULT- 11 IGNORE Warning: Your previous response to this message was 
invalid
wfprod 41 Out Office AutoReply: IGNORE

Case #2: SQL> select name, tag_id, action, pattern from wf_mailer_tags

-WF_DEFAULT- 1 UNDELVRD -- Unsent message follows --
-WF_DEFAULT- 2 UNDELVRD Host unknown
-WF_DEFAULT- 3 UNDELVRD The following addresses had delivery problems
-WF_DEFAULT- 4 UNDELVRD Transcript of session follows
-WF_DEFAULT- 5 UNAVAIL Auto-Reply: 
-WF_DEFAULT- 6 UNAVAIL Auto-reply: 
-WF_DEFAULT- 7 UNAVAIL Returned mail:
-WF_DEFAULT- 8 IGNORE SENDMAIL
-WF_DEFAULT- 9 IGNORE POSTMASTER
-WF_DEFAULT- 10 IGNORE Warning For Unsolicited Mail
-WF_DEFAULT- 21 IGNORE Warning: Your previous response to this message was 
invalid
-WF_DEFAULT- 11 IGNORE Warning: Your previous response to this message was 
-WFMAIL 22IGNORE Remittance Advice

Cause

Case #1 The Workflow Mailer Configuration was done and the Node Name was defined with lowercase lettters opposed to all UPPERCASE.

The Workflow Node Name must be capitalized during configuration.
If not, when creating a new tag, the NAME column for the tag will default to
the same name in the initial configuration. If not capitalized, the tag will
not be recognized, therefore the mailer will send an undoliciated message
and direct the intended message from its INBOX to the DISCARD folder.

Case #2: The Workflow Mailer is attempting to Parse the following Subject: Autoreply-Re: FYI: Remittance Advice

What is currently setup as a MAILER Tag has all the words divided into multiple tags when it should be just 1 tag

Solution

Case #1:

1. In the OAM >Workflow Mailer Configuration, change the Mailer Node Name to UPPERCASE Letters opposed  Lowercase

2. In the Mailer Tags, Delete the Out of Office Reply:

3. Save the Settings.

4. Stop and Restart the Mailer

5. In the Mailer Tags, Redo do the Out of Office Reply:

6. Now the Node Name in the Tag should be in Uppercase

SQL > select * from wf_mailer_tags

7. Test the Out of Office AutoReply: email now.

Case #2 

1. In the OAM >Workflow Mailer Configuration, add a TAG for  Subject: Autoreply-Re: FYI: Remittance Advice

2. In the Mailer Tags, Delete the following tags:

WFMAIL 25 IGNORE Autoreply
WFMAIL 26 IGNORE Re:
WFMAIL 42 IGNORE Remittance Advice
WFMAIL 43 IGNORE Automatic-Reply
WFMAIL 44 IGNORE FYI



3. Create the following new Mailer Tag:

WFMAIL 25 IGNORE Autoreply-Re: FYI: Remittance Advice


4. Save the Settings.

5. Stop and Restart the Mailer

6. SQL > select * from wf_mailer_tags

7. Test the Remittance Advise Workflow now