Min-Max Planning (Oracle Inventory Help)

ERRBUFF and RETCODE : PL/Sql Mandatory Parameters for Concurrent Programs

In Oracle EBS, we can use Concurrent Programs to call a procedure within a package. Every PL/SQL procedure of packages which is being called in a concurrent program must have two mandatory OUT parameters – ERRBUFF and RETCODE

ERRBUF : is used to get error messages.
RETCODE: is used to get the status of the concurrent program. It can take any of the following values

0 – Concurrent Program is successful.
1 – Concurrent program completed with warning.
2 – Concurrent Program has some Error.

Why Do we Have This
Different business have different needs. These two mandatory parameters makes it flexible for the developers to end a concurrent program in error or warning as per their custom needs.

For eg : Lets say we have a procedure that computes salary of a person. A negative salary is an error scenario for the business but not for oracle. As a result concurrent program will end up in success on completion of request. But using RETCODE we can make the concurrent program to end in error by setting its value to 2 and in the similar way we can add our custom error message to the ERRBUF parameter.

Sample Procedure Declaration

CREATE OR REPLACE PACKAGE XX_CUSTOM_PKG PROCEDURE validate_salary (X_ERRBUF OUT VARCHAR2, X_RETCODE OUT NUMBER, P_USER_ID IN NUMBER); BEGIN . . . EXCEPTION WHEN NEGATIVE_SALARY THEN X_ERRBUF : 'Negative salary computed. Incorrect data'; X_RETCODE = 2; END;

Assembly Pull in Oracle Apps

Assembly Pull components are issued to discrete jobs and repetitive schedules when you complete assemblies in Work in Process. They are pulled from the supply subinventory assigned to the component requirement.
If you must assign pull components to a bill of material or job that is not associated with a routing, you must assign an Assembly Pull, rather than operation pull, supply type.
You cannot assign this supply type to a non-standard discrete job if you have not specified an assembly or bill of material reference for that job.

Oracle Apps Receivables (AR) Tables

Oracle Apps Receivables (AR) Tables
Transactions
RA_CUSTOMER_TRX_ALL Transaction Header table
RA_CUSTOMER_TRX_LINES_ALL Transaction Lines table along with Tax lines.
RA_CUST_TRX_LINE_GL_DIST_ALL Distribution for Transaction Lines
RA_CUST_TRX_LINE_SALESREPS_ALL Salesrep information for Transaction Lines


Transaction Interface Tables
RA_INTERFACE_LINES_ALL Transaction Lines interface
RA_INTERFACE_SALESCREDITS_ALL Transaction Sales credit information
RA_INTERFACE_DISTRIBUTIONS_ALL Transaction Distribution information
RA_INTERFACE_ERRORS_ALL Transaction errors table
AR_PAYMENTS_INTERFACE_ALL Interface table to import receipts
AR_INTERIM_CASH_RECEIPTS_ALL Lockbox transfers the receipts that pass validation to the interim tables
AR_INTERIM_CASH_RCPT_LINES_ALL Lockbox transfers the receipts that pass validation to the interim tables


Receipts tables
AR_CASH_RECEIPTS_ALL Cash Receipt Header tables
AR_RECEIVABLE_APPLICATIONS_ALL stores Receipt Application details
AR_PAYMENT_SCHEDULES_ALL This table is updated when an activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, bills receivable
AR_CASH_RECEIPT_HISTORY_ALL This table stores all of the activity that is contained for the life cycle of a receipt.
• Each row represents one step.
• The status field for that row tells you which step the receipt has reached.
• Possible statuses are Approved, Confirmed, Remitted, Cleared, and Reversed.


Customer Tables
HZ_PARTIES A party is an entity that can enter into a business relationship.
HZ_CUST_ACCOUNTS This table stores information about customer/financial relationships established between a Party and the deploying company.
HZ_PARTY_SITES This table links a party (HZ_PARTIES) and a location (HZ_LOCATIONS) and stores location-Specific party information such as a person’s mail stops at their work address.
HZ_CUST_ACCT_SITES_ALL This table stores information about customer/financial account sites information.
HZ_CUST_SITE_USES_ALL This table stores information about the business purposes assigned to a customer account site
HZ_LOCATIONS A location is a point in geographical space described by an address and/or geographical Indicators such as latitude or longitude.


Setup tables
RA_CUST_TRX_TYPES_ALL This table stores information about each transaction type for all classes of transactions, for example, invoices, commitments, and credit memos.
AR_RECEIPT_CLASSES This table stores the different receipt classes that you define.
AR_RECEIPT_METHODS This table stores information about Payment Methods, receipt attributes that you define and assign to Receipt Classes to account for receipts and their applications

Oracle Apps: Payables Table structure

Oracle Apps Payable Table Structure:

Supplier Tables
AP_SUPPLIERS This table replaces the old PO_VENDORS table.
It stores information about your supplier level attributes.
AP_SUPPLIER_SITES_ALL It stores information about your supplier site level attributes.
IBY_EXTERNAL_PAYEES_ALL stores Payee(supplier) information.
HZ_PARTIES Party data for the suppliers.
HZ_PARTY_SITES Party site data for the supplier sites.B4


Supplier Interface tables
ap_suppliers_int Supplier Interface. Execute Supplier Open Interface Import Program
ap_supplier_sites_int Supplier site interface. Execute "Supplier Sites Open Interface Import" Program


Invoice
AP_INVOICES_ALL It contains records for invoices you enter
AP_INVOICE_LINES_ALL It contains records for invoice lines entered manually, generated automatically or imported from the Open Interface.
AP_INVOICE_DISTRIBUTIONS_ALL It holds the distribution information that is manually entered or system-generated


Invoice Interface
AP_INVOICES_INTERFACE Invoice Header Information. Run Payables Open Interface Import concurrent program
AP_INVOICE_LINES_INTERFACE Invoice Lines and Distributions Information


Payments:
AP_CHECKS_ALL Check Header Information
AP_INVOICE_PAYMENTS_ALL Details of Invoice paid by a Check/Payment
AP_PAYMENT_HISTORY_ALL
AP_INV_SELECTION_CRITERIA_ALL
AP_SELECTED_INVOICES_ALL
AP_PAYMENT_DISTRIBUTIONS_ALL
CE_PAYMENT_DOCUMENTS AP_CHECK_STOCKS_ALL
IBY_PAY_SERVICE_REQUESTS   Payment Process Request information  (11i Terminology is  Payment Batch)
IBY_PAY_INSTRUCTIONS_ALL Payment Instruction information
IBY_DOC_PAYABLES_ALL Invoice information stored by IBY for generating payment
IBY_PAYMENTS_ALL Payment Information


Accounting
XLA_EVENTS replaces AP_ACOCUNTING_EVENTS_ALL
XLA_AE_HEADERS replaces AP_AE_HEADERS_ALL
XLA_AE_LINES replaces AP_AE_LINES_ALL


Bank Accounts
CE_BANK_ACCOUNTS AP_BANK_ACCOUNTS_ALL
CE_BANK_ACCT_USES_ALL   replaces AP_BANK_ACCOUNT_USES_ALL


Credit Card Transactions Table
AP_EXPENSE_FEED_LINES Use the Credit Card Transaction Interface Table to load transaction datafrom your credit card issuers into your Payables system. When younsubmit the Credit Card Transaction Validation and Exception Report, for records where the CREATE_DISTRIBUTION_FLAG is Y, Payables
uses the data in the AP_EXPENSE_FEED_LINES table to create distributions with proper accounts in the AP_EXPENSE_FEED_DISTS
table

Item Cross Reference/Customer Items/Customer Items Architecture/ Manufacturer Items/Item Relationships

Item Cross Reference

Item Cross-references:

Cross-reference types define relationships between items and entities such as old item numbers or supplier item numbers (not for customer and manufacturer parts). This helps in searching the inventory items based on such cross references.

Assign inventory items with various items of above cross reference type here.

Search Items for above cross reference type ie Venodr and for Item value Item1.We can get our inventory item Item1A , hence cross references can be used to search items effectively.

4.6.1 Item Cross-references Architecture

MTL_CROSS_REFERENCE_TYPES CROSS_REFERENCE_TYPE   DESCRIPTION            DISABLE_DATE           VALIDATE_FLAG

MTL_CROSS_REFERENCES INVENTORY_ITEM_ID      ORGANIZATION_ID        CROSS_REFERENCE_TYPE  CROSS_REFERENCE        DESCRIPTION            ORG_INDEPENDENT_FLAG  UOM_CODE               REVISION_ID

Query to find  item cross-references : set lines 150 set pages 150 col item form a24 col reference_type form a16 col cross_reference form a24 col description form a28 select msi.segment1 item, mcr.cross_reference_type reference_type, mcr.cross_reference, mcr.description from mtl_cross_references mcr,      mtl_system_items msi where mcr.cross_reference_type='Vendor' and mcr.inventory_item_id=msi.inventory_item_id and mcr.organization_id=msi.organization_id order by 1,2 /

Customer Items 

A customer item defined at the Customer level is recognized across all address and address categories for that customer. If you ship an item to multiple customer ship-to sites that have been grouped as an address category, you can define the customer item for that address category. You would define a customer item at the address level if you ship the item to only one ship-to site for that customer.Assign the customer item to a Commodity Code (Comm. Codes Shown later).You can reference a  customer item as a Model by entering the inventory item number of an existing Model item. (Model Tab).Enter the default master and detail containers for this customer item as well as the minimum fill percent for the container. (Container Tab).Also check Required to indicate that items must be departure planned before they released and Before Build to indicate that ATO items must be departure planned before they are built.(Departure Tab).Use the Customer Item Cross References window to define and update cross references between your inventory items and the customer item numbers.

 Customer Items Architecture

 

MTL_CUSTOMER_ITEMS CUSTOMER_ITEM_ID               CUSTOMER_ID                    CUSTOMER_CATEGORY_CODE         ADDRESS_ID                     CUSTOMER_ITEM_NUMBER           ITEM_DEFINITION_LEVEL          CUSTOMER_ITEM_DESC             MODEL_CUSTOMER_ITEM_ID         COMMODITY_CODE_ID              MASTER_CONTAINER_ITEM_ID       CONTAINER_ITEM_ORG_ID          DETAIL_CONTAINER_ITEM_ID       MIN_FILL_PERCENTAGE            DEP_PLAN_REQUIRED_FLAG         DEP_PLAN_PRIOR_BLD_FLAG        INACTIVE_FLAG                  DEMAND_TOLERANCE_POSITIVE      DEMAND_TOLERANCE_NEGATIVE

MTL_CUSTOMER_ITEM_XREFS CUSTOMER_ITEM_ID       INVENTORY_ITEM_ID      MASTER_ORGANIZATION_ID PREFERENCE_NUMBER      INACTIVE_FLAG

Query to find Customer items : set lines 150 set pages 150 col item form a24 col item_desc form a40 col customer_item_desc form a40 col customer_item_number form a18 col item_definition_level form a12 col customer form a24 select    hp.party_name customer,    ci.customer_item_number,    ci.customer_item_desc,    msi.segment1 item,    msi.description item_desc,    ci.customer_category_code,    ci.item_definition_level,    ci.commodity_code_id,    ci.address_id from hz_parties hp, hz_cust_accounts hca, mtl_system_items msi, mtl_customer_items ci, mtl_customer_item_xrefs ix where ci.customer_item_id=ix.customer_item_id and ix.inventory_item_id=msi.inventory_item_id and ix.master_organization_id=msi.organization_id and ci.customer_id=hca.cust_account_id and hca.party_id=hp.party_id order by 1,2 /

  Manufacturer Items 

If u define manufacturer part numbers for items, you can use this information for reporting purposes and in catalog searches for such items.

MTL_MANUFACTURERS  MANUFACTURER_ID         MANUFACTURER_NAME      DESCRIPTION

MTL_MFG_PART_NUMBERS MANUFACTURER_ID        MFG_PART_NUM           INVENTORY_ITEM_ID      ORGANIZATION_ID        MRP_PLANNING_CODE      DESCRIPTION            FIRST_ARTICLE_STATUS   APPROVAL_STATUS

Query to find Manufacturer items : set lines 150 set pages 150 col manufacturer_name form a24 col description form a24 col mfg_part_num  form a24 col inv_item  form a24 col item_desc   form a50 select mm.manufacturer_name,    mp.mfg_part_num,    mp.description,    msi.segment1 inv_item,    msi.description item_desc from  mtl_system_items msi,  mtl_mfg_part_numbers mp,  mtl_manufacturers mm where mm.manufacturer_id=mp.manufacturer_id and   mp.inventory_item_id=msi.inventory_item_id and   mp.organization_id=msi.organization_id order by 1,2 /

Item Relationships

You can define relationships between items. This allows you to search for items through these relationships. Within Oracle Purchasing you can define acceptable substitute items for receiving. You must define a list of substitutes before you receive a substitute item in place of an originally ordered item. In OM, u can see the related items while ordering and cross-sell the related items.

Item Relationships Architecture

MTL_RELATED_ITEMS INVENTORY_ITEM_ID              ORGANIZATION_ID                RELATED_ITEM_ID                RELATIONSHIP_TYPE_ID           RECIPROCAL_FLAG                PLANNING_ENABLED_FLAG         START_DATE END_DATE

Query to find related items : set lines 150 set pages 150 col item form a24 col description form a40 col related_item form a24 col relation form a24 select ito.segment1 item, ito.description, itr.segment1 related_item, itr.description, ml.meaning relation, ri.reciprocal_flag from mfg_lookups ml, mtl_system_items itr, mtl_system_items ito, mtl_related_items ri where ri.inventory_item_id=ito.inventory_item_id and ri.organization_id=ito.organization_id and ri.related_item_id=itr.inventory_item_id and ri.organization_id=itr.organization_id and ri.relationship_type_id=ml.lookup_code and ml.lookup_type(+)='MTL_RELATIONSHIP_TYPES' order by 1,2 /

Displaying a same column in another column

select* from employeed

select salary, employeed.*,employeed.doj from employeed