Install Base creation for all the Sales Order for which it is missing

Install Base creation for all the Sales Order for which it is missing:-
------------------------------------------------------------------------------

You can using the following package to create the IB (Install base) for the sales Order It has missed.

Normally, we enable the Install base option in the "Master Item" form for the Items for which we want to track. If we enable this option, then when we create the sales order with the Item for which Install base option is check then, you would see the IB got created.

CREATE OR REPLACE PACKAGE alloracletech_ib_pkg
IS
PROCEDURE create_install_base ( errbuf OUT VARCHAR2
, retcode OUT NUMBER);
PROCEDURE WRITE (p_type IN VARCHAR2, p_message IN VARCHAR2);
END alloracletech_ib_pkg;
/

CREATE OR REPLACE PACKAGE BODY alloracletech_ib_pkg
IS

PROCEDURE create_install_base ( errbuf OUT VARCHAR2
, retcode OUT NUMBER)
IS
/************************************************************************
Purpose : This procedure creates IB for all the order which are missed.
*************************************************************************/
l_header_id NUMBER;
l_mtl_txn_id NUMBER;
l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;

CURSOR c_ib
IS
SELECT -- oeh.order_number,
oel1.line_id
-- oeh.creation_date,
-- msib1.inventory_item_id,
-- msib1.segment1
FROM oe_order_lines_all oel1,
mtl_system_items_b msib1,
oe_order_headers_all oeh
WHERE oel1.ordered_item = msib1.segment1
AND msib1.comms_nl_trackable_flag = 'Y'
AND msib1.shippable_item_flag = 'Y'
AND msib1.organization_id = 22
AND oeh.header_id = oel1.header_id
AND oel1.flow_status_code = 'CLOSED'
AND oel1.line_category_code = 'ORDER'
AND oeh.order_type_id = 1008
-- AND oel1.line_id IN (5599900, 5742086)
-- AND oeh.order_number IN ( )
AND oeh.shipping_method_code IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM csi.csi_item_instances cii
WHERE cii.last_oe_order_line_id = oel1.line_id);
BEGIN
FOR i IN c_ib
LOOP
BEGIN
SELECT transaction_id
INTO l_mtl_txn_id
FROM mtl_material_transactions
WHERE trx_source_line_id = i.line_id
AND transaction_type_id = 33;

-- dbms_output.put_line(i.order_number||' '||i.line_id);
csi_inv_txn_hook_pkg.posttransaction
(p_header_id => l_header_id,
p_transaction_id => l_mtl_txn_id,
x_return_status => l_return_status
);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
WRITE ('L', 'No Transaction ID for the Line ID : ' || i.line_id );
WHEN TOO_MANY_ROWS THEN
WRITE ('L','More then one Transaction ID for the Line ID : ' || i.line_id );
WHEN OTHERS THEN
WRITE ('L',
'Error in LineID :' || i.line_id || CHR (10) || SQLCODE || ' : ' || SQLERRM
);
END;
END LOOP;
END create_install_base;

/************************************************************************/
PROCEDURE WRITE (p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
/************************************************************************
Purpose : This procedure writes to the output file or log file depending
on the parameter p_type passed.
*************************************************************************/
BEGIN
IF p_type = 'L'
THEN
fnd_file.put_line (fnd_file.LOG, p_message);
ELSIF p_type = 'O'
THEN
fnd_file.put_line (fnd_file.output, p_message);
END IF;
END WRITE;
END alloracletech_ib_pkg;
/

Use the following to Execute the Procedure.

begin
alloracletech_ib_pkg.create_install_base;
end;
/

The following query can be used to find out whether Install Base (IB) created or not for the Order line.

select * from csi.csi_item_instances
where last_oe_order_line_id IN (Your Order Line ID);

Example:-

select * from csi.csi_item_instances
where last_oe_order_line_id IN (6912858, 6912859, 6912860);

I hope you find the above information help full.

Note:- There are few things which are hard-corded like order_type_id = 1008 etc, these are specific to my instance setup. It may vary for your Instance setup. Change the values accordingly.

Note:- I have tested above script in the 11i Instances.

Application Implementation Methodology

Application Implementation Methodology:-
--------------------------------------------------

I have received lot of request from many people asking for the AIM document information. For the best of my Knowledge, I have prepared the following information. If you have any more information related to AIM document, please share it across with Oracle Community.

Application Implementation Methodology in short referred as AIM.

AIM- Oracle’s Application Implementation Methodology is a proven approach to implement Oracle Applications.

AIM defines an organization’s business needs at the beginning of the project and maintains their visibility through out the implementation

AIM Phases-AIM project is conducted in phases. The phases provide quality and control checkpoints to coordinate project activities that have a common goal.

The phases are

1) Definition- During this phase, we plan the project ,review the organization’s business objectives ,understand the business processes and evaluate the feasibility of meeting those objectives under time, resources and budget constraints. The emphasis is on building an achievable work plan and introducing guidelines on how the organization will work to achieve the objectives.To achieve an early understanding of current business operations and future processes,the team also performs baseline and process modeling during this phase.If business change is required,then the high level future process designs are drawn.The goal is to identify the future business and system requirements,propose a future business model and determine the current application and information technology architecture.The information gathered provides input to the downstream activities in subsequent phases.All business requirements are associated with planned future business processes.So sharing an accurate understanding of the requirements is a critical success factor to the project.

2) Operations Analysis- The project team develops the business requirement scenarios based on deliverable drawn out of the definition stages.These business requirement scenarios are used assess the level of fit between the detailed business requirement and standard application functionality.Gaps are identified and new proposed solutions are developed.Proposed solutions for gaps evolve into detailed design during a solution design phase.

3) Solution Design- The purpose of the solution design is to develop the detailed design to meet the future business requirements.

4) Build-The coding and testing of all customizations,data conversions and interfaces are done in this phase.Business system testing is performed to validate that the functionality meets the business requirements.If customizations,extensions and conversions are not required ,build phase is still important because it includes business system test..In confererence room pilot(CRP),the business system test validates the new system in an environment that resembles the production.

5) Transition-During transition,the project team deploys the new system into the organization.All the elements must come together to tranisition successfully to actual production.During transition users perform an acceptance test of the new system.

6) Production-It marks the last phase of the implementation and the beginning of system support cycle.

AIM Processes

1) Business process architecture (BP)
Business process architecture addresses the organization’s business processes and aligns them with the business requirement and applications to be implemented. High level process designs (BP0.070) and future process model (BP0.080) are a part of this.

2) Business requirements Definition (RD)
Business Requirements Definition defines the business needs that must be met by the implementation project .This is RD 0.020 which is the current business needs.

3) Business Requirement Mapping (BR)
Business requirement mapping compares the future business requirements to standard oracle application functionality and identifies the gaps. Business requirements scenarios (RD0.050) are then mapped to oracle functionality.

4) Application and Technical Architecture (TA)
During Application and Technical Architecture, the information systems are designed to reflect the business vision

5) Module Design and Build (MD)
Module design and build produces custom application extensions for gaps in functionality identified during BR.Custom application extensions include program modules( forms, reports, alerts and database triggers) that must be designed, built and tested before being transitioned in the new system.

6) Data Conversions (CV)
Data conversion defines the tasks and deliverable to convert legacy data to oracle application base tables.

7) Documentation (DO)
It defines the documentation requirements for the project and establishes which of the documentation tasks are required.

8) Business System Testing (TE)
Early in the project life-cycle, Business System Testing focuses on
linking test requirements back to business requirements and securing
project resources needed for testing.
Business System Testing provides a formal integrated approach to
Testing

9) Performance Testing (PT)
Performance Testing enables you to define, build, and execute a
performance test. The Performance Testing team defines the scope of testing and relates it to point-in-time snapshots of the transactions expected in the real
production system

10) Adoption and Learning (AP)
Adoption and Learning establishes a measurement system that provides
an evaluation of organizational performance to help make sure that
expectations are met during implementation and after production
cut-over

11) Production Migration (PM)
Production Migration moves the company, system, and people to the
new enterprise system.

I hope you find this information helpful.

Permission names for specific User

Permission names for specific User:-
------------------------------------------

With the following query you can know what all the permissions you have for any Specific Application User.

I have used this query when working on some custom page. Based on the permissions given to the User, you can provide some additional functionality to the Users.

Note:- I have commented out the permission name. in the below query. You can use this condition if you want to search specific to the permission name.

SELECT fnd.user_id
, fnd.description
, p.permission_name
FROM jtf_auth_principals_b u
, jtf_auth_principal_maps pm
, jtf_auth_role_perms rp
, jtf_auth_permissions_b p
, fnd_user fnd
WHERE fnd.user_id='Your USER ID'
AND fnd.user_name=u.principal_name
-- AND p.permission_name ='CSI_SEARCH_PRODUCT_VIEW'
AND u.jtf_auth_principal_id = pm.jtf_auth_principal_id
AND pm.jtf_auth_parent_principal_id = rp.jtf_auth_principal_id
AND rp.jtf_auth_permission_id = p.jtf_auth_permission_id

Note:- You can know the USER ID of specific to some USER from the following Query.

select * from fnd_user where USER_NAME like 'User Name';

Get the Assembly Details

Get the Assembly Details:-
------------------------------

The following query would get all the details about the Assembly

SELECT a.assembly_item_id
, b.component_item_id
, c.segment1 component_item
, c.shelf_life_code
, c.shelf_life_days
, c.lot_control_code
,a.assembly_item_id
,a.organization_id
FROM bom_bill_of_materials_v a,
bom_inventory_components_v b,
mtl_system_items_b c
WHERE a.bill_sequence_id = b.bill_sequence_id
AND b.component_item_id = c.inventory_item_id
AND a.organization_id = c.organization_id
-- AND a.organization_id = l_organization_id
-- AND a.assembly_item_id = l_item_id;

Get all the Assembly completion Lot Numbers

Get all the Assembly completion Lot Numbers: -
-------------------------------------------------------

The following query gets the information about the Lot Numbers of Assembly Completion.

Note:- I have commented "mmt.organization_id = your_organization_id" condition. If you want to get the results specific to some inventory organization then, you can use this condition. Organization_id is representing the Inventory Organization.

SELECT mmt.transaction_id
, mmt.transaction_date
, mmt.inventory_item_id
, mmt.organization_id
, mtn.lot_number
, msi.shelf_life_days
, msi.shelf_life_code
, msi.segment1
FROM mtl_material_txns_val_v mmt
,mtl_transaction_lot_numbers mtn
,mtl_lot_numbers mln
, mtl_system_items_b msi
WHERE mmt.transaction_id = mtn.transaction_id
AND mmt.inventory_item_id = msi.inventory_item_id
AND mmt.organization_id = msi.organization_id
AND mtn.lot_number = mln.lot_number
AND mtn.organization_id = mln.organization_id
AND mmt.transaction_type_name = 'WIP Completion'
AND mmt.transaction_action = 'Assembly completion'
AND NVL (mln.attribute1, 'N') <> 'Y'
-- AND mmt.organization_id = your_organization_id
AND mmt.transaction_date >= mmt.transaction_date
AND mmt.transaction_date <= mmt.transaction_date
AND mmt.inventory_item_id >= mmt.inventory_item_id
AND mmt.inventory_item_id <= mmt.inventory_item_id;

Retrieves all PO transaction quantities

Retrieves all PO transaction quantities:-
----------------------------------------------

Following Query would get all the PO Numbers, Item ID, Quantity Order, Received, Canceled etc. The same information can be found in the PO order form from the Front end Application.

SELECT
POL.ITEM_ID "Inventory Item ID",
POLL.SHIP_TO_LOCATION_ID "Ship to Location ID",
poll.quantity "Quantity",
poll.Quantity_received "Quantity Received",
poll.QUANTITY_cancelled "Quantity Canceled",
POH.SEGMENT1 "PO Order Number"
FROM po_headers_all poh
, po_lines_all pol
, po_line_locations_all poll
WHERE poh.po_header_id=pol.po_header_id
AND pol.po_line_id=poll.po_line_id
AND poll.po_header_id=pol.po_header_id
AND poh.authorization_status='APPROVED'
AND poh.type_lookup_code='STANDARD'
AND NVL(poh.cancel_flag,'N') ='N'
AND POL.ITEM_ID IS NOT NULL
AND NVL (poll.quantity, 0) > (NVL (Quantity_received,0) + NVL (Quantity_cancelled,0))

To identify the items at Pricelist level against active modifiers

To identify the items at Price-list level against active modifiers :-
---------------------------------------------------------------------------

When we are define Modifier in the OM module. We can define Modifier at the particular Item or all the Items in the Price List/Item Category etc.

The following query will give the Modifier Name and other details of Particular Item of Price List:-
-------------------------------------------------------------------------------------------------

SELECT DISTINCT qpa.list_header_id "Modifier Header ID",
qlh.COMMENTS "Modifier Name (Description)",
qpa.list_line_id "Modifier Line ID",
qll.start_date_active "Modifier start date",
qll.end_date_active "Modifier end date",
qq.qualifier_attr_value "Price List ID",
qllv.product_attr_value "Inventory Item ID",
msi.segment1 "SKU",
qll.arithmetic_operator_type "Application Method",
qll.operand "Value",
qll.product_precedence "Precedence",
qll.incompatibility_grp "Incompatibility Group",
qll.pricing_group_sequence "Bucket"
FROM qp_pricing_attributes qpa,
mtl_system_items_b msi,
qp_qualifiers_v qq,
qp_list_headers_b qlh,
qp_list_lines_v qllv,
qp_modifier_summary_v qll
WHERE qpa.product_attribute = 'PRICING_ATTRIBUTE3'
AND qq.qualifier_attribute = 'QUALIFIER_ATTRIBUTE4'
AND qllv.product_attribute = 'PRICING_ATTRIBUTE1'
AND qllv.product_attr_value = msi.inventory_item_id
AND qllv.list_header_id = qq.qualifier_attr_value
AND qlh.list_header_id = qq.list_header_id
AND qpa.list_header_id = qll.list_header_id
AND qpa.list_line_id = qll.list_line_id
AND qll.list_header_id = qlh.list_header_id
AND qlh.active_flag = 'Y'
AND msi.segment1 = 'Your Item Name'
AND SYSDATE BETWEEN qll.start_date_active AND qll.end_date_active
AND TRUNC (NVL (qllv.end_date_active, SYSDATE)) <= TRUNC (SYSDATE)
-- AND rownum <=10