Responsibility assign from backend API

SELECT fa.application_short_name,

  fr.responsibility_key,

  fsg.security_group_key,

  frt.description

FROM apps.fnd_responsibility fr,

  fnd_application fa,

  fnd_security_groups fsg,

  fnd_responsibility_tl frt

WHERE frt.responsibility_name = 'MWA Administrator'                -------------System Administrator

AND frt.LANGUAGE              = USERENV ('LANG')

AND frt.responsibility_id     = fr.responsibility_id

AND fr.application_id         = fa.application_id

AND fr.data_group_id          = fsg.security_group_id;

API to add Responsibility :

DECLARE

   v_user_name             VARCHAR2 (30)  := '&user_name';

   v_responsibility_name   VARCHAR2 (100) := '&responsibility_name';

   v_application_name      VARCHAR2 (100) := NULL;

   v_responsibility_key    VARCHAR2 (100) := NULL;

   v_security_group        VARCHAR2 (100) := NULL;

   v_description           VARCHAR2 (100) := NULL;

BEGIN

   SELECT fa.application_short_name, fr.responsibility_key,

          fsg.security_group_key, frt.description

     INTO v_application_name, v_responsibility_key,

          v_security_group, v_description

     FROM apps.fnd_responsibility fr,

          fnd_application fa,

          fnd_security_groups fsg,

          fnd_responsibility_tl frt

    WHERE frt.responsibility_name = v_responsibility_name

      AND frt.LANGUAGE = USERENV ('LANG')

      AND frt.responsibility_id = fr.responsibility_id

      AND fr.application_id = fa.application_id

      AND fr.data_group_id = fsg.security_group_id;

   fnd_user_pkg.addresp (username            => v_user_name,

                         resp_app            => v_application_name,

                         resp_key            => v_responsibility_key,

                         security_group      => v_security_group,

                         description         => v_description,

                         start_date          => SYSDATE,

                         end_date            => NULL

                        );

   COMMIT;

   DBMS_OUTPUT.put_line(   'Responsiblity '

                         || v_responsibility_name

                         || ' is attached to the user '

                         || v_user_name

                         || ' Successfully'

                        );

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line

                         (   'Unable to attach responsibility to user due to'

                          || SQLCODE

                          || ' '

                          || SUBSTR (SQLERRM, 1, 100)

                         );

END;

How to create user from backend in oracle applications R12

API to CREATE FND User

DECLARE

   lc_user_name                  VARCHAR2 (100) := 'TEST';

   lc_user_password            VARCHAR2 (100) := 'welcome1';

   ld_user_start_date           DATE           := TO_DATE ('23-JUN-2018');

   ld_user_end_date            VARCHAR2 (100) := NULL;

   ld_password_date            VARCHAR2 (100) := TO_DATE ('23-JUN-2018');

   ld_password_lifespan_days   NUMBER         := 90;

   ln_person_id                   NUMBER         := 32979;

   lc_email_address            VARCHAR2 (100) := 'TEST@host.com';

BEGIN

   fnd_user_pkg.createuser

                      (x_user_name                      => lc_user_name,

                       x_owner                              => NULL,

                       x_unencrypted_password    => lc_user_password,

                       x_start_date                        => ld_user_start_date,

                       x_end_date                         => ld_user_end_date,

                       x_password_date               => ld_password_date,

                       x_password_lifespan_days => ld_password_lifespan_days,

                       x_employee_id                  => ln_person_id,

                       x_email_address                => lc_email_address

                      );

   COMMIT;

EXCEPTION

   WHEN OTHERS

   THEN

      ROLLBACK;

      DBMS_OUTPUT.put_line (SQLERRM);

END;

/

How to Assign a Concurrent Program to a Responsibility....

Suppose you want to run a Program, lets take an example of  "Create Consumption Advise"

Navigate to this responsibility > View> Requests>Submit a new request>single Request

and enter "Create Consumption Advise"

Using sysadmin navigate to Security>Responsibility> Define and query for ''Inventory"

Note the request group Name: All inclusive

 Now Navigate to Responsibility> Request

Query for All inclusive

# Insert a new line and add Program "Create Consumption Advice" in it

Save your Work

Now go to "Inventory, and try running Create Consumption Advise. You will find the request in the List of values

Consigned Inventory in Oracle Applications Setup & Process

Consigned Inventory in Oracle Applications Setup & Process

Consigned Inventory Overview

Consigned Inventory from Supplier exists when inventory is in the possession of one party(A Dealer,Agent,Distributor) ,but remains the property of another party (such as the manufacturer or primary contractor) by mutual agreement . Consigned  inventory from Supplier refers to one type of consigned inventory practice where you receive and maintain goods belonging to one or more of your suppliers Both Suppliers and Customers benefit through consigned inventory strategy.

1 .Suppliers are able to compete on the basis of availability and delivery when
finished goods are at the customer site, particularly when lead times are
lengthy.
 2. Holding material on consignment reduces the lead time for items to fill sales
orders.
3. Customers experience increased inventory turns—reducing funds invested in
inventory. Financial resources are free until customer commitments are ensured
or items used in production. 

Setup Steps : -

Create an Item "10490277-0001". Apply Purchasing template. Also tick "Use Approved Supplier" in purchasing tab and also enable Consigned Check box in General Planning Tab.

save it and assign the item to organization.

we need two subinventories, one for Asset sub inventory which is in possession of Customer and one for Expense in which supplier puts item. Expense subinventory will be placed in Customer Premises but possession of that subinventory will be to Supplier.

For creating subinventory go to Inventory > Setup > Organizations > Subinventories

And then create new subinventory (FG as Asset subinventory).

Now define Expense subinventory.

Set  Profile Option or Validate the Profile Option

INV: Allow Expense to Asset Transfer = Yes at Site Level 

Define Self Billing option at Supplier Level 

Supply Base > Suppliers > Search by Supplier name & Click on Purchasing  > Self Billing

Set Pay On = ‘Use’ and Invoice Summary Level = ‘Pay Site’

Create Blanket Purchase Order between Supplier and Customer.

Navigation: Purchasing > Purchase Order > Purchase Orders

 Make sure pay on "Use" is ticked in Supplier setups

Create an ASL for this Item and Supplier combination. On Item-attributes form assign above blanket Agreement in the Source Documents tab and  in Inventory tab click  the check box Consigned from Supplier.

Define Consigned\VMI Consumption rules in inventory.

 Nav : Inventory > Setup>Transactions>Consigned\VMI Consumption. 

Note : The weight value allows you to set the processing order. For example, if two transactions satisfy the transaction criteria, the system processes the transaction with the highest weight.

   Define Transaction Type

NAV: Inventory > Setup > Transactions > Types

Add Create Consumption Advice

Navigation: SYSADMIN > Security > Responsibility > Request  

Add the Concurrent Program ‘’ Create Consumption Advice ’’

Run the ‘’ Create Consumption Advice ’’ concurrent program. By default, this concurrent program will not be available in Inventory responsibility. You need to add it manually. In order to do so, check the Request group name in the responsibility & then Navigate Create consumption advice request has been processed.

Test Case 

Create Standard PO for same item and Supplier 

Go to shipment & check under More Consigned Check box is checked 

Approve the Purchase Order

Create Receipt and deliver the goods under Consigned non Asset Subinventory 

Check On hand 

Now transfer material from Consigned store to regular through Sub inventory transfer

NAV Inventory  > Transactions > Consigned Transactions

Now check the status of item on hand at the material workbench  Owning party information  

now transfer qty through

subinventory transfer from Consigned to FG 

Now, Run the concurrent “Create Consumption Advice”  under Inventory

And then 

Run the concurrent Pay on Receipt Auto Invoice with a transaction source of “ERS_AND_USE” for our inventory receipt number.

Thanks ,

------------------------------------------------------------------------------------------------------------------------------------------------------------------
Please share this knowledge with your friends and subscribe at our blog for more updates. If you have any queries, please participate in our Forum or by leaving your comments below.
-------------------------------------------------------------------------------------------------------

Buy One Get other item Free

Promotions

Lets create a promotional setup in Advance pricing for "Buy One Get other item free or get 50% discount on other Item"

 Steps

Create 2 item "Test Item” and "Free Item".

 Enter both items in Price list with price as 1000 for both of them

Now Navigate to Modifier> Modifier setups and enter header information Like type as Promotion, Currency as USD, enter any number you like and check Active and Automatic. Optionally you can provide start and end dates

↑  Now enter line level information. Modifier number will come automatically. Select Type as Promotional Goods

Pricing Phase as "List Line Adjustment" and select product attribute value as "TEST Item" and as this is recurring discount so you can select Break type as Recurring. Values are between 1 to 10 only ie. if order qty is between these values only then item this modifier is applicable

Now click on Define Details

 in this under Get please enter free Item details. Application method is percent and give value as 100% i.e. its totally free. if you provide the value as 50% i.e. that means there is 50% discount on second item

save you work

 Run "QP: Maintains the denormalized data in QP Qualifiers".

Enter a sales order for Test Item.

When you save the Sales order, you will get a message that a new line has been added for free item

Click OK

same way if you specify 50% value in Define Details, customer will get 50% discount on second item. 

Go to sales order add one more line with ''TEST Item''

now save the record ,you will get a message that a new line has been added  

''Free item'' add
with 50% discount 

Tables Joins in Oracle apps WIP, BOM, INV

Tables Joins in Oracle apps WIP, BOM, INV

1) BOM :

BOM_RESOURCES 

BOM_BILL_OF_MATERIALS 

BOM_DEPARTMENTS (BD)

BOM_OPERATIONAL_ROUTINGS 

BOM_OPERATION_SEQUENCES 

BOM_INVENTORY_COMPONENTS 

BOM_STANDARD_OPERATIONS 

2) INV : 

MTL_PARAMETERS 

MTL_SYSTEM_ITEMS_B 

MTL_MATERIAL_TRANSACTION 

MTL_SERIAL_NUMBERS 

MTL_ITEM_REVISIONS 

MTL_ITEM_CATEGORIES 

MTL_DEMAND 

MTL_TRANSACTION_TYPES 

MTL_CATEGORIES 

CST_ITEM_COSTS 

CST_COST_ELEMENTS 

3) WIP :

WIP_PARAMETERS 

WIP_DISCRETE_JOBS 

WIP_ENTITIES 

WIP_OPERATIONS 

WIP_REQUIREMENT_OPERATIONS 

WIP_OPERATION_RESOURCES 

WIP_TRANSACTIONS 

WIP_TRANSACTION_ACCOUNTS 

WIP_LINES 

WIP_EAM_WORK_REQUESTS 

WIP_EAM_PARAMETERS 

1) JOINS IN BOM and BOM-INV

BOM_BILL_OF_MATERIALS (BBOM)                       MTL_SYSTEM_ITEMS_B (MSIB)

organization_id                                                                  organization_id

assembly_item_id                                                              inventory_item_id

MTL_SYSTEM_ITEMS_B (MSIB)                                BOM_OPERATIONAL_ROUTINGS (BOR)

inventory_item_id                                                              assembly_item_id

organization_id                                                                  organization_id

BOM_BILL_OF_MATERIALS (BBOM)                       BOM_INVENTORY_COMPONENTS (BIC)

bill_sequence_id                                                                bill_sequence_id

BOM_INVENTORY_COMPONENTS (BIC)               MTL_SYSTEM_ITEMS_B (MSIB)

component_item_id                                                           inventory_item_id

BOM_OPERATIONAL_ROUTINGS(BOR)                  BOM_OPERATION_SEQUENCES(BOS)

routing_sequence_id                                                          routing_sequence_id

BOM_OPERATION_SEQUENCES (BOS)                   BOM_STANDARD_OPERATIONS (BSO)

standard_operation_id                                                       standard_operation_id

BOM_OPEARATION_SEQUENCES(BOS)                  BOM_DEPARTMENTS (BD)

department_id                                                                   department_id

BOM_DEPARTMENTS (BD)                                        BOM_DEPARTMENT_CLASSES

department_id                                                                   department_id

operation_sequence_id                                                      operation_sequence_id

BOM_OPERATION_RESOURCE (BORE)                   BOM_RESOURCES (BR)

resource_id                                                                       resource_id

BOM_DEPARMTENTS (BD)                                         BOM_RESOURCES (BR)

organization_id                                                                  organization_id 

2) JOINS IN INV :

MTL_SYSTEM_ITEMS_B (MSIB)                                 MTL_PARAMETERS (MP)

organization_id                                                                   organization_id

MTL_MATERIAL_TRANSACTION (MMT)                 MTL_TRANSACTION_TYPES (MTT)

transaction_type_id                                                            transaction_type_id

MTL_MATERIAL_TRANSACTION (MMT)                 MTL_ITEM_CATEGORIES (MIC)

organization_id                                                                  organization_id

inventory_item_id                                                              inventory_item_id

MTL_MATERIAL_TRANSACTION (MMT)                MTL_SYSTEM_ITEMS_B(MSIB)

inventory_item_id                                                              inventory_item_id

MTL_TRANSACTION_TYPES (MTT)                         MTL_SYSTEM_ITEMS_B(MSIB)

inventory_item_id                                                              inventory_item_id

MTL_ITEM_REVISIONS(MIR)                                     MTL_SYSTEM_ITEMS_B(MSIB)

inventory_item_id                                                              inventory_item_id

organization_id                                                                  organization_id

CST_ITEM_COSTS(CIC)                                              MTL_SYSTEM_ITEMS_B(MSIB)

inventory_item_id                                                              inventory_item_id

organization_id                                                                  organization_id

MTL_ITEM_CATEGORIES (MIC)                                MTL_CATEGORIES (MC)

category_id                                                                       category_id

JOINS IN INV WITH OTHER MODULES :

MTL_SYSTEM_ITEMS_B (MSIB)                                 RCV_TRANSACTIONS (RT)

organization_id                                                                  organization_id

MTL_SYSTEM_ITEMS_B (MSIB)                                 HR_EMPLOYEES (HE)

buyer_id                                                                            employee_id

3) JOINS IN WIP :

WIP_ENTITIES (WE)                                                     WIP_TRANSACTIONS (WT)

organization_id                                                                  organization_id

wip_entity_id                                                                     wip_entity_id

WIP_TRANSACTION_ACCOUNTS (WTA)                WIP_TRANSACTIONS (WT)

transaction_id                                                                    transaction_id

WIP_LINES (WL)                                                           WIP_TRANSACTIONS(WT)

line_id                                                                                line_id

WIP_DISCRETE_JOBS (WDJ)                                       WIP_ENTITIES (WE)

wip_entity_id                                                                      wip_entity_id

WIP_ENTITIES (WE)                                                       WIP_OPERATION_RESOURCES (WOR)

wip_entity_id                                                                       wip_entity_id

WIP_ENTITIES (WE)                                                     WIP_REQUIREMENT_OPERATIONS

organization_id                                                                    organization_id

WIP_ENTITIES                                                                 WIP_OPERATIONS

wip_entity_id                                                                       wip_entity_id

organization_id                                                                    organization_id

JOINS IN WIP WITH OTHER MODULES

WIP_TRANSACTION_ACCOUNTS (WTA)               PO_HEADERS_ALL (PHA)

po_header_id                                                                   po_header_id

CST_COST_ELEMENTS (CCE)                                   WIP_TRANSACTION (WT)

cst_element_id                                                                 cst_element_id

WIP_OPERATIONS (WO)                                            BOM_DEPARTMENTS (BD)

department_id                                                                  department_id

WIP_ENTITIES (WE)                                                     MTL_SYSTEM_ITEMS_B (MSIB)

organization_id                                                                  organization_id

primary_item_id                                                                inventory_item_id

WIP_OPERATION_RESOURCES (WOR)                    BOM_RESOURCES (BR)

resource_id                                                                       resource_id