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);

 SELECT cra.receipt_number AS "Receipt Number",
         rm.name AS "Receipt Method",
         cra.amount AS "Amount",
         TO_CHAR (cra.receipt_date, 'DD-MON-YYYY') AS "Receipt Date",
         (SELECT rstat.meaning
            FROM applsys.fnd_lookup_values rstat
           WHERE     rstat.lookup_code = crh.status
                 AND rstat.lookup_type = 'RECEIPT_CREATION_STATUS'
                 AND rstat.language = 'US')
            AS "Receipt Status",
         TO_CHAR (crh.gl_date, 'DD-MON-YYYY') AS "GL Date",
         tsa.tangibleid AS "PSON",
         (SELECT treq.meaning
            FROM applsys.fnd_lookup_values treq
           WHERE     treq.lookup_code = tsa.reqtype
                 AND treq.lookup_type = 'IBY_TRXN_REQTYPES'
                 AND treq.language = 'US')
            AS "IBY Request Type",
         (SELECT ttype.meaning || ' | ' || ttype.description
            FROM applsys.fnd_lookup_values ttype
           WHERE     ttype.lookup_code = tsa.trxntypeid
                 AND ttype.lookup_type = 'IBY_TRXNTYPES'
                 AND ttype.language = 'US')
            AS "IBY Transaction Type",
         tsa.status AS "IBY Status Code",
         (SELECT istat.meaning || ' | ' || istat.description
            FROM applsys.fnd_lookup_values istat
           WHERE     istat.lookup_code = tsa.status
                 AND istat.lookup_type = 'IBY_TRANSACTION_STATUS'
                 AND istat.language = 'US')
            AS "IBY Status Description",
         tsa.bepcode AS "Payment System Error Code",
         tsa.bepmessage AS "Payment System Error Message"
    FROM ar.ar_cash_receipts_all cra
         INNER JOIN ar.ar_cash_receipt_history_all crh
            ON cra.cash_receipt_id = crh.cash_receipt_id
         INNER JOIN iby.iby_fndcpt_tx_operations fto
            ON cra.payment_trxn_extension_id = fto.trxn_extension_id
         INNER JOIN iby.iby_trxn_summaries_all tsa
            ON fto.transactionid = tsa.transactionid
         INNER JOIN ar.ar_receipt_methods rm
            ON cra.receipt_method_id = rm.receipt_method_id
   WHERE     1 = 1
                AND  crh.status NOT IN ('CLEARED', 'REVERSED')
         -- AND  cra.receipt_number                     = '11068970'--:Receipt_Number
         AND rm.name='CardConnect US'
         AND crh.current_record_flag = 'Y'
         AND cra.receipt_date >='01-OCT-2016'
         AND tsa.status<>0
         AND tsa.updatedate = (SELECT MAX (tsa2.updatedate)
                                 FROM iby.iby_trxn_summaries_all tsa2
                                WHERE tsa2.transactionid = tsa.transactionid)
ORDER BY cra.receipt_date DESC, cra.receipt_number;

References:
http://sairamgoudmalla.blogspot.com/2014/05/credit-card-sales-order-queries.html

iby_trxn_summaries_all.status & TRXNTYPEID

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

Value data-source is IBY_TRXN_SUMMARIES_ALL.TRXNTYPEID

• 2 (Auth only)
• 3 (Auth capture)
• 5 (Return)
• 8/9 (Capture/Settlement)
• 11 (Credit)
  
  

References:
http://sairamgoudmalla.blogspot.com/2014/05/ibytrxnsummariesallstatus.html

Query To Get Form Personalizations Details

1)  SELECT FORM_ID, FORM_NAME, USER_FORM_NAME, DESCRIPTION
     FROM FND_FORM_VL
     WHERE FORM_NAME IN (SELECT FORM_NAME FROM FND_FORM_CUSTOM_RULES      GROUP BY FORM_NAME)

2) Query to Get Form Personalization Details ( Oracle Applications )  from Database.

FND_FORM_CUSTOM_RULES - The Rules for the form customization's. A rule must have 1 more more FND_FORM_CUSTOM_SCOPES and a rule may have 1 or more FND_FORM_CUSTOM_ACTIONS.
FND_FORM_CUSTOM_ACTIONS - Holds the Actions for a specified Rule
FND_FORM - stores information about your registered application forms. Each row includes names (the actual SQL*Forms form name, and the Easy Form form title) and a description of the form. Each row also includes a flag that indicates whether this form is included in the Audit Trail audit set. You need one row for each form in each application. Oracle Application


Select Distinct
    A.Id,
    A.Form_Name ,
    A.Enabled,
    C.User_Form_Name,
    D.Application_Name ,
    A.Description,
    Ca.Action_Type,
    Ca.Enabled,
    Ca.Object_Type,
    ca.message_type,
    ca.message_text
from
    FND_FORM_CUSTOM_RULES a,
    FND_FORM b,
    FND_FORM_TL c,
    Fnd_Application_Tl D,
    Fnd_Form_Custom_Actions ca
where a.form_name = b.form_name
    And B.Form_Id = C.Form_Id
    And B.Application_Id = D.Application_Id
    And D.Application_Id = 230 --For Order Management
    And C.User_Form_Name Like 'Inventory%'  --All the Forms that Start with Sales
    And A.Enabled ='Y'
    and a.id = ca.rule_id


SELECT
    ffv.form_id          "Form ID",
    ffv.form_name        "Form Name",
    ffv.user_form_name   "User Form Name",
    ffv.description      "Form Description",
    ffcr.sequence        "Sequence",
    ffcr.description     "Personalization Rule Name"
FROM fnd_form_vl             ffv,
       fnd_form_custom_rules   ffcr
WHERE ffv.form_name = ffcr.form_name
ORDER BY ffv.form_name, ffcr.sequence;



SELECT 
    ffcr.SEQUENCE "Seq", ffcr.description "Description",
    DECODE (ffcr.rule_type,
           'F', 'Form',
            'A', 'Function',
            'Other'
           ) "Level",
    ffcr.enabled "Enabled",
    ffcr.trigger_event "Trigger Event",
    ffcr.trigger_object "Trigger Object",
    ffcr.condition "Condition",
    DECODE (ffcr.fire_in_enter_query,
            'Y', 'Both',
            'N', 'Not in Enter-Query Mode',
            'O', 'Only in Enter-Query Mode',
            'Other'
           ) "Processing Mode"
FROM apps.fnd_form_custom_rules ffcr
WHERE ffcr.function_name = 'PO_POXPOEPO'
    AND ffcr.form_name = 'POXPOEPO'
ORDER BY ffcr.SEQUENCE;

References:
http://amaralam.blogspot.com/2016_04_01_archive.html

Query to Find Requisition Number from Purchase Order Number

Query to find the purchase order number from requisition number:

SELECT DISTINCT pha.segment1
FROM po_headers_all pha,po_distributions_all pda,po_req_distributions_all rda,
po_requisition_headers_all rha,po_requisition_lines_all rla
WHERE 1=1
AND pha.po_header_id=pda.po_header_id
AND pda.req_distribution_id=rda.distribution_id
AND rda.requisition_line_id=rla.requisition_line_id
AND rla.requisition_header_id=rha.requisition_header_id
AND rha.segment1=:P_REQ_NUM;

Query to find the requisition number from purchase order number:

SELECT DISTINCT rha.segment1
FROM po_requisition_headers_all rha,po_requisition_lines_all rla,
po_req_distributions_all rda,po_distributions_all pda,po_headers_all pha
WHERE 1=1
AND rha.requisition_header_id=rla.requisition_header_id
AND rla.requisition_line_id=rda.requisition_line_id
AND rda.distribution_id=pda.req_distribution_id
AND pda.po_header_id=pha.po_header_id
AND pha.segment1=:P_PO_NUMBER;

References:
http://amaralam.blogspot.com/2016_04_01_archive.html

Multi-Org or Multiple Organization Access (MOAC) in R12

What is MOAC?
Multi-Org or multiple organization access (MOAC) is basically an ability to access multiple operating units from a single application responsibility.

Why it has been created?
Prior to R12, end users use to toggle / switch / change responsibilities in order to do transactions (like invoice / payment processing in AP) in different operating units. This is a very time consuming and inefficient way of recording transactions when you have 100s of operating units specially Internet based organizations who have worldwide operations in almost all the countries. To address this, a new feature in R12 has been introduced in which user can switch between operating units within a responsibility something similar to “Change Organization” feature in inventory. Prior to R12, user would have to switch responsibilities in order to enter transactions in respective operating units (tagged to the responsibility).

What are its advantages?
Multi-Org Access Control (MOAC) enables companies that have implemented a Shared Services operating model to efficiently process business transactions by allowing them to access, process and report on data for an unlimited number of operating units within a single applications responsibility.
This increases the productivity of Shared Service Centers, as users no longer have to switch application responsibilities when processing transactions for multiple operating units at a time.
Ability to view data from multiple operating units from a single responsibility, gives users more information. This enables them to make better decisions.
The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them.

SELECT   psp.SECURITY_PROFILE_NAME,
         psp.SECURITY_PROFILE_ID,
         hou.NAME,
         hou.ORGANIZATION_ID
FROM     PER_SECURITY_PROFILES psp,
         PER_SECURITY_ORGANIZATIONS pso,
         HR_OPERATING_UNITS hou
WHERE    pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
         AND pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;
There are three Profile Options you need to be aware of related to Multi-Org that should be set at the Responsibility Level.
MO: Security Profile- Always evaluated first.
MO: Operating Unit- Secondary priority being evaluated after ‘MO: Security Profile’
MO: Default Operating Unit- Sets the default Operating Unit for transactions when running under a Security Profile.

How it is done in R12?
In Release 12, one creates a Security Profile and assigns as many operating units as you required. One can tie that security profile to a single responsibility using a profile option called MO: Security Profile. For example, you could assign the security profile to the EMEA Payables responsibility to allow that responsibility to process invoices across all operating units.
In Release 12, define a security profile in HR using the Security profile form or the Global Security profile form, and assign all of the operating units that one would want a responsibility to access. The one needs to run a concurrent request called “Run Security List Maintenance” from HR which will make those security profile available and allow one to assign them to a responsibility via a profile option called MO: Security Profile.
One can define another profile option called MO: Default Operating Unit which is optional and allows one to specify a default operating unit that will be the default when you open different subledger application forms.

References:
http://amaralam.blogspot.com/2013/04/what-is-moac-multi-org-or-multiple.html