Error in Pack Slip Generation during Ship Confirmation

Pack Slip Generating Issues

During Ship confirmation, below error is coming and Pack Slip is not generating

Error

Error: There is an error generating/validating the sequence number. Please check the assignments are defined and are valid.

————————————

Warning: Pack Slip information could not be automatically created for delivery 115506201.

————————————

Error: There is an error generating/validating the sequence number. Please check the assignments are defined and are valid.

————————————

Warning: Bill of Lading information could not be automatically created for Delivery 115506201.

————————————

Submitted 1 out of 1 documents for this document set. (REQ_IDS=293613)

————————————

Warning: Failed to submit document set for delivery 115506201

————————————

Interface Trip Stop request 293614 has been submitted

————————————

Warning: 1 deliveries will be marked as In-Transit/Closed.

Issue:

This order will be able to ship confirm, if re-queried but Pack Slip will not be generated.

Solution:

As in this case, this is not able to properly generate the sequence number, it cannot insert record into WSH_DOCUMENT_INSTANCES table

In most business, Delivery Name been used as the pack slip number.

So for every pack slip document, data being inserted into the table with entity_id as the delivery name.

So as in this case, it is not able to generate the sequence number, this will not be inserted in the table and pack slip canot be generated.

So for this issue, the solution is to do the below profile option setup in following level

Profile Option: Sequential Numbering

Value: Always Used

Level: Application

Application Level values: Shipping Execution and Order Management

If you don’t want to use the same value across all sites, then you can do the setup at specific responsibility levels too.

Oracle Order Management: Drop Ship Tables and SQL Query

Below are the basic tables used during Drop Ship flow

apps.oe_order_headers_all 

apps.oe_order_lines_all

apps.oe_drop_ship_sources

apps.po_requisitions_interface_all

apps.po_interface_errors

apps.po_requisition_headers_all

apps.po_headers_all

apps.po_lines_all

apps.po_line_locations_all

apps.po_distibutions_all

apps.po_releases_all

Below is the SQL Statement to find out the Drop Ship SO and corresponding Requistiona and PO details along with their status

selectooh.order_number

       ,ool.ordered_item

       ,ool.ordered_quantity

       ,ooh.flow_status_code header_status                   

       ,ool.flow_status_code line_status

       ,prha.segment1 requisition

       ,poh.segment1 po_number

       ,poh.closed_code po_status

       ,pll.quantity

       ,pll.quantity_received

       ,pll.closed_code po_shipment_status

fromapps.oe_order_headers_all                   ooh

    ,apps.oe_order_lines_all                     ool

    ,apps.oe_drop_ship_sources                   odss

    ,apps.po_requisition_headers_all             prha

    ,apps.po_headers_all                         poh

    ,apps.po_lines_all                           pol

    ,apps.po_line_locations_all                  pll

              

whereool.header_id = ooh.header_id

and   odss.header_id = ooh.header_id

and   odss.line_id = ool.line_id

and   prha.requisition_header_id = odss.requisition_header_id

and   poh.po_header_id = odss.po_header_id

and   pol.po_line_id = odss.po_line_id

and   pol.po_header_id = poh.po_header_id

and   pll.po_line_id = pol.po_line_id

and   ooh.order_number = ‘66411’;

Oracle Order Management: Drop Ship Flow

Setting up the Item first

Master Level:

Inventory:

Inventory Item, Stockable, Transactable

Purchasing:

Purchased, Purchasable, List Price

Order Management:

Customer Ordered, Customer Order Enabled, Shippable, OE Transactable

Default SO Source Type should be External

Internal Ordered, Internal Order Enabled (These 2 if the drop ship will happen from internal organization)

Invoicing

Organization Level Setup:

Inventory

Costing

Receiving

Now create the Sales Order

Make sure source type is External

Now Book the order

Now the line is in Booked

Workflow is deferred for Purchase Release

Run the workflow Background

After the program completes, re query the SO

Now the time, you can see the record both in Drop Ship table and Po Req Interface table

select * from apps.oe_order_headers_all whereorder_number = ‘66411’;

select * from apps.OE_DROP_SHIP_SOURCES whereheader_id = 190500;

select * from apps.po_requisitions_interface_all where creation_date >= sysdate–1/24;

Now run the Requisition Import Program

If this program not able to create the requisition, then please check the error if any in below table

select * from apps. PO_INTERFACE_ERRORS where transaction_id =

Once the program, completes, lets check the Req details in Sales order line, additional Information , Drop Ship tab

PO Creation

Now take this Requisition and Auto Create PO from this using the Purchasing responsibility

Click Automatic

Select the supplier and Site and click Create

This will create the PO

Now check the details and Submit for Approval

Wait till you get the confirmation from supplier for the shipments either by ASN or any EDI communication.

Now you can see this PO details in SO Line, Drop Ship tab

PO Receiving

Once confirmed, then go ahead and do the receipt

Once received, check the transaction completion.

Once the receiving transaction completed, go and check the SO line status

Now run the workflow background program, this will close the line

This will interface the record to Receivable

Now run the Autoinvoice Master program to create the Receivable Invoice

Query to find the Organization structure of a business

Run this script giving inventory org as the parameter

SELECT MP.ORGANIZATION_CODE,
       HAOU.NAME,

      HAOUBG.NAME BUSINESS_GROUP,
      GSOB.NAME SOB_NAME, /*gsob.short_name,*/
      GSOB.CURRENCY_CODE,
      GSOB.PERIOD_SET_NAME,
      (SELECT SEGMENT1 || ‘.’ || SEGMENT2 || ‘.’ ||SEGMENT3 || ‘.’ ||
              SEGMENT4 || ‘.’ || SEGMENT5 || ‘.’ ||SEGMENT6 || ‘.’ ||
              SEGMENT7 || ‘.’ || SEGMENT8 || ‘.’ ||SEGMENT9
         FROM APPS.GL_CODE_COMBINATIONS
        WHERE CODE_COMBINATION_ID =GSOB.CHART_OF_ACCOUNTS_ID) CHART_OF_ACCOUNT,
      HLE.NAME LE_NAME,
      HLE.VAT_REGISTRATION_NUMBER,
      HOU.NAME OU_NAME
—  ,hri.*
 FROM APPS.HR_ALL_ORGANIZATION_UNITS   HAOU,
      APPS.HR_ORGANIZATION_INFORMATION HRI,
      APPS.MTL_PARAMETERS              MP,
      APPS.GL_SETS_OF_BOOKS            GSOB,
      APPS.HR_LEGAL_ENTITIES           HLE,
      APPS.HR_OPERATING_UNITS          HOU,
      APPS.HR_ALL_ORGANIZATION_UNITS   HAOUBG,
      APPS.HR_ORGANIZATION_INFORMATION HRIBG

WHERE HRI.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
  AND HAOU.ORGANIZATION_ID = MP.ORGANIZATION_ID
  AND MP.ORGANIZATION_CODE IN (‘V1’)
  AND HRI.ORG_INFORMATION_CONTEXT = ‘Accounting Information’
  AND GSOB.SET_OF_BOOKS_ID = HRI.ORG_INFORMATION1
  AND HLE.ORGANIZATION_ID = HRI.ORG_INFORMATION2
  AND HOU.ORGANIZATION_ID = HRI.ORG_INFORMATION3
  AND HAOUBG.ORGANIZATION_ID = HLE.BUSINESS_GROUP_ID
  AND HRIBG.ORG_INFORMATION_CONTEXT = ‘Business Group Information’
  AND HRIBG.ORGANIZATION_ID =HAOUBG.ORGANIZATION_ID; 

Oracle SCM Functional Interview Questions: Purchasing

Purchasing

1. What are the approval hierarchy
2. What approval hierarchy you use in your implementation?
3. How to check what is the approval hierarchy used in your system?                                        Ans: If Use Approval Hierarchy option is enabled in the Financial Options form, it will use the position based hierarchy. If it is disabled then it will use the supervisor based hierarchy.
4. How to send the PO to a person before even approval who has that approval limit not through workflow hierarchy
5. Where to do the setup for specific approval limit for a person?

Oracle SCM Functional Interview Questions: Inventory

Inventory

1. What are different planning methods in Inventory
2. What are the setups for different planning method (Min-Max)
3. What is the concurrent program for the planning
4. What are the setups for expense item?
5. How many key flexfields are there in Inventory?
6. What is the use of sales order KFF?
7. How we can have the item key flexfield with 2 segments, is it possible, if yes then how, if no then why?
8. How will you inactivate the Inventory locations
9. What all setups you have done in inventory organization implementation

Applying Deposit to an AR Invoice

 1.        Create an AR transaction type for Advance Invoice with class Deposit 
 2.        Using this transaction type, create a Deposit for the same customer 
 3.        After receiving the amount from customer against this Deposit Invoice, create a Receipt 
 4.        Apply the receipt with the Deposit 
 5.        Now Create the required Sales order for the customer and ship confirm 
 6.        This will create the end customer invoice with full amount 
 7.        Now query the end customer invoice and apply deposit(Action–> Apply Deposit) by querying the deposit which created earlier 
 8.        Save the transaction and then click the refresh button in the Balance Due block, you can now see the difference amount after Deposit deduction 
 9.        Also you can see all adjustments in the Details button

Note: Final Invoice date should fall in between Deposit Invoice effective dates 

To Query Profile option details for any user or responsibility

SELECT FPO.PROFILE_OPTION_ID,
      FPO.PROFILE_OPTION_NAME,
      FPOT.USER_PROFILE_OPTION_NAME,
      FPOV.LEVEL_ID,
      DECODE(FPOV.LEVEL_ID,
             ‘10001’,
             ‘Site’,
             ‘10002’,
             ‘Appllication’,
             ‘10003’,
             ‘Responsibility’,
             ‘10004’,
             ‘User’,
             FPOV.LEVEL_ID) LEVEL_TYPE,
      FPOV.LEVEL_VALUE,
      DECODE(FPOV.LEVEL_ID,
             ‘10001’,
             ‘Site’,
             ‘10002’,
             (SELECT APPLICATION_NAME
                FROM APPS.FND_APPLICATION_TL
               WHERE APPLICATION_ID =FPOV.LEVEL_VALUE),
             ‘10003’,
             (SELECT RESPONSIBILITY_NAME
                FROM APPS.FND_RESPONSIBILITY_TL
               WHERE RESPONSIBILITY_ID =FPOV.LEVEL_VALUE),
             10004,
             (SELECT DESCRIPTION
                FROM APPS.FND_USER
               WHERE USER_ID = FPOV.LEVEL_VALUE),
             FPOV.LEVEL_ID) LEVEL_VALUE,
      FPOV.PROFILE_OPTION_VALUE,
      FPOV.LAST_UPDATE_DATE,
      FPO.SQL_VALIDATION,
      TRUNC(FPOV.LAST_UPDATE_DATE),
      (SELECT DESCRIPTION
         FROM APPS.FND_USER
        WHERE USER_ID = FPOV.LAST_UPDATED_BY)UPDATED_BY
 FROM APPS.FND_PROFILE_OPTIONS       FPO,
      APPS.FND_PROFILE_OPTION_VALUES FPOV,
      APPS.FND_PROFILE_OPTIONS_TL    FPOT
WHERE FPOV.PROFILE_OPTION_ID = FPO.PROFILE_OPTION_ID
  AND FPOT.PROFILE_OPTION_NAME =FPO.PROFILE_OPTION_NAME
  AND FPOV.LEVEL_VALUE =
      (SELECT USER_ID FROM FND_USER WHERE USER_NAMEIN (‘305030341’)) –for user
  /*AND (FPOV.LEVEL_ID = 10003 AND
      FPOV.LEVEL_VALUE IN
      (SELECT RESPONSIBILITY_ID
          FROM FND_RESPONSIBILITY_TL
         WHERE RESPONSIBILITY_NAME LIKE ‘%%’)) –for responsibility*/
  /*AND UPPER(FPOT.USER_PROFILE_OPTION_NAME) LIKE ‘%WMS%TCP%’ — for profile option*/ 

Interview Q & A -1: FlexFields

FLEX FIELD

What is the difference between key flexfield and Descriptive flexfield?

Key Flexfield is used to describe unique identifiers that will have a better meaning than using number IDs. e.g a part number, a cost centre etc
Desc Flex is used to just capture extra information.
Key Flexfields have qualifiers whereas Desc Flexfields do not.
Desc Flexfields can have context sensitive segments while Key flexfields cannot.

And one more differenct that KFF displays like text item but DFF displays like [ ] .

Which procedure should be called to enable a DFF in a form?

FND_DESCR_FLEX.DEFINE

(BLOCK                                 => ‘BLOCK_NAME’
                ,FIELD                                    => ‘FORM_FIELD_NAME’
               ,APPL_SHORT_NAME      => ‘APP_NAME’
               ,DESC_FLEX_NAME         => ‘DFF_NAME’
               );

In Brief,

We need to follow the following steps in your form:
a) Create a displayed field to hold your concatenated segment values (the [ ] field).
b) Create fields (normally hidden) for each of the flexfield columns in your table (the

      one used for your flex).
c) In the form-level WHEN-NEW-FORM-INSTANCE,

call FND_DESCR_FLEX.DEFINE,

setting BLOCK parameter to the name of the block containing your [ ] field,

the FIELD parameter to the name of your [ ] field,

the APPL_SHORT_NAME parameter to the short name of your application and the DESC_FLEX_NAME parameter to the name of your descriptive flex.

In the form-level WHEN-NEW-ITEM-INSTANCE,

WHEN-VALIDATE-ITEM,

PRE-INSERT,

PRE-UPDATE,

POST-QUERY and

PRE-QUERY triggers place a call to FND_FLEX.EVENT as follows:

         FND_FLEX.EVENT ( ‘TRIGGER_NAME’);
where TRIGGER_NAME is the name of the trigger,

e.g. in the PRE-QUERY trigger …. FND_FLEX.EVENT(‘PRE-QUERY’);

Which procedure should be used to make the DFF read only at run time?

FND_DESCR_FLEX.UPDATE_DEFINITION()

What is the difference between flexfield qualifier and segment qualifier?

Flexfiled qualifier identifies segement in a flexfield and segment qualifier identifies value in a segment.

There are four types of flexfiled qualifier

1)     Balancing segment qualifier

2)     cost center

3)     natural account and

4)     intercompnay

segemtn qualifier :-

1)     allow budgeting

2)     allow posting

3)     account type

4)     contral account and

5)     reconciliation flag

What are the components of DFF (like structure, segment)?
Howmany Key flexfiels are there in Inventory?
What is Value Set?
Value Sets define and store the valid items of data, which may be entered into a field. Key Flexfields, Descriptive Flexfields and many standard fields use Value Sets. Oracle already comes with hundreds of Value Sets. We define additional Value Sets to support our own user-defined Key and Descriptive Flexfields (although we may use any existing standard Value Sets if they suit our purpose). In defining a new Value Set, we are defining the physical format of valid data, which can reside in that Value Set. Data in a Value Set can be of several types: 

•  Independently loaded into a Value Set (through a standard form). 
•  Resident in a table (to which we direct the Value Set definition). 
•  There could be No Validation (any data can go into the field, but still subject to the formatting rules.) 
•  Dependent on the value of data in a preceding Independent segment (loaded through a standard form). 

Ex: For Supplies & Accessories CC in ATP, we define GEMS_GPO_ASSIGN_SET value set for the assignment set associated with that OU.

Interview Q & A: FlexFields

FLEX FIELD

What is the difference between key flexfield and Descriptive flexfield?

Key Flexfield is used to describe unique identifiers that will have a better meaning than using number IDs. e.g a part number, a cost centre etc
Desc Flex is used to just capture extra information.
Key Flexfields have qualifiers whereas Desc Flexfields do not.
Desc Flexfields can have context sensitive segments while Key flexfields cannot.

And one more differenct that KFF displays like text item but DFF displays like [ ] .

Which procedure should be called to enable a DFF in a form?

FND_DESCR_FLEX.DEFINE

(BLOCK                                 => ‘BLOCK_NAME’
                ,FIELD                                    => ‘FORM_FIELD_NAME’
               ,APPL_SHORT_NAME      => ‘APP_NAME’
               ,DESC_FLEX_NAME         => ‘DFF_NAME’
               );

In Brief,

We need to follow the following steps in your form:
a) Create a displayed field to hold your concatenated segment values (the [ ] field).
b) Create fields (normally hidden) for each of the flexfield columns in your table (the

      one used for your flex).
c) In the form-level WHEN-NEW-FORM-INSTANCE,

call FND_DESCR_FLEX.DEFINE,

setting BLOCK parameter to the name of the block containing your [ ] field,

the FIELD parameter to the name of your [ ] field,

the APPL_SHORT_NAME parameter to the short name of your application and the DESC_FLEX_NAME parameter to the name of your descriptive flex.

In the form-level WHEN-NEW-ITEM-INSTANCE,

WHEN-VALIDATE-ITEM,

PRE-INSERT,

PRE-UPDATE,

POST-QUERY and

PRE-QUERY triggers place a call to FND_FLEX.EVENT as follows:

         FND_FLEX.EVENT ( ‘TRIGGER_NAME’);
where TRIGGER_NAME is the name of the trigger,

e.g. in the PRE-QUERY trigger …. FND_FLEX.EVENT(‘PRE-QUERY’);

Which procedure should be used to make the DFF read only at run time?

FND_DESCR_FLEX.UPDATE_DEFINITION()

What is the difference between flexfield qualifier and segment qualifier?

Flexfiled qualifier identifies segement in a flexfield and segment qualifier identifies value in a segment.

There are four types of flexfiled qualifier

1)     Balancing segment qualifier

2)     cost center

3)     natural account and

4)     intercompnay

segemtn qualifier :-

1)     allow budgeting

2)     allow posting

3)     account type

4)     contral account and

5)     reconciliation flag

What are the components of DFF (like structure, segment)?
Howmany Key flexfiels are there in Inventory?