P2P & O2C Technical Flow

                                 P2P Backend FLOW

Indent

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1=5671

SELECT * FROM PO_REQUISITION_LINES_ALL

WHERE REQUISITION_HEADER_ID = (SELECT REQUISITION_HEADER_ID FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='5671')

PO Related

SELECT * FROM PO_HEADERS_ALL WHERE SEGMENT1=4500

SELECT * FROM PO_LINES_ALL

WHERE PO_HEADER_ID= (SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1=4500)

SELECT * FROM PO_LINE_LOCATIONS_ALL

WHERE PO_HEADER_ID=(SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1=4500)

SELECT * FROM PO_DISTRIBUTIONS_ALL

WHERE PO_HEADER_ID=(SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1=4500).

Receipt Information

 SELECT * FROM RCV_SHIPMENT_HEADERS    WHERE RECEIPT_NUM =9621

SELECT * FROM RCV_SHIPMENT_LINES WHERE SHIPMENT_HEADER_ID IN (SELECT SHIPMENT_HEADER_ID FROM RCV_SHIPMENT_HEADERS  

        WHERE RECEIPT_NUM =9621 AND SHIPMENT_HEADER_ID=69428)

Select * from RCV_TRANSACTIONS

          Where PO_HEADER_ID IN (SELECT PO_HEADER_ID   FROM PO_HEADERS_ALL

                                   WHERE SEGMENT1=4500) 

SELECT * FROM mtl_material_transactions

WHERE RCV_TRANSACTION_ID IN (select TRANSACTION_ID

    From RCV_TRANSACTIONS  Where PO_HEADER_ID IN (SELECT PO_HEADER_ID FROM

                       PO_HEADERS_ALL WHERE SEGMENT1=4500))

        

            AP Invoice related

SELECT * FROM AP_INVOICES_ALL  WHERE INVOICE_NUM=4579

SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE INVOICE_ID IN (SELECT INVOICE_ID

                       FROM AP_INVOICES_ALL WHERE INVOICE_NUM='4579')

Payment Related

 SELECT * FROM AP_PAYMENT_SCHEDULES_ALL WHERE INVOICE_ID IN (SELECT INVOICE_ID FROM AP_INVOICES_ALL WHERE INVOICE_NUM='4579')

SELECT * FROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID IN (SELECT INVOICE_ID FROM AP_INVOICES_ALL

        WHERE INVOICE_NUM='4579')

SELECT * FROM AP_PAYMENT_DISTRIBUTIONS_ALL WHERE INVOICE_PAYMENT_ID IN (SELECT INVOICE_PAYMENT_ID  FROM AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID IN (SELECT INVOICE_ID FROM AP_INVOICES_ALL  WHERE INVOICE_NUM='4579'))

SELECT * FROM AP_CHECKS_ALL   WHERE CHECK_ID=28698  

à Corresponding vendor_id and bank_account is related to that particular vendor

Bank Reconciliation in Cash Management

SELECT STATEMENT_HEADER_ID FROM CE_STATEMENT_HEADERS_ALL

WHERE STATEMENT_HEADER_ID =75826

SELECT * FROM CE_STATEMENT_LINES WHERE STATEMENT_HEADER_ID IN (SELECT STATEMENT_HEADER_ID FROM CE_STATEMENT_HEADERS_ALL

WHERE STATEMENT_HEADER_ID =75826)

GL Related

Ø  After these Invoice’s are transferred to GL by running

  Payables Transfer to General Ledger  PROGRAM

The tables affected are

    GL_JE_BATCHES

    GL_JE_HEADERS

    GL_JE_LINES    

                                           O2C FLOW

Order Related

SELECT * FROM oe_order_headers_all WHERE order_number = 57803

SELECT * FROM oe_order_lines_all WHERE header_id = (SELECT header_id FROM oe_order_headers_all WHERE order_number = 57803);

Once order is booked and pick release is next step

 SELECT * FROM wsh_delivery_details WHERE source_header_id = (SELECT header_id FROM oe_order_headers_all WHERE order_number = 57803)

Once order is pick confirm

SELECT delivery_id FROM wsh_delivery_assignments WHERE delivery_detail_id =

(SELECT delivery_detail_id FROM wsh_delivery_details WHERE source_header_id = (SELECT header_id FROM oe_order_headers_all WHERE order_number = 57803))

SELECT * FROM wsh_new_deliveries WHERE delivery_id = 211904

SELECT * FROM mtl_material_transactions WHERE transaction_id IN (11262482, 11262483)

    Once Move order is transacted

SELECT header_id FROM mtl_txn_request_headers WHERE request_number = '187252'

       SELECT * FROM mtl_txn_request_lines WHERE header_id = 209289

Once order is ship confirmed

Interface trip stop program automatically launches and order line status gets interfaced, and delivery id status is closed.

SELECT * FROM wsh_delivery_legs WHERE delivery_id = 211904

SELECT * FROM wsh_trip_stops WHERE stop_id IN (216963, 216964)

SELECT * FROM wsh_trips WHERE trip_id = 179789

Inventory Interface- SRS updates inventory with the ship confirmation information.

Inventory balances are decremented and reservations relieved. This program always spawns the Order Management Interface - SRS program. It is very important in the process flow that the Inventory Interface complete before the Order Management Interface to ensure the integrity of the reservation information.

Inventory Interface picks records from MTI (MTL_TRANSACTIONS_INTERFACE) and inserts them into MMTT (MTL_MATERIAL_TRANSACTIONS_TEMP). The Inventory Manager then processes rows and inserts them in to MTL_MATERIAL_TRANSACTIONS.

Order Management  Interface runs to update Order Management with the ship confirmation information. Order line shipped quantities will be updated.

At the end of ship confirm, invoice lines are created in Ra_interface_lines_all table.

Once Ship Confirm is done, Workflow background process runs, and inserts data in below table

Auto Invoice Master program runs to generate entry in AR tables

SELECT * FROM ra_interface_lines_all where interface_line_attribute1 = <order_number>

AR Invoices Related Tables

SELECT * FROM Ra_customer_trx_lines_all where interface_line_attribute1 = <order_number>

SELECT * FROM Ra_customer_trx_all where trx_header_id = <trx_header_id> (trx_header_id fetched from Ra_customer_trx_lines_all)

AR Receipt Table

Select * from ar_cash_receipts_all

Select * from ar_receivable_applications

Transfer TO GL

Run General Ledger Transfer Program, to transfer Accounting info in to GL.

Select * from GL_INTERFACE

Journal Import

Run Journal Import program, to import GL interface data into GL Tables.

GL_JE_BATCHES, GL_JE_HEADERS, GL_JE_LINES :- While importing GL , data in these tables will be in UN Posted Form.

Post IN GL

Once GL Batch is posted, Data will be finally available in

Select * from GL_BALANCES

Order to Cash Cycle - Tables get Affected @ Each Step

1) Order Entry
------------------------------------
oe_order_headers_all.flow_status_code = 'ENTERED'
oe_order_headers_all.booked_flag = 'N'

oe_order_lines_all.flow_status_code = 'ENTERED'
oe_order_lines_all.booked_flag = 'N'
oe_order_lines_all.open_flag = 'Y'

2) Order Booking
------------------------------------
oe_order_headers_all.flow_status_code = 'BOOKED'
oe_order_headers_all.booked_flag = 'Y'

oe_order_lines_all.flow_status_code = 'AWAITING SHIPPING'
oe_order_lines_all.booked_flag = 'Y'

wsh_delivery_details.released_status = 'R' (ready to release)

wsh_delivery_assignments.delivery_id = BLANK

3) Reservation
------------------------------------
mtl_demand

mtl_reservations

4) Pick Release
------------------------------------
wsh_new_deliveries.status_code = 'OP'

wsh_delivery_assignments.delivery_id gets assigned

wsh_delivery_details.released_status = 'S' (submitted for release)

mtl_txn_request_headers

mtl_txn_request_lines

mtl_material_transactions_temp

5) Pick Confirm
------------------------------------
oe_order_lines_all.flow_status_code = 'PICKED'

mtl_material_transactions

wsh_delivery_details.released_status = 'Y' (Released)

mtl_onhand_quantities

6) Ship Confirm
------------------------------------
Data will be removed from wsh_new_deliveries

oe_order_lines_all.flow_status_code = 'SHIPPED'

wsh_delivery_details.released_status = 'C' (Shipped)

wsh_serial_numbers

data will be deleted from mtl_demand and mtl_reservations

item qty gets deducted from mtl_onhand_quantities

7) Enter Invoice
------------------------------------
After shipping the order, workflow backgroud engine picks up records and post it to

ra_interface_lines_all

Auto invoice program picks up records from interface table and insert them into

ra_customer_trx_all (trx_number is invoice number)

ra_customer_trx_lines_all (line_attribute_1 = Order number and line_attribute_6 = Order line id)

8) Complete Line
------------------------------------
oe_order_lines_all.flow_status_code = 'SHIPPED'
oe_order_lines_all.open_flag = 'N'

9) Close Order
------------------------------------
oe_order_headers_all.flow_status_code = 'CLOSED'

oe_order_lines_all.flow_status_code = 'CLOSED'
oe_order_lines_all.open_flag = 'N'

10) Create Receipt
------------------------------------
AR_CASH_RECEIPTS_ALL

11) Transfer to General Ledger
------------------------------------
GL_INTERFACE

12) Journal Import
------------------------------------
GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES

13) Posting
------------------------------------
GL_BALANCES

Whenever Production team raises request for Move order against any batch in OPM, Automated mail should go to Stores Team.

Oracle Alert : Move order Created from Production Batch

I Got one Requirement from Production and Store Team, as below
So I created one Event Based Alert on Move Order Headers Table.


Query is:



SELECT  distinct ORG.ORGANIZATION_NAME,
         TRH.REQUEST_NUMBER,
         GBH.BATCH_NO,
         TRL.TO_SUBINVENTORY_CODE,
         TRL.STATUS_DATE,
         (SELECT (ppf.first_name || ' ' || ppf.last_name)
            FROM fnd_user fnd, per_all_people_f ppf
           WHERE fnd.user_id = trh.created_by
             AND ppf.person_id = fnd.employee_id) creater,
          (SELECT NVL (ppf.email_address, fnd.email_address)
            FROM fnd_user fnd, per_all_people_f ppf
           WHERE fnd.user_id = trh.created_by
             AND ppf.person_id = fnd.employee_id) Email
      INTO &ENTITY,
           &MOVE_ORDER,
           &BATCH_NO,
           &SUB_INV,
           &MV_DATE,
           &CREATER,
           &EMAIL_ID      
    FROM MTL_TXN_REQUEST_HEADERS TRH,
         MTL_TXN_REQUEST_LINES TRL,
         GME_BATCH_HEADER GBH,
         ORG_ORGANIZATION_DEFINITIONS ORG,
         MTL_PARAMETERS MP,
         MFG_LOOKUPS ML1,
         MTL_SYSTEM_ITEMS MSI,
         MFG_LOOKUPS ML2
   WHERE TRH.HEADER_ID = TRL.HEADER_ID
     AND TRH.ORGANIZATION_ID = TRL.ORGANIZATION_ID
     AND TRH.ORGANIZATION_ID = ORG.ORGANIZATION_ID
     AND TRL.TXN_SOURCE_ID=GBH.BATCH_ID
     AND GBH.ORGANIZATION_ID = TRH.ORGANIZATION_ID
     AND GBH.ORGANIZATION_ID = ORG.ORGANIZATION_ID
     AND TRH.ORGANIZATION_ID = MP.ORGANIZATION_ID
     AND TRH.MOVE_ORDER_TYPE = ML1.LOOKUP_CODE
     AND ML1.LOOKUP_TYPE = 'MOVE_ORDER_TYPE'
     AND TRL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
     AND TRL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
     AND TRL.LINE_STATUS = ML2.LOOKUP_CODE
     AND ML2.LOOKUP_TYPE = 'MTL_TXN_REQUEST_STATUS'
     AND TRH.MOVE_ORDER_TYPE=5
     AND TRL.LINE_STATUS IN (3,7)
     AND ORG.OPERATING_UNIT=82--------------Operating unit ID
    AND TRH.rowid=:rowid
     ORDER BY 3,4





Message Body :

Stores Team : Below Move Order Generated from Plant Against Batch No :&BATCH_NO From &SUB_INV


Message Text:

From :Support Team

Below Move Order Generated from Plant

MOVE ORDER NO       : &MOVE_ORDER
ORGANIZATION NAME   : &ENTITY

Thank You
(ERP Support Team)



Screen Shots Captured As:

Provide Email Id's in Action Details and The Message Subject as per the need.

Sunday, September 13, 2015

Run Concurrent Program from Form: Launch SRS Built In

Form Personalization to run Concurrent Program from Form 

Built-in : Launch SRS Program.

Example :

Our Requirement is to Run Production Batch Sample Label from Production Batch Form.

We could completed this requirement through Form Personalization in below steps.

A)-Define 1 Menu : Special Menu for the Report, that shall show in tools Menu bar.

B)-Attach Concurrent Program menu with this Special Menu (defined in the above step).

C)- Store batch id ( For parameter to run your concurrent) in one Global variable.

D)- In Concurrent Definition window , Assign Global variable value to the Parameter for Batch id.

Here are the screen shots.

Requirement is like Below.

Goto Help --> Diagnostics --> Custom Code --> Personalize.

Add 1 New Sequence for You personalization, and Select Event as When-New-Form-Instance.

Goto Actions, and Choose any 1 special Menu.

Once menu is define, we need to attach Concurrent program to this menu.

Create new Sequence, for attaching The concurrent program with special menu defined in above step.

Goto Actions, here we need to define 2 Actions

1)- Global Variable to store Batch ID for Parameter.

2)- Attach Concurrent program to the Special Menu.

Global Variable name should be Logical and unique, as this needs to be pass to your concurrent program.

Save your work, Till here personalization is done for Concurrent program is attached and run, now Concurrent program will be run , but we have to assign Parameters manually, it will not pick any parameter automatically.

Step-3, To pass parameters from Batch form automatically.

GO TOà System adminà applicationà Concurrentà define

Now we have to attach Global variable from above steps to Concurrent Program.

Go to Parameters, and then pass global variable from the earlier form i.e, Batch Form.

Select Batch id parameter.

Its Default Type Should be SQL Statement.

Then Pass the 

“select :GLOBAL.XX_BATCH_ID from dual”

Now try to run report from Batch form,

It will automatically pass your current (Batch opened at form) to the report.

Tuesday, July 28, 2015

Daily Production Query with LOT

SELECT ood.organization_code, ood.organization_id, ood.organization_name,
       ood.operating_unit, gbh.batch_no,
     /*   DECODE (gmg.group_id,1002, 'PLANT_1'
                           ,1003, 'PLANT_2'
                           ,1004, 'PLANT_3'
                           ,1001 ,'WIP_CUBE'
                           ,1000, 'OSP' ) Plant, */
       TRUNC (gbh.actual_cmplt_date) date_of_production,
       TRUNC (gbh.actual_start_date) actual_start_date,
       msik.concatenated_segments product,
       msik.description product_description, gmd.actual_qty qty_produced,
       gmd.plan_qty, gmd.dtl_um uom,
       (Select gms.SOURCE_COMMENT
        from GMD_SAMPLES gms
        where gms.LOT_NUMBER = mtln.lot_number
        and gms.organization_id = msik.organization_id
        and gms.INVENTORY_ITEM_ID = msik.INVENTORY_ITEM_ID
        and rownum = 1) QC_Comments,
       ROUND (  gmd.actual_qty
              * 100
              / (SELECT DECODE (SUM (gmd1.actual_qty),
                                NULL, 1,
                                0, 1,
                                SUM (gmd1.actual_qty)
                               )
                   FROM gme_material_details gmd1
                  WHERE gmd1.line_type = '-1' AND gmd1.batch_id = gbh.batch_id),
              2
             ) yield_percent,
       flv.meaning batch_status,
                                --mmt.transaction_id,
                                --mmt.source_code,
                                mtln.lot_number,
          av.STATUS_CODE quality_status,
     /*  (SELECT flv_spec_disp.meaning spec_disposition
          FROM gmd_samples gs,
               gmd_sampling_events gse,
               gmd_event_spec_disp gesd,
               fnd_lookup_values flv_spec_disp
         WHERE gs.sampling_event_id = gse.sampling_event_id
           AND gse.sampling_event_id = gesd.sampling_event_id
           AND gesd.disposition = flv_spec_disp.lookup_code
           AND flv_spec_disp.lookup_type = 'GMD_QC_SAMPLE_DISP'
           AND gs.lot_number = mtln.lot_number
           AND gs.inventory_item_id = gmd.inventory_item_id
           AND gs.organization_id = gmd.organization_id
           AND ROWNUM = 1) quality_status,*/
       mmt.transaction_quantity, mmt.transaction_uom, mmt.transaction_date,
       gbh.attribute1 dcs_batch_number, gbh.attribute2 dcs_batch_date
  FROM gme_batch_header gbh,
       gme_material_details gmd,
       mtl_system_items_kfv msik,
       MTL_LOT_NUMBERS_ALL_V av,
       org_organization_definitions ood,
   --    gme_batch_groups_association gmg    ,
       fnd_lookup_values flv,
       (SELECT *
          FROM mtl_material_transactions
         WHERE transaction_type_id IN (44, 17)) mmt,
       mtl_transaction_lot_numbers mtln
 WHERE gbh.batch_id = gmd.batch_id
   --AND gmg.batch_id=gbh.batch_id
   AND gmd.line_type = 1
   AND gmd.cost_alloc =
                  (SELECT MAX (gmd1.cost_alloc)
                     FROM gme_material_details gmd1
                    WHERE gmd1.batch_id = gmd.batch_id AND gmd1.line_type = 1)
   AND gmd.inventory_item_id = msik.inventory_item_id
   AND gmd.organization_id = msik.organization_id
   AND gmd.organization_id = ood.organization_id
   AND gbh.batch_status = flv.lookup_code
   and av.inventory_item_id=mtln.inventory_item_id
   and av.organization_id = mtln.organization_id
   and av.lot_number = mtln.lot_number
   AND flv.lookup_type = 'GME_BATCH_STATUS'
   AND flv.LANGUAGE (+) =  'PTB'
   AND gmd.batch_id = mmt.transaction_source_id(+)
   AND gmd.inventory_item_id = mmt.inventory_item_id(+)
   --AND mmt.source_code(+) = 'OPM'
   AND mmt.transaction_id = mtln.transaction_id(+)
    --  AND ood.organization_id in (select ood.organization_id from
--AND gbh.batch_no = '3040004832';
   AND ood.operating_unit = :p_org_id
  /*  and DECODE (gmg.group_id,1002, 'PLANT_1'
                           ,1003, 'PLANT_2'
                           ,1004, 'PLANT_3'
                           ,1001 ,'WIP_CUBE'
                           ,1000, 'OSP' ) = nvl(:p_plant,  DECODE (gmg.group_id,1002, 'PLANT_1'
                                               ,1003, 'PLANT_2'
                                               ,1004, 'PLANT_3'
                                               ,1001 ,'WIP_CUBE'
                                               ,1000, 'OSP' )) */
   AND gbh.batch_no BETWEEN NVL (:p_from_batch, gbh.batch_no)
                        AND NVL (:p_to_batch, gbh.batch_no)
   AND flv.meaning = NVL (:p_status, flv.meaning)
   AND msik.concatenated_segments between NVL(:P_FROM_ITEM,msik.concatenated_segments)
                                                                 AND NVL(:P_TO_ITEM,msik.concatenated_segments)
   AND TRUNC (mmt.transaction_date) BETWEEN NVL
                                                (TRUNC (TO_DATE (:p_from_date,
                                                                 'DD-MON-RRRR'
                                                                )
                                                       ),
                                                 TRUNC (mmt.transaction_date)
                                                )
                                         AND NVL
                                               (TRUNC (TO_DATE (:p_to_date,
                                                                'DD-MON-RRRR'
                                                               )
                                                      ),
                                                TRUNC (mmt.transaction_date)
                                               )
ORDER BY gbh.batch_no

Friday, July 24, 2015

SQL Query for Oracle OPM Receipe and Formula Dump

select gmd.RECIPE_NO Receipe_code,RECIPE_DESCRIPTION Receipe_Name ,gmd.RECIPE_VERSION,
(select distinct description from mtl_system_items mtl1,FM_MATL_DTL fd1
where fd1.line_type=1
and fd1.formula_id=fd.formula_id
and fd1.inventory_item_id = mtl1.inventory_item_id
and fd1.organization_id= mtl1.organization_id) product,
fm.formula_no,fm.FORMULA_DESC1,fm.FORMULA_VERS,
decode (gmd.RECIPE_STATUS,'900', 'Frozen','700','Approved for General Use') Rec_Status,
b.segment1||'-'||b.segment2 Item_code,
b.description item_desc,
fd.line_no,
fd.qty
from FM_FORM_MST fm,
FM_MATL_DTL fd,
mtl_system_items b,
GMD_RECIPE_VALIDITY_RULES GV,
gmd_recipes gmd
where  fd.formula_id=fm.formula_id
and fd.organization_id=fm.OWNER_ORGANIZATION_ID
and fm.formula_id =gmd.formula_id
and fm.owner_organization_id=gmd.owner_organization_id
AND GV.RECIPE_ID=GMd.RECIPE_ID
and b.inventory_item_id=fd.inventory_item_id
and b.organization_id=fm.OWNER_ORGANIZATION_ID
and fm.OWNER_ORGANIZATION_ID in (XXXX)
and fd.line_type in (-1,2)
--and fm.FORMULA_STATUS=700
and gmd.RECIPE_STATUS in (700)
--and gmd.RECIPE_NO=''XXXX
order by 1

Oracle EBS Practical Approaches

Run Concurrent Program from Form: Launch SRS Built In

Form Personalization to run Concurrent Program from Form 

Built-in : Launch SRS Program.

Example :

Our Requirement is to Run Production Batch Sample Label from Production Batch Form.

We could completed this requirement through Form Personalization in below steps.

A)-Define 1 Menu : Special Menu for the Report, that shall show in tools Menu bar.

B)-Attach Concurrent Program menu with this Special Menu (defined in the above step).

C)- Store batch id ( For parameter to run your concurrent) in one Global variable.

D)- In Concurrent Definition window , Assign Global variable value to the Parameter for Batch id.

Here are the screen shots.

Requirement is like Below.

Goto Help --> Diagnostics --> Custom Code --> Personalize.

Add 1 New Sequence for You personalization, and Select Event as When-New-Form-Instance.

Goto Actions, and Choose any 1 special Menu.

Once menu is define, we need to attach Concurrent program to this menu.

Create new Sequence, for attaching The concurrent program with special menu defined in above step.

Goto Actions, here we need to define 2 Actions

1)- Global Variable to store Batch ID for Parameter.

2)- Attach Concurrent program to the Special Menu.

Global Variable name should be Logical and unique, as this needs to be pass to your concurrent program.

Save your work, Till here personalization is done for Concurrent program is attached and run, now Concurrent program will be run , but we have to assign Parameters manually, it will not pick any parameter automatically.

Step-3, To pass parameters from Batch form automatically.

GO TOà System adminà applicationà Concurrentà define

Now we have to attach Global variable from above steps to Concurrent Program.

Go to Parameters, and then pass global variable from the earlier form i.e, Batch Form.

Select Batch id parameter.

Its Default Type Should be SQL Statement.

Then Pass the 

“select :GLOBAL.XX_BATCH_ID from dual”

Now try to run report from Batch form,

It will automatically pass your current (Batch opened at form) to the report.

Friday, July 24, 2015

ZOOM Functionality and Global variable in Form Personalization

1)- Global Variable in forms Personalization.

  A)- These variables are accessible through out the application until the current active session becomes inactive.

B)- Data type for Global variables are always CHAR, hence if global variable needs to initialize or assign for any numeric value , use TO_CHAR

C)- Oracle 10g Can store as max as 4000 Bytes in Global variable, as compared wit just 255 bytes in 6i.


Syntax to Access of global variable.

  :GLOBAL.VARIABLE_NAME


2)- ZOOM Functionality.

   This functionality gives feature to pass the control from one form to Another.

So For any event we can set zoom functionality , for example on button click , control passes from the current form to another form, that is defined in personlization.



ZOOM Functionality is same as BUILT-IN Launch SRS Form with just one difference is that
In Launch SRS Form built-in we have to pass parameters manually, but in zoom functionality parameters can be passed automatically form the values available on current form.

Oracle Process Manufacturing (OPM) Product Development Status Codes

This appendix outlines how status codes change when workflow is and is not running.

Workflow Running and Approval Required for the Current Data

A status of New changes to a status of:

• Pending Approval for Laboratory Use when the Requester selects Approve for Laboratory Use.
  
• Pending Approval for General Use when the Requester selects Approve for General Use.
  
• Obsolete or Archived when the Requester selects Obsolete or Archived.

A status of Pending Approval for Laboratory Use changes to a status of:

• Approved for Laboratory Use when the Approver approves the request.
  
• Revise for Laboratory Use when the Approver rejects the request.

A status of Revise for Laboratory Use changes to a status of:

• Pending Approval for Laboratory Use when the Requester selects Approve for Laboratory Use.

A status of Approved for Laboratory Use changes to a status of:

• Pending Approval for General Use when the Requester selects Approve for General Use.
  
• On hold when the Requester selects On Hold.
  
• Obsolete or Archived when the Requester selects Obsolete or Archived.

A status of Pending Approval for General Use changes to a status of:

• Approved for General Use when the Approver approves the request.
  
• Revise for General Use when the Approver rejects the request.

A status of Revise for General Use changes to a status of:

• Pending Approval for General Use when the Requester selects Approve for General Use.

A status of Approved for General Use changes to a status of:

• On hold when the Requester selects On Hold.
  
• Frozen when the Requester selects Frozen.
  
• Obsolete or Archived when the Requester selects Obsolete or Archived.

A status of On Hold changes to a status of:

• Pending Approval for Laboratory Use when the Requester selects Approve for Laboratory Use.
  
• Revise for Laboratory Use when the Requester selects Revise for Laboratory Use.
  
• Revise for General Use when the Requester selects Revise for General Use.
  
• Pending Approval for General Use when the Requester selects Approve for General Use.
  
• Obsolete or Archived when the Requester selects Obsolete or Archived.

A status of Frozen changes to a status of:

• On hold when the Requester selects On Hold.
  
• Obsolete or Archived when the Requester selects Obsolete or Archived.

A status of Obsolete or Archived changes to a status of:

• Revise for Laboratory Use when the Requester selects Revise for Laboratory Use.
  
• Revise for General Use when the Requester selects Revise for General Use.

Workflow Not Running

A status of New changes to a status of:

• Approve for Laboratory Use when the Requester selects Approve for Laboratory Use.
  
• Approve for General Use when the Requester selects Approve for General Use.
  
• Obsolete or Archived when the Requester selects Obsolete or Archived.

A status of Approved for Laboratory Use changes to a status of:

• Approve for General Use when the Requester selects Approve for General Use.
  
• On hold when the Requester selects On Hold.
  
• Obsolete or Archived when the Requester selects Obsolete or Archived.

A status of Approved for General Use changes to a status of:

• On hold when the Requester selects On Hold.
  
• Frozen when the Requester selects Frozen.
  
• Obsolete or Archived when the Requester selects Obsolete or Archived.

A status of On Hold changes to a status of:

• Approve for Laboratory Use when the Requester selects Approve for Laboratory Use.
  
• Approve for General Use when the Requester selects Approve for General Use.
  
• Obsolete or Archived when the Requester selects Obsolete or Archived.

A status of Frozen changes to a status of:

• On hold when the Requester selects On Hold.
  
• Obsolete or Archived when the Requester selects Obsolete or Archived.

A status of Obsolete or Archived changes to a status of:

• Approve for Laboratory Use when the Requester selects Approve for Laboratory Use.
  
• Approve for General Use when the Requester selects Approve for General Use.

Set up Workbench Responsibilities for each user. The following are menu and submenu selections available to each listed Workbench Responsibility:

• Formulator
  
  • Formulator Workbench
    
  • Formulas
    
  • Recipes
    
  • Mass Search and Replace
    
  • Simulator
    
  • Formulation Specification Workbench
    
  • Inventory Items
    
  • Indented Bill of Materials
    
  • Formula Inquiry
    
  • Validity Rules - Product
    
  • Validity Rules - Recipe
    
  • Technical Data: Technical Parameters
    
  • Technical Data: Technical Parameter Sequences
    
  • Technical Data: Item Technical Data
    
  • Formula Analysis: Parameters
    
  • Formula Analysis: Analysis
    
  • Setup: Formula Class
    
  • Setup: Status Codes
    
  • Setup: Recipe Generation
    
  • Setup: Item Substitution List Setup
    
  • Setup: Process Engineering Items
    
    • Master Items
    • Organization Items
  • Others
    
    • Profile Options
    • Lookups
    • Change Organization
    • Session Parameters
    • User Organizations
  • Workflow
    
    • Advanced Worklist
  • Self Service Workflow
    
    • Home
    • Status Monitor
    • Notifications
  • Recipe Designer
• Process Engineer
  
  • Engineering Workbench
    
  • Process Routings
    
  • Process Operations
    
  • Recipes
    
  • Mass Search and Replace
    
  • Setup: Routing Classes
    
  • Setup: Operation Classes
    
  • Setup: Activities
    
  • Setup: Generic Resources
    
  • Setup: Plant Resources
    
  • Setup: Alternate Resources
    
  • Setup: Status Codes
    
  • Setup: Process Parameters
    
    • Define Parameters
    • Parameter Sets
    • Generic Resource Parameters
    • Plant Resource Parameters
  • Others
    
    • Profile Options
    • Lookups
    • Change Organization
    • Session Parameters
    • User Organizations
  • Workflow
    
    • Advanced Worklist
  • Self Service Workflow
    
    • Home
    • Status Monitor
    • Notifications
  • Recipe Designer
    
  • Routing Designer
• Product Development Security Manager
  
• Product Development Security Profile Manager

'Error_code as 'Transaction processor error and Error Explanation as 'Negative balances not allowed'

'Pending Transactions' 

Transaction Pending in the  MTL_MATERIAL_TRANSACTIONS_TEMP Table, if you are unable to clear please you this Data Fix Suggested by Oracle .

Error_code as 'Transaction
processor error and Error Explanation as 'Negative balances not allowed'

check MTL_MATERIAL_TRANSACTIONS_TEMP table
the values

•  process_flag,
•  lock_flag,
•  transaction_mode 

Then Use this Update Script :

 Update MTL_MATERIAL_TRANSACTIONS_TEMP
Set PROCESS_FLAG = 'Y',
LOCK_FLAG = 'N',
TRANSACTION_MODE = 3,
ERROR_CODE = NULL
Where TRANSACTION_HEADER_ID = '& TRANSACTION_HEADER_ID';

Oracle Period End Processing/Closing, Preliminary/Final Close

Oracle Period End Processing/Closing, Preliminary/Final Close  11i and R12

Introduction

Period End Processing - Summary

1. A summary of the period-end procedures for each of the Financials applications
2. An indication of the order in which the steps should be undertaken.

Modules :

Payables

1. Complete All Transactions for the Period Being Closed
2. Run the Payables AutoApproval Process for All Invoices / Invoice Batches
3. Review & Resolve Amounts to Post to the General Ledger
4. Reconcile Payments to Bank Statement Activity for the Period
5. Transfer All Approved Invoices Payments, Reconciled Payments to the General Ledger
6. Review the Payables to General Ledger Posting Process After Completion
7. Submit the Unaccounted Transactions Sweep Program
8. Close the Current Oracle Payables Period
9. Accrue Uninvoiced Receipts
10. Reconcile Oracle Payables Activity for the Period
11. Run Mass Additions Transfer to Oracle Assets
12. Open the Next Payables Period
13. Run Reports for Tax Reporting Purposes (Optional)
14. Run the Key Indicators Report (Optional)
15. Purge Transactions (Optional)

Purchasing

1. Complete All Transactions for the Period Being Closed
2. Review the Current and Future Commitments (Optional)
3. Review the Outstanding and Overdue Purchase Orders (Optional)
4. Follow up Receipts-Check with Suppliers
5. Identify and Review Un-invoiced Receipts (Period End Accruals)
6. Follow Up Outstanding Invoices
7. Complete the Oracle Payables- Period End Process
8. Run Receipt Accruals - Period End Process
9. Reconcile Accounts - Perpetual Accruals
10. Perform Year End Encumbrance Processing. (Optional)
11. Close the Current Purchasing Period.
12. Open the Next Purchasing Period.
13. Run Standard Period End Reports (Optional)

Inventory/WIP

1. Complete All Transactions for the Period Being Closed.
2. Check Inventory and Work In Process Transaction Interfaces.
3. Check Oracle Order Management Transaction Process.
4. Review Inventory Transactions.
5. Balance the Perpetual Inventory.
6. Validate Work In Process Inventory.
7. Transfer Summary or Detail Transactions
Close the Current Oracle Payables and Oracle Purchasing Periods 8.
9. Close the Current Inventory Period
10. Open the Next Inventory Period
11. Run Standard Period End Reports (Optional)

Order Management

1. Complete All Transactions for the Period Being Closed
2. Ensure all Interfaces are Completed for the Period (Optional)
3. Review Open Orders and Check the Workflow Status
4. Review Held Orders
5. Review Discounts
6. Review Backorders
7. Review and Correct Order Exceptions
8. Reconcile to Inventory
9. Reconcile to Receivables (Optional)
10. Run Standard Period End Reports

Receivables

1. Complete All Transactions for the Period Being Closed
2. Reconcile Transaction Activity for the Period
3. Reconcile Outstanding Customer Balances
4. Review the Unapplied Receipts Register
5. Reconcile receipts.
6. Reconcile Receipts to Bank Statement Activity for the Period
7. Post to the General Ledger
8. Reconcile the General Ledger Transfer Process
9. Reconcile the Journal Import Process
10. Print Invoices
11. Print Statements (Optional)
12. Print Dunning (Reminder) Letters (Optional)
13. Close the Current Oracle Receivables Period
14. Reconcile Posted Journal Entries
15. Review Unposted Items Report
16. Open the Next Oracle Receivables Period
17. Run Reports for Tax Reporting Purposes (Optional)
18. Run Archive and Purge programs (Optional)

Assets

1. Complete All Transactions for the Period Being Closed
2. Assign All Assets to Distribution Lines
3. Run Calculate Gains and Losses (Optional)
4. Run Depreciation
5. Create Journal Entries
6. Rollback Depreciation and/or Rollback Journal Entries (Optional)
7. Create Deferred Depreciation Journal Entries (Optional)
8. Depreciation Projections(Optional)
9. Review and Post Journal Entries
10. Reconcile Oracle Assets to Oracle General Ledger Using Reports.
11. Run Responsibility Reports (Optional)
12. Archive and Purge Transactions (Optional)

Projects

1. Change the Current Oracle Projects Period Status from Open to Pending Close
2. Open the Next Oracle Projects Period
3. Complete All Maintenance Activities
4. Run Maintenance Processes
5. Complete All Transaction Entry for the Period Being Closed
6. Run the Final Cost Distribution Processes
7. Interface Transactions to Other Applications (AP, GL, FA)
8. Generate Draft Revenue for All Projects
9. Generate Invoices
10. Run Final Project Costing and Revenue Management Reports
11. Transfer Invoices to Oracle Receivables
12. Interface Revenue to General ledger (Project Billing Only)
13. Run Period Close Exception and Tieback Reports
14. Change the Current Period Oracle Projects Status from Pending Close to Closed
15. Advance the PA Reporting Period (Optional)
16. Update Project Summary Amounts
17. Restore Access to User Maintenance Activities
18. Permanently Close the Oracle Projects Period (Optional)
19. Reconcile Cost Distribution Lines with General Ledger (Optional)

Cash Management

1. Load Bank Statements
2. Reconcile Bank Statements
3. Create Miscellaneous Transactions
4. Review Auto Reconciliation Execution Report
5. Resolve Exceptions on the Auto Receonciliation Execution Report
6. Run Bank Statement Detail Report
7. Run Transactions Available for Reconciliation Report
8. Resolve Un-reconciled Statement Lines
9. Run the GL Reconciliation Report
10. Run the Account Analysis Report for the General Ledger Cash Account
11. Review the Account Analysis Report
12. Correct any Invalid Entries to the General Ledger Cash Account (Optional)
13. Perform the Bank Reconciliation

General Ledger

1. Ensure the Next Accounting Period Status is Set to Future Entry
2. Complete Oracle Sub-ledger Interfaces to Oracle General Ledger
3. Upload Journals from ADI (Applications Desktop Integrator) to Oracle General Ledger
4. Complete Non-Oracle Sub-ledger Interfaces to Oracle General Ledger (Optional)
5. Generate Reversal Journals (Optional)
6. Generate Recurring Journals (Optional)
7. Generate Mass Allocation Journals (Optional)
8. Review and Verify Journal Details of Unposted Journal Entries
9. Post All Journal Batches
10. Run General Ledger Trial Balances and Preliminary Financial Statement Generator Reports (FSGs)
11. Revalue Balances (Optional)
12. Translate Balances (Optional)
13. Consolidate Sets of Books (Optional)
14. Review and Correct Balances (Perform Reconciliations)
15. Enter Adjustments and / or Accruals and Post
16. Perform Final Adjustments
17. Close the Current Oracle Gneral Ledger Period
18. Open the Next Oracle General Ledger Period
19. Run Financial Reports for the Closed Period
20. Run Reports for Tax Reporting Purposes (Optional)
21. Perform Encumbrance Year End Procedures (Optional)

OPM Inventory Tables R12

BISOPM_ONHANDSALE_SUM

BIS_OPM_PROD_SUM

GMI_AUTO_ALLOCATION_BATCH

GMI_CATEGORY_SETS

GMI_CLOS_WAREHOUSES

GMI_DISCRETE_TRANSFERS

GMI_DISCRETE_TRANSFER_LINES

GMI_DISCRETE_TRANSFER_LOTS

GMI_ITEMS_XML_INTERFACE

GMI_ITEM_CATEGORIES

GMI_ITEM_CONV_AUDIT

GMI_ITEM_CONV_AUDIT_DETAILS

GMI_ITEM_ORGANIZATIONS

GMI_LOTS_CONV_XML_INTERFACE

GMI_LOTS_XML_INTERFACE

GMI_LOT_TRACE

GMI_MASSTRANS_CTL

GMI_MIGRATION_PARAMETERS

GMI_OBSOLETE_ITEM_COLUMNS

GMI_QUANTITY_XML_INTERFACE

GMI_SUBLOT_GENERATE

GMI_TRAN_TMP

IC_ADJS_JNL

IC_ALLC_CLS

IC_ALOT_PRM

IC_CLDR_DTL

IC_CLDR_HDR

IC_CLDR_HDR_B

IC_CLDR_HDR_TL

IC_COMD_CDS

IC_COST_CLS

IC_CRUL_CLS

IC_CTMS_CLS

IC_CYCL_ADT

IC_CYCL_DTL

IC_CYCL_ERR

IC_CYCL_HDR

IC_CYCL_MSC

IC_DECM_MST

IC_FRGT_CLS

IC_GLED_CLS

IC_INVN_CLS

IC_INVN_TYP

IC_ITEM_CDT

IC_ITEM_CNV

IC_ITEM_CPG

IC_ITEM_HIERARCHY

IC_ITEM_MST

IC_ITEM_MST_B

IC_ITEM_MST_B_MIG

IC_ITEM_MST_TL

IC_ITEM_WHS

IC_JRNL_MST

IC_LOCT_INV

IC_LOCT_MST

IC_LOTS_CPG

IC_LOTS_MST

IC_LOTS_MST_MIG

IC_LOTS_STS

IC_PERD_BAL

IC_PHYS_CNT

IC_PHYS_ERR

IC_PKGS_MST

IC_PLNT_INV

IC_PRCE_CLS

IC_PRCH_CLS

IC_PURG_PRM

IC_RANK_MST

IC_SALE_CLS

IC_SHIP_CLS

IC_STAT_RPT

IC_STOR_CLS

IC_SUMM_INV

IC_TAXN_ASC

IC_TAXN_CLS

IC_TEXT_HDR

IC_TEXT_TBL_TL

IC_TRAN_ARC

IC_TRAN_CMP

IC_TRAN_PND

IC_TXN_REQUEST_HEADERS

IC_TXN_REQUEST_LINES

IC_WHSE_INV

IC_WHSE_MST

IC_WHSE_REL

IC_WHSE_STS

IC_XFER_MST

IN_ADDR_MST

IN_CTRY_MST

IN_ITEM_MST

IN_REGN_MST

IN_STAT_CTL

IN_STAT_DTL

IN_STAT_HDR

IN_STAT_MSG

IN_TEXT_HDR

IN_TEXT_TBL_TL

IN_TOFD_MST

SY_WF_ITEM_ROLES