API to assign a Workflow role to user

 

begin

Wf_local_synch.PropagateUserRole(

p_user_name => p_user_name,

p_role_name => p_role_name,

p_start_date=>'01-SEP-21',

p_expiration_date=>'31-DEC-30');

commit;

excePtion

when others then

null;

end;

Query to find locked sessions of a particular Table

 select a.sid||'|'|| a.serial#||'|'|| a.process

 from gv$session a, gv$locked_object b, dba_objects c

 where b.object_id = c.object_id

 and a.sid = b.session_id

 and OBJECT_NAME=upper('<Table Name>');

OAF List personalizations

 

   begin  

  jdr_utils.listCustomizations('/oracle/apps/icx/por/req/webui/EditSubmitPG');  

  jdr_utils.listCustomizations('/oracle/apps/icx/por/req/webui/ShoppingCartPopupRN');  

 end;  

 

To Delete Customizations

 

begin  

   jdr_utils.deleteDocument('/oracle/apps/icx/por/req/webui/customizations/site/0/EditSubmitPG');  

   jdr_utils.deleteDocument('/oracle/apps/icx/por/req/webui/customizations/site/0/ShoppingCartPopupRN');

end;

/

XLA to AP link

select xdl.* from apps.XLA_DISTRIBUTION_LINKS xdl, apps.ap_invoices_all aia, apps. ap_invoice_distributions_all aida
where APPLIED_TO_ENTITY_CODE = 'AP_INVOICES'
and applied_to_source_id_num_1 = aia.invoice_id
AND xdl.source_distribution_type = 'AP_INV_DIST'
and SOURCE_DISTRIBUTION_ID_NUM_1 = aida.invoice_distribution_id
and aida.invoice_id=aia.invoice_id
and aia.invoice_num = &Inv_num
and aia.org_id = &org_id

Long running SQL Query

   SELECT CLIENT_IDENTIFIER

             USER_NAME,

         SYSDATE,

         ROUND (((S.LAST_CALL_ET / 86400)) * 24 * 60, 2)

             WAIT_TIME,

         S.SID,

         S.SERIAL#,

         S.Sql_id,

         s.inst_id,

         DECODE (s.sql_hash_value, 743038952, 'SAME', 'DIFFERENT')

             Hash_value,

         s.sql_hash_value,                                         --743038952

         S.STATUS,

         S.plsql_entry_object_id,

         S.MODULE,

         S.ACTION,

         SYSDATE - (S.LAST_CALL_ET / 86400)

             LAST_CALL,

         S.TYPE,

         S.USERNAME

             DB_USERNAME,

         S.OSUSER

             CLIENT_USERNAME,

         S.MACHINE,

         S.CLIENT_INFO,

         S.TERMINAL,

         S.LOGON_TIME,

         S.PROCESS,

         P.SPID,

         P.PID,

         s.*

    FROM GV$SESSION S, GV$PROCESS P

   WHERE     S.PADDR = P.ADDR(+)

         AND s.inst_id = p.inst_id

         AND S.MODULE IS NOT NULL

         AND S.STATUS = 'ACTIVE'

         AND NVL (S.TYPE, 'XXXXX') <> 'BACKGROUND'

         AND S.MODULE LIKE '%007%'                            -- CP Short name

--and sql_id = 'd1u4rr2gkcq33'

--  S.ACTION LIKE :ACTIONTYPE  AND

--S.OSUSER LIKE  :USR

--AND ROUND(((S.LAST_CALL_ET / 86400))*24*60,2) > :WAIT_TIME

ORDER BY ((S.LAST_CALL_ET / 86400)) * 24 * 60 DESC;

Table size query

 select  segment_name,sum(bytes)/1024/1024/1024 GB 

from dba_segments 

where segment_type='TABLE' 

and segment_name like 'XX%'

and segment_name=upper('XX_INV_ITEMS_STG') 

group by segment_name

PO Receipt with Lot details query

  SELECT poh.segment1

             po_number,

         asp.segment1

             vendor_number,

         asp.vendor_name,

         TRUNC (rt.transaction_date)

             receipt_date,

         (SELECT ood.organization_name

            FROM apps.org_organization_definitions ood

           WHERE organization_id = mmt.organization_id)

             "Receipt Org",

         TO_CHAR (TRUNC (rt.transaction_date), 'MON-YY')

             "Month",

         rsh.receipt_num,

         msi.segment1

             sku,

         rsl.item_revision,

         msi.description,

         rt.quantity

             line_quantity,

         mtln.lot_number,

         mtln.transaction_quantity

             lot_qty,

         msi.lot_control_code

    FROM apps.rcv_transactions           rt,

         apps.rcv_shipment_headers       rsh,

         apps.rcv_shipment_lines         rsl,

         apps.mtl_system_items_b         msi,

         apps.po_headers_all             poh,

         apps.ap_suppliers               asp,

         apps.mtl_transaction_lot_numbers mtln,

         apps.mtl_material_transactions  mmt

   WHERE     1 = 1

         AND rsh.shipment_header_id = rt.shipment_header_id

         AND rsl.shipment_header_id = rsh.shipment_header_id

         AND rsl.po_header_id = rt.po_header_id

         AND rsl.po_line_id = rt.po_line_id

         AND rsl.po_line_location_id = rt.po_line_location_id

         AND poh.vendor_id = asp.vendor_id

         AND rt.po_header_id = poh.po_header_id

         AND rsl.item_id = msi.inventory_item_id

         AND rsl.to_organization_id = msi.organization_id

         AND rt.transaction_date > '01-JAN-21'

         AND rt.source_document_code = 'PO'

         AND rt.transaction_type = 'DELIVER'

         AND mmt.transaction_id = mtln.transaction_id(+)

         AND mmt.rcv_transaction_id = rt.transaction_id

         AND mmt.organization_id = rt.organization_id

         AND mmt.inventory_item_id = rsl.item_id

         --and   mmt.transaction_type_id=18

         AND poh.org_id = &P_ORG_ID

         AND poh.attribute15 IS NULL

ORDER BY rt.transaction_date,

         receipt_num,

         sku,

         lot_number 

Oracle Financials - India Localization Notes (GST E-invoice)

 NOTE:2628076.1 - Oracle E-Business Suite: India GST E-Invoice, October 1, 2020

NOTE:2710671.1 - Oracle Financials for India (OFI) - Goods and Services Tax(GST) - E-Invoice Functionality

NOTE:2709910.1 - Oracle Financials for India (OFI) - Goods and Services Tax(GST) - E-Invoice Functionality Technical Flow

NOTE:2640446.2 - E-Business Suite Release 12: Oracle Financials for India (OFI) Information Center

fnd_conc_req_outputs - To View XML Publisher generated output files

Initial output file generated by Concurrent Request will be stored in FND_CONCURRENT_REQUESTS table (logfile_name and outfile_name) and all remaining output files generated Concurrent Request including XML Publisher generated output will be stored in fnd_conc_req_outputs table.

Query to find Credit memo and debit memo of a particular Order

select rca_cm.interface_header_attribute1 so_number,rca_cm.trx_number cm_number, rca_dm.trx_number dm_number,ps_cm.AMOUNT_DUE_ORIGINAL cm_amt_original, ps_cm.amount_due_remaining cm_amt_due_remaining, ps_dm.AMOUNT_DUE_ORIGINAL dm_amt_original, ps_dm.amount_due_remaining dm_amt_remaining

 from apps.ra_customer_trx_all rca_cm

    , apps.ra_customer_trx_all rca_dm   

    , apps.RA_CUST_TRX_TYPES_ALL  rtt_cm

    , apps.RA_CUST_TRX_TYPES_ALL  rtt_dm

    , apps.AR_PAYMENT_SCHEDULES_ALL ps_cm

    , apps.AR_PAYMENT_SCHEDULES_ALL ps_dm

 where rca_cm.interface_header_attribute1 = '40642183'

 and   rca_cm.org_id=rtt_cm.org_id

 and   rtt_cm.type='CM'

 and   rca_cm.cust_trx_type_id=rtt_cm.CUST_TRX_TYPE_ID

 and   rca_cm.interface_header_attribute1=rca_dm.interface_header_attribute1

 and   rtt_dm.type='DM'

 and   rca_dm.org_id=rtt_dm.org_id

 and   rca_dm.cust_trx_type_id=rtt_dm.CUST_TRX_TYPE_ID

 and   ps_cm.class='CM'

 and   ps_cm.status='OP'

 and   ps_cm.customer_trx_id=rca_cm.customer_trx_id

 and   ps_dm.class='DM'

 and   ps_dm.status='OP'

 and   ps_dm.customer_trx_id=rca_dm.customer_trx_id

Inventory Pending Transactions (MTL_MATERIAL_TRANSACTIONS_TEMP)

The inventory pending transactions can be found using below navigation

Inventory  -->  Transactions --> Pending Transactions.

Alternately directly in table, MTL_MATERIAL_TRANSACTIONS_TEMP

The transaction modes have following meaning

NULL or 1:  Online Processing
Online processing is used by the Oracle Applications to immediately process records. This does not require that a concurrent program be run. The Transaction Manager does not process transactions marked for online processing.

2: Concurrent Processing
Transactions marked as concurrent transaction mode are processed by a dedicated transaction worker to explicitly process a set of transactions. The Transaction Manager does not process transactions marked for concurrent processing.

3: Background Processing
Interface transactions marked for Background processing will be picked up by the transaction manager polling process and assigned to a transaction worker. These transactions will not be processed unless the transaction manager is running.

8:  Internal Processing
Transaction mode 8 is not a mode normally visible to the user as it is used for internal processing.  Transactions with this mode are not visible in the Pending transactions form. Transaction_mode of 8 is used internally to identify if records came from the interface table to the pending table or came directly into the pending table.


Some useful oracle support notes:

Master Note for Inventory (Doc ID 1192649.1)
Note 568012.1 - FAQ: Inventory Standard Datafixes
Note 1472074.1 - Inventory Standard Datafix Instruction #17: Fixing Duplicate Sales Order Transactions In MTI, MMTT and MMT
Note 1177935.1 - for Inventory and WIP transactions

SQL query to convert Comma/Colon separated List to Rows

        select  regexp_substr('san@abc.com; san@xyz.com','[^; ]+', 1, level) 
from dual
        connect by regexp_substr('san@abc.com; san@xyz.com', '[^; ]+', 1, level) is not null;

Query to fetch Prepayment amount used from a Prepayment invoice

Query to fetch Prepayment used from a Prepayment invoice
  SELECT SUM(total_dist_amount -
         NVL(prepay_amount_remaining, total_dist_amount))
    FROM ap_invoice_distributions_all aid,
         ap_invoice_lines_all ail
   WHERE aid.invoice_id = &P_invoice_id
     AND aid.invoice_id = ail.invoice_id
     AND aid.invoice_line_number = ail.line_number
     AND ail.line_type_lookup_code <> 'TAX'
     AND aid.line_type_lookup_code IN
         ('ITEM', 'ACCRUAL', 'REC_TAX', 'NONREC_TAX')
     AND NVL(reversal_flag,'N') <> 'Y';

Query to fetch Prepaid amount for a Standard Invoice

Query to fetch Prepaid amount for a Standard Invoice

 SELECT  (0 - SUM(NVL(aid.amount,0))) prepaid_amount
    FROM ap_invoice_distributions_all aid,
         ap_invoice_lines_all         ail
     WHERE ail.invoice_id = &P_invoice_id -- invoice id of std invoice
     AND   ail.invoice_id = aid.invoice_id
     AND   ail.line_number = aid.invoice_line_number
     AND   aid.line_type_lookup_code = 'PREPAY'
     AND   aid.prepay_distribution_id IS NOT NULL
     AND   NVL(ail.invoice_includes_prepay_flag, 'N') = 'N';

OR

  SELECT ABS(SUM(amount))
    FROM ap_invoice_lines_all ail
   WHERE ail.invoice_id = &P_invoice_id -- invoice id of std invoice
     AND ail.line_type_lookup_code = 'PREPAY';

EGO/EFF - User Defined Attributes -- Useful tables

ego_attrs_v -- All info about User-defined attributes.

ego_fnd_dsc_flx_ctx_ext -- Metadata about Attribute Groups. This is extension to FND_DESCR_FLEX_CONTEXTS.

JPX Import command for loading VO Substiution

Steps:
1. Generate JPX file from JDev
2. Copy the file to instance middle-tier
3. Execute below command to load the JPX

java oracle.jrad.tools.xml.importer.JPXImporter $XXCUST_TOP/mds/XXVOTEST.jpx -username APPS -password $apps_pwd -dbconnection $tns_string 

Transact move order using API

Steps:
Create Move Order and approve --> Allocate Qty to move order lines --> Run API to transact material from the allocated sub inventory


DECLARE
    p_org_id             NUMBER := &p_org_id;
    p_invorg_id          NUMBER := &p_invorg_id;
    p_sales_order_num    VARCHAR2 (200) := &p_sales_order_num;
    l_api_version        NUMBER := 1.0;
    l_init_msg_list      VARCHAR2 (2) := FND_API.G_TRUE;
    l_commit             VARCHAR2 (2) := FND_API.G_FALSE;
    x_return_status      VARCHAR2 (2);
    x_msg_count          NUMBER := 0;
    x_msg_data           VARCHAR2 (20000);
    l_return_status      VARCHAR2 (2);
    l_msg_data           VARCHAR2 (255);
    l_idx                NUMBER := 0;
    l_from_date          DATE;
    l_to_date            DATE;


    -- API variable declarations
    l_move_order_type    NUMBER := 1;
    l_transaction_mode   NUMBER := 1;
    l_tmoline_tbl        INV_MOVE_ORDER_PUB.trolin_tbl_type;
    l_mold_tbl           INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type;
    x_mmtt_tbl           INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type;
    x_trolin_tbl         INV_MOVE_ORDER_PUB.trolin_tbl_type;
    l_transaction_date   DATE := SYSDATE;

    CURSOR trans_move_order_cur (cp_org_id       NUMBER,
                                 cp_inv_org_id   NUMBER,
                                 cp_order_num    VARCHAR2)
    IS
        SELECT oola.line_id,
               oola.line_number,
               wdd.released_status,
               wdd.move_order_line_id,
               oola.ship_from_org_id
          FROM oe_order_lines_all            oola,
               wsh_delivery_details          wdd,
               org_organization_definitions  mtlp,
               hr_operating_units            hou
         WHERE     1 = 1
               AND mtlp.operating_unit = cp_org_id
               AND mtlp.operating_unit = hou.organization_id
               AND hou.organization_id = cp_org_id
               AND mtlp.organization_id =
                   NVL (cp_inv_org_id, mtlp.organization_id)
               AND wdd.organization_id = mtlp.organization_id
               AND wdd.source_code = 'OE'
               AND wdd.source_line_id = oola.line_id
               AND wdd.released_status = 'S'
               AND wdd.oe_interfaced_flag = 'N'
               AND wdd.inv_interfaced_flag = 'N'
               AND oola.flow_status_code = 'AWAITING_SHIPPING'
               AND EXISTS
                       (SELECT '1'
                          FROM ont.oe_order_headers_all ooha
                         WHERE     header_id = wdd.source_header_id
                               AND ooha.order_number + 0 =
                                   NVL (cp_order_num, ooha.order_number));

    TYPE trans_move_order_tab IS TABLE OF trans_move_order_cur%ROWTYPE;

    cr_move_order_tbl    trans_move_order_tab;
BEGIN
    fnd_global.apps_initialize (fnd_global.user_id, fnd_global.resp_id, 665); -- 665-- WSH


    OPEN trans_move_order_cur (p_org_id, p_invorg_id, p_sales_order_num);

    FETCH trans_move_order_cur BULK COLLECT INTO cr_move_order_tbl;

    CLOSE trans_move_order_cur;


    DBMS_OUTPUT.put_line (
        'Orders -- move from Released to Warehouse to Ship Confirm');

    FOR i IN 1 .. cr_move_order_tbl.COUNT
    LOOP
        l_tmoline_tbl (l_idx).line_id :=
            cr_move_order_tbl (i).move_order_line_id;
        l_idx := NVL (l_tmoline_tbl.COUNT, 0) + 1;
    END LOOP;



    IF (l_tmoline_tbl.COUNT > 0)
    THEN
        INV_PICK_WAVE_PICK_CONFIRM_PUB.Pick_Confirm (
            p_api_version_number   => l_api_version,
            p_init_msg_list        => l_init_msg_list,
            p_commit               => l_commit,
            x_return_status        => x_return_status,
            x_msg_count            => x_msg_count,
            x_msg_data             => x_msg_data,
            p_move_order_type      => l_move_order_type,
            p_transaction_mode     => l_transaction_mode,
            p_trolin_tbl           => l_tmoline_tbl,
            p_mold_tbl             => l_mold_tbl,
            x_mmtt_tbl             => x_mmtt_tbl,
            x_trolin_tbl           => x_trolin_tbl,
            p_transaction_date     => l_transaction_date);

        IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
        THEN
            DBMS_OUTPUT.put_line ('Message count: ' || x_msg_count);

            IF (x_msg_count > 1)
            THEN
                FOR i IN 1 .. x_msg_count
                LOOP
                    x_msg_data :=
                        fnd_msg_pub.get (p_msg_index   => i,
                                         p_encoded     => FND_API.G_FALSE);
                    DBMS_OUTPUT.put_line ('message :' || x_msg_data);
                END LOOP;
            END IF;
        END IF;
    END IF;

    COMMIT;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line (
            'Error in XX_WSH_TRANSACT_MOVE_ORDER_PKG.' || SQLERRM);
END;

Move Order Types

Value Move Order Type
1 Requisition
2 Replenishment
3 Pick Wave
4 Receipt
5 Manufacturing Component Pick
6 Put Away

Data fix to insert missing move order header

DECLARE
    CURSOR missing_mtrh_cur IS
        SELECT mtrl.header_id, --move order header id
               wdd.organization_id,
               enforce_ship_set_and_smc,
               wpb.pick_grouping_rule_id,
               wpb.name --
          FROM wsh_delivery_details     wdd,
               wsh_shipping_parameters  wsp,
               mtl_txn_request_lines    mtrl,
               wsh_picking_batches      wpb
         WHERE     line_id = move_order_line_id
               AND source_code = 'OE'
               AND wdd.move_order_line_id IS NOT NULL
               AND wsp.organization_id = wdd.organization_id
               AND wdd.batch_id = wpb.batch_id
               AND released_status = 'S'
               AND NOT EXISTS
                       (SELECT 1
                          FROM mtl_txn_request_headers mtrh
                         WHERE mtrh.header_id = mtrl.header_id);

    l_counter   NUMBER := 0;
    l_count      NUMBER;
BEGIN
    FOR missing_mtrh_rec IN missing_mtrh_cur
    LOOP
        SELECT COUNT (*)
          INTO l_count
          FROM mtl_txn_request_headers
         WHERE header_id = missing_mtrh_rec.header_id;

        IF (l_count = 0)
        THEN
            INSERT INTO mtl_txn_request_headers (created_by,
                                                 creation_date,
                                                 header_id,
                                                 header_status,
                                                 last_updated_by,
                                                 last_update_date,
                                                 last_update_login,
                                                 organization_id,
                                                 request_number,
                                                 move_order_type,
                                                 transaction_type_id,
                                                 grouping_rule_id)
                 VALUES (-1,
                         SYSDATE,
                         missing_mtrh_rec.header_id,
                         7,
                         -1,
                         SYSDATE,
                         0,
                         missing_mtrh_rec.organization_id,
                         missing_mtrh_rec.name,
                         3, -- Pick Wave
                         52, -- Sales Order Pick
                         missing_mtrh_rec.pick_grouping_rule_id);
        END IF;

        l_counter := l_counter + 1;
    END LOOP;

    DBMS_OUTPUT.put_line ('Number of Records Processed : ' || l_counter);
    COMMIT;
END;
/

Script to check for Move Order lines with missing Move Order headers

SELECT mtrl.header_id,
                   wdd.organization_id,
                   wsp.enforce_ship_set_and_smc,
                   wpb.pick_grouping_rule_id,
                   wpb.NAME
              FROM wsh_delivery_details     wdd,
                   wsh_shipping_parameters  wsp,
                   mtl_txn_request_lines    mtrl,
                   wsh_picking_batches      wpb
             WHERE     mtrl.line_id = wdd.move_order_line_id
                   AND wdd.source_code = 'OE'
                   AND wdd.move_order_line_id IS NOT NULL
                   AND wsp.organization_id = wdd.organization_id
                   AND wdd.batch_id = wpb.batch_id
                   AND wdd.released_status = 'S'
                   AND NOT EXISTS
                           (SELECT 1
                              FROM mtl_txn_request_headers mtrh
                             WHERE mtrh.header_id = mtrl.header_id);