WMS LPN License Plate Number ,License Plate Numbers (LPNs) ,oracle lpn r12 ,Different types of LPN (licence plate number) Status in WMS module

WMS LPN License Plate Number

LPN(License Plate Number) is an object carries the location where it is residing and items that are holding. LPN need not be a physical object, it could be logical grouping. LPN simply a group of items represented systematically. The major advantage of LPN is less number of transactions. Entire LPN can be transacted at once instead of by each line that LPN holds. 

In a word,A LPN allows you to logically group items together that physically travel together on the floor.

All material received through Oracle Warehouse Management is associated with a unique LPN.
This enables material to easily be tracked and transacted throughout your warehouse without scanning the item, quantities, lots or serial numbers.(There is a good example,see here)

A Corresponding Concept

Packed Items(LPN) <--> Loose Material(Non-LPN)

In Oracle Mobile,All transactions can be for either loose material or LPN triggered.
LPN triggered transactions require less data entry because an LPN has its current subinventory, location, and item contents all associated with a single identifier.
The benefit of an LPN triggered transaction is greater when it contains serial controlled items because the amount of data entry is greater.

Using LPNs

Oracle Warehouse Management enables you to track, transact, and nest LPNs. 

1. Store information about an LPN such as item, revision, lot, serial, organization, subinventory, or locator
2. Track contents of any container in receiving, inventory, or in-transit
3. Receive, store, and pick material by LPN
4. View on hand balances by LPN
5. Move multiple items in a transaction by LPN
6. Transfer LPN contents
7. Pack, unpack, consolidate, split, and update LPNs
8. Print labels and reports for referencing container contents
9. Track nested LPNs Oracle Internal & OAI Use OnlyOracle Only
10. Reuse empty LPNs
11. Receive and send LPN information on an ASN

Nesting License Plate Numbers

Oracle Warehouse Management enables you to nest LPNs. 

For example, pallet LPN P5555 contains three nested LPNs: LPN P5552, LPN P5553, and LPN P5554. In the system, you would see LPN P5555 as the top-level LPN, and each of the of the three box LPNs would fall under LPN P5555.

You can nest LPNs within other LPNs. In the above example, item A is packed in LPN 2, and LPN1, LPN2, and LPN3 are nested within LPN4. When you transact LPN4, all of the LPNs nested within it are transacted.

LPN Transactions(refer)

LPN transactions allow movement of material in to and out of LPNs and enable you to manually create nested LPNs. The available LPN Transactions are as follows:

• Pack transaction- The Pack transaction enables you to pack loose material into an LPN.
• Consolidate transaction- The Consolidate transaction enables you to nest a child LPN inside a parent LPN.
• Unpack transaction- The Unpack transaction enables you to unpack either material or a nested LPN from a parent LPN.
• Split transaction- The Split transaction is essentially an Unpack and Pack combined together, creating a new LPN with some material from another LPN.
• Update LPN transaction- The Update LPN transaction enables you to update the weight, volume, and container item of an LPN. As in all other transactions, LPN transactions of lot, serial, or revision controlled material requires you to enter the item controls.

Relationship between Some LPN Related Tables 

Meaning of LPN Context Code(refer)

Oracle Warehouse Management system (WMS) defines a context for each LPN to denote the current state of the LPN. Some of these contexts can be used in the WMS Rules Engine to define specialized picking and put away rules. 

Hi Folks,


Different types of LPN (licence plate number) Status in WMS module


1 - Resides in Inventory
2 - Resides in WIP
3 - Resides in Receiving
4 - Issued out of Stores
5 - Defined but not used
6 - Intransit
7 - At Vendor
8 - Packing context
9 - Loaded to Dock
10 - Prepack for WIP
11 - Picked
12 - Loaded in Staging

LPN Context Value	Description
1	Resides in Inventory
2	Resides in WIP
3	Resides in Receiving
4	Resides in Stores
5	Pre-generated
6	Resides in Intransit
7	Resides in Vendor Site
8	Packing context, used as a temporary context value
9	Loaded for shipment
10	Prepack of WIP
11	Picked

Query to list Customer (Party), Account, Site data

Query to list Customer (Party), Account, Site data

As my client was going through its customer related data clean up in its Oracle R12 applications, I wrote the following query to retrieve all the information that were needed to provide a list of its customer (party), account, site, address, collector, and other related information.

--=============================================================================

SELECT

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

       -- Party Information

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

       hp.party_number                      "Registry ID",

       hp.party_name                        "Party Name",

       hp.party_type                        "Party Type",

       DECODE(hp.status,

              'A', 'Active',

              'I', 'Inactive',

              hp.status)                    "Party Status",

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

       -- Account Information

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

       hca.account_number                   "Account Number",

       DECODE(hca.status,

              'A', 'Active',

              'I', 'Inactive',

              hca.status)                   "Account Status",

       hca.account_name                     "Account Description",

       hca.customer_class_code              "Classification",

       DECODE(hca.customer_type,

              'R', 'External',

              'I', 'Internal',

              hca.customer_type)            "Account Type",

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

       -- Site Information

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

       hps.party_site_number                "Customer Site Number",

       DECODE(hcas.status,

              'A', 'Active',

              'Inactive')                   "Site Status",

       DECODE(hcas.bill_to_flag,

              'P', 'Primary',

              'Y', 'Yes',

              hcas.bill_to_flag)            "Bill To Flag",

       DECODE(hcas.ship_to_flag,

              'P', 'Primary',

              'Y', 'Yes',

              hcas.ship_to_flag)            "Ship To Flag",

       hcas.cust_acct_site_id               "Customer Acct Site ID",

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

       -- Address Information

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

       hl.address1                          "Address1",

       hl.address2                          "Address2",

       hl.address3                          "Address3",

       hl.address4                          "Address4",

       hl.city                              "City",

       hl.state                             "State",

       hl.postal_code                       "Zip Code",

       ter.name                             "Territory",

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

       -- DFF Information (specific to client)

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

       hcas.attribute4                      "SMG Key",

       hcas.attribute8                      "GLN Key",

       hca.attribute3                       "Credit Approval Date",

       hca.attribute7                       "Credit Approved By",

       hca.attribute4                       "Acct Opened Date",

       hca.attribute5                       "Credit Collection Status",

       hca.attribute1                       "BPCS Last Trx Date",

       hca.attribute2                       "BPCS Avg Pay Days",

       hca.attribute6                       "BPCS RCM Reference",

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

       -- Collector Information

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

       col.name                             "Collector Name",

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

       -- Account Profile Information

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

       hcp.credit_checking                  "Credit Check Flag",

       hcp.credit_hold                      "Credit Hold Flag",

       hcpa.auto_rec_min_receipt_amount     "Min Receipt Amount",

       hcpa.overall_credit_limit            "Credit Limit",

       hcpa.trx_credit_limit                "Order Credit Limit",

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

       -- Attachment Flag

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

       NVL((SELECT 'Y'

              FROM fnd_documents_vl doc,

                   fnd_lobs         blo,

                   fnd_attached_documents att

             WHERE doc.media_id = blo.file_id

               AND doc.document_id = att.document_id

               AND att.entity_name = 'AR_CUSTOMERS'

               AND att.pk1_value   = hca.cust_account_id

               AND ROWNUM = 1), 'N'

       ) "Attachment Flag",

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

       -- Party Relationship Flag

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

       NVL((SELECT 'Y'

              FROM hz_cust_acct_relate_all hzcar

             WHERE hzcar.cust_account_id = hca.cust_account_id

               AND hzcar.relationship_type = 'ALL'

               AND ROWNUM = 1), 'N'

       ) "Party Relationship Flag",

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

       -- Account Relationship Flag

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

       NVL((SELECT 'Y'

              FROM hz_cust_acct_relate_all hzcar

             WHERE hzcar.cust_account_id = hca.cust_account_id

               AND ROWNUM = 1), 'N'

       ) "Account Relationship Flag",

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

       -- Party Contact Flag

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

       NVL((SELECT 'Y'

              FROM hz_parties hp2

             WHERE 1=1

               AND hp2.party_id = hp.party_id

               AND (

                    hp2.url IS NOT NULL OR

                    -- LENGTH(TRIM(hp.email_address)) > 5

                    INSTR(hp2.email_address, '@') > 0 OR

                    hp2.primary_phone_purpose IS NOT NULL

                    )

           ), 'N'

       ) "Party Contact Flag",

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

       -- Account Contact Flag

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

       NVL((SELECT 'Y'

              FROM hz_contact_points

             WHERE status = 'A'

               AND owner_table_id =

                   (SELECT hcar.party_id

                      FROM hz_cust_account_roles   hcar,

                           ar_contacts_v           acv

                     WHERE hcar.cust_account_id   = hca.cust_account_id

                       AND hcar.cust_account_role_id = acv.contact_id

                       AND hcar.cust_acct_site_id IS NULL  -- look for account level only

                       AND ROWNUM = 1 -- add this row to show inactive sites (i.e. with no site id)

                    )

               AND ROWNUM = 1), 'N'

       ) "Account Contact Flag",

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

       -- Site Contact Flag

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

       NVL((SELECT 'Y'

              FROM hz_contact_points

             WHERE status = 'A'

               AND owner_table_id =

                   (

                      SELECT hcar.party_id

                        FROM hz_cust_account_roles   hcar,

                             ar_contacts_v           acv

                       WHERE hcar.cust_acct_site_id     =  hcas.cust_acct_site_id

                         AND hcar.cust_account_role_id  =  acv.contact_id

                         AND ROWNUM = 1  -- add this row to show inactive sites (i.e. with no site id)

                   )

               AND ROWNUM = 1), 'N'      -- any contact (email, phone, fax) would suffice this condition

       ) "Site Contact Flag"

  FROM

       hz_parties              hp,

       hz_party_sites          hps,

       hz_cust_accounts_all    hca,

       hz_cust_acct_sites_all  hcas,

       hz_customer_profiles    hcp,

       hz_cust_profile_amts    hcpa,

       hz_locations            hl,

       ra_territories          ter,

       ar_collectors           col

 WHERE

       1=1

   AND hp.party_id            =  hca.party_id

   AND hca.cust_account_id    =  hcas.cust_account_id(+)

   AND hps.party_site_id(+)   =  hcas.party_site_id

   AND hp.party_id            =  hcp.party_id  

   AND hca.cust_account_id    =  hcp.cust_account_id

   AND hps.location_id        =  hl.location_id(+)

   AND col.collector_id       =  hcp.collector_id

   AND hcas.territory_id      =  ter.territory_id(+)

   AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id

   ----

   AND hp.party_type          = 'ORGANIZATION'    -- only ORGANIZATION Party types

   AND hp.status              = 'A'               -- only Active Parties/Customers

   ----

   -- following conditions are for testing purpose only

   -- comment/uncomment as needed

   ----

   -- AND hp.party_number        = 11530

   -- AND hca.account_number     = 32253 --32396 --31753 --32253 --31038

 ORDER BY TO_NUMBER(hp.party_number), hp.party_name, hca.account_number;