Create ASN in 'EXPECTED' or 'FULLY_RECEIVED' Status.

Create ASN in 'EXPECTED' or 'FULLY_RECEIVED' Status.

We can create an ASN in Oracle apps in either 'EXPECTED' or 'FULLY RECEIVED' Status.

This can be achieved or bifurcated using 'AUTO_TRANSACT_CODE' Column.

A)- In case of ASN to be created in 'EXPECTED' Status then

Insert in RHI and RTI with below values.

AUTO_TRANSACT_CODE  = 'SHIP'


INTO rcv_headers_interface (header_interface_id,
                                                GROUP_ID,
                                                processing_status_code,
                                                receipt_source_code,
                                                transaction_type,
                                                last_update_date,
                                                last_updated_by,
                                                creation_date,
                                                created_by,
                                                shipped_date,
                                                vendor_id,
                                                employee_id,
                                                validation_flag,
                                                ship_to_organization_code,
                                                expected_receipt_date,
                                                shipment_num,
                                                asn_type,
                                                auto_transact_code)
                  VALUES (v_header_interface_id,
                          v_group_id,
                          'PENDING',
                          'VENDOR',
                          'NEW',
                          gd_date,
                          gn_user_id,
                          gd_date,
                          gn_user_id,
                          gd_date,
                          v_vendor_id,
                          v_agent_id,
                          'Y',
                          v_organization_code,
                          gd_date,
                          'REFERENCE_VALUE'--- user specified value
                          'ASN',
                          'SHIP');

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

INSERT
                       INTO rcv_transactions_interface (
                               interface_transaction_id,
                               header_interface_id,
                               GROUP_ID,
                               last_update_date,
                               last_updated_by,
                               creation_date,
                               created_by,
                               transaction_type,
                               transaction_date,
                               processing_status_code,
                               processing_mode_code,
                               transaction_status_code,
                               quantity,
                               unit_of_measure,
                               auto_transact_code,
                               receipt_source_code,
                               source_document_code,
                               po_header_id,
                               po_line_id,
                               po_line_location_id,
                               ship_to_location_id,
                               validation_flag,
                               to_organization_code,
                               item_num)
                     VALUES (v_interface_transaction_id,
                             v_header_interface_id,
                             v_group_id,
                             SYSDATE,
                             fnd_global.user_id,
                             SYSDATE,
                             fnd_global.user_id,
                             'SHIP',
                             SYSDATE,
                             'PENDING',
                             'BATCH',
                             'PENDING',
                             a_detail.shipped_quantity,
                             v_unit_of_measure,
                             'SHIP',
                             'VENDOR',
                             'PO',
                             a_detail.PO_HEADER_ID,
                             a_detail.PO_LINE_ID,
                             v_line_location_id,
                             v_ship_to_location_id,
                             'Y',
                             v_org_code,
                             v_item_num);
----------------------------------------------------------------------------------

                     INSERT
                       INTO mtl_transaction_lots_interface (
                               transaction_interface_id,
                               last_update_date,
                               last_updated_by,
                               creation_date,
                               created_by,
                               lot_number,
                               lot_expiration_date,
                               transaction_quantity,
                               primary_quantity,
                               product_code,
                               product_transaction_id)
                     VALUES (mtl_material_transactions_s.NEXTVAL,
                             SYSDATE,
                             fnd_global.user_id,
                             SYSDATE,
                             fnd_global.user_id,
                             v_lot_number,
                             v_exp_date,
                             a_detail.shipped_quantity,
                             v_primary_quantity,
                             'RCV',
                             v_interface_transaction_id);


----- *******************************************************************------

B)- In case of ASN to be created in 'FULLY_RECEIVED' Status then 

Insert in RHI and RTI with below values.

AUTO_TRANSACT_CODE  = 'RECEIVE'


INTO rcv_headers_interface (header_interface_id,
                                                GROUP_ID,
                                                processing_status_code,
                                                receipt_source_code,
                                                transaction_type,
                                                last_update_date,
                                                last_updated_by,
                                                creation_date,
                                                created_by,
                                                shipped_date,
                                                vendor_id,
                                                employee_id,
                                                validation_flag,
                                                ship_to_organization_code,
                                                expected_receipt_date,
                                                shipment_num,
                                                asn_type,
                                                auto_transact_code)
                  VALUES (v_header_interface_id,
                          v_group_id,
                          'PENDING',
                          'VENDOR',
                          'NEW',
                          gd_date,
                          gn_user_id,
                          gd_date,
                          gn_user_id,
                          gd_date,
                          v_vendor_id,
                          v_agent_id,
                          'Y',
                          v_organization_code,
                          gd_date,
                          'REFERENCE_VALUE'--- user specified value
                          'ASN',
                          'RECEIVE');

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

INSERT
                       INTO rcv_transactions_interface (
                               interface_transaction_id,
                               header_interface_id,
                               GROUP_ID,
                               last_update_date,
                               last_updated_by,
                               creation_date,
                               created_by,
                               transaction_type,
                               transaction_date,
                               processing_status_code,
                               processing_mode_code,
                               transaction_status_code,
                               quantity,
                               unit_of_measure,
                               auto_transact_code,
                               receipt_source_code,
                               source_document_code,
                               po_header_id,
                               po_line_id,
                               po_line_location_id,
                               ship_to_location_id,
                               validation_flag,
                               to_organization_code,
                               item_num)
                     VALUES (v_interface_transaction_id,
                             v_header_interface_id,
                             v_group_id,
                             SYSDATE,
                             fnd_global.user_id,
                             SYSDATE,
                             fnd_global.user_id,
                             'RECEIVE',
                             SYSDATE,
                             'PENDING',
                             'BATCH',
                             'PENDING',
                             a_detail.shipped_quantity,
                             v_unit_of_measure,
                             'RECEIVE',
                             'VENDOR',
                             'PO',
                             a_detail.PO_HEADER_ID,
                             a_detail.PO_LINE_ID,
                             v_line_location_id,
                             v_ship_to_location_id,
                             'Y',
                             v_org_code,
                             v_item_num);
----------------------------------------------------------------------------------

                     INSERT
                       INTO mtl_transaction_lots_interface (
                               transaction_interface_id,
                               last_update_date,
                               last_updated_by,
                               creation_date,
                               created_by,
                               lot_number,
                               lot_expiration_date,
                               transaction_quantity,
                               primary_quantity,
                               product_code,
                               product_transaction_id)
                     VALUES (mtl_material_transactions_s.NEXTVAL,
                             SYSDATE,
                             fnd_global.user_id,
                             SYSDATE,
                             fnd_global.user_id,
                             v_lot_number,
                             v_exp_date,
                             a_detail.shipped_quantity,
                             v_primary_quantity,
                             'RCV',
                             v_interface_transaction_id);



C)- Then Call receiving Transaction processor

  fnd_request.submit_request (application   => 'PO',
                                                 program       => 'RVCTP',
                                                 start_time    => SYSDATE,
                                                 description   => NULL,
                                                 sub_request   => FALSE,
                                                 argument1     => 'BATCH',
                                                 argument2     => v_group_id);

Add Sec/Min/Hour Functionality in oracle

Interval Function can be use to achieve the functionality to add or subtract Sec/Min/our intervals in Date.


SELECT SYSDATE,
       SYSDATE + (1 / (24 * 60 * 60)) "1Sec Addition",
       sysdate + interval '1' second "1SecAddition Intervalfunc",
       SYSDATE + (1 / (24 * 60)) "1Min Addition",
       sysdate + interval '1' minute "1MinAddition Intervalfunc",
       SYSDATE + (1 / 24) "1HR Addition",
       sysdate + interval '1' hour "1hourAddition Intervalfunc"
  FROM DUAL

Generating a Random Number by Using DBMS_RANDOM Function

Generating a Random Number by Using DBMS_RANDOM Function


select dbms_random.value , --- Positive Number between (0,1)
      dbms_random.value (n,m), --- Positive decimal Number between (n,m), where n,m are Numbers
      trunc(dbms_random.value (n,m)), --- returns only Integer value between (n,m)
      dbms_random.string ('A',n),  -- alpha characters only (mixed case both upper and lower)
      dbms_random.string ('p',n),  -- any printable char (ASCII subset) including Special characters
      dbms_random.string ('U',n), --upper case alpha characters only and with range of n
      dbms_random.string ('L',n), --Lower case alpha characters only and with range of n
      dbms_random.string ('X',n),  -- any alpha-numeric characters (upper case only)
      sysdate + trunc(dbms_random.value (1,n)) ------- Random dates 
from dual
connect by level <=m  --- m rows

SQL Loader Loading Options

Loading Options:  INSERT,  APPEND, REPLACE and TRUNCATE

Insert           :  Loads rows only if the target table is empty.

Append         :  Load rows if the target table is empty or not.

Replace         :  First deletes all the rows in the table and then load rows

Truncate        :  First truncates the table and then load rows.

3 C's in Oracle Fin. Setup

1st C--> Chart of Accounts (CoA)--.
Defined for Accounting Entries --> Represent by Code Combinations

CoA is a solution to record the financial transactions securely based on the segments.

It is a combination of different segments based on the company's discretion. Generally 6-8 segments are used to define a CoA Set up. 
 Maximum limits of segments can be reach up to 30 Segments.



2nd C--> Currency ---> 
No need to define currency , already all the currencies are defined in system, but we need have to just enable it through Currency Window.

We can enable Multicurrency Feature in case of Global Organizations, like if primary currency is INR and Customer is paying in USD, then we can enter the amount in USD while punching the invoice , but the final reporting currency will be INR only.


3rd C--> Calendar--> 
Its defined to setup the Fiscal and Current Year and the periods and Dates in FY and CYs.

We Generally Define Number of periods in a year as 13. (12+1 Adjustment Period)
Adjustment period is define when in case all the 12 periods are closed for a particular year and we are in new year and by any chance we have to record a transaction in the previous year , then we can't create a transaction in closed periods, hence we can use the Adjustment one.


IN  11i :

These 3C's Combined known as "Set of Books"  in terms of oracle application and "Books of Account" in terms of Actual Business.

IN R12:

1 More C was introduced , known as "Accounting Method " or "Sub-ledger Accounting Methods (SLA)"

These 4C's Combined known as "Ledger"  in terms of oracle application and "Books of Account" in terms of Actual Business.

Important Note:   If any of the C  changes , then we need to define a new Ledger.
i.e. In case of different geographic locations like countries, Currency changes hence separate Ledger has to be setup in system.

4th C (SLA) are of 3 types.

A)- Standard Accrual--- Mostly Used Method

B)- Standard Cash-- Conventional Method

C)-Standard Encumbrance-- Rarely Used Method


Organizational Data


Organization Structure --> 

Business Group (BG)
Business Group is used to centralize or secure employee data, can be based on Legal entity wise or Country wise.

Legal Entity (LE)

To Secure Company Legal and financial Data. GL Defined at LE level.

Balancing Segments (BS)

Balancing segments is assigned to LE , and 1 LE can have multiple BS attached , so it is one to one or many to One relationship between BS and LE.

Operating Unit (ORG ID)
To Secure Operational Data (like Purchasing, Sale, payments, Invoicing etc..)  OU needs to be Setup.
OU always assigned to LE, and can be One to One or Many to One for OU to LE.
AP,AR, PO, INV defined at OU Level.

Inventory orgs (Warehouse/Organization_id)
To Secure Inventory Data/Item data, use Inv Orgs.


Basic Setup Steps and responsibility those can be used to perform these setups.

XMLP Report Generated through PL SQL

Generate a XML Report from PL SQL Package.

CREATE OR REPLACE PACKAGE BODY XXORT_TEST_USER

AS

  PROCEDURE XXORT_USER (ERRBUF  VARCHAR2,

                        RETCODE  NUMBER,

                        XORDER_NUM IN VARCHAR2)

     IS

     CURSOR C_HEAD IS

        SELECT ORDER_NUMBER,

               HEADER_ID,

               FLOW_STATUS_CODE

         FROM OE_ORDER_HEADERS_ALL

         WHERE ORDER_NUMBER=XORDER_NUM;

        

     CURSOR C_LINES (P_HEADER_ID IN VARCHAR2)

     IS

        SELECT  ORDERED_ITEM, ORDERED_QUANTITY , LINE_ID

        FROM OE_ORDER_LINES_ALL

        WHERE HEADER_ID= P_HEADER_ID;

       

       

      LV_LOT_NUMBER VARCHAR2(100);

    

     BEGIN

    

       FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<?xml version="1.0" encoding="UTF-8"?>');

       FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<ORDER_INFO>');

         

            FOR RC_HEAD IN C_HEAD

             LOOP

            

              FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<P_ORDER_HEADER>');

              FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<ORDER_NUMBER>' || RC_HEAD.ORDER_NUMBER || '</ORDER_NUMBER>');

              FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<HEADER_ID>' || RC_HEAD.HEADER_ID || '</HEADER_ID>');

              FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<FLOW_STATUS_CODE>' || RC_HEAD.FLOW_STATUS_CODE || '</FLOW_STATUS_CODE>');

            

            

                  FOR RC_LINES IN C_LINES (RC_HEAD.HEADER_ID)

                         LOOP

                                 

                        

                         BEGIN

                        

                         SELECT LOT_NUMBER

                         INTO LV_LOT_NUMBER

                         FROM WSH_DELIVERY_DETAILS

                         WHERE SOURCE_LINE_ID = RC_LINES.LINE_ID;

                        

                         EXCEPTION

                         WHEN OTHERS

                         THEN NULL;

                        

                         END ;                

                          FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<P_ORDER_LINES>');

                          FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<ORDERED_ITEM>' || RC_LINES.ORDERED_ITEM || '</ORDERED_ITEM>');

                          FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<ORDERED_QUANTITY>' || RC_LINES.ORDERED_QUANTITY ||  '</ORDERED_QUANTITY>');

                          FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '<LOT_NUMBER>' || LV_LOT_NUMBER ||  '</LOT_NUMBER>');

                         

                          FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '</P_ORDER_LINES>');

                        

                         END LOOP;

                    FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '</P_ORDER_HEADER>');    

             END LOOP;

    

     FND_FILE.PUT_LINE(FND_FILE.OUTPUT , '</ORDER_INFO>');

     END ;

    

                       

                   

 

END XXORT_TEST_USER;