Table Lock - Query in Oracle APPS

 SELECT client_identifier,

       module,

       action,

       s.*

  FROM v$session s

 WHERE sid IN (SELECT session_id

                 FROM dba_dml_locks

                WHERE owner = 'AR' AND name = 'AR_ADJUSTMENTS_ALL')

Oracle EBS R12 – Query to View Consolidated Invoice Details with Individual Transactions

SELECT

    hp.party_name                  AS customer_name,

    aca.account_number             AS customer_account,

    hci.cons_billing_number        AS consolidated_bill_number,

    hci.issue_date                 AS bill_issue_date,

    rct.trx_number                 AS invoice_number,

    cit.transaction_type,

    cit.amount_original,

    cit.tax_original

FROM

    ar_cons_inv_all        hci,

    ar_cons_inv_trx_all    cit,

    hz_cust_accounts       aca,

    hz_parties             hp,

    ra_customer_trx_all    rct

WHERE

    hci.cons_inv_id          = cit.cons_inv_id

    AND hci.customer_id      = aca.cust_account_id

    AND aca.party_id         = hp.party_id

    AND rct.customer_trx_id  = cit.customer_trx_id

    -- AND hci.status        = 'ACCEPTED'        -- Uncomment if needed

    AND rct.trx_number       = '31380885'       -- Change as required

    AND hci.issue_date      >= TRUNC(SYSDATE) - 30

ORDER BY

    hci.issue_date DESC;

Oracle EBS: Query to Link OM Order Source with AR Grouping Rules and Attributes

SELECT 

    hou.name,

    rtsa.name "Order Source/Transaction Source",

    rtsa.description "Description",

    gr1.name grouping_rule_name,

    gr1.description grouping_rule_desc,

    gr1.start_date,

    gr1.end_date,

    al.meaning type,

    from_column_name,

    end_user_column_name

FROM 

    ra_grouping_rules gr1,

    ra_line_ordering_rules ord,

    ra_grouping_trx_types gt,

    ra_group_bys gb,

    ra_group_by_columns gc,

    ar_lookups al,

    fnd_descr_flex_col_usage_vl co,

    ra_batch_sources_all rtsa,

    hr_operating_units hou

WHERE 

    gr1.ordering_rule_id = ord.ordering_rule_id(+)

    AND gr1.grouping_rule_id = gt.grouping_rule_id(+)

    AND gt.grouping_trx_type_id = gb.grouping_trx_type_id(+)

    AND gb.column_id = gc.column_id

    AND al.lookup_type = 'GROUPING_TRX_TYPE'

    AND al.lookup_code = gt.class

    AND co.descriptive_flex_context_code = 'ORDER ENTRY'

    AND 'L.' || co.application_column_name = from_column_name

    AND rtsa.grouping_rule_id = gr1.grouping_rule_id(+)

    AND rtsa.name = 'Order Management'

    AND hou.organization_id = rtsa.org_id

ORDER BY 

    1;

Customer Credit Card Query

 select hca.cust_account_id,
       hca.party_id,
       hcas.party_site_id,
       site_use_id,
       hcsu.org_id,
       hca.account_number,
       hca.account_name,
       hps.party_site_number,
       location bill_location,
       creditcardeo.card_issuer_code      "CARD TYPE",
       creditcardeo.ccnumber              "CARD NUMBER",
       chname,
       trunc(ipiu.start_date)             "VALID FROM",
       trunc(creditcardeo.expirydate)            "EXP DATE",
       expired_flag,
       active_flag,
       hp.party_name                      "OWNER NAME",
       hcsu.location,
       hl.address1,
       hl.address2,
       hl.city,
       hl.state,
       hl.postal_code,
       creditcardeo.description           "CARDHOLDER NAME",
       creditcardeo.invalidation_reason,       
       (select iss.segment_cipher_text
          from apps.iby_fndcpt_tx_extensions extn,
               iby.iby_security_segments     iss
         where extn.instr_assignment_id = ipiu.instrument_payment_use_id
           and extn.instr_code_sec_segment_id = iss.sec_segment_id
           and rownum = 1)                 cvv
from   apps.hz_cust_accounts_all        hca,
       apps.hz_cust_acct_sites_all      hcas,
       apps.hz_cust_site_uses_all       hcsu,
       apps.hz_party_site_uses          hpsu,
       apps.iby_creditcard              creditcardeo,
       apps.iby_pmt_instr_uses_all      ipiu,
       apps.hz_parties                  hp,
       hz_locations hl,            
       hz_party_sites                   hps
where  hca.party_id = hp.party_id
and    hps.party_site_id = hcas.party_site_id
and    hps.location_id= hl.location_id
and    hca.cust_account_id = hcas.cust_account_id
and    hcas.cust_acct_site_id = hcsu.cust_acct_site_id
and    hcas.party_site_id = hpsu.party_site_id
and    hpsu.site_use_type = 'CC_BILLING'
and    creditcardeo.instrid = ipiu.instrument_id
and    ipiu.instrument_type = 'CREDITCARD'
and    creditcardeo.card_owner_id = hp.party_id
and    hcsu.site_use_code = 'BILL_TO'
and    hcsu.location = '36732870'--'36732870';
and    creditcardeo.ccnumber like '%5615'
--and    active_flag='Y'

;

SELECT ipiu.instrument_payment_use_id,

       ipiu.payment_flow,

       ipiu.ext_pmt_party_id,

       ipiu.instrument_type,

       ipiu.instrument_id,

       ic.ccnumber,

       ic.card_issuer_code,

       ipiu.payment_function,

       DECODE (ipiu.order_of_preference,

               1,

               'Yes',

               'NO'

              ) primary_flag,

       ipiu.start_date,

       ipiu.end_date,

       ic.expirydate,

       ipiu.debit_auth_flag,

       ipiu.debit_auth_method,

       ipiu.debit_auth_reference,

       ipiu.debit_auth_begin,

       ipiu.debit_auth_end

  FROM iby_pmt_instr_uses_all ipiu,

       iby_creditcard ic,

       iby_external_payers_all iep

 WHERE ipiu.instrument_id    = ic.instrid

   AND ipiu.ext_pmt_party_id = iep.ext_payer_id

   AND iep.cust_account_id   = 71950349

   AND iep.acct_site_use_id  = 36732870

;

Item - Category Query

      SELECT 
    msi.segment1 AS Item_Code,  
    msi.DESCRIPTION AS Item_Desc,  
    mcs.CATEGORY_SET_NAME,  
    mck.CONCATENATED_SEGMENTS,  
    mck.SEGMENT1, mck.SEGMENT2, mck.SEGMENT3, mck.SEGMENT4, mck.SEGMENT5,  
    mck.SEGMENT6, mck.SEGMENT7, mck.SEGMENT8, mck.SEGMENT9, mck.SEGMENT10,  
    mck.SEGMENT11, mck.SEGMENT12, mck.SEGMENT13, mck.SEGMENT14, mck.SEGMENT15,  
    mck.SEGMENT16, mck.SEGMENT17, mck.SEGMENT18, mck.SEGMENT19, mck.SEGMENT20  
FROM 
    mtl_system_items_b msi,  
    mtl_item_categories mic,  
    MTL_CATEGORIES_KFV mck,  
    MTL_CATEGORY_SETS_TL mcs  
WHERE 
    msi.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID  
    AND msi.ORGANIZATION_ID = mic.ORGANIZATION_ID  
    AND mic.CATEGORY_ID = mck.CATEGORY_ID  
    AND mcs.CATEGORY_SET_ID = mic.CATEGORY_SET_ID  
    AND mcs.LANGUAGE = 'US' 
    AND msi.ORGANIZATION_ID = :P_Org_id
    AND mcs.CATEGORY_SET_NAME='Tax Classification'
    AND msi.segment1='23542345234'

Oracle Order Management - Tax Calculation Stages in Sales Orders

 

Tax calculation in Oracle Order Management can be controlled at the order header level during any of the following events:

1. Order Entry
2. Order Booking
3. Invoicing

This is configured when Order Types are created in the Order Management Transaction Types window. The specific event to calculate tax for an entire order is specified during setup.

Navigation Steps to Configure:

1. Navigate to:
   India Local Order Management → Oracle Order Management → Setup → Transaction Types → Define → Finance Tab

2. In the Finance Tab, specify the event at which tax should be calculated for the entire order.

Order Entry: Tax Calculation at Entry

• When tax calculation is set to Order Entry, tax is calculated as each order line is entered.
• This is commonly used in scenarios where businesses require the user performing order entry to view the total order amount (including tax) to provide a quote to the customer.
• To include tax in the commitment applied amount, ensure that the tax event is set to Entry.

Order Booking: Tax Calculation at Booking

• When tax calculation is set to Order Booking, tax is calculated on each of the booked order lines.
• This option is suitable for businesses that require tax visibility for booked orders, but wish to improve order entry efficiency by avoiding tax calculation at entry.

Invoicing: Tax Calculation at Invoicing

• When tax calculation is set to Invoicing, no tax calculations occur within Order Management.
• Instead, tax calculation is performed in Oracle Receivables at the time the order or order line is invoiced.

Price List Query for Item

 SELECT qph.list_header_id,

       qph.name,
       qph.description,
       qphh.start_date_active,
       qphh.currency_code,
       qphh.source_system_code,
       qphh.active_flag,
       qphh.orig_system_header_ref,
       qphh.orig_org_id,
       qphh.global_flag,
       qpl.list_line_id,
       qpl.start_date_active,
       qpl.end_date_active,
       qpl.arithmetic_operator,
       qpl.operand,
       qpl.orig_sys_line_ref,
       qpp.pricing_attribute_id,
       qpp.product_attribute_context,
       qpp.product_attribute,
       qpp.product_attr_value,
       qpp.product_uom_code,
       qpp.comparison_operator_code,
       qpp.orig_sys_pricing_attr_ref,
       mtl.inventory_item_id,
       mtl.segment1,
       mtlc.cross_reference_type,
       mtlc.cross_reference
  FROM apps.qp_list_headers_b       qphh,
       apps.qp_list_headers_tl      qph,
       apps.qp_list_lines           qpl,
       apps.qp_pricing_attributes   qpp,
       apps.mtl_system_items_b      mtl,
       apps.mtl_cross_references_b  mtlc
WHERE     qph.list_header_id = qphh.list_header_id
       AND qph.list_header_id = qpl.list_header_id
       AND qph.list_header_id = qpp.list_header_id
       AND qpl.list_line_id = qpp.list_line_id
       AND to_char(mtl.inventory_item_id) = (qpp.product_attr_value)
      -- AND   mtl.organization_id   = 344 
--       (SELECT UNIQUE master_organization_id FROM   mtl_parameters)
       AND mtl.inventory_item_id = mtlc.inventory_item_id
       AND SYSDATE BETWEEN qpl.start_date_active
                       AND NVL (qpl.end_date_active, SYSDATE)
       AND SYSDATE BETWEEN qphh.start_date_active
                       AND NVL (qphh.end_date_active, SYSDATE)
                       and  qph.name like '%SG%'
                       AND qpl.END_DATE_ACTIVE IS NULL
       AND mtl.segment1 = '9780749172008';

       select * from mtl_system_items_b where segment1='A101708534498'

EBS - Form currently using user details for submission

 WITH vs AS (
     SELECT
         ROWNUM rnum,
         inst_id,
         sid,
         serial#,
         status,
         username,
         last_call_et,
         command,
         machine,
         osuser,
         module,
         action,
         resource_consumer_group,
         client_info,
         client_identifier,
         type,
         terminal,
         sql_id,
         sql_child_number
     FROM
         gv$session
)
SELECT
     vs.inst_id,
     vs.sid,
     serial#       serial,
     vs.sql_id,
     vs.sql_child_number,
     vs.username   "Username",
     CASE
         WHEN vs.status = 'ACTIVE' THEN last_call_et
         ELSE NULL
     END "Seconds in Wait",
     (
         SELECT
             command_name
         FROM
             v$sqlcommand
         WHERE
             command_type = vs.command
     ) "Command",
     vs.machine    "Machine",
     vs.osuser     "OS User",
     lower(vs.status) "Status",
     vs.module     "Module",
     vs.action     "Action",
     vs.resource_consumer_group,
     vs.client_info,
     vs.client_identifier
FROM
     vs
WHERE
     vs.username IS NOT NULL
     AND nvl(vs.osuser,'x') <> 'SYSTEM'
     AND vs.type <> 'BACKGROUND'
     AND module = 'e:ONT:frm:OEXOEORD'
ORDER BY
     1,
     2,
     3;

EBS - Order Type associated with Workflow details query

select * from hr_operating_units;

exec MO_GLOBAL.SET_POLICY_CONTEXT('S',12762);

SELECT OWA.order_type_id,
       ol.name,
       oe.meaning item_type_code,
       wf.display_name line_flow
  FROM wf_activities_vl wf,
       oe_workflow_assignments OWA,
       oe_line_types_v ol,
       oe_lookups oe
 WHERE     OWA.line_type_id = ol.line_type_id
       AND oe.lookup_type(+) = 'WF_ASSIGN_ITEM_TYPES'
       AND oe.lookup_code(+) = OWA.item_type_code
       AND wf.name = OWA.process_name
       AND wf.item_type = 'OEOL'
       AND wf.version =
              (SELECT MAX (version)
                 FROM wf_activities_vl wf1
                WHERE wf1.name = wf.name AND wf1.item_type = 'OEOL');

EBS Order Holds details and release responsibility

  SELECT ooh.order_number, 
                ooh.ordered_date,
                ooh.flow_status_code,
                ooh.credit_card_approval_code,
                ooh.order_category_code,
                ohd.name              "Hold Name",
                ohs.released_flag,
                ohr.release_reason_code,
                ohr.creation_date     "Hold Release Date",
                ooh.payment_type_code
           FROM apps.oe_order_headers_all ooh,
                apps.oe_order_lines_all   ool,
                apps.oe_order_holds_all  ohld,
                apps.oe_hold_sources_all ohs,
                apps.oe_hold_definitions ohd,
                apps.oe_hold_releases    ohr
          WHERE  1=1--   TRUNC (ooh.ordered_date) BETWEEN '10-NOV-15' AND '14-NOV-15'
              --  AND ooh.order_category_code = 'RETURN'
                AND ohld.header_id(+) = ooh.header_id
                AND ohs.hold_source_id(+) = ohld.hold_source_id
                AND ohd.hold_id(+) = ohs.hold_id
                AND ohr.hold_release_id(+) = ohs.hold_release_id
                AND ool.header_id=ooh.header_id
                AND ohld.line_id(+) = ool.line_id
                and ohs.released_flag='N'
                AND ooh.order_number in ('12345')
               -- and ohld.line_id=111338124
       ORDER BY ooh.order_number

SELECT frt.responsibility_name,

       authorized_action_code,

       oha.start_date_active,

       oha.end_date_active

  FROM apps.oe_hold_authorizations oha,

       apps.fnd_responsibility_tl frt,

       apps.oe_hold_definitions ohd

 WHERE 1 = 1

   AND oha.hold_id = ohd.hold_id                           

   AND ohd.NAME ='Deferred Invoice Hold'

   AND oha.responsibility_id = frt.responsibility_id

   AND oha.application_id = frt.application_id

   AND LANGUAGE = 'US'

   AND authorized_action_code = 'REMOVE'

EBS Order details along with workflow

 SELECT hr.name ou,h.order_number, 
       h.flow_status_code header_Status,
       l.flow_status_code line_status,
       CUST_ACCT.ACCOUNT_NUMBER,ship_su.location SHIP_TO_LOCATION, bill_su.location  INVOICE_TO_LOCATION,
       ship_ps.party_site_number ship_to_site_number,
       bill_ps.party_site_number bill_to_site_number,
       ship_from_org.organization_code
       SHIP_FROM,
       L.LINE_ID,
       L.ORG_ID,
       L.HEADER_ID,
       s.NAME source_name,
       L.LINE_NUMBER,
       L.ORDERED_ITEM,
       L.ORDERED_QUANTITY,
       l.cancelled_quantity,
       l.unit_selling_price,
       l.unit_list_price,
       l.tax_value,
       l.tax_line_value,
       L.INVENTORY_ITEM_ID,
       (select user_name from fnd_user where user_id=l.created_by) creater,
       (select user_name from fnd_user where user_id=l.last_updated_by) updater,
       L.CREATION_DATE LINE_CREATION,
       L.LAST_UPDATE_DATE LINE_UPDATE,
       (select sh.order_number||'->'||sl.line_number||'->'||sl.ordered_item||'->'||sl.ordered_quantity from oe_order_headers_all sh, oe_order_lines_all sl where sl.header_id=sh.header_id and sl.line_id=l.reference_line_id)
        original_order,
       L.REFERENCE_LINE_ID,
       L.REFERENCE_HEADER_ID,
       L.LINE_CATEGORY_CODE,
       lt.name  LINE_TYPE,
       PARTY.PARTY_NAME  SOLD_TO,
       L.SUBINVENTORY
           SUBINVENTORY,
           ship_loc.location_id,
       ship_loc.address1
           SHIP_TO_ADDRESS1,
       DECODE (ship_loc.city, NULL, NULL, ship_loc.city || ', ')
       || DECODE (ship_loc.state,
                  NULL, ship_loc.province || ', ',
                  ship_loc.state || ', ')
       || DECODE (ship_loc.postal_code,
                  NULL, NULL,
                  ship_loc.postal_code || ', ')
       || DECODE (ship_loc.country, NULL, NULL, ship_loc.country)
           SHIP_TO_ADDRESS5,
       bill_loc.address1
           INVOICE_TO_ADDRESS1,
          DECODE (bill_loc.city, NULL, NULL, bill_loc.city || ', ')
       || DECODE (bill_loc.state,
                  NULL, bill_loc.province || ', ',
                  bill_loc.state || ', ')
       || DECODE (bill_loc.postal_code,
                  NULL, NULL,
                  bill_loc.postal_code || ', ')
       || DECODE (bill_loc.country, NULL, NULL, bill_loc.country)
           INVOICE_TO_ADDRESS5,
       H.order_type_id,
       H.ordered_date,
       L.return_reason_code,
       L.ordered_item_id,
       L.item_identifier_type,
       L.booked_flag,
       L.cancelled_flag,
       L.open_flag,
       l.sold_from_org_id,
       l.shipping_instructions,
       l.flow_status_code,
       l.SHIPPABLE_FLAG,L.LINE_TYPE_ID,
       L.SOLD_TO_ORG_ID,
       L.SHIP_FROM_ORG_ID,
       L.SHIP_TO_ORG_ID,
       L.INVOICE_TO_ORG_ID,
        L.ORIG_SYS_DOCUMENT_REF,
       L.ORIG_SYS_LINE_REF,
       l.ATTRIBUTE16  "Return entered by" ,
       l.ATTRIBUTE19  "Return to Customer Order Ref" ,
       l.ATTRIBUTE12  "Return Id" ,
       l.ATTRIBUTE4  "Return Line ID" ,
       l.ATTRIBUTE1  "Return Receipt Date" ,
       l.ATTRIBUTE3  "Return Credit Denial Reason1" ,
       l.ATTRIBUTE4  "Return Credit Denial Reason2" ,
       l.RETURN_ATTRIBUTE4  "Certiport Return Line ID",
       l.ATTRIBUTE18  PO_NUMBER,
       l.attribute10,
       l.context,
       cursor(SELECT wias.item_key,
         wpa.activity_name,
         wias.activity_status,
         wias.activity_result_code,
         wias.assigned_user,
         wias.begin_date,
         wias.end_date
    FROM WF_ITEM_ACTIVITY_STATUSES wias, WF_PROCESS_ACTIVITIES wpa
   WHERE     wias.process_activity = wpa.instance_id(+)
         AND wpa.process_item_type = wias.item_type
         AND wias.end_date IS NULL
         AND wias.item_type='OEOL'
         AND wias.item_key =to_char(l.line_id)
ORDER BY begin_date DESC) workflow_line_status,
 cursor(SELECT wias.item_key,
         wpa.activity_name,
         wias.activity_status,
         wias.activity_result_code,
         wias.assigned_user,
         wias.begin_date,
         wias.end_date
    FROM WF_ITEM_ACTIVITY_STATUSES wias, WF_PROCESS_ACTIVITIES wpa
   WHERE     wias.process_activity = wpa.instance_id(+)
         AND wpa.process_item_type = wias.item_type
         AND wias.end_date IS NULL
         AND wias.item_type='OEOH'
         AND wias.item_key =to_char(l.header_id)
ORDER BY begin_date DESC) workflow_header_status
  FROM mtl_parameters           ship_from_org,
       hz_cust_site_uses_all    ship_su,
       hz_party_sites           ship_ps,
       hz_locations             ship_loc,
       hz_cust_acct_sites_all   ship_cas,
       hz_cust_site_uses_all    bill_su,
       hz_party_sites           bill_ps,
       hz_locations             bill_loc,
       hz_cust_acct_sites_all   bill_cas,
       hz_parties               party,
       hz_cust_accounts         cust_acct,
       oe_order_headers_all         H,
       oe_order_lines_all       L,
       oe_order_sources s,
       OE_TRANSACTION_TYPES_TL  lt,
       hr_operating_units hr
 WHERE     L.line_type_id = LT.transaction_type_id
       AND LT.language = USERENV ('LANG')
       AND L.sold_to_org_id = cust_acct.cust_account_id(+)
       AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
       AND L.ship_from_org_id = ship_from_org.organization_id(+)
       AND l.ship_to_org_id = ship_su.site_use_id(+)
       AND ship_su.cust_acct_site_id = ship_cas.cust_acct_site_id(+)
       AND ship_cas.party_site_id = ship_ps.party_site_id(+)
       AND ship_loc.location_id(+) = ship_ps.location_id
       AND l.invoice_to_org_id = bill_su.site_use_id(+)
       AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
       AND bill_cas.party_site_id = bill_ps.party_site_id(+)
       AND bill_loc.location_id(+) = bill_ps.location_id
       AND L.header_id = H.header_id
       AND h.order_source_id=s.order_source_id
       AND hr.organization_id=L.ORG_ID
     --  AND  L.REFERENCE_LINE_ID =98618576
      -- AND L.REFERENCE_HEADER_ID                                                     
   --    and L.LINE_ID=99176446       
    -- and L.ORDERED_ITEM in('9780132156554')
  --     and h.creation_date>sysdate-5
      -- and l.header_id=31494876
      -- and l.line_id=99176446       
       and h.order_number ='12727162'--
       order by line_id
       
          select ooh.order_number
       ,ool.ordered_item
       ,ool.ordered_quantity
       ,ooh.flow_status_code header_status                   
       ,ool.flow_status_code line_status
       ,prha.segment1 requisition
       ,poh.segment1 po_number
       ,poh.closed_code po_status
       ,pll.quantity
       ,pll.quantity_received
       ,pll.closed_code po_shipment_status
from apps.oe_order_headers_all                   ooh
    ,apps.oe_order_lines_all                     ool
    ,apps.oe_drop_ship_sources                   odss
    ,apps.po_requisition_headers_all             prha
    ,apps.po_headers_all                         poh
    ,apps.po_lines_all                           pol
    ,apps.po_line_locations_all                  pll              
where ool.header_id = ooh.header_id
and   odss.header_id = ooh.header_id
and   odss.line_id = ool.line_id
and   prha.requisition_header_id = odss.requisition_header_id
and   poh.po_header_id = odss.po_header_id
and   pol.po_line_id = odss.po_line_id
and   pol.po_header_id = poh.po_header_id
and   pll.po_line_id = pol.po_line_id
and   ooh.order_number = '12727162'--'12727161';

SELECT ooh.order_number

              ,ool.ordered_item

              ,ool.line_id

              ,ool.ordered_quantity

              ,ool.shipped_quantity

              ,ool.invoiced_quantity

              ,wdd.delivery_detail_id

              ,wnd.delivery_id

              ,rctl.interface_line_attribute1 order_number

              ,rctl.interface_line_attribute3

              ,rctl.interface_line_attribute6 order_line_id

              ,rct.org_id

              ,rct.creation_date invoice_creation_date

              ,rct.last_update_date invoice_update_date

              ,ooh.creation_date order_creation_date

              ,ooh.last_update_date order_update_date          

             ,trx_number

              ,rctl.quantity_ordered

              ,rct.interface_header_context

  FROM oe_order_headers_all ooh

             ,oe_order_lines_all ool

             ,wsh_delivery_details wdd

             ,wsh_new_deliveries wnd

             ,wsh_delivery_assignments wda

             ,ra_customer_trx_all rct

             ,ra_customer_trx_lines_all rctl

 WHERE ooh.header_Id=ool.header_id

      AND wdd.source_header_id=ooh.header_id

      AND wdd.delivery_detail_Id=wda.delivery_detail_id

      AND wda.delivery_id=wnd.delivery_id

      AND rctl.interface_line_attribute1=to_char(ooh.order_number)

      AND rctl.interface_line_attribute6=to_char(ool.line_id)

      AND rctl.interface_line_attribute3=to_char(wnd.delivery_id)

     AND rctl.customer_trx_id=rct.customer_trx_id

      AND rct.interface_header_context='ORDER ENTRY'

      AND ooh.order_number in ('12727161');

SELECT TRX.TRX_NUMBER         INVOICE_NUMBER,

       TRX.TRX_DATE           INVOICE_DATE,

       HZA.ACCOUNT_NUMBER     CUSTOMER_NUMBER,

       RAT.NAME               TERM_NAME,

       ARPS.DUE_DATE          INVOICE_DUE_DATE,

       trx.creation_date invoice_creation_date

       ,trx.last_update_date invoice_update_date

       ,ooh.creation_date order_creation_date

       ,ooh.last_update_date order_update_date  

  FROM RA_CUSTOMER_TRX_ALL       TRX,

       oe_order_headers_all      OOH,

       HZ_CUST_ACCOUNTS          HZA,

       RA_TERMS                  RAT,

       AR_PAYMENT_SCHEDULES_ALL  ARPS

 WHERE     to_char(TRX.ct_reference) = to_char(OOH.order_number)

       AND OOH.SOLD_TO_ORG_ID = HZA.CUST_ACCOUNT_ID

       AND RAT.TERM_ID = TRX.TERM_ID

       AND ARPS.CUSTOMER_TRX_ID(+) = TRX.CUSTOMER_TRX_ID

       AND ooh.order_number = '12727161';

   SELECT ooh.order_number

              ,ool.ordered_item

              ,ool.line_id

              ,ool.ordered_quantity

              ,ool.shipped_quantity

              ,ool.invoiced_quantity

              ,wdd.delivery_detail_id

              ,wnd.delivery_id

              ,ooh.creation_date order_creation_date

              ,ooh.last_update_date order_update_date          

  FROM oe_order_headers_all ooh

             ,oe_order_lines_all ool

             ,wsh_delivery_details wdd

             ,wsh_new_deliveries wnd

             ,wsh_delivery_assignments wda

 WHERE ooh.header_Id=ool.header_id

      AND wdd.source_header_id=ooh.header_id

      AND wdd.delivery_detail_Id=wda.delivery_detail_id

      AND wda.delivery_id=wnd.delivery_id

      and wnd.name='526398067'

select request_id,interface_status ,interface_line_attribute6 line_id,a.* from ra_interface_lines_all a   where sales_order='8044839' --req id 124077809

                     and interface_line_attribute6 in (133090970,133090946)

select * from ra_interface_errors_all where MESSAGE_TEXT='A Party Tax Profile does not exist for this Party.'

and org_id=16916 --INTERFACE_LINE_ID in (select a.INTERFACE_LINE_ID from ra_interface_lines_all a where sales_order='8044839')

MOAC Setup - Concurrent Program

CREATE SECURITY PROFILE

Go to Human Resource Responsiblity --> Security --> Define Security Profile

Name : CDMAP Security Profile
Business Group : Vision Corporation

Submit concurrent program "Security List Maintenance"

ATTACH SECURITY PROFILE TO RESPONSIBLITY

Go To System Administrator Responsibility --> Profile --> System

check Responsibility "Payables, Vision Operations (USA)"

Profile :

Select the "MO: Security Profile" row the values is CDMAP Security Profile

Check the concurrent program SRS Window , Concurrent program "CDMAP_TEST" Its Default Operating Unit is Null

This program calls the package CDMAP_TEST_PKG.MAIN, It using the Oracle View AP_INVOICES view, it will returns the data once the OU is initialized

create or replace package CDMAP_TEST_PKG as

procedure MAIN(piv_errorbuf OUT VARCHAR2, pin_errorcode OUT NUMBER);

END;


create or replace package BODY CDMAP_TEST_PKG as

procedure MAIN(piv_errorbuf OUT VARCHAR2, pin_errorcode OUT NUMBER) IS

ln_count number := 0;

BEGIN

--ln_orgid :=fnd_global.org_id;

select count(*) into ln_count from AP_INVOICES;

fnd_file.put_line(1, 'count-' || ln_count);

END;

END;

Enable MOAC for the Concurrent Program

sdsdsd

Go to System Administration Responsibility  --> System Administration --> Concurrent --> Programs

Click to Update Icon --> Go to Request Tab --> Under Request Settings --> Operating Unit Mode--> Change to "Single"

Apply --> Click Ok in confirmation page

Retry the program in SRS Window

Go to the responsibility "Payables Vision Operations (USA)"

Go to concurrent program SRS Window , Concurrent program "CDMAP_TEST" Its Operating Unit is Populated from the Security profile "CDMAP Security Profile"

Based on Selected Operating Unit will get the data from AP_INVOICES views

O2C FLOW IN ORACLE APPS R12 BACKEND PROCESS

DATA FLOW Diagram - Order to Cash

 YouTube Videos


https://www.youtube.com/channel/UCWFJCCCLUY0sgLrBsJQtjiw?view_as=subscriber

ORDER 2 CASH

CREATE ITEM IN MASTER ORG-V1

select * from  MTL_SYSTEM_ITEMS_B where  SEGMENT1='CDMO2C'

-- INVENTORY_ITEM_ID=244205

select * from  MTL_ONHAND_QUANTITIES_DETAIL where  INVENTORY_ITEM_ID=244205

select * from  MTL_MATERIAL_TRANSACTIONS where  INVENTORY_ITEM_ID=244205

CREATE THE ORDER

----------------

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_HEADERS_ALL A where  ORDER_NUMBER='69341'

--STATUS ENTERED

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_LINES_ALL A where  HEADER_ID=362781

-- STATUS ENTERED

--BOOKED THE ORDER

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_HEADERS_ALL A where  ORDER_NUMBER='69341'

--STATUS BOOKED

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_LINES_ALL A where  HEADER_ID=362781

-- STATUS AWAITING_SHIPPING

select A.RELEASED_STATUS,A.* from  WSH_DELIVERY_DETAILS A   where  SOURCE_HEADER_ID=362781

-- R --> Ready to Release

select * from  WSH_DELIVERY_ASSIGNMENTS where  DELIVERY_DETAIL_ID=6219051 -- UNTIL RELEASE THE ITEM, DELIVERY ID IS NOT POPULATED

-- RELEASE THE ORDER

------------------------

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_HEADERS_ALL A where  ORDER_NUMBER='69341'

--STATUS ENTERED

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_LINES_ALL A where  HEADER_ID=362781

-- STATUS ENTERED

select A.RELEASED_STATUS,A.* from  WSH_DELIVERY_DETAILS A   where  SOURCE_HEADER_ID=362781

-- Y --> Pick Confirmed

-- S --> Pick released

Staged/Pick Confirmed

select * from  WSH_DELIVERY_ASSIGNMENTS where  DELIVERY_DETAIL_ID=6219051 -- DELIVERY ID IS  POPULATED

select * from  wsh_new_deliveries where  delivery_id=6242831

--status OP OPEN

--Ship Confirm/Close Trip Stop

--MOVE ORDER

6391343

select * from  mtl_reservations where  INVENTORY_ITEM_ID=244205

select * from  mtl_txn_request_headers where  request_number='6391343'

select * from  mtl_txn_request_lines where  header_id=6391344

select * from  MTL_MATERIAL_TRANSACTIONS where  INVENTORY_ITEM_ID=244205

---

SHIP CONFIRMATION

----------------

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_HEADERS_ALL A where  ORDER_NUMBER='69341'

--STATUS ENTERED

select A.FLOW_STATUS_CODE,A.* from  OE_ORDER_LINES_ALL A where  HEADER_ID=362781

-- STATUS SHIPPED

select A.RELEASED_STATUS,A.* from  WSH_DELIVERY_DETAILS A   where  SOURCE_HEADER_ID=362781

-- C --> Shihp Confirmed

select * from  WSH_DELIVERY_ASSIGNMENTS where  DELIVERY_DETAIL_ID=6219051 -- DELIVERY ID IS  POPULATED

select * from  wsh_new_deliveries where  delivery_id=6242831

--status CL CLOSED

select * from  mtl_reservations where  INVENTORY_ITEM_ID=244205-- DELTED ONCE SHIP CONFIRMED

select * from  mtl_txn_request_headers where  request_number='6391343'

select * from  mtl_txn_request_lines where  header_id=6391344

select * from  MTL_MATERIAL_TRANSACTIONS where  INVENTORY_ITEM_ID=244205

-- DECREASED THE ONHAND QUANTITIES

select * from  MTL_ONHAND_QUANTITIES where  INVENTORY_ITEM_ID=244205

---- CREATE AR INVOICE

 RUN WORKFLOW BACKGROUND PROCESS FROM OM RESP

 -- INTERFACE THE INV RECORDS

 -- AUTOMATICALLY CALL AUTOINVOICE PROGRAM

-- AR INVOICE IS IMPORTED IN EBS

select * from  RA_INTERFACE_LINES_ALL where  INTERFACE_LINE_ATTRIBUTE1='69341'

select * from  RA_INTERFACE_SALESCREDITS_ALL where  INTERFACE_LINE_ID=1952381

select * from  RA_INTERFACE_DISTRIBUTIONS_ALL where  INTERFACE_LINE_ID=1952381

select * from  RA_INTERFACE_ERRORS_ALL

select * from  RA_CUSTOMER_TRX_ALL where  CUSTOMER_TRX_ID=1205195

--TRX NUMBER 10047340

select * from  RA_CUSTOMER_TRX_LINES_ALL where  INTERFACE_LINE_ATTRIBUTE1='69341'

select * from  RA_CUST_TRX_LINE_GL_DIST_ALL  where  CUSTOMER_TRX_LINE_ID=1952381

select * from  RA_CUST_TRX_LINE_SALESREPS_ALL where  SALESREP_ID=1006 AND CUSTOMER_TRX_ID=1205195

select * from  JTF_RS_SALESREPS where  SALESREP_ID=1006

select * from  AR_PAYMENT_SCHEDULES_ALL where  CUSTOMER_TRX_ID=1205195

-- AMOUNT_DUE_REMAINIG 795.01

SLA TABLES

----------

select * from  XLA_EVENTS where  EVENT_ID=6198573

select * from  XLA.XLA_TRANSACTION_ENTITIES where  TRANSACTION_NUMBER='10047340'

-- CREATE ACOUNTNG PROGRAM

select * from  XLA_AE_HEADERS where  EVENT_ID=6198573

select * from  XLA_AE_LINES where  AE_HEADER_ID=8110572

select * from  GL_IMPORT_REFERENCES where  (GL_SL_LINK_ID,GL_SL_LINK_TABLE) IN (

select GL_SL_LINK_ID,GL_SL_LINK_TABLE from  XLA_AE_LINES where  AE_HEADER_ID=8110572)

GL TABLES

select * from  GL_INTERFACE where  REFERENCE26=6198573

select * from  GL_JE_BATCHES where  je_batch_id=5351742

-- Receivables A 3825428 7481722

select * from  GL_JE_HEADERS where  je_header_id=7105885

select * from  GL_JE_LINES where  je_header_id=7105885

----

CREATE RECEIPTS

select * from  AR_CASH_RECEIPTS_ALL where  RECEIPT_NUMBER='10047340_REC'

select * from  AR_PAYMENT_SCHEDULES_ALL where  CUSTOMER_TRX_ID=1205195

-- AMOUNT_DUE_REMAINIG 795.01

select * from  AR_RECEIVABLE_APPLICATIONS_ALL where  CASH_RECEIPT_ID=211297

select * from  AR_CASH_RECEIPT_HISTORY_ALL where  CASH_RECEIPT_ID=211297

---

select * from  XLA_EVENTS where  EVENT_ID=6198574

select * from  XLA.XLA_TRANSACTION_ENTITIES where  TRANSACTION_NUMBER='10047340_REC'

-- CREATE ACOUNTNG PROGRAM

select * from  XLA_AE_HEADERS where  EVENT_ID=6198574

select * from  XLA_AE_LINES where  AE_HEADER_ID=8110573

select * from  GL_IMPORT_REFERENCES where  (GL_SL_LINK_ID,GL_SL_LINK_TABLE) IN (

select GL_SL_LINK_ID,GL_SL_LINK_TABLE from  XLA_AE_LINES where  AE_HEADER_ID=8110573)

GL TABLES

select * from  GL_INTERFACE where  REFERENCE26=6198574

select * from  GL_JE_BATCHES where  je_batch_id=5351744

-- Receivables A 3825429 7481727

select * from  GL_JE_HEADERS where  je_header_id=7105887

select * from  GL_JE_LINES where  je_header_id=7105887

-----------------------

SETUP DETAILS

select * from  RA_CUSTOMER_TRX_ALL where  CUSTOMER_TRX_ID=1205195

--TRX NUMBER 10047340

select * from  RA_CUSTOMER_TRX_LINES_ALL where  INTERFACE_LINE_ATTRIBUTE1='69341'

select * from  RA_BATCH_SOURCES_ALL where  BATCH_SOURCE_ID=1001

select * from  RA_CUST_TRX_TYPES_ALL where  CUST_TRX_TYPE_ID=1361

select * from  RA_TERMS where  TERM_ID=4

select * from  RA_GROUPING_RULES