Oracle EBS - SQL Query to get Inventory Interface Manager Status

 

SELECT

    x.process_type       "Name",

    decode((

        SELECT

            '1'

        FROM

            apps.fnd_concurrent_requests       cr, apps.fnd_concurrent_programs_vl    cp, apps.fnd_application               a

        WHERE

                cp.concurrent_program_id = cr.concurrent_program_id

            AND cp.concurrent_program_name = x.process_name

            AND cp.application_id = a.application_id

            AND a.application_short_name = x.process_app_short_name

            AND phase_code != 'C'

            AND ROWNUM = 1

    ),

           '1',

           'Active',

           'Inactive')   "Status",

    x.worker_rows        "Worker Rows",

    x.timeout_hours      "Timeout Hours",

    x.timeout_minutes    "Timeout Minutes",

    x.process_hours      "Process Interval Hours",

    x.process_minutes    "Process Interval Minutes",

    x.process_seconds    "Process Interval Seconds"

FROM

    (

        SELECT

            mipc.process_code,

            mipc.process_status,

            mipc.process_interval,

            mipc.manager_priority,

            mipc.worker_priority,

            mipc.worker_rows,

            mipc.processing_timeout,

            mipc.process_name,

            mipc.process_app_short_name,

            a.meaning                                                                                                                                                                                      process_type,

            floor(mipc.process_interval / 3600)                                                                                                                                                              process_hours,

            floor((mipc.process_interval -(floor(mipc.process_interval / 3600) * 3600)) / 60)                                                                                                            process_minutes,

            ( mipc.process_interval - ( floor(mipc.process_interval / 3600) * 3600 ) - ( floor((mipc.process_interval -(floor(mipc.

            process_interval / 3600) * 3600)) / 60) * 60 ) )                         process_seconds,

            floor(mipc.processing_timeout / 3600)                                                                                                                                                            timeout_hours,

            floor((mipc.processing_timeout - floor(mipc.processing_timeout / 3600) * 3600) / 60)                                                                                                          timeout_minutes

        FROM

            apps.mtl_interface_proc_controls    mipc,

            apps.mfg_lookups                    a

        WHERE

                a.lookup_type = 'PROCESS_TYPE'

            AND a.lookup_code = mipc.process_code

    ) x 

 -- WHERE x.PROCESS_TYPE = 'Cost Manager' -- uncomment this to display only the cost manager; Possible Values: Cost Manager; Lot Move Transaction; Material transaction; Move transaction

ORDER BY

    1;

Oracle EBS - SQL Query to check Purchase Order Receipt Routing Type

TABLE: RCV_TRANSACTIONS

COLUMN: ROUTING_HEADER_ID

VALUES: 

1 (Standard Receipt), 

2 (Inspection Required) And 

3 (Direct Delivery).

SQL Query:

SELECT pha.segment1                   po_number,

             pla.line_num                   po_line_number,

             pra.release_num                po_release_number,

             rsh.receipt_num                receipt_number,

             decode(rt.routing_header_id,1,'Standard Receipt', 2, 'Inspection Required', 3, 'Direct Delivery',null) Receipt_Routing,

             msik.concatenated_segments     item_number,

             msik.description,

             rs.unit_of_measure,

             rs.item_revision,

             rs.receipt_date,

             plla.quantity                  total_quantity,

             rs.quantity                    quantity_to_inspect,

             plla.quantity_received,

             plla.quantity_accepted,

             plla.quantity_rejected,

             rt.vendor_lot_num,

             rt.comments                    vendor_serial_num,

             rsh.bill_of_lading             customs_number,

             rsh.comments                   customs_date,

             rt.attribute1                  manufactured_date,

             rs.po_line_id,

             rs.po_header_id,

             rs.po_release_id,

             rs.po_line_location_id,

             rs.shipment_header_id,

             rs.shipment_line_id,

             rs.rcv_transaction_id,

             rs.item_id,

             rs.to_organization_id,

             rs.supply_source_id,

             mp.organization_code

        FROM apps.rcv_supply           rs,

             apps.mtl_system_items_kfv msik,

             apps.po_headers_all       pha,

             apps.po_lines_all         pla,

             apps.po_releases_all      pra,

             apps.po_line_locations_all plla,

             apps.rcv_transactions     rt,

             apps.rcv_shipment_headers rsh,

             apps.mtl_parameters       mp

       WHERE     rs.to_organization_id = mp.organization_id

             AND mp.organization_code IN ('SKO', 'TIF', 'TIS', 'TIW')

             AND rs.supply_type_code = 'RECEIVING'

             AND rs.po_header_id = pha.po_header_id

             AND msik.inventory_item_id = rs.item_id

             AND msik.organization_id = rs.to_organization_id

             AND rs.po_line_id = pla.po_line_id

             AND rs.po_release_id = pra.po_release_id(+)

             --AND TRUNC(rs.receipt_date) = TRUNC(SYSDATE)

             AND rs.po_line_location_id = plla.line_location_id

             AND rs.rcv_transaction_id = rt.transaction_id

             AND rt.transaction_type = 'RECEIVE'

             AND rt.inspection_status_code = 'NOT INSPECTED'

             AND rs.shipment_header_id = rsh.shipment_header_id

--             AND pha.segment1='333428'

--             AND rt.routing_header_id=2 -- 1 (Standard receipt), 2 (Inspection required) and 3 (Direct delivery)

             

    ORDER BY 9,

             5,

             1,

             2,

             4;

Oracle EBS - How to Enable New Tax Setup

Step1: Need to assign below responsibility to in User Account for Tax Setup Configuration

Responsibility: Tax Managers

 

Step2: New Tax Setup for ‘FREMONT CA-PARTIAL’

Tax Regime Name: STATE TAX

Tax Regime: STATE TAX

Tax Name: FREMONT CA - PARTIAL

Tax Jurisdiction: FREMONT CA - PARTIAL

Tax Status: FREMONT CA - PARTIAL

Tax Rate Code: FREMONT CA - PARTIAL

Tax Classification: FREMONT CA - PARTIAL

Configuration Owner: Global Configuration Owner

Rate Type: Percentage

%Rate: 6.3125

 

Step1: Define Tax Regime:

(Note: No Need to perform Regime Setup If Regime Setup already Exists)

Navigation: Tax Manager Responsibility > Tax Configuration > Tax Regime

Click on Create Button.

Tax Regime: STATE TAX

Click on Apply.

 

Step2: Define Tax

Navigation: Tax Manager Responsibility > Tax Configuration > Taxes

Click on Create Button.

Tax Name: FREMONT CA - PARTIAL

Click on ‘Tax Account’ button

Step3: Define Tax Jurisdictions

Navigation: Tax Manager Responsibility > Tax Configuration > Tax Juridictions

Click on Create Button

 

Step4: Define Tax Statuses

Navigation: Tax Manager Responsibility > Tax Configuration > Tax Statuses

Click on Create Button.

Step5: Define Tax Rates.

Navigation: Tax Manager Responsibility > Tax Configuration > Tax Rates

Click on Create Button

Tax Regime: STATE TAX

Configuration Owner: Global Configuration Owner

Tax Rate Code: FREMONT CA - PARTIAL

Rate Type: Percentage

%Rate: 6.3125

Click on Rate Details Icon.

Tax Rate Detail:

Tax Rate Name: FREMONT CA - PARTIAL

Tax Rate Description: FREMONT CA - PARTIAL

 

Click on Apply.

Tax Account:

Click on Tax Account Icon.

 

Step6: Define Tax Rule

Navigation: Tax Manager Responsibility > Tax Configuration > Tax Rules

Need to Search with

Configuration Owner: Global Configuration Owner

Tax Regime: STATE TAX

Tax Name: FREMONT CA - PARTIAL

Need to update the following details.

 

Determine Place of Supply: Ship to, use bill to if ship to is not found

Determine Tax Applicability: Applicable

Determine Tax Registration: Bill From Party

Determine Tax Status: FREMONT CA - PARTIAL

Determine Tax Rate: FREMONT CA - PARTIAL

Determine Taxable Basis: STANDARD_TB

Calculate Tax Amounts: STANDARD_TC

 

Tax Rule Code: P2P_TAX_CODE_NOT_APPLY_OM

Name: P2P_TAX_CODE_NOT_APPLY_OM

 

Click on Next

Location Type: Bill To

Geography Type: Country

Operator: Not equal to

Value: Afghanistan

 Click on Next.

Click on Next

Click on Next

Determining Factor Set: FCP_DFS_P2P_1

Name: FCP_DFS_P2P_1

Condition Set: FCP_CS_P2P_1

Name: FCP_CS_P2P_1

Click on Finish.

Click on Pencil Icon.

Select Enable Checkbox

Click on Next & Finish.

 

Step7: Now we Need to Enable Tax.

Navigation: Tax Manager Responsibility > Tax Configuration > Taxes

Country: United States

Regime: STATE TAX

Tax Rate Name: FREMONT CA - PARTIAL      

 

Search with above values

Click on Update Button.

Enable the Checkbox and click on Apply

 

 

Now we can test the Tax Setup on Purchase Order Transaction.