Thursday, 13 December 2012


 
Click to add to FavoritesTo BottomTo Bottom

16-Jun-2011HOWTOPUBLISHED3
There are no commentsComments (0)Rate this documentEmail link to this documentOpen document in new windowPrintable Page
In this Document
 


Solution

Following SAMPLE scripts are intended

    * To create a simple ASN Shipment via ROI for a Standard Purchase Order.
    * To enter a Receive and Deliver to Inventory Transaction with Inventory destination type for the ASN Shipment already created through the Receiving Open Interface (ROI) (ie Perform the RECEIVE and DELIVER transaction at the same time) for a standard item.

The script will load records into the tables:

    * RCV_HEADERS_INTERFACE,
    * RCV_TRANSACTIONS_INTERFACE

SETUP

0) Ensure to apply the patches listed in Document 367396.1 Recommended Patches for Enhanced Receiving Validation after upgrade to or install of 11.5.10 (or 11i.SCM_PF.J); For release 12 see Document 423541.1

1) Purchasing / Purchase Orders / Purchase Orders
    Create and Approve Standard PO Number 17646
    Vendor = F_Supplier

    One line / shipment line / distribution line with quantity = 20 ,
    Item = F-Item1
    Destination org = V1
    Destination Type = Inventory

2) Run the following scripts so to find the necessary information to insert  into the RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE tables


Select * from PO_HEADERS_ALL where SEGMENT1 = '&po_number'

PO_HEADER_ID    97981
TYPE_LOOKUP_CODE STANDARD
SEGMENT1     17646
VENDOR_ID    7927
VENDOR_SITE_ID    4724
APPROVED_FLAG    Y
ORG_ID    204


Select * from PO_LINES_ALL where PO_HEADER_ID=&Po_header_id;

PO_LINE_ID    105281
PO_HEADER_ID    97981
LINE_NUM    1
ITEM_ID    26816
CATEGORY_ID    1
ITEM_DESCRIPTION    Standard Purchased Item
UNIT_MEAS_LOOKUP_CODE    Each
QUANTITY    20
ORG_ID    204


Select * from PO_LINE_LOCATIONS_ALL where PO_HEADER_ID=&Po_header_id;

LINE_LOCATION_ID    139830
PO_HEADER_ID    97981
PO_LINE_ID    105281
QUANTITY    20
QUANTITY_RECEIVED    0
SHIP_TO_LOCATION_ID    204
SHIP_TO_ORGANIZATION_ID    204
SHIPMENT_NUM    1
ORG_ID    204


Select * from PO_DISTRIBUTIONS_ALL where PO_HEADER_ID=&Po_header_id;

PO_DISTRIBUTION_ID    141115
PO_LINE_LOCATION_ID    139830
PO_HEADER_ID    97981
PO_LINE_ID    105281
QUANTITY_DELIVERED    0
DELIVER_TO_LOCATION_ID
DELIVER_TO_PERSON_ID
DESTINATION_TYPE_CODE    INVENTORY
DESTINATION_ORGANIZATION_ID    204
DESTINATION_SUBINVENTORY    Stores
DISTRIBUTION_NUM    1
ORG_ID    204


A)   Via ROI Create a ASN [ship,ship]  for a quantity =3 on STANDARD PURCHASE ORDER Create  via ROI an Advanced Shipment Notice ASN number F-ASN-PO-17646-3
for  Standard Purchase Order Number 17646 (PO_HEADER_ID=97981) on Vendor =F_Supplier of 3 items in destination organization V1

1) Insert following Data:
. 1 record in RCV_HEADERS_INTERFACE
    RECEIPT_SOURCE_CODE = VENDOR
    ASN_TYPE  = ASN
    TRANSACTION_TYPE=   NEW
    AUTO_TRANSACT_CODE =  SHIP
    SHIPMENT_NUM  = F-ASN-PO-17646-3

. 1 record in RCV_TRANSACTIONS_INTERFACE
   TRANSACTION_TYPE =   SHIP
   AUTO_TRANSACT_CODE = SHIP
   RECEIPT_SOURCE_CODE = VENDOR
   QUANTITY  = 3

 Use the insert script of Document 225233.1 to create ASN shipment number
  'F-ASN-PO-17646-3' SHIPMENT_NUM with quantity shipped=3
  for Standard PO 17646 - Line 1 - Shipment line 1


INSERT INTO RCV_HEADERS_INTERFACE
            (HEADER_INTERFACE_ID,
             GROUP_ID,
             PROCESSING_STATUS_CODE,
             RECEIPT_SOURCE_CODE,
             ASN_TYPE,
             TRANSACTION_TYPE,
             AUTO_TRANSACT_CODE,
             LAST_UPDATE_DATE,
             LAST_UPDATED_BY,
             LAST_UPDATE_LOGIN,
             CREATION_DATE,
             CREATED_BY,
             SHIPMENT_NUM,
             VENDOR_ID,
             SHIP_TO_ORGANIZATION_ID,
             SHIPPED_DATE,
             EXPECTED_RECEIPT_DATE,
             EMPLOYEE_ID,
            VALIDATION_FLAG
            )
            VALUES
             (rcv_headers_interface_s.nextval , --Header_Interface_Id
             rcv_interface_groups_s.nextval,   --Group_Id
             'PENDING', --Processing_Status_Code
             'VENDOR', --Receipt_Source_Code
             'ASN', --ASN_TYPE
             'NEW', --Transaction_Type
             'SHIP',  --Auto_Transact_Code
             SYSDATE, --Last_Update_Date
             0,  --Last_Updated_By
             0,  --Last_Update_Login
             SYSDATE, --Creation_Date
             0, --Created_By
             'F-ASN-PO-17646-3', --SHIPMENT_NUM
             7927,   --Vendor_Id
             204, -- SHIP_TO_ORGANIZATION_ID
             SYSDATE, --SHIPPED_DATE
             SYSDATE+1, --Expected_Receipt_Date
             13706,    --Employee_Id
             'Y'     --Validation_Flag
             );

INSERT INTO RCV_TRANSACTIONS_INTERFACE
          (INTERFACE_TRANSACTION_ID,
           GROUP_ID,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           CREATION_DATE,
           CREATED_BY,
           LAST_UPDATE_LOGIN,
           TRANSACTION_TYPE,
           TRANSACTION_DATE,
           PROCESSING_STATUS_CODE,
           PROCESSING_MODE_CODE,
           TRANSACTION_STATUS_CODE,
           QUANTITY,
           UNIT_OF_MEASURE,
           ITEM_ID,
           AUTO_TRANSACT_CODE,
           RECEIPT_SOURCE_CODE,
           SOURCE_DOCUMENT_CODE,
           DOCUMENT_NUM,
           DOCUMENT_LINE_NUM,
           SHIP_TO_LOCATION_ID,
           TO_ORGANIZATION_ID,
           HEADER_INTERFACE_ID,
           VALIDATION_FLAG
          )
        SELECT
           rcv_transactions_interface_s.nextval, --Interface_Transaction_id
           rcv_interface_groups_s.currval,         --Group_id
           SYSDATE,              --Last_update_date
           0,                    --Last_updated_by
           SYSDATE,              --Creation_date
           0,                    --Created_by
           0,                    --Last_update_login
           'SHIP',               --TRANSACTION_TYPE_SHIP
           SYSDATE,              --TRANSACTION_DATE
           'PENDING',            --PROCESSING_STATUS_CODE
           'BATCH',              --PROCESSING_MODE_CODE
           'PENDING',            --TRANSACTION_STATUS_CODE
           3,                    --QUANTITY
           'Each',               --UNIT_OF_MEASURE
           26816,                --ITEM_ID
           'SHIP',               --AUTO_TRANSACT_CODE
           'VENDOR',             --RECEIPT_SOURCE_CODE
           'PO',                 --SOURCE_DOCUMENT_CODE
           '17646',              --DOCUMENT_NUM
           '1',                  --DOCUMENT_LINE_NUM
           204,                  --SHIP_TO_LOCATION_ID
           204,                  --TO_ORGANIZATION_ID
           rcv_headers_interface_s.currval, --Header_interface_id
           'Y'     --Validation_Flag
          FROM DUAL;
COMMIT;


Note: On R12 instance, RHI.ORG_ID or RTI.ORG_id need to be populated.
On 11.5.10 instance in RHI and in RTI ORG_ID columns do not exist

2) Run the following scripts to check data have been correctly inserted


SQL> Select * from RCV_HEADERS_INTERFACE
where SHIPMENT_NUM like 'F-ASN-PO-17646-3%;

HEADER_INTERFACE_ID     488353
GROUP_ID        63266
PROCESSING_STATUS_CODE  PENDING
RECEIPT_SOURCE_CODE     VENDOR
ASN_TYPE        ASN
TRANSACTION_TYPE        NEW
AUTO_TRANSACT_CODE      SHIP
LAST_UPDATE_DATE        11/01/2008 12:14:52
LAST_UPDATED_BY 0
LAST_UPDATE_LOGIN       0
CREATION_DATE   11/01/2008 12:14:52
CREATED_BY      0
SHIPMENT_NUM    F-ASN-PO-17646-3
RECEIPT_NUM   
RECEIPT_HEADER_ID     
FROM_ORGANIZATION_ID  
SHIP_TO_ORGANIZATION_CODE     
SHIP_TO_ORGANIZATION_ID 204
LOCATION_CODE 
SHIPPED_DATE    11/01/2008 12:14:52
FREIGHT_CARRIER_CODE  
EXPECTED_RECEIPT_DATE   11/01/2008 12:14:52
EMPLOYEE_ID     13706
VALIDATION_FLAG Y
PROCESSING_REQUEST_ID 


SQL> Select * from RCV_TRANSACTIONS_INTERFACE where HEADER_INTERFACE_ID=488353;

INTERFACE_TRANSACTION_ID        2159384
GROUP_ID        63266
TRANSACTION_TYPE        SHIP
TRANSACTION_DATE        11/01/2008 12:15:12
PROCESSING_STATUS_CODE  PENDING
PROCESSING_MODE_CODE    BATCH
PROCESSING_REQUEST_ID 
TRANSACTION_STATUS_CODE PENDING
QUANTITY        3
UNIT_OF_MEASURE Each
ITEM_ID 26816
AUTO_TRANSACT_CODE      SHIP
SHIPMENT_HEADER_ID    
SHIPMENT_LINE_ID      
SHIP_TO_LOCATION_ID     204
RECEIPT_SOURCE_CODE     VENDOR
TO_ORGANIZATION_ID      204
SOURCE_DOCUMENT_CODE    PO
HEADER_INTERFACE_ID     488353
DOCUMENT_NUM    17646
DOCUMENT_LINE_NUM       1
VALIDATION_FLAG Y


3) In Purchasing Responsibility, Change to receiving organization V1 and run the Receiving Transaction Processor for the given group_id used in RHI and RTI:
Run RVCTP  for GROUP_ID=63266
-> Request 6913750 RVCTP module: Receiving Transaction Processor


4) Navigate to Manage Shipments form

    Org V1
    Receiving/Manage Shipments
    In Manage Shipments form   
    Find In-Transit Shipments
    Search Criteria
             Source Type=Supplier
             Shipment = F-ASN-PO-17646-3
    One shipment line is retrieved for Qty Shipped =3 for PO Number 17646



5)  Check how the following application tables have been populated/updated


SQL> Select * from RCV_HEADERS_INTERFACE
where SHIPMENT_NUM like 'F-ASN-PO-17646-3%;

HEADER_INTERFACE_ID     488353
GROUP_ID        63266
RECEIPT_HEADER_ID 1920749
PROCESSING_STATUS_CODE  SUCCESS
PROCESSING_REQUEST_ID   6913750


SQL> Select * from RCV_SHIPMENT_HEADERS
where SHIPMENT_NUM like 'F-ASN-PO-17646-3%;

1 record has been created
SHIPMENT_HEADER_ID      1920749
RECEIPT_SOURCE_CODE     VENDOR
VENDOR_ID       7927
VENDOR_SITE_ID  4724
SHIPMENT_NUM    F-ASN-PO-17646-3
RECEIPT_NUM   
SHIPPED_DATE    11/01/2008 12:14:52
EXPECTED_RECEIPT_DATE   12/01/2008 12:14:52
EMPLOYEE_ID     13706
REQUEST_ID      6913750
ASN_TYPE        ASN
ASN_STATUS      NEW_SHIP
SHIP_TO_ORG_ID  204


SQL> Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_header_id;

->One record with
SHIPMENT_LINE_ID        1874712
SHIPMENT_HEADER_ID      1920749
LINE_NUM        1
CATEGORY_ID     1
QUANTITY_SHIPPED        3
QUANTITY_RECEIVED       0
UNIT_OF_MEASURE Each
ITEM_DESCRIPTION        Standard Purchased Item
ITEM_ID 26816
SHIPMENT_LINE_STATUS_CODE       EXPECTED
SOURCE_DOCUMENT_CODE    PO
PO_HEADER_ID    97981
PO_LINE_ID      105281
PO_LINE_LOCATION_ID     139830
PO_DISTRIBUTION_ID    
DESTINATION_TYPE_CODE   RECEIVING
TO_ORGANIZATION_ID      204
REQUEST_ID      6913750
PRIMARY_UNIT_OF_MEASURE Each
ASN_LINE_FLAG   Y
ORIGINAL_ASN_PARENT_LINE_ID   
ORIGINAL_ASN_LINE_FLAG
SHIP_TO_LOCATION_ID     204


B)   Via ROI Create a Receive / Deliver transaction  for a quantity =1  on ASN already created

Perform the deliver transaction for the ASN for a quantity = 1
Follow instructions of  Document 461928.1   For Receiving an ASN which is already  created and Document 368811.1

1)  Insert the following data
. 1 record in RCV_HEADERS_INTERFACE
    RECEIPT_SOURCE_CODE = VENDOR
    ASN_TYPE  = null (or STD)
    TRANSACTION_TYPE=   NEW
    AUTO_TRANSACT_CODE =  DELIVER
    SHIPMENT_NUM  = RCV_SHIPMENT_HEADERS.SHIPMENT_NUM
    RECEIPT_HEADER_ID = RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID
    VENDOR_ID= RCV_SHIPMENT_HEADERS.VENDOR_ID

. 1 record in RCV_TRANSACTIONS_INTERFACE
   TRANSACTION_TYPE=   RECEIVE
   AUTO_TRANSACT_CODE =  DELIVER
   RECEIPT_SOURCE_CODE = VENDOR
   VENDOR_ID= RCV_SHIPMENT_HEADERS.VENDOR_ID
   QUANTITY  = 1
   SHIPMENT_HEADER_ID = RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID
   SHIPMENT_LINE_ID = RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID
   PO_HEADER_ID=PO_HEADERS_ALL.PO_HEADER_ID


INSERT INTO RCV_HEADERS_INTERFACE
            (HEADER_INTERFACE_ID,
             GROUP_ID,
             PROCESSING_STATUS_CODE,
             RECEIPT_SOURCE_CODE,
             ASN_TYPE,
             TRANSACTION_TYPE,
             AUTO_TRANSACT_CODE,
             LAST_UPDATE_DATE,
             LAST_UPDATED_BY,
             LAST_UPDATE_LOGIN,
             CREATION_DATE,
             CREATED_BY,
             SHIPMENT_NUM,
             RECEIPT_HEADER_ID,
             VENDOR_ID,
             SHIP_TO_ORGANIZATION_ID,
             EXPECTED_RECEIPT_DATE,
             EMPLOYEE_ID,
             VALIDATION_FLAG
              )
            VALUES
             (rcv_headers_interface_s.nextval , --Header_Interface_Id
             rcv_interface_groups_s.nextval,   --Group_Id
             'PENDING', --Processing_Status_Code
             'VENDOR', --Receipt_Source_Code
             null, --ASN_TYPE
             'NEW', --Transaction_Type
             'DELIVER',  --Auto_Transact_Code
             SYSDATE, --Last_Update_Date
             0,  --Last_Updated_By
             0,  --Last_Update_Login
             SYSDATE, --Creation_Date
             0, --Created_By
             'F-ASN-PO-17646-3', --SHIPMENT_NUM
             1920749,  --RECEIPT_HEADER_ID
             7927,     --VENDOR_ID
             204, -- SHIP_TO_ORGANIZATION_ID
             SYSDATE+1, --Expected_Receipt_Date
             13706,    --Employee_Id,
             'Y'     --Validation_Flag
             );

INSERT INTO RCV_TRANSACTIONS_INTERFACE
          (INTERFACE_TRANSACTION_ID,
           GROUP_ID,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           CREATION_DATE,
           CREATED_BY,
           LAST_UPDATE_LOGIN,
           TRANSACTION_TYPE,
           TRANSACTION_DATE,
           PROCESSING_STATUS_CODE,
           PROCESSING_MODE_CODE,
           TRANSACTION_STATUS_CODE,
           QUANTITY,
           UNIT_OF_MEASURE,
           ITEM_ID,
           AUTO_TRANSACT_CODE,
           SHIPMENT_HEADER_ID,
           SHIPMENT_LINE_ID,
           SHIP_TO_LOCATION_ID,
           RECEIPT_SOURCE_CODE,
           VENDOR_ID,
           TO_ORGANIZATION_ID,
           PO_HEADER_ID,
           DESTINATION_TYPE_CODE,
           DELIVER_TO_PERSON_ID,
           LOCATION_ID,
           DELIVER_TO_LOCATION_ID,
           SUBINVENTORY,
           HEADER_INTERFACE_ID,
           VALIDATION_FLAG
          )
        SELECT
           rcv_transactions_interface_s.nextval, --Interface_Transaction_id
           rcv_interface_groups_s.currval,         --Group_id
           SYSDATE,              --Last_update_date
           0,                    --Last_updated_by
           SYSDATE,              --Creation_date
           0,                    --Created_by
           0,                    --Last_update_login
           'RECEIVE',               --TRANSACTION_TYPE_RECEIVE
           SYSDATE,              --TRANSACTION_DATE
           'PENDING',            --PROCESSING_STATUS_CODE
           'BATCH',              --PROCESSING_MODE_CODE
           'PENDING',            --TRANSACTION_STATUS_CODE
           1,                    --QUANTITY
           'Each',               --UNIT_OF_MEASURE
           26816,                --ITEM_ID
           'DELIVER',               --AUTO_TRANSACT_CODE
           1920749,                 --SHIPMENT_HEADER_ID
           1874712,                  --SHIPMENT_LINE_ID
           204,                  --SHIP_TO_LOCATION_ID
           'VENDOR',             --RECEIPT_SOURCE_CODE
           7927,                 --VENDOR_ID
           204,                  --TO_ORGANIZATION_ID
           97981,           --PO_HEADER_ID
            'INVENTORY',          --DESTINATION_TYPE_CODE
           13706,                 --DELIVER_TO_PERSON_ID
           204,                  --LOCATION_ID
           204,                  --DELIVER_TO_LOCATION_ID
           'Stores',             --SUBINVENTORY
           rcv_headers_interface_s.currval, --Header_interface_id
           'Y'                   --VALIDATION_FLAG
          FROM DUAL;
COMMIT;


Note: On R12 instance, RHI.ORG_ID or RTI.ORG_ID need to be populated.
On 11.5.10 instance in RHI and in RTI ORG_ID columns do not exist

2) Run the following scripts to check data have been correctly inserted


SQL> Select * from RCV_HEADERS_INTERFACE
where SHIPMENT_NUM like 'F-ASN-PO-17646-3%;

There is a new record with
HEADER_INTERFACE_ID     491353
GROUP_ID        63406 
PROCESSING_STATUS_CODE  PENDING
RECEIPT_SOURCE_CODE     VENDOR
ASN_TYPE                null
TRANSACTION_TYPE        NEW
AUTO_TRANSACT_CODE      DELIVER
SHIPMENT_NUM            F-ASN-PO-17646-3
RECEIPT_HEADER_ID       1920749
VENDOR_ID               7927
SHIP_TO_ORGANIZATION_ID 204   
EXPECTED_RECEIPT_DATE   15/01/2008 15:58:50
EMPLOYEE_ID     13706
VALIDATION_FLAG Y
PROCESSING_REQUEST_ID 


SQL> Select * from RCV_TRANSACTIONS_INTERFACE
where HEADER_INTERFACE_ID = 491353;

INTERFACE_TRANSACTION_ID        2167384
GROUP_ID        63406
LAST_UPDATE_DATE        14/01/2008 15:59
LAST_UPDATED_BY 0
CREATION_DATE   14/01/2008 15:59
CREATED_BY      0
LAST_UPDATE_LOGIN       0
REQUEST_ID    
TRANSACTION_TYPE        RECEIVE
TRANSACTION_DATE        14/01/2008 15:59
PROCESSING_STATUS_CODE  PENDING
PROCESSING_MODE_CODE    BATCH
PROCESSING_REQUEST_ID 
TRANSACTION_STATUS_CODE PENDING
QUANTITY        1
UNIT_OF_MEASURE Each
ITEM_ID 26816
AUTO_TRANSACT_CODE      DELIVER
SHIPMENT_HEADER_ID      1920749
SHIPMENT_LINE_ID        1874712
SHIP_TO_LOCATION_ID     204
RECEIPT_SOURCE_CODE     VENDOR
VENDOR_ID       7927
TO_ORGANIZATION_ID      204
PO_HEADER_ID    97981
DESTINATION_TYPE_CODE   INVENTORY
DELIVER_TO_PERSON_ID    13706
LOCATION_ID     204
DELIVER_TO_LOCATION_ID  204
SUBINVENTORY    Stores
HEADER_INTERFACE_ID     491353
VALIDATION_FLAG Y


3) In Purchasing Responsibility, Change to receiving organization and run the Receiving Transaction Processor for the given group_id used in RHI and RTI
Run RVCTP  for GROUP_ID=63406
Request 6924706 RVCTP module: Receiving Transaction Processor


4) Navigate to Receiving Transactions Summary form

     Receiving / Receiving Transactions Summary RCVRCVRC Form
    Org V1
    When searching for Receipts for Shipment F-ASN-PO-17646-3
    or Purchase Order 17646
    Receipt Number 9033 appears and 2 transactions can be seen
    receipt_num in rcv_shipment_headers is now = 9033

5)  Check how the following application tables have been populated / updated


SQL> Select * from RCV_HEADERS_INTERFACE
where SHIPMENT_NUM like 'F-ASN-PO-17646-3%;

2 records have SUCCESS status


SQL> Select * from RCV_SHIPMENT_HEADERS
where SHIPMENT_NUM like 'F-ASN-PO-17646-3%;

SHIPMENT_HEADER_ID      1920749
RECEIPT_SOURCE_CODE     VENDOR
VENDOR_ID       7927
VENDOR_SITE_ID  4724
SHIPMENT_NUM    F-ASN-PO-17646-3
RECEIPT_NUM     9033
SHIPPED_DATE    11/01/2008 12:14
EXPECTED_RECEIPT_DATE   15/01/2008 15:58
EMPLOYEE_ID     13706
REQUEST_ID      6913750
ASN_TYPE        ASN
SHIP_TO_ORG_ID  204


SQL> Select * from RCV_SHIPMENT_LINES
where SHIPMENT_HEADER_ID=&Shipment_header_id;

SHIPMENT_LINE_ID        1874712
SHIPMENT_HEADER_ID      1920749
LINE_NUM        1
QUANTITY_SHIPPED        3
QUANTITY_RECEIVED       1
UNIT_OF_MEASURE Each
ITEM_DESCRIPTION        Standard Purchased Item
ITEM_ID 26816
SHIPMENT_LINE_STATUS_CODE       PARTIALLY RECEIVED
SOURCE_DOCUMENT_CODE    PO
PO_HEADER_ID    97981
PO_LINE_ID      105281
PO_LINE_LOCATION_ID     139830
TO_ORGANIZATION_ID      204
REQUEST_ID      6924706
ASN_LINE_FLAG   Y


SQL> Select * from RCV_TRANSACTIONS where SHIPMENT_HEADER_ID=&Shipment_header_id;



2 receive and deliver transactions have been created

For TRANSACTION_TYPE = RECEIVE
    TRANSACTION_ID    2016379
    REQUEST_ID    6924706
    SHIPMENT_HEADER_ID    1920749
    SHIPMENT_LINE_ID    1874712
    DESTINATION_TYPE_CODE    RECEIVING
    PARENT_TRANSACTION_ID    -1
    PO_HEADER_ID    97981
    QUANTITY    1
    INTERFACE_TRANSACTION_ID    2167384

For TRANSACTION_TYPE = DELIVER
    TRANSACTION_ID    2016380
    REQUEST_ID    6924706
    SHIPMENT_HEADER_ID    1920749
    SHIPMENT_LINE_ID    1874712
    DESTINATION_TYPE_CODE    INVENTORY
    PARENT_TRANSACTION_ID    2016379
    PO_HEADER_ID    97981
    QUANTITY    1
    INTERFACE_TRANSACTION_ID    2167384



Friday, 23 November 2012

Use of WORKFLOW BACKGROUND PROCESS IN ORACLE APPS



Workflow Background Process is a concurrent program which is run for processing deferred activities, timed out activities, and stuck processes using the parameters specified. The background engine executes all activities that satisfy the given arguments at the time that the background engine is invoked. This procedure does not remain running long term, so you must restart this procedure periodically. Any activities that are newly deferred or timed out or processes that become stuck after the current background engine starts are processed by the next background engine that is invoked.

Workflow Background Process is run with the help of Workflow Background Engine which is PL/SQL Procedure which runs this concurrent program with specified parameters.


Workflow Background Process Parameters:

Item Type – Specify an item type to restrict this engine to activities associated with that item type. If you do not specify an item type, the engine processes any activity regardless of its item type.
Minimum Threshold – Specify the minimum cost that an activity must have for this background engine to execute it, in hundredths of a second.
Maximum Threshold – Specify the maximum cost that an activity can have for this background engine to execute it, in hundredths of a second. By using Minimum Threshold and Maximum Threshold you can create multiple background engines to handle very specific types of activities. The default values for these arguments are null so that the background engine runs activities regardless of cost.
Process Deferred – Specify whether this background engine checks for deferred activities. Setting this parameter to Yes allows the engine to check for deferred activities.
Process Timeout – Specify whether this background engine checks for activities that have timed out. Setting this parameter to Yes allows the engine to check for timed out activities.
Process Struck -
Specify whether this background engine checks for struck process.




Monday, 5 November 2012


Financials and India tax accounting entries with general ledger identification querys

(Notes taken from metalink Doc Id--- How to Reconcile Financials for India Tax Accounting Entries with General Ledger [ID 790203.1]
      1.Query to identify receipts for which multiple entries are generated with Transaction Type = 'RECEIVE'
     2.Query to identify total tax amount generated at the time of Receiving
      3.Query to identify total tax amount generated at the time of delivery
      4.Query to identify total modvat amount generated for CENVAT entries
      5.Query to identify multiple entries populated into mtl_transaction_accounts (Standard Costing Organization) when material is delivered
      6.Return to Receiving Entries: Query to identify multiple entries generated for Return to Receiving entries
      7.Query to identify total tax amount generated at the time of Return to Receipt
      8.Return to Vendor Entries: Query to identify multiple entries generated for Return to vendor entries
      9.Query to identify total tax amount generated at the time of Return to Vendor
      10.Query to find the Modvat entries which hit PLA for RTV when the PLA pref =1
      11.Query to identify multiple entries generated at time of Receipt for AP Accrual account
      12.Query to identify total tax amount genearted for AP Accrual account at the time of Receipt
      13.Query to identify total tax amount generated for AP Accrual at the time of Return to Vendor
      14.Sum of Credit not equal to Sum of Debit for Return To Vendor transactions
      15.Query to give total tax amount for AP Accrual where the tax entries are not generated
      16.Query to identify cases of discrepancy in invoice distribution and the corresponding tax amount in Receipt / PO
      17.Query to give total sum of the taxes based on transaction type from JAI_RCV_JOURNAL_ENTRIES (IL Subledger)
      18.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise
      19.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise
      20.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise
      21.Query to Find the Sum of Tax Amounts from JAI_RCV_JOURNAL_ENTRIES WHERE TRANSACTION_TYPE='RETURN TO VENDOR'
      22.Query to identify Receipts for which accounting entries are not generated
      23.Query to identify receipts where tax entries have been updated in IL subledger but missing in General Ledger
      24.Query to identify Receipt where accounting entries not generated for Deliver transactions
      25.Query to find Receipts for which RTR entries are not generated
      26.Query to Find (RTV) Returns for which accounting is not generated
      27.Query to identify Receipts not yet claimed
      Step 3


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







This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.





Applies to: 
Oracle Financials for India - Version: 12.0.0 to 12.0.6 - Release: 12 to 12
Information in this document applies to any platform.
 Oracle Application - India Localization - Version: 12.0 to 12.0.6

Purpose
  This Note will be helpful to Reconcile Financials for India Tax Accounting Entries with General Ledger


Questions and Answers
 Reconciliation of the General Ledger with the subledger data provided by India Localization shall be done as per the following process:


Step 1

Import all the records pending in interface table and ensure that there are no pending transactions in the interface. Compare the
GL account balances with the India Localization sub-ledger balances to verify whether the balances are tallying. The following are the India
Localization reports that could be used for this purpose:

1. India Receiving account distrubution report.
2. India Receiving accounts report.
3. Accrual re-build reconcillation report.
4. India cenvat monthly return inputs and capital goods.
5. India cenvat monthly abstract report.


Step 2

If any differences are observed in these balances, following queries should be run to find out the exceptions.
The list of queries are given below:


Tax accounting entries generated at the time of Receipt:

1.Query to identify receipts for which multiple entries are generated with Transaction Type = 'RECEIVE' 



select receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr, count(*) cnt,
sum(nvl(entered_cr,0)) - avg(nvl(entered_cr,0)) "Excess Credit",
sum(nvl(entered_dr,0))-avg(nvl(entered_dr,0)) "Excess Debit" ,
gcc.concatenated_segments
from JAI_RCV_JOURNAL_ENTRIES a,gl_code_combinations_kfv gcc
where 0 < (
select count(*)
from JAI_RCV_JOURNAL_ENTRIES b
where a.transaction_id=b.transaction_id
and a.code_combination_id=b.code_combination_id
and a.acct_nature=b.acct_nature
and a.acct_type=b.acct_type
and nvl(a.entered_cr,0) = nvl(b.entered_cr,0)
and nvl(a.entered_dr,0) = nvl(b.entered_dr,0)
and a.rowid <> b.rowid
)
and a.period_name='&period_name'
and a.organization_code='&organization_code'
and a.transaction_type ='RECEIVE'
and a.code_combination_id=&CCID and gcc.code_combination_id = a.code_combination_id
group by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr,gcc.concatenated_segments
order by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr





2.Query to identify total tax amount generated at the time of Receiving


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RECEIVE'
AND TRUNC(E.CREATION_DATE)
BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'



3.Query to identify total tax amount generated at the time of delivery

SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='DELIVER'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'



4.Query to identify total modvat amount generated for CENVAT entries


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.ACCT_NATURE ='MODVAT'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'



5.Query to identify multiple entries populated into mtl_transaction_accounts (Standard Costing Organization) when material is delivered


Select
b.transaction_id,b.reference_account,b.base_transaction_value,count(*),
nvl(sum(base_transaction_value),0) - nvl(avg(base_transaction_value),0) "Excess Amount Passed"
from mtl_transaction_accounts b, org_acct_periods c, mtl_material_transactions a
where 0 < (select count(1)
from mtl_transaction_accounts d
where b.transaction_id = d.transaction_id
and b.reference_account = d.reference_account
and b.organization_id = d.organization_id
and b.request_id is null
)
and a.transaction_id = b.transaction_id
and a.organization_id = b.organization_id
and b.reference_account = &code_id
and b.request_id is null
and a.acct_period_id = c.acct_period_id
and a.organization_id = c.organization_id
and c.period_name = '&PERIOD_NAME'
and c.organization_id = &org_id
group by b.transaction_id,b.reference_account,b.base_transaction_value
having nvl(sum(base_transaction_value),0) - nvl(avg(base_transaction_value),0) <> 0
order by b.transaction_id




6.Return to Receiving Entries: Query to identify multiple entries generated for Return to Receiving entries


select receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr, count(*) cnt,
sum(nvl(entered_cr,0)) - avg(nvl(entered_cr,0)) "Excess Credit",
sum(nvl(entered_dr,0))-avg(nvl(entered_dr,0)) "Excess Debit" ,
gcc.concatenated_segments
from JAI_RCV_JOURNAL_ENTRIES a,gl_code_combinations_kfv gcc
where 0 < (
select count(*)
from JAI_RCV_JOURNAL_ENTRIES b
where a.transaction_id=b.transaction_id
and a.code_combination_id=b.code_combination_id
and a.acct_nature=b.acct_nature
and a.acct_type=b.acct_type
and nvl(a.entered_cr,0) = nvl(b.entered_cr,0)
and nvl(a.entered_dr,0) = nvl(b.entered_dr,0)
and a.rowid <> b.rowid
)
and a.organization_code='&organization_code'
and gcc.code_combination_id = a.code_combination_id
and a.TRANSACTION_TYPE ='RETURN TO RECEIVING'
and trunc(a.creation_date) between '&from_date' and '&to_date'
group by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr,gcc.concatenated_segments
order by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr




7.Query to identify total tax amount generated at the time of Return to Receipt


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RETURN TO RECEIVING'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'




8.Return to Vendor Entries: Query to identify multiple entries generated for Return to vendor entries


select receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr, count(*) cnt,
sum(nvl(entered_cr,0)) - avg(nvl(entered_cr,0)) "Excess Credit",
sum(nvl(entered_dr,0))-avg(nvl(entered_dr,0)) "Excess Debit" ,
gcc.concatenated_segments
from JAI_RCV_JOURNAL_ENTRIES a,gl_code_combinations_kfv gcc
where 0 < (
select count(*)
from JAI_RCV_JOURNAL_ENTRIES b
where a.transaction_id=b.transaction_id
and a.code_combination_id=b.code_combination_id
and a.acct_nature=b.acct_nature
and a.acct_type=b.acct_type
and nvl(a.entered_cr,0) = nvl(b.entered_cr,0)
and nvl(a.entered_dr,0) = nvl(b.entered_dr,0)
and a.rowid <> b.rowid
)
and a.period_name='&period_name'
and a.organization_code='&organization_code'
and gcc.code_combination_id = a.code_combination_id
and a.transaction_type ='RETURN TO VENDOR'
and a.code_combination_id=&CCID
group by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr,gcc.concatenated_segments
order by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr



9.Query to identify total tax amount generated at the time of Return to Vendor


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RETURN TO VENDOR'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'



10.Query to find the Modvat entries which hit PLA for RTV when the PLA pref =1


select receive.organization_id "Organization",
recLine.inventory_item_id "Item Id",
itemMaster.item_class "Item Class",
receive.transaction_id "Receive Transaction id",
rtv.transaction_id "Return Transaction id",
receive.quantity "Receive Qty",
rtv.quantity "Returned Quantity",
pla.dr_basic_ed "PLA amount"
from
rcv_transactions receive,
rcv_transactions rtv,
JAI_RCV_LINES recLine,
JAI_INV_ITM_SETUPS itemMaster,
JAI_CMN_RG_PLA_TRXS pla
where
receive.transaction_type = 'RECEIVE'
and rtv.transaction_type = 'RETURN TO VENDOR'
and receive.shipment_line_id = recLine.shipment_line_id
and rtv.shipment_line_id = recLine.shipment_line_id
and receive.shipment_line_id = rtv.shipment_line_id
and receive.organization_id = rtv.organization_id
and rtv.organization_id = &organization_id
and recLine.inventory_item_id = itemMaster.inventory_item_id
and recLine.organization_id = itemMaster.organization_id
and rtv.transaction_date between '&date_start' and '&date_end'
and pla.ref_document_id = rtv.transaction_id
and pla.transaction_id = 19
order by 1, 2




11.Query to identify multiple entries generated at time of Receipt for AP Accrual account


select receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr, count(*) cnt,
sum(nvl(entered_cr,0)) - avg(nvl(entered_cr,0)) "Excess Credit",
sum(nvl(entered_dr,0))-avg(nvl(entered_dr,0)) "Excess Debit" ,
gcc.concatenated_segments
from JAI_RCV_JOURNAL_ENTRIES a,gl_code_combinations_kfv gcc
where 0 < (
select count(*)
from JAI_RCV_JOURNAL_ENTRIES b
where a.transaction_id=b.transaction_id
and a.code_combination_id=b.code_combination_id
and a.acct_nature=b.acct_nature
and a.acct_type=b.acct_type
and nvl(a.entered_cr,0) = nvl(b.entered_cr,0)
and nvl(a.entered_dr,0) = nvl(b.entered_dr,0)
and a.rowid <> b.rowid
)
and a.period_name='&period_name'
and a.organization_code='&organization_code'
and gcc.code_combination_id = a.code_combination_id
and a.TRANSACTION_TYPE ='RECEIVE'
and a.code_combination_id = &CCID and trunc(a.creation_date) between '&p_date_from' and '&p_date_to'
group by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr,gcc.concatenated_segments
order by receipt_num,transaction_id,shipment_line_id,acct_type,acct_nature,
a.code_combination_id,entered_dr,entered_cr





12.Query to identify total tax amount genearted for AP Accrual account at the time of Receipt


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RECEIVE'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'
AND E.CODE_COMBINATION_ID =&CCID




13.Query to identify total tax amount generated for AP Accrual at the time of Return to Vendor


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RETURN TO VENDOR'
AND TRUNC(E.CREATION_DATE) BETWEEN '&from_date' AND '&to_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'
AND E.CODE_COMBINATION_ID =&CCID



14.Sum of Credit not equal to Sum of Debit for Return To Vendor transactions


SELECT NVL(SUM(ENTERED_CR),0), NVL(SUM(ENTERED_DR),0),RECEIPT_NUM
FROM JAI_RCV_JOURNAL_ENTRIES
WHERE TRANSACTION_TYPE='RETURN TO VENDOR'
GROUP BY RECEIPT_NUM
HAVING NVL(SUM(ENTERED_CR),0) <> NVL(SUM(ENTERED_CR),0)
ORDER BY RECEIPT_NUM



15.Query to give total tax amount for AP Accrual where the tax entries are not generated


select rct.organization_id, jrl.receipt_num, jrtl.currency,
sum(jrtl.tax_amount),rcv.code_combination_id
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct,
RCV_RECEIVING_SUB_LEDGER rcv
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and acct_nature = 'Receiving'
)
and rct.transaction_id=rcv.rcv_transaction_id
and NVL(rcv.entered_cr,0) > 0
and rcv.code_combination_id=&CCID
group by rct.organization_id, jrl.receipt_num, jrtl.currency, rcv.code_combination_id
order by rct.organization_id,jrl.receipt_num,jrtl.currency, rcv.code_combination_id




16.Query to identify cases of discrepancy in invoice distribution and the corresponding tax amount in Receipt / PO


select f.segment1 "PO Number", g.receipt_num "Receipt Number",
b.transaction_id "Receipt tran id", a.po_distribution_id "PO Dist.Id",
e.invoice_num "Invoice Number ", a.invoice_id "Invoice_id",
a.distribution_line_number "Distribution Line No", a.posted_flag "Posted",
c.tax_amount "Receipt Tax Amount", a.amount "Invoice Distribution Amt.", (c.tax_amount - a.amount ) "Differential Amt"
from ap_invoice_distributions_all a,
rcv_transactions b,
JAI_RCV_LINE_TAXES c,
JAI_AP_MATCH_INV_TAXES d,
ap_invoices_all e,
po_headers_all f,
rcv_shipment_headers g
where a.rcv_transaction_id = b.transaction_id
and b.shipment_line_id = c.shipment_line_id
and a.invoice_id = d.invoice_id
and a.distribution_line_number = d.distribution_line_number
and c.tax_id = d.tax_id
and a.invoice_id = e.invoice_id
and b.po_header_id = f.po_header_id
and b.shipment_header_id = g.shipment_header_id
and b.transaction_type= 'RECEIVE'
and e.source = 'ERS'
and abs(c.tax_amount - a.amount ) >= 1
and b.transaction_date between '&from_date' and '&to_date'




17.Query to give total sum of the taxes based on transaction type from JAI_RCV_JOURNAL_ENTRIES (IL Subledger)


SELECT
NVL(SUM(B.TAX_AMOUNT),0)
FROM
JAI_RCV_LINES A,
JAI_RCV_LINE_TAXES B,
MTL_SYSTEM_ITEMS C,
RCV_SHIPMENT_LINES D,
JAI_RCV_JOURNAL_ENTRIES E
WHERE
A.SHIPMENT_HEADER_ID = B.SHIPMENT_HEADER_ID
AND A.SHIPMENT_LINE_ID = B.SHIPMENT_LINE_ID
AND A.SHIPMENT_LINE_ID = D.SHIPMENT_LINE_ID
AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
AND C.INVENTORY_ITEM_ID = D.ITEM_ID
AND A.SHIPMENT_LINE_ID = E.SHIPMENT_LINE_ID
AND E.TRANSACTION_TYPE ='RECEIVE'
AND D.TO_ORGANIZATION_ID = C.ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) > '&parameter_date'
AND D.TO_ORGANIZATION_ID = &P_ORGANIZATION_ID
ORDER BY D.TO_ORGANIZATION_ID,TO_NUMBER(A.RECEIPT_NUM)




18.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise


SELECT
NVL(SUM(B.TAX_AMOUNT),0)
FROM
JAI_RCV_LINES A,
JAI_RCV_LINE_TAXES B,
MTL_SYSTEM_ITEMS C,
RCV_SHIPMENT_LINES D,
JAI_RCV_JOURNAL_ENTRIES E
WHERE
A.SHIPMENT_HEADER_ID = B.SHIPMENT_HEADER_ID
AND A.SHIPMENT_LINE_ID = B.SHIPMENT_LINE_ID
AND A.SHIPMENT_LINE_ID = D.SHIPMENT_LINE_ID
AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
AND C.INVENTORY_ITEM_ID = D.ITEM_ID
AND A.SHIPMENT_LINE_ID = E.SHIPMENT_LINE_ID
AND E.TRANSACTION_TYPE ='DELIVER'
AND D.TO_ORGANIZATION_ID = C.ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) > '&parameter_date'
AND D.TO_ORGANIZATION_ID = &P_ORGANIZATION_ID
ORDER BY D.TO_ORGANIZATION_ID,TO_NUMBER(A.RECEIPT_NUM)




19.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise


SELECT
NVL(SUM(B.TAX_AMOUNT),0)
FROM
JAI_RCV_LINES A,
JAI_RCV_LINE_TAXES B,
MTL_SYSTEM_ITEMS C,
RCV_SHIPMENT_LINES D,
JAI_RCV_JOURNAL_ENTRIES E
WHERE
A.SHIPMENT_HEADER_ID = B.SHIPMENT_HEADER_ID
AND A.SHIPMENT_LINE_ID = B.SHIPMENT_LINE_ID
AND A.SHIPMENT_LINE_ID = D.SHIPMENT_LINE_ID
AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
AND C.INVENTORY_ITEM_ID = D.ITEM_ID
AND A.SHIPMENT_LINE_ID = E.SHIPMENT_LINE_ID
AND E.TRANSACTION_TYPE ='RETURN TO RECEIVING'
AND D.TO_ORGANIZATION_ID = C.ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) > '&parameter_date'
AND D.TO_ORGANIZATION_ID = &P_ORGANIZATION_ID
ORDER BY D.TO_ORGANIZATION_ID,TO_NUMBER(A.RECEIPT_NUM)




20.Query to Find The Sum of Tax Amounts from JAI_RCV_LINE_TAXES table, Organization Wise


SELECT
NVL(SUM(B.TAX_AMOUNT),0)
FROM
JAI_RCV_LINES A,
JAI_RCV_LINE_TAXES B,
MTL_SYSTEM_ITEMS C,
RCV_SHIPMENT_LINES D,
JAI_RCV_JOURNAL_ENTRIES E
WHERE
A.SHIPMENT_HEADER_ID = B.SHIPMENT_HEADER_ID
AND A.SHIPMENT_LINE_ID = B.SHIPMENT_LINE_ID
AND A.SHIPMENT_LINE_ID = D.SHIPMENT_LINE_ID
AND A.SHIPMENT_HEADER_ID = D.SHIPMENT_HEADER_ID
AND C.INVENTORY_ITEM_ID = D.ITEM_ID
AND A.SHIPMENT_LINE_ID = E.SHIPMENT_LINE_ID
AND E.TRANSACTION_TYPE ='RETURN TO VENDOR'
AND D.TO_ORGANIZATION_ID = C.ORGANIZATION_ID
AND TRUNC(A.CREATION_DATE) > '&parameter_date'
AND D.TO_ORGANIZATION_ID = &P_ORGANIZATION_ID
ORDER BY D.TO_ORGANIZATION_ID,TO_NUMBER(A.RECEIPT_NUM)




21.Query to Find the Sum of Tax Amounts from JAI_RCV_JOURNAL_ENTRIES WHERE TRANSACTION_TYPE='RETURN TO VENDOR'


SELECT
NVL(SUM(ENTERED_CR),0) SUM_CREDIT,
NVL(SUM(ENTERED_DR),0) SUM_DEBIT
FROM
JAI_RCV_JOURNAL_ENTRIES E
WHERE
E.TRANSACTION_TYPE ='RETURN TO VENDOR'
AND TRUNC(E.CREATION_DATE) > '&parameter_date'
AND E.ORGANIZATION_CODE = '&P_ORGANIZATION_CODE'




22.Query to identify Receipts for which accounting entries are not generated


select substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35),sum(jrtl.tax_amount),rcv.code_combination_id
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct,
RCV_RECEIVING_SUB_LEDGER rcv,
gl_code_combinations_kfv glc
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and transaction_type = 'RECEIVE'
)
and jrtl.tax_amount != 0
and rct.transaction_id=rcv.rcv_transaction_id
and rcv.code_combination_id = glc.code_combination_id
and NVL(rcv.entered_cr,0) > 0
and JRL.CREATION_DATE BETWEEN '&P_DATE_FROM' AND '&P_DATE_TO'
and rct.organization_id=&p_organization_id
group by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35), rcv.code_combination_id
order by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
rcv.code_combination_id



23.Query to identify receipts where tax entries have been updated in IL subledger but missing in General Ledger


select substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35),sum(jrtl.tax_amount),rcv.code_combination_id
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct,
RCV_RECEIVING_SUB_LEDGER rcv,
gl_code_combinations_kfv glc
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and not exists (select '1'
from gl_je_lines
where reference_5 = to_char(jrtl.transaction_id)
and description like 'India%'
)
and jrtl.tax_amount != 0
and rct.transaction_id=rcv.rcv_transaction_id
and rcv.code_combination_id = glc.code_combination_id
--and NVL(rcv.entered_Dr,0) > 0
and JRL.CREATION_DATE BETWEEN '&P_DATE_FROM' AND '&P_DATE_TO'
and rct.organization_id=&p_organization_id
group by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35), rcv.code_combination_id
order by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
rcv.code_combination_id



24.Query to identify Receipt where accounting entries not generated for Deliver transactions


select rct.organization_id, jrl.receipt_num, jrtl.currency,
sum(jrtl.tax_amount)
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'DELIVER'
and nvl(jrtl.modvat_flag, 'N') = 'N'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and transaction_type = 'DELIVER'
)
and jrl.creation_date between '&from_date' and '&to_date'
group by rct.organization_id, jrl.receipt_num, jrtl.currency
order by rct.organization_id, jrl.receipt_num, jrtl.currency



25.Query to find Receipts for which RTR entries are not generated


select rct.organization_id, jrl.receipt_num, jrtl.currency,
sum(jrtl.tax_amount)
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RETURN TO RECEIVING'
and nvl(jrtl.modvat_flag, 'N') = 'N'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and transaction_type = 'RETURN TO RECEIVING'
)
and jrl.creation_date between '&from_date' and '&to_date'
group by rct.organization_id, jrl.receipt_num, jrtl.currency
order by rct.organization_id, jrl.receipt_num, jrtl.currency



26.Query to Find (RTV) Returns for which accounting is not generated


select substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35),sum(jrtl.tax_amount),rcv.code_combination_id
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
RCV_TRANSACTIONS rct,
RCV_RECEIVING_SUB_LEDGER rcv,
gl_code_combinations_kfv glc
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RETURN TO VENDOR'
and not exists (select '1'
from JAI_RCV_JOURNAL_ENTRIES
where shipment_line_id = jrtl.shipment_line_id
and transaction_type = 'RETURN TO VENDOR'
)
and jrtl.tax_amount != 0
and rct.transaction_id=rcv.rcv_transaction_id
and rcv.code_combination_id = glc.code_combination_id
and NVL(rcv.entered_cr,0) > 0
and JRL.CREATION_DATE BETWEEN '&P_DATE_FROM' AND '&P_DATE_TO'
and rct.organization_id=&p_organization_id
group by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
substr(CONCATENATED_SEGMENTS,1,35), rcv.code_combination_id
order by substr(rct.organization_id,1,3),substr(jrl.receipt_num,1,10),substr(jrtl.currency,1,5),
rcv.code_combination_id



27.Query to identify Receipts not yet claimed


select rct.organization_id, jrl.receipt_num, jrtl.currency,
sum(jrtl.tax_amount)
from JAI_RCV_LINES jrl,
JAI_RCV_LINE_TAXES jrtl,
rcv_transactions rct
where jrl.shipment_header_id = jrtl.shipment_header_id
and jrl.shipment_line_id = jrtl.shipment_line_id
and jrl.shipment_header_id = rct.shipment_header_id
and jrl.shipment_line_id = rct.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and nvl(jrtl.modvat_flag, 'N') = 'Y'
and exists (select '1'
from JAI_RCV_CENVAT_CLAIMS
where shipment_line_id = jrtl.shipment_line_id
and cenvat_claimed_ptg <> 100
)
and jrl.creation_date between '&from_date' and '&to_date'
group by rct.organization_id, jrl.receipt_num, jrtl.currency
order by rct.organization_id, jrl.receipt_num, jrtl.currency



Sunday, 4 November 2012

Quick Review on MRP setups....




Introduction

Setting up the Oracle Master  Scheduling/MRP and Oracle Supply Chain Planning modules is easy.  Determining how your business will leverage the features,
is the hard part. This document can be used as a guide during your implementation.


Before you set up Oracle Supply Chain Planning, you must complete the setup for the following: products:

Oracle Inventory
Oracle Purchasing
Oracle Bills of Material
Oracle Work in Process
Oracle Project Manufacturing


Planning Setup Checklist

Required steps for System Administration, General Ledger, Inventory, Purchasing, Bills of Material, and WIP modules need to be completed before
Planning is setup.

The following steps need to be completed in the order shown:

Step 1:

Define your Master Scheduling/MRP Setup Parameters (Required)
NAV/Supply Chain Planning/Setup/Parameters or
NAV/Material Planning/Setup/Parameters

You can override the execution defaults, when defining the MPS, MRP or DRP for your organization(s).  
To have all past due demand (sales orders, forecast, etc.) reflected in your plans, select Null for Include MDS Days.
Entering a value will limit the past due information visible in the Plan.

Step 2: 

Define your Deliver-to Locations (Optional)
NAV/Inventory/Setup/Organizations/Locations

Step 3: 

Define your Employees (Optional)
NAV/Work In Progress/Setup/Employees
Note: Enter Employees through Human Resources, if installed.  
A required step when implementing planning recommendations from the Planner Workbench (Step 21).

Step 4: 

Set Profile Options (Required with Defaults)
NAV/Profiles/System/Find MRP

Note: Use the Sysadmin Responsibility. The value for Snapshot Workers can be increased depending on the number of items being planned.  Use the
following formula to determine how many Standard Managers are required to support the process.  The value for the Planning Manager Max Workers should
not be more than the number of Standard Managers.

(Snapshot workers value *2) + 4 = # of Standard Managers
(2*2) + 4 = 8 Standard Managers

Recommended initial values:

MRP:Environment variable to set path for MRP files Null/Blank
MRP:Planning Manager Max Workers                        6
MRP:Retain Dates within Calendar Boundary               Yes
MRP:Snapshot Workers                                    2
MRP:Use Direct Load Option                              No


Step 5: 

Define your Forecast Sets (Optional)
NAV/Supply Chain Planning/Forecast/Sets
NAV/Material Planning/Forecast/Sets

Step 6:

Define your Master Demand Schedules (MDS)(Optional)
NAV/Supply Chain Planning/MDS/Names
NAV/Material Planning/MDS/Names

Step 7:

Define your Master Production Schedules (MPS) (Optional)
NAV/Supply Chain Planning/MPS/Names
NAV/Material Planning/MPS/Names

Step 8: 

Define your Material Requirements Plans (MRP);
Define your Distribution Requirements Plans (DRP) (Optional)
NAV/Supply Chain Planning/MRP/Names
NAV/Supply Chain Planning/DRP/Names
NAV/Material Planning/MRP/Names

Step 10:

Define your Sourcing Rules or Bills of Distribution (BOD)
NAV/Supply Chain Planning/Sourcing
Note: When you select All Orgs for your Sourcing Rule, it can be used by all organizations when defining an Assignment Set.  A Local
Sourcing Rule would only have the Org selected, and can only be used by the defining organization.   The Planning Active box will only be checked
if the allocation % = 100.  The planning process will not use the rule, unless the Planning Active box is checked

Step 11: 

Create your Assignment Sets
NAV/Supply Chain Planning /Sourcing/AssignSourcing Rules/BOD
Note:  A Local Sourcing Rule assigned at the Item-Organization level, overrides all others for that item.

Step 12: 

Define your Inter-organization Shipping Network
NAV/Inventory/Setup/Organizations/Shipping Network

Step 13:

Define Shipping Methods and associated intransit lead times
NAV/Inventory/Setup/Organization/Shipping Methods
NAV/Inventory/Setup/Organization/Shipping Network
  Special Menu:  Shipping Methods  Intransit Times

Step 14: 

Run the Information Audit (Optional-highly recommended)
NAV/Supply Chain Planning/Reports/Audit Information Report
NAV/Material Planning/Reports/Audit Information Report

Step 15: 

Define your Planning Parameters (Required)
NOTE: this is a duplicate of Step 1.

Step 16: 

Start the Planning Manager (Required)
NAV/Supply Chain Planning/Setup/Planning Manager or
NAV/Material Planning/Setup/Planning Manager
(See screen shot below; the active box should be checked and you should see current messages)

Step 17:

Define your Planners (Optional)
NAV/Inventory/Setup/Planners

Step 18: 

Define your Planning Exception Sets (Optional)
NAV/Supply Chain Planning/Setup/Exception Sets
Or NAV/ Material Planning/Setup/Exception Sets

Step 19:

Define your Demand Classes (Optional)
NAV/Supply Chain Planning/Setup/Demand Classes
OR NAV/Material Planning/Setup/Demand Classes
Note: Selecting a Demand Class when defining your MDS or MPS,restricts data seen in the schedules.  Only Sales Orders with the
demand class entered at the line level will be included in your MDS.
Only Discrete Jobs with the demand class added,(NAV/WIP/Discrete/Discrete Jobs/More) will be included in your MPS.

Step 20: 

Create Source Lists (Optional)
NAV/Supply Chain Planning/Forecast or MDS or MPS /Source List
NAV/Material Planning/Forecast or MDS or MPS/Source List


Step 21: 

Set Up Planner Workbench (Required)
For instructions, see: Setting Up Planner Workbench:
Oracle Master Scheduling/MRP and Supply Chain Planning User's Guide Release 11: page 1-28.

Step 22: 

Set Up for Supplier Planned Inventories (Optional) For instructions on setting up Supplier Planned Inventories, see the prerequisites in
Oracle Master Scheduling/MRP and Oracle Supply Chain Planning User's Guide Release 11, page 5-8.