Wednesday, 12 September 2012

WIP JOB Related Scripts


Query to find WIP Job details:
SELECT
 E.WIP_ENTITY_ID,
 E.WIP_ENTITY_NAME Job,
 M.SEGMENT1 ITEM, --M.INVENTORY_ITEM_ID,
 M.DESCRIPTION Item_Desc,
 D.DESCRIPTION JOB_DESCRIPTION,
 M.PRIMARY_UOM_CODE UOM,
 D.START_QUANTITY Start_Quantity,
 D.JOB_TYPE Job_Type,
 D.COMPLETION_SUBINVENTORY Subinventory,
 D.COMPLETION_LOCATOR_ID Compl_Loc_Id,
 D.BOM_REVISION BOM_Revision,
 D.BOM_REVISION_DATE BOM_REVISION_DATE,
 SUBSTR(TO_CHAR(D.BOM_REVISION_DATE,'HH24:MI'),1,5) BOM_TIME,
 D.SCHEDULED_START_DATE Scheduled_Start,
 SUBSTR(TO_CHAR(D.SCHEDULED_START_DATE,'HH24:MI'),1,5) START_TIME,
 LU.MEANING Status,
 D.ROUTING_REVISION Routing_Revision,
 D.ROUTING_REVISION_DATE ROUTING_REVISION_DATE,
 SUBSTR(TO_CHAR(D.ROUTING_REVISION_DATE,'HH24:MI'),1,5) ROUTING_TIME,
 D.SCHEDULED_COMPLETION_DATE Scheduled_Complete,
 SI2.DESCRIPTION Bill_Ref_Description,
 SI3.DESCRIPTION Rout_Ref_Description, SUBSTR(TO_CHAR(D.SCHEDULED_COMPLETION_DATE,'HH24:MI'),1,5) COMPLETION_TIME,
 SG.SCHEDULE_GROUP_NAME,
 D.BUILD_SEQUENCE,
 WL.LINE_CODE
FROM MTL_SYSTEM_ITEMS_VL M,
 MTL_SYSTEM_ITEMS SI2,
 MTL_SYSTEM_ITEMS SI3,
 MFG_LOOKUPS LU,
 MTL_ITEM_LOCATIONS L,
 WIP_LINES WL,
 WIP_SCHEDULE_GROUPS SG,
 WIP_ENTITIES E,
 WIP_DISCRETE_JOBS D
WHERE E.WIP_ENTITY_NAME='17303'
AND E.ORGANIZATION_ID = 207
AND SG.SCHEDULE_GROUP_ID (+) = D.SCHEDULE_GROUP_ID
AND SG.ORGANIZATION_ID(+) = D.ORGANIZATION_ID
AND WL.LINE_ID (+) = D.LINE_ID
AND WL.ORGANIZATION_ID (+) = D.ORGANIZATION_ID
AND L.INVENTORY_LOCATION_ID(+) = NVL(D.COMPLETION_LOCATOR_ID,'-1')
AND L.ORGANIZATION_ID (+) = D.ORGANIZATION_ID
AND D.WIP_ENTITY_ID = E.WIP_ENTITY_ID
AND M.INVENTORY_ITEM_ID(+) = E.PRIMARY_ITEM_ID
AND LU.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND LU.LOOKUP_CODE = D.STATUS_TYPE
--AND D.STATUS_TYPE IN (1,3,4,6)
AND E.ENTITY_TYPE in (1,5)
AND D.ORGANIZATION_ID = 207
AND M.ORGANIZATION_ID(+) = 207
AND    SI2.ORGANIZATION_ID (+) = 207
AND    SI3.ORGANIZATION_ID (+) = 207
AND    SI2.INVENTORY_ITEM_ID (+) = D.BOM_REFERENCE_ID
AND    SI3.INVENTORY_ITEM_ID (+) = D.ROUTING_REFERENCE_ID
ORDER BY 1
/

Query to find WIP Job components:
SELECT WRO.WIP_ENTITY_ID WIP_Entity_ID,
       SI.SEGMENT1 ITEM,
       SI.DESCRIPTION CI_Description,
       WRO.OPERATION_SEQ_NUM CI_Op_Seq,
       SI.PRIMARY_UOM_CODE CI_UOM,
       WRO.REQUIRED_QUANTITY Required_Quantity,
       WRO.QUANTITY_ISSUED Quantity_Issued,
       ( SELECT NVL(SUM(OHQD.PRIMARY_TRANSACTION_QUANTITY),0)
           FROM MTL_ONHAND_QUANTITIES_DETAIL OHQD
           WHERE OHQD.ORGANIZATION_ID = WRO.ORGANIZATION_ID
             AND OHQD.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
         ) Quantity_On_Hand,
       WRO.DATE_REQUIRED Date_Required,
       ( SELECT ML1.MEANING
           FROM MFG_LOOKUPS ML1
           WHERE ML1.LOOKUP_TYPE = 'SYS_YES_NO'
             AND ML1.LOOKUP_CODE = WRO.MRP_NET_FLAG
         ) MRP_Net,
       ( SELECT ML2.MEANING
           FROM MFG_LOOKUPS ML2
           WHERE ML2.LOOKUP_TYPE = 'WIP_SUPPLY_SHORT'
             AND ML2.LOOKUP_CODE = WRO.WIP_SUPPLY_TYPE
         ) CI_Supply_Type,
       WRO.SUPPLY_SUBINVENTORY Subinv,
       ( SELECT WRO.SUPPLY_LOCATOR_ID
           FROM MTL_ITEM_LOCATIONS IL
           WHERE IL.INVENTORY_LOCATION_ID = WRO.SUPPLY_LOCATOR_ID
             AND IL.ORGANIZATION_ID = WRO.ORGANIZATION_ID
         ) C_Supply_Loc_Data
  FROM  MTL_SYSTEM_ITEMS_VL SI,
        WIP_REQUIREMENT_OPERATIONS WRO
 WHERE  WRO.WIP_ENTITY_ID = 31649
 AND   WRO.ORGANIZATION_ID = 207
 AND    SI.ORGANIZATION_ID = 207
 AND    SI.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
 AND    WRO.WIP_SUPPLY_TYPE <> 6
ORDER BY 1,2
/
Query to find WIP Job operations:
SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID,
       WO.OPERATION_SEQ_NUM Op_Seq,
       BD.DEPARTMENT_CODE Department,
       ML1.MEANING Count_Point_Type,
       ML2.MEANING Backflush,
       TO_CHAR(WO.LAST_UNIT_COMPLETION_DATE) Op_Cmpl_Date,
       TO_CHAR(WO.LAST_UNIT_COMPLETION_DATE, 'HH24:MI') Op_Cmpl_Time,
       WO.SCHEDULED_QUANTITY Op_Scheduled_Qty,
       (WO.QUANTITY_IN_QUEUE +
       WO.QUANTITY_RUNNING +
       WO.QUANTITY_WAITING_TO_MOVE +
       WO.QUANTITY_REJECTED +
       WO.QUANTITY_SCRAPPED) Qty_In_Operation,
       WO.QUANTITY_COMPLETED Op_Qty_Completed,
       WO.DESCRIPTION Description
FROM   MFG_LOOKUPS ML1,
       MFG_LOOKUPS ML2,
       BOM_DEPARTMENTS BD,
       WIP_DISCRETE_JOBS DJ,
       WIP_OPERATIONS WO,
       WIP_ENTITIES WE
WHERE WO.WIP_ENTITY_ID = 31649
AND    WO.ORGANIZATION_ID = 207
AND    DJ.ORGANIZATION_ID = 207
AND    BD.ORGANIZATION_ID = 207
AND    DJ.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND    WO.DEPARTMENT_ID = BD.DEPARTMENT_ID
AND    ML1.LOOKUP_TYPE = 'BOM_COUNT_POINT_TYPE'
AND    ML1.LOOKUP_CODE = WO.COUNT_POINT_TYPE
AND    ML2.LOOKUP_TYPE = 'SYS_YES_NO'
AND    ML2.LOOKUP_CODE = WO.BACKFLUSH_FLAG
ORDER BY WO.OPERATION_SEQ_NUM
/

Query to find WIP Job resources:
SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID,
       WOR.OPERATION_SEQ_NUM R_Op_Seq,
       WOR.RESOURCE_SEQ_NUM Res_Seq,
       WOR.SCHEDULE_SEQ_NUM Sched_Seq,
       BR.RESOURCE_CODE Res_Name,
       WOR.UOM_CODE Res_Uom,
       ML1.MEANING Basis_Type,
       WOR.USAGE_RATE_OR_AMOUNT Usage_Rate,
       NVL(WOR.ASSIGNED_UNITS,0) Assigned_Units,
       ML2.MEANING Scheduled,
       CA.ACTIVITY Activity,
       ML3.MEANING Autocharge,
       ML4.MEANING Standard_Rate,
       DECODE(WOR.BASIS_TYPE,1,WOR.USAGE_RATE_OR_AMOUNT *
       DJ.START_QUANTITY,2,WOR.USAGE_RATE_OR_AMOUNT) Standard_Units,
       WOR.APPLIED_RESOURCE_UNITS Units_Applied
FROM   MFG_LOOKUPS ML1,
       MFG_LOOKUPS ML2,
       MFG_LOOKUPS ML3,
       MFG_LOOKUPS ML4,
       BOM_RESOURCES BR,
       CST_ACTIVITIES CA,
       WIP_OPERATION_RESOURCES WOR,
       WIP_DISCRETE_JOBS DJ,
       WIP_ENTITIES WE
WHERE  WOR.WIP_ENTITY_ID = 31649
AND    WOR.ORGANIZATION_ID = 207
AND    DJ.ORGANIZATION_ID = 207
AND    BR.ORGANIZATION_ID = 207
AND    DJ.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID
AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND    WOR.RESOURCE_ID = BR.RESOURCE_ID
AND    WOR.ACTIVITY_ID = CA.ACTIVITY_ID (+)
AND    ML1.LOOKUP_TYPE = 'CST_BASIS'
AND    ML1.LOOKUP_CODE = WOR.BASIS_TYPE
AND    ML2.LOOKUP_TYPE = 'BOM_RESOURCE_SCHEDULE_TYPE'
AND    ML2.LOOKUP_CODE = WOR.SCHEDULED_FLAG
AND    ML3.LOOKUP_TYPE = 'BOM_AUTOCHARGE_TYPE'
AND    ML3.LOOKUP_CODE = WOR.AUTOCHARGE_TYPE
AND    ML4.LOOKUP_TYPE = 'SYS_YES_NO'
AND    ML4.LOOKUP_CODE = WOR.STANDARD_RATE_FLAG
ORDER BY WOR.OPERATION_SEQ_NUM, WOR.RESOURCE_SEQ_NUM
/

Query to find WIP Job reservations:
SELECT DJ.WIP_ENTITY_ID WIP_Entity_ID,
       WRV.DEMAND_SOURCE_LINE_NUMBER CR_Line,
       WRV.DEMAND_CLASS_CODE Reservation_Demand_Class,
       WRV.PRIMARY_QUANTITY  Reserved_Primary_Qty,
       WRV.PRIMARY_UOM_CODE  Reserved_Primary_UOM,
        WRV.RESERVED_LINE_UOM_QUANTITY Reserved_Line_Qty,
        WRV.ORDER_LINE_UOM_CODE Reserved_Line_UOM
FROM   MTL_SALES_ORDERS MS,
       WIP_DISCRETE_JOBS DJ,
       WIP_RESERVATIONS_V WRV,
       WIP_ENTITIES WE
WHERE  WRV.WIP_ENTITY_ID = 31649
AND    WRV.ORGANIZATION_ID = 207
AND    DJ.ORGANIZATION_ID = 207
AND    DJ.WIP_ENTITY_ID = WRV.WIP_ENTITY_ID
AND    DJ.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND    MS.SALES_ORDER_ID = WRV.DEMAND_SOURCE_HEADER_ID
ORDER BY  WRV.DEMAND_SOURCE_LINE_NUMBER

Standard API/Interface Needed to Perform Work Order Completion: WIP Assembly Completion Transaction For Discrete Jobs Can Be Performed Through Mtl_Transactions_Interface Table [ID 458801.1]


Goal

Need open API/Interface to perform Work Order (Job) completion activity:
a) WIP Assembly Completion For Non lot/Serial Controlled assembly item
b) WIP Assembly Completion For lot Controlled assembly item
c) WIP Assembly Completion For Serial Controlled assembly item
Solution


WIP completion transaction can be performed through mtl_transactions_interface and launching Inventory Transaction Manager.
- If the assembly is lot controlled then user needs to populate the lot information in mtl_transaction_lots_interface in additon to mtl_transactions_interface
- If the assembly is serial controlled then user needs to populate the serial information in mtl_serial_numbers_interface additon to mtl_transactions_interface
- If the costing method is Average Costing then in addition to lot/serial tables need to insert date into cst_comp_snap_interface table.

The following are the prototype scripts provided individually. Based on your requirement need to populate appropiate tables.

1. Data inserting into mtl_transactions_interface.

insert into mtl_transactions_interface
( source_code,
source_line_id,
source_header_id,
process_flag,
validation_required,
transaction_mode,
lock_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
inventory_item_id,
organization_id,
transaction_quantity,
primary_quantity,
transaction_uom,
transaction_date,
subinventory_code,
transaction_source_id,
transaction_source_type_id,
transaction_action_id,
transaction_type_id,
transaction_reference,
wip_entity_type,
operation_seq_num,
bom_revision_date,
routing_revision_date,
scheduled_flag,
Flow_schedule,
revision,
transaction_interface_id
)
values
(
'test', --source code
1, -- source line id
-1, -- source header id
1, -- process flag
1, -- validation required
3, -- transaction mode
2, -- lock flag
sysdate, -- last update date
1068, -- last updated by
sysdate, -- creation date
1068, -- created by
&itemid, -- inventory item id
&org, -- org id
20, -- transaction quantity
20, -- primary quantity
'Ea', -- transaction uom
sysdate, -- transaction date
'Stores', -- subinventory code
&tsourceid, -- transaction source id
5, -- transaction source type id
31, -- transaction action id
44, -- transaction type id
'testing', -- transaction reference
1, -- wip entity type
-1, -- operation seq num
sysdate, -- bom revision date
sysdate, -- routing revision date
2, -- scheduled flag
'N', -- flow schedule
'A' , -- Revision
interface_id
);

2. Data inserting into mtl_transaction_lots_interface

insert into mtl_transaction_lots_interface
(TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LOT_NUMBER,
TRANSACTION_QUANTITY,
ERROR_CODE,
PROCESS_FLAG)
values(interface_id,
'test', -- Source Code
1, -- Source Line id
sysdate, - Last update date
1318, -- Last updated by
sysdate, - Creation Date
1318, -- Created By
'SAB11513', -- Lot number Needs input
2, -- Transaction Quantity
NULL,
1 -- Process Flag
);

3. Data inserting mtl_serial_numbers_interface

insert into mtl_serial_numbers_interface
(TRANSACTION_INTERFACE_ID,
SOURCE_CODE,
SOURCE_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
ERROR_CODE,
PROCESS_FLAG)
values(interface_id,
'test', -- Source Code
1, -- Source Line id
sysdate, --Last Update date
1068, -- Last updated by
sysdate, --Creastion date
1068, --Created by
1068, -- Last update login
'&fm_serial', -- Needs input
'&to_serial', -- Needs input
NULL, --Error code
1 --Process Flag
);

4. Data inserting cst_comp_snap_interface
insert into CST_COMP_SNAP_INTERFACE
(TRANSACTION_INTERFACE_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
QUANTITY_COMPLETED,
PRIMARY_QUANTITY)
VALUES
(
INTERFACE_ID,
179726, --Job id
20,--Operatoin Seq No
SYSDATE, --Last update date
1318, --Last updated by
SYSDATE, --Creation date
1318, --Created by
2, --Quantity Completed
2 --Priamry Quantity
);


For further details, please refer Oracle Documentation
APIs and Open Interfaces Manual
Release 11i
Part No. A95955-03

Note:


TRANSACTION_SOURCE_ID---can be wip_entity_id of the job--You have to enter it..


TRANSACTION_SOURCE_ID or the corresponding flexfield segment columns
(DSP_SEGMENT1 to DSP_SEGMENT30) are required for all transaction source
types other than those that are user-defined. You should enter the foreign key ID
that points to the context table identified by the transaction source type.
*******


Interface_id is just an id for reference.. you can enter any number.. but you hace to give the same in the other tables as well..


Table 7–3 TRANSACTION_SOURCE_ID, Foreign Key References
Source Type Foreign Key Reference
Account GL_CODE_COMBINATIONS.CODE_COMBINATION_ID
Account Alias MTL_GENERIC_DISPOSITIONS.DISPOSITION_ID
Job or Schedule WIP_ENTITIES.WIP_ENTITY_ID
Sales Order MTL_SALES_ORDERS.SALES_ORDER_ID


Lot and Serial Transaction Detail Relationships
If you are transacting items under lot and/or serial control, you need to link the
lot/serial transaction detail rows to their parent row. You accomplish this by
populating MTL_TRANSACTIONS_INTERFACE.
TRANSACTION_INTERFACE_ID with a unique value to be used as the primary
key to link the child lot/serial rows. If the item is under lot control, you populate
the foreign key MTL_TRANSACTION_
LOTS_INTERFACE.TRANSACTION_INTERFACE_ID with the same value for all
child lot rows of the transaction and ensure that the total of all the lot quantities
adds up to the transaction quantity on the parent row. Similarly, if the item is under
serial control, you populate the foreign key MTL_SERIAL_NUMBERS_
INTERFACE.
TRANSACTION_INTERFACE_ID with the value in the parent row and ensure that
the total number of serial numbers adds up to the transaction quantity of the parent
row.
If the item is under both lot and serial control, the serial interface rows must belong
to lot parent rows. This means that the relationship between MTL_
TRANSACTIONS_INTERFACE and MTL_
TRANSACTION_LOT_NUMBERS remains the same as in the case where the item
is under only lot control, but you also need to populate each lot row with a unique
value in MTL_TRANSACTION_LOT_
NUMBERS.SERIAL_TRANSACTION_TEMP_ID. You then need to populate the
foreign key MTL_SERIAL_NUMBERS_INTERFACE.
TRANSACTION_INTERFACE_ID with the value in the parent lot row and ensure
that the total number of serial numbers adds up to the lot quantity in the parent
row.

Tuesday, 11 September 2012


over all view and the stages of Project implementation and the documents used  (AIM)

First Stage : Analysis
Document used :
RD 120 - Requirement Gathering - As Is To Be Process
BR 150 - Fit GAP Analysis (Its been done on the basis of above doc)

Second Stage : Designing
Document used :
BR 100 : Set up Document for the Functional Consultants
MD 200 : Set up Document for the Technical Consultants

Third Stage : Build - DEMO / PROTO TYPE
Document used :
BR 050

Fourth Stage : Testing
Document used :
TE 050

Fifth Stage : Go Live

Six Stage : Post Production

All these documents are the part of AIM (Oracle Application Implementation Methodology) but not all companies are using the same AIM instead they are using their own giving different names but the formats of all the documents are more or less same.

WIP-Questions

1. Which intra operation steps might you want to enable if you have a just-in-time production environment?
Ans: You would probably enable only Run and To Move to model your environment.

2. You cannot move more assemblies than are available at a routing operation.
a) True b) False

3.What are the three transaction available in WIP?
Ans: Material transaction, Move transaction, Resource transaction.

4.When the lot based resource gets charged?
Ans: When the first unit is moved, the lot based resources will be charged.

5.In a assembly, all the components have supply type as operational pull, they are serial controled. During move transaction, how we can select the serial numbers.
Ans: During move transaction, a window “ Backflush” will popup, there we can select serial numbers.

6.For an assembly, the move transaction does not allow me to complete in that screen iteself? What will be the problem.
Ans: The assembly will be serial control, therefore you have to complete it in completion transaction screen only

7.In an assembly, one of the item has supply type = bulk, what transaction should i perform.
Ans: no need to perform any transaction, it is just a label.

8.In profile option, WIP: Define discrete job, it has been defined as concurrent. While creating a Discrete order, what concurrent will be running?
Ans: WIP concurrent exploder.

9.Explain shopfloor status?
Ans: Shop floor status are control move transactions which can be assigned to interaoperations steps.

10.What are the two types of scheduling available in WIP?
Ans: Dynamic lead time offsetting and Detailed scheduling.

11. Which status in discrete job cannot be reversed?
Ans: Complete – No charges

12.Which field in Discrete job is non- editable before saving?
Ans: UOM

13.How can we add material and Resource in a discrete job? What is the implicaiton of this action on bills and routing?
Ans: In Job / Schedule details we can add materials and resource for the particulat order. This will be effect for this order only. Engineering BOM and Manufacturing BOM has no effect.

14.What is repetative schedule?
Ans: Repetative schedule builts an assembly on a line at specifited production rate and for specified number of days.

15.How can you reverse manual resource charges?
Ans: You can manually charge the same resource to the job or schedule and enter a negative value in the Units Applied field.

16.In Repetative schedule, while associating line and assembly, what will happen if we enter hourly prodcution rate higher than the maximum hourly rate defined in production line?
Ans: An error message will pupulate and wont allow to enter quantity greater than maximum hourly rate defined in production.

17.What are the different rescheduling options available in WIP?
Ans: Forward , Backward and Midpoint.

18.For a purchased item, how “processing lead time” is calculated in WIP?
Ans: WIP won't calculate “Processing lead time” for purchased item, we have to manually enter.

19.In OSP, what will happen if we receive quantity greater than PO quantity?
Ans: we cannot close the job, it will be shown in pending transaction.

20. In an assembly, same component is used in operation 10 (first) & 50 (last). How to specify this in a bill?
Ans: Use reference designators to generate a range of Notion numbers with referencing to the specific operations. This might be drawing conventions, general informations..Etc

21.I want to check what are all the components used for final assembly, their qty, the routing & availability of child assemblies in Inventory. After checking, I want to convert that into discrete job. What is the facility given by oracle to do the above scenario?
Ans: Use “Simulate discrete Job”

22.I use Substitute items in WIP material transaction?
a) True b) False

23.What are the controls available to generate requisitions during OSP process?
Ans: In WIP Parameters, Operation start time of OSP operation (Queue Stage in OSP),At the time of creation of Job/schedule, Manual

24.Can we add an operation after it starting prodction. ( partial move transaction)
a) Yes b) No

25.Explain the status “ Pending Mass loaded” in Repetative schedule
Ans: The out put of MRP will be “ Pending Mass loaded” , since MRP does not consider existing schedules.

Descriptive Questions


1.What are the types of WIP accounting class available in Oracle?
Ans: Standard, Asset non-standard, Expense non-standard, Repetitive, Standard lot based, Expense non standard lot based, Maintenance.

2.Client is Pen manufacturer, He uses discrete manufacturing for his production. he sells pens and refills (as spare), During manufacturing of pen, refill is one of the component and it is non stocked . What setup to be done to manufacture and sell both pen and refill ( instead of defining two seperate items for refill)
Ans: Create items Pen and refill with supply type push / pull
Creat bill for pen, attach refill as its one of the component,
Change the default supply tpe to Phantom. ( this will effect this particular bill only)

3.What is the difference between “ Complete” and “ Close” ?
Ans:
S.No Complete Close
1 When completed, the WIP value gets decremented and Inventory value gets incremented When closed, the details are transferred to GL
2 Valuation accounts gets hit Variance account gets hit

4.In a Routing, an items’ manufacturing process is as per below..
10- Assembly
20 -Cleaning
30- Labeling
40- Inspection
50- Packing ,Here 40, Inspection is an occasional operation, not always be done. & 20. Cleaning is always done but non-critical. Hence these are requested not to be done while performing “ Move transaction “ in-order to have less manual effort. How to designate this requirement?
Ans:In standard operation, 20. Cleaning – Disable “ Count Point” & Select “ Auto charge”. This will not default this particular operation in the move transaction. This way can skip that particular operation.
40. Inspection, Count point & Auto charge to be disabled. Hence whenever occasional need calls, Add this operation to the Job by Job/Schedule Operations.

5.Create Discrete JOB for 10 nos, complete only 5 leave the remaining is Rejection. Hence this rejected qty not to be considered while booking sales order? What setup to be done so that can be avoided?
Ans: Create a Sub-Inventory and disable “Allow reservation – No”. (Call it as Rejection sub inventory)
Assign this DJ – Routing __ completion sub inv, so as rejected components can be moved here while Making “Move transaction”
Can check in the Availability window in the sales order screen, Available to reserve qty.

6.What are some reasons Oracle Work in Process would prevent you from performing a completion transaction?
Ans: There is no available quantity in the To Move step of the last operation in the routing.
–There is a shop floor status preventing movement from the To Move step of the last operation in the routing.
–The job/schedule status prevents transactions.
–The item is not transact able

7.what is the difference between Standard and Non-standard discrete jobs?
Ans:
S.No Standard DJ Non-Standard DJ
1 Standard DJ controls material and operation required to build assemblies and collect manufacturing cost Non-standard DJ controls miscellaneous activities eg: Rework, repair
2 Assembly in mandatory Assembly is not mandatory
3 We can “ Firm” Cannot check “ Firm”
4 Bill reference field is grey out Can enter item / assembly
5 Revision is not optional Revision is mandatory if the assembly has revision
6 Both Valuation and variance will be hit Only Variance will be hit

8.What are the status of DJ ( both user status and Processing status)
Ans
S.No User status Processing Status
1 Unreleased Pending routing load
2 released Failed routing load
Complete Pending bill load
Complete – No charge Failed bill load
Cancel Pending scheduling
Hold Pending closed
Closed Failed close


9.Can i complete an assembly with out order? Explain in detail

Ans: Yes, In Workorder less completion we can complete an assembly with out order.
Ther are 4 options available: WIP assembly compeltion , WIP assembly return, WIP assembly scrap, WIP return from scrap.

10.How to unclose an order, what are the prequestes for forecast

Ans: In Close Discrete jobs form query with the order number. Tools--> Unclose.
The prequestes are a) Job status should be closed.
b) Accounting period should be open

Thanks & Regards
Natchirajan.S

Saturday, 8 September 2012


Important questions to be remembered by a Oracle Consultant

What is Set of Books ? What are the four conditions when you change your SOBs?Chart of Accounts, Currency & Calendar
It is similar to the bank passbook, used to record all the financial transactions. There could be one primary set of books and many reporting set of books. Defining COA, currency and calendar are pre-requisite to define the SOB.

What is an Invoice? How many types of invoices ar...
There are 9 types of Invoices in AP: Standard, Credit Memo, Debit Memo, Prepayment, Withholding Tax, Mixed Invoice, Expense Report, PO default and Quick Match.
There are 6 types of Invoice in AR : Invoice, Credit Memo, Debit Memo, Chargeback, Deposit and Guarantee.

What is the difference between data conversion and data migration?
Data Migration is upgrading from one version to another version fro e.g. from 11.5.9 to 11.5.10.. Data Conversion is receiving data from a legacy system or receiving data from another module ( e.g. PA), validating it and converting it to ur our the target module using the standard import program.

Set ups need to approve invoice in AP
For approving an invoice in AP we have to raise one invoice for raising an invoice we have some mandatory fields like supplier, supplier number, Payment terms, distribution sets, date, payment method, bank, payment document etc. After defining all the above we can raise a invoice, There's no need of defining the approval group it s not a mandatory,

What is a profile option? What are the types?The profile options are available to every product in Oracle Applications. For each profile option, we give a brief overview of how Oracle Application Object Library uses the profile's setting.
1) User Level
2) Responsibility Level
3) Application Level
4) Site Level.

What are _ALL tables in Oracle Apps?
_ALL tables in oracle applications give the info about multiple organizations info about these tables.

What is descriptive flex field and what is the useDescriptive Flexfileds r used to add additional informations, and these values r stored to the column attributes. Go to sysadmin application-flex fields-descriptive-segments
What is a FlexField? What are Descriptive and Key...
A flexfield is made up of sub-fields or segments.. A flexfield appers on ur form as a pop-up window that contains a prompt for each segment. Each segment has a name and a set of valid values..
Two types of Flex field..
Key Flexfield: Key flexfield are flexible enough to let any organization use the code scheme they want wothout programming.Key flexfield can be used to represent codes that is made up of meaningful segment to identify GL a/c Part no. and other business entities..Oracle app store these codes in key flexfields..
Descriptive Flexfield: They provide customizable "expansion space" on ur forms. You can use desc flexfields to tract additional information important and unique to ur business that would not otherwise be captured by the form.

What Credit memo / Debit MemoBoth Credit and Debit Memo are used for adjusting the suppliers balance and both is a negative amount. Debit Memo is created by you and send to the supplier and credit memo is recieved from the supplier and record it.

What is a request Sets? how to create a request Se..
Request set is a group of requests.It is made to perform the request in a certain sequence.Request se can be created from System administrator responsibility.

What are the types of Concurrent Managers?Ca...
There are many concurrent managers, each monitoring the flow within each apps area.
but there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatability checks.
We cannot delete a concurrent manager... but we can disable it... but it's not recommended.

What is Multiple Organization technical architecture?
Multi Organization :Using a single installation of any oracle application product , to support any number of an organization even it has different Set Of Books Bussiness Level(It Secures Human TransactionInformation) Set Of Books(It secures all Transaction Information In Oracle Genral Ledger) Leagel Entry(All leagal Information in the Organization) Operating Unit(It Uses Order management, Cash managment,AR,AP...it may be Sales Office , Division,Department) Inventry Organization(Inventry Details)
before multi org existed, we could have only one bussiness group,set of books, legal entity, operating unit in one installation of oracle E-bussiness suite, and now with the multi organisation structure in place, we can have multiple bussiness groups, set of books, legal entity and operating unit and the best part is once these are set up intercompany accounting is automatically taken care of, say for example i have Two inventory organisation, and these use a common set of books(to start with) , now if we have sales order on one inventory organisation A1 and if that item is not available in A1, and we have inventory for the item in Inventory org A2, we need to take the order in A1 as internal order and run the order import concurrent program and for the item in inventory A1(org assignment) we need to set up the source(purchase tab) as inventory organisation A2,and also specify shipping network between A1 and A2, and once we have run the order import program and Oracle applications now imports the order to organisation A2, with the ship to address as A1 location. and after you perform pick release and pick confirm process, and run auto invoice in A2, it automatically sends invoice to inventory organisation A1 and now we can receive the item in Inventory organisation A1 from organisation A2.
and all transactions are taken care of, courtesy multi organisation structure.

What are different period types ?Year Quarter Month Week

What are the different types of files used in SQLDifferent types of files are Data File,Control File,Discard File,Bad file

What is Oracle Financials?
Oracle Financials products provide organizations with solutions to a wide range of long- and short-term accounting system issues. Regardless of the size of the business, Oracle Financials can meet accounting management demands with:
o Oracle Assets: Ensures that an organization's property and equipment investment is accurate and that the correct asset tax accounting strategies are chosen.
o Oracle General Ledger: Offers a complete solution to journal entry, budgeting, allocations, consolidation, and financial reporting needs.
o Oracle Inventory: Helps an organization make better inventory decisions by minimizing stock and maximizing cash flow.
o Oracle Order Entry: Provides organizations with a sophisticated order entry system for managing customer commitments.
o Oracle Payables: Lets an organization process more invoices with fewer staff members and tighter controls. Helps save money through maximum discounts, bank float, and prevention of duplicate payment.
o Oracle Personnel: Improves the management of employee- related issues by retaining and making available every form of personnel data.
o Oracle Purchasing: Improves buying power, helps negotiate bigger discounts, eliminates paper flow, increases financial controls, and increases productivity.
o Oracle Receivables:. Improves cash flow by letting an organization process more payments faster, without off-line research. Helps correctly account for cash, reduce outstanding receivables, and improve collection effectiveness.
o Oracle Revenue Accounting: Gives an organization timely and accurate revenue and flexible commissions reporting.
o Oracle Sales Analysis: Allows for better forecasting, planning. and reporting of sales information.

What is the difference between Fields and FlexFields?
A field is a position on a form that one uses to enter, view, update, or delete information. A field prompt describes each field by telling what kind of information appears in the field, or alternatively, what kind of information should be entered in the field.
A flexfield is an Oracle Applications field made up of segments. Each segment has an assigned name and a set of valid values. Oracle Applications uses flexfields to capture information about your organization. There are two types of flexfields: key flexfields and descriptive flexfields.

Difference between Conversions and Interfaces.
Conversion is to bring the data from other (non-OraApps) system to Oracle-Application system. This is one time activity (bring account details, transactions, orders, receipts and so on). So conversion itself is a project.
Interfaces are Concurrent PRograms/Program sets (pl/sql, pro*C, unix scripts, executables) . Interfaces are basically of two types (Oracle supplied (vanila programs eg:Autoinvoice in AR) and created by developer).
Hope this clarifies the concepts.
Conversion means one time activityinterface means periodic activityexample:- to transfer the data old version to new version it is called conversion to transfer the data from staging table to interface table it is called interface , it is process on every day or every hour ........

Which module is not a multiorg? 
General Ledger and CRM Foundation Modules

What are the types of Concurrent Managers?Can we delete a Concurrent Manager?
There are many concurrent managers, each monitoring the flow within each apps area.
but there are 3 MASTER CONCURRENT MANAGERS:
1. Internal Conccurent Manager (ICM): This is the one which monitors all other CMs
2. Standard Manager (SM) : This takes care of report running and batch jobs
3. Conflict Resolution Manager (CRM): checks concurrent program definitions for incompatability checks.
We cannot delete a concurrent manager... but we can disable it... but it's not recommended.

Trading community Architecture(TCA)
ICA (Internet Computing Architecture)

What are AP setup steps ?setup---->suppliers....>invoices..>payments.....>reports......>periods.....>transfer to GL

What does US mean in appl_top/au/11.5.0/reports/US?
US is the language directory specifying that the source files to be placed under this dir is for English/American Language
This is the standard of apps directory structure that for very language you implement oracle apps there should be a language specific folder

What are SQLCODE and SQLERRM and why are they important for PL/SQL developers? 
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.

What are the Back ground processes in Oracle and what are they.
There are basically 9 Processes.They do the house keeping activities for the Oracle and are common in any system.The various background processes in oracle are:
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files.This is required since the data is not written whenever a transaction is commited.
b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance startup.This is useful for recovery from system failure
d)Process Monitor(PMON) :: The Process Monitor peforms process recovery when user Process fails. Pmon Clears and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the
most recent checkpoint
f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecture
i) Lckn :: We can have upto 10 lock processes for inter instance locking in parallel sql.

How many types of Sql Statements are there in Oracle
There are basically 6 types of sql statments.They are
a) Data Defination Language(DDL) :: The DDL statments define and maintain objects and drop objects.
b) Data Manipulation Language(DML) :: The DML statments manipulate database data.
c) Transaction Control Statements :: Manage change by DML
d) Session Control :: Used to control the properties of current session enabling and disabling roles and changing .e.g, Alter Statements,Set Role
e) System Control Statements :: Change Properties of Oracle Instance .e.g, Alter System
f) Embedded Sql :: Incorporate DDL,DML and T.C.S in Programming Language.e.g, Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close

What is a Transaction in Oracle
A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statment and ends when it is explicitly commited or rolled back.

Key Words Used in Oracle
The Key words that are used in Oracle are ::
a) Commiting :: A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements.
b) Rollback :: A transaction that retracts any of the changes resulting from SQL statements in Transaction.
c) SavePoint :: For long transactions that contain many SQL statements, intermediate markers or savepoints are declared. Savepoints can be used to divide a transactino into smaller points.
d) Rolling Forward :: Process of applying redo log during recovery is called rolling forward.
e) Cursor :: A cursor is a handle ( name or a pointer) for the memory associated with a specific stament. A cursor is basically an area allocated by Oracle for executing the Sql Statement. Oracle uses an implicit cursor statement for Single row query and Uses Explcit cursor for a multi row query.
f) System Global Area(SGA) :: The SGA is a shared memory region allocated by the Oracle that contains Data and control information for one Oracle Instance.It consists of Database Buffer Cache and Redo log Buffer.
g) Program Global Area (PGA) :: The PGA is a memory buffer that contains data and control information for server process.
g) Database Buffer Cache :: Databese Buffer of SGA stores the most recently used blocks of datatbase data.The set of database buffers in an instance is called Database Buffer Cache.
h) Redo log Buffer :: Redo log Buffer of SGA stores all the redo log entries.
i) Redo Log Files :: Redo log files are set of files that protect altered database data in memory that has not been written to Data Files. They are basically used for backup when a database crashes.
j) Process :: A Process is a 'thread of control' or mechansim in Operating System that executes series of steps.

What are Procedure,functions and Packages
* Procedures and functions consist of set of PL/SQL statements that are grouped together as a unit to solve a specific problem or perform set of related tasks.
* Procedures do not Return values while Functions return one One Value
*Packages :: Packages Provide a method of encapsulating and storing related procedures, functions, variables and other Package Contents

What are Database Triggers and Stored Procedures
Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules.We can have the trigger as Before trigger or After Trigger and at Statement or Row level.
e.g:: operations insert,update ,delete 3
before ,after 3*2 A total of 6 combinatons
At statment level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12.
Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures :: Stored Procedures are Procedures that are stored in Compiled form in the database.The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.

How many Integrity Rules are there and what are they
There are Three Integrity Rules. They are as follows ::
a) Entity Integrity Rule :: The Entity Integrity Rule enforces that the Primary key cannot be Null
b) Foreign Key Integrity Rule :: The FKIR denotes that the relationship between the foreign key and the primary key has to be enforced.When there is data in Child Tables the Master tables cannot be deleted.
c) Business Integrity Rules :: The Third Intigrity rule is about the complex business processes which cannot be implemented by the above 2 rules.

What are the Various Master and Detail Relation ships.
The various Master and Detail Relationship are
a) NonIsolated :: The Master cannot be deleted when a child is exisiting
b) Isolated :: The Master can be deleted when the child is exisiting
c) Cascading :: The child gets deleted when the Master is deleted.

What are the Various Block Coordination Properties
The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deffered with No Auto Query
The operator must navigate to the detail block and explicitly execute a query

What are the Different Optimisation Techniques
The Various Optimisation techniques are
a) Execute Plan :: we can see the plan of the query and change it accordingly based on the indexes
b) Optimizer_hint ::
set_item_property('DeptBlock',OPTIMIZER_HINT,'FIRST_ROWS');
Select /*+ First_Rows */ Deptno,Dname,Loc,Rowid from dept
where (Deptno > 25)
c) Optimize_Sql ::
By setting the Optimize_Sql = No, Oracle Forms assigns a single cursor for all SQL statements.This slow downs the processing because for evertime the SQL must be parsed whenver they are executed.
f45run module = my_firstform userid = scott/tiger optimize_sql = No
d) Optimize_Tp ::
By setting the Optimize_Tp= No, Oracle Forms assigns seperate cursor only for each query SELECT statement. All other SQL statements reuse the cursor.
f45run module = my_firstform userid = scott/tiger optimize_Tp = No

Metalink Note numbers for Oracle Order Management Implementation Issues
--------------------------------------------------------------------------------
Here are a few Metalink Note numbers useful with Order Management Implementation Issues.
Oracle Receivables Auto Invoice Troubleshooting Guide 219926.1
Autoinvoice Is Unable to Derive A GL Date for A Transaction 178299.1
How does one book an Return Material Authorization ( RMA) to only receive product back in inventory without issuing a credit ?391088.1
How To Import Order With Price Adjustments 333429.1
System Copy Parameter 'Call Line Dff Extension API' is not Working 312563.1
ORA-20120: 'Currency Conversion Rate Not Defined In The System' 382839.1
Cannot Approve Internal Requisitions Ora-20002 300745.1
What Are The Steps Required To Setup An Internal Requisition In Purchasing? 406312.1
How To Limit Users To Only Be Able To View/Query A Certain Order Type 364255.1
Understanding Logic of Credit Checking and Manually Released Holds 209478.1
How to Generate the COGS Account from the Order Type 414314.1
How to Verify/Start WorkFlow Background Engine? 112935.1
How to find out the Invoice Number for a Sales Order Line ? 297457.1
How does one restrict the list of values(LOV) for the field 'price list' on the sales order header to limit the LOV by Customer entered on the sales order header?434123.1
How does one setup user action ""Raise Integration Event'"" to send an e-mail on any update action of Unit Selling Price?422354.1
How to default a null value for ordered date for specific order types, while defaulting sysdate for other order types. 276603.1
How To Link Receivables Credit Memo Transaction Types To OM Transaction Types? 402355.1
How to Set Up Data Collection for Order Management ATP Availability 401158.1
How to Avoid One Shipment Creating Two Invoices 396121.1
How to restrict the Customer LOV of Sales Order Form 272864.1
How to Make Order Line Notes Show on Shipping Transactions Form 290728.1
How to Turn On the Add Customer Functionality in OEXOEORD - Sales Order Form 164139.1
How To Setup a Special View Only Menu on OEXOEORD 223079.1
R12: How To Setup Multi Org Access Control (MOAC) for Order Management? 393634.1

Monday, 3 September 2012

OM: Credit Memo Not Generated for RMA / Sales Order Return [ID 1085864.1]


Applies to:
Oracle Order Management - Version: 12.1.2 and later   [Release: 12.1 and later ]
Information in this document applies to any platform.

Symptoms
 Credit Memo is not getting generated for Sales Order Returns. The Return Material Authorization (RMA) Line is closed with Invoice Interface as "Not Eligible"
 Cause
 One cause for the above scenario is that Profile:  "OM: Generate credit for rejected returns" is set to 'No'
 Solution
 Please review the setup of Profile "OM: Generate credit for rejected returns"





The Profile Option can be set at Site, Responsibility and/or Application levels.

The possible values for this profile are "Yes" and "No".

If the profile is set to "Yes", when a Deliver Transaction is done in Inventory for the Rejected Quantity for an RMA Line, the Customer will be given credit for the Rejected Quantity.

If the profile is set to "No", when a Deliver Transaction is done in Inventory for the Rejected Quantity for an RMA Line, the Customer will NOT be given credit for the Rejected Quantity.

Sunday, 2 September 2012


While making Receipt, Receiving Transaction Processor Landing into following error:-

RVTTH-010: Subroutine rvtthins() returned error

Cause: Subroutine rvtthins() returned an internal error.

Action: Note this error number and the actions you are trying to perform. Contact your system administrator for assi
RVTTH-192.rvtthins: Subroutine ORA-20001: APP--20110: Encountered the error in trigger JAI_RCV_RT_BRIUD_T1ORA-20001: APP--20110:
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.JAI_RCV_RT_BRIUD_T1", line 490
ORA-040 returne
Concurrent program RVCTP returned error

What is the cause and how to resolve the same?

Solution
The above error encountered becuase the PO against which the receipt is being created does not have the tax currency for the tax being used. In order to resolve the error please perform the following steps:-

1. Please copy and delete the existing tax applied.
2. Reapply the taxes ensuring that the tax currency is getting defaulted
3. Apply and Save
4. Retest the issue
5. Migrate the solution to appropriate environment