You are html tracking Visitor

Tuesday, April 14, 2009

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.

Thursday, April 2, 2009

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.