, , ,

Write message in Log or Out file

When ever, I have to create new package, I normally code a procedure to write any information in the log or Out files of the program.

The following is the Procedure which I use it in any package. This is standard in some companies coding. This save a lot of time and this makes easy to other people who goes through your program.

PROCEDURE write(p_type IN VARCHAR2, p_message IN VARCHAR2)
IS
/************************************************************************
Purpose : Procedure writes to the log file or output file
based on type.O=Output File, L=Log File
*************************************************************************/

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;

The above write procedure can be used in other Procedure/Function in the package to write any information in the Log or Out files. 

PROCEDURE main(errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_par1 IN NUMBER
)
IS
v_errbuf VARCHAR2(1000) := NULL;
v_retcode NUMBER := 0;
v_file_name VARCHAR2(100);
BEGIN

v_retcode := 0;

v_file_name := fnd_profile.value(‘XYZ’);

IF v_file_name IS NULL
THEN
write(‘O’,’Profile XYZ is not defined or the value is not set’);
retcode := 2;
RETURN;
END IF;
END;

Note:- In the above Procedure, I am using the write Procedure and returning 2 for the retcode (0 – Complete, 1- Warning and 2 will be for Error).

Note:- This is one time process and you will realise, how much helpful it will be when ever you have to right something in log or out file.

More then information, this post is more of suggestion. Hope this post will help you make your code easy. 

/by
, , ,

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.

/by
, , ,

Hard Reservation for the Standard Sales order Line against Internal Sales order

Note:- You need to have the ASCP Module Installed for the following script to work.

Note:- ASCP decide whether it have to create the PO or Internal sales order
based on the Sourcing Rules defined for a Item in the Inventory Organization.

When any sales Order created for any particular item for any Quantity and if that
quantity is not available then ASCP would created the PO (Purchase order to get the
Material required for third party or could create the Internal requisition which would
again creates the Internal sales order) and It has to reserve the quantity which
the Purchase Order or Internal sales order has.

Here, we are considering if ASCP has decided to create the Internal Sales Order.

You can use the following script.

/************************************************************
Hard Reservation for Standard Sales Order line against Internal Sales Order

Created by : Dhanunjaya Sadhu
Creation Date : 

Primarily applicable for oracle ASCP implemented projects
Can be customized based on the requirement change.

For others, the input values can be passed directly without CURSOR.

************************************************************/
PROCEDURE create_iso_hard_reservation
(p_line_id IN NUMBER,
p_delivery_date IN DATE,
p_ship_from_org_id IN NUMBER)

IS
p_rsv inv_reservation_global.mtl_reservation_rec_type;
p_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_msg_count NUMBER;
x_msg_data VARCHAR2(240);
x_rsv_id NUMBER;
x_dummy_sn inv_reservation_global.serial_number_tbl_type;
x_status VARCHAR2(1);
x_qty NUMBER;
l_inventory_item_id number;
l_source_header_id number;
l_reservation_qty number;
l_error_message VARCHAR2(300);
l_order_number NUMBER;

CURSOR C_ISO_RESV(c_line_id)
—– Cursor to select ISO pegged to the standard sales order line
IS
SELECT
DISTINCT ool.HEADER_ID,
ool.LINE_ID,
mfp.ALLOCATED_QUANTITY
FROM OE_ORDER_LINES_ALL ool,
OE_ORDER_HEADERS_ALL ooh ,
MSC_DEMANDS md,
MSC_SUPPLIES ms,
MSC_FULL_PEGGING mfp,
MSC_PLANS mp
WHERE mp.PLAN_ID=mfp.PLAN_ID
AND mp.plan_id=ms.plan_id
AND mp.plan_id=md.plan_id
AND ool.HEADER_ID=ooh.HEADER_ID
AND ooh.ORIG_SYS_DOCUMENT_REF = ms.ORDER_NUMBER
AND ool.SOURCE_DOCUMENT_LINE_ID=ms.PO_LINE_ID
AND ms.TRANSACTION_ID=mfp.TRANSACTION_ID
AND md.DEMAND_ID=mfp.DEMAND_ID
AND md.SALES_ORDER_LINE_ID=c_line_id;

BEGIN

— initialize required input params to call reservation api–

— get the item id–
SELECT DISTINCT INVENTORY_ITEM_ID
INTO l_inventory_item_id
FROM mtl_system_items
WHERE segment1=( SELECT ordered_item
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID=p_line_id);

— get the sales_order_id from mtl_sales_orders table
—which should be passed as one of the input params

SELECT SALES_ORDER_ID,segment1
INTO l_source_header_id,l_order_number
FROM mtl_sales_orders
WHERE SEGMENT1=(SELECT TO_CHAR(oh.order_number)
FROM OE_ORDER_HEADERS_ALL oh,OE_ORDER_LINES_ALL ol
WHERE oh.header_id=ol.header_id
AND ol.line_id=p_line_id);

FOR C_REC IN C_ISO_RESV(p_line_id)

———– sending the sales order line to the cursor variable

LOOP

—– Initialize al required inputs to perform HARD RESERVATION——

—–fnd_global.APPS_Initialize(2572700,20559,300);
—–p_user_id, p_resp_id, p_resp_appl_id

p_rsv.reservation_id := NULL;
—- cannot know
—- will be generated once reservation is successful
p_rsv.requirement_date := p_delivery_date;
p_rsv.organization_id := p_ship_from_org_id; —————————mtl_parameters.organization id
p_rsv.inventory_item_id := l_inventory_item_id;
—————————mtl_system_items.Inventory_item_id
p_rsv.demand_source_type_id := inv_reservation_global.g_source_type_oe;
p_rsv.demand_source_name := NULL;
——————————————also u can save it as ‘SALES ORDER’ ;
p_rsv.demand_source_header_id :=l_source_header_id;—————————–mtl_sales_orders.sales_order_id for ordernumber
p_rsv.demand_source_line_id := p_line_id;
———————————— oe_order_lines.line_id
p_rsv.primary_uom_code := ‘EA’;
p_rsv.primary_uom_id := NULL;
p_rsv.reservation_uom_code := ‘EA’;
p_rsv.reservation_uom_id := NULL;
p_rsv.reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.primary_reservation_quantity := C_REC.ALLOCATED_QUANTITY;
p_rsv.autodetail_group_id := NULL;
p_rsv.external_source_code := NULL;
p_rsv.external_source_line_id := NULL;
p_rsv.supply_source_type_id := inv_reservation_global.g_source_type_internal_ord;
p_rsv.supply_source_header_id :=C_REC.HEADER_ID;
——————————-Header id of Internal sales order
p_rsv.supply_source_line_id :=C_REC.LINE_ID;
———————————Line id of Internal sales order
p_rsv.supply_source_name := NULL;
p_rsv.supply_source_line_detail := NULL;
p_rsv.revision := NULL;
p_rsv.subinventory_code := NULL;
————————–subinventory code can be mentioned based on your customization.
p_rsv.subinventory_id := NULL;
p_rsv.locator_id := NULL;
————————–17930; — A10-L2-B09
p_rsv.lot_number :=NULL;
—————————‘200801225083 ‘;
p_rsv.lot_number_id := NULL;
p_rsv.pick_slip_number := NULL;
p_rsv.lpn_id := NULL;
p_rsv.attribute_category := NULL;
p_rsv.attribute1 := NULL;
p_rsv.attribute2 := NULL;
p_rsv.attribute3 := NULL;
p_rsv.attribute4 := NULL;
p_rsv.attribute5 := NULL;
p_rsv.attribute6 := NULL;
p_rsv.attribute7 := NULL;
p_rsv.attribute8 := NULL;
p_rsv.attribute9 := NULL;
p_rsv.attribute10 := NULL;
p_rsv.attribute11 := NULL;
p_rsv.attribute12 := NULL;
p_rsv.attribute13 := NULL;
p_rsv.attribute14 := NULL;
p_rsv.attribute15 := NULL;
p_rsv.ship_ready_flag := NULL;
p_rsv.demand_source_delivery := NULL;

— Validating whether the pegging is done WITH AN ISO—
—- FInally performing Hard Reservation ———

inv_reservation_pub.create_reservation
(
p_api_version_number => 1.0
, x_return_status => x_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_rsv_rec => p_rsv
, p_serial_number => p_dummy_sn
, x_serial_number => x_dummy_sn
, x_quantity_reserved => x_qty
, x_reservation_id => x_rsv_id
);

IF x_status=’S’ THEN
— HARD RESERVATION IS DONE SUCCESFULLY —–
COMMIT;

dbms_output.put_line(‘reservation succesful’);
dbms_output.put_line(‘reservation id: || x_rsv_id);

ELSE

—— getting error msgs from fnd_msg_pub——

if(nvl(x_msg_count,0) = 0) then
dbms_output.put_line(‘no message return’);
else
for I in 1..x_msg_count LOOP
l_error_message := fnd_msg_pub.get(I, ‘F’);
end LOOP;
end if;

— HARD RESERVATION API fails —

dbms_output.put_line(‘Reservation API Error Message: ‘||l_error_message);

END IF;

EXCEPTION
WHEN OTHERS THEN — In case of any pl/sql error
l_error_message := SQLERRM;
dbms_output.put_line(‘Plsql Error Message: ‘||l_error_message);

END create_iso_hard_reservation;

Hope you find the above document helpful in your work.

/by
, ,

Initialize Apps,

To run or execute the Oracle API from the back-end, we need to Initialize the Apps first.

We have API to Initialize the Apps.

To initialize apps using the API, we need to pass few IN parameter values.

The following is the script prepared to initialize apps based on the
Application, User-name and Responsibility name given.

Example:-

DECLARE
   l_appl_id             NUMBER;
   l_appl_name           VARCHAR2 (100) := 'PA';
   l_user_id             NUMBER;
   l_user_name           VARCHAR2 (100) := 'OPERATIONS';
   l_responsibility_id   NUMBER;
   l_resp_name           VARCHAR2 (200)
                                       := 'Projects, Vision Operations (USA)';
BEGIN

-- To get the Application ID of given Application.
   SELECT application_id
     INTO l_appl_id
     FROM fnd_application
    WHERE application_short_name = l_appl_name;

-- To get the User ID information of  given user
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = l_user_name;

-- To get the Resp ID information of the given responsibility.
   SELECT responsibility_id
     INTO l_responsibility_id
     FROM fnd_responsibility_tl
    WHERE responsibility_name = l_resp_name AND application_id = l_appl_id;

--Initialixze the Application to use the API. 
   fnd_global.apps_initialize (l_user_id, l_responsibility_id, l_appl_id);
END;

I Hope the above script would help understanding about the Initializing the Apps API.
/by
, , ,

Registering Executable, Concurrent Program etc from backend

For Registering the Executable from backend.

PROMPT Creating Concurrent Executable XXM_XYZ_EMPLOYEE ……
PROMPT

BEGIN
FND_PROGRAM.executable(‘XXM_XYZ_EMPLOYEE’ — executable
, ‘ABC’ — application
, ‘ABC_XYZ_EMPLOYEE’ — short_name
, ‘Executable for Migrating Employee’ — description
, ‘PL/SQL Stored Procedure’ — execution_method
, ‘abc_xyz_employee_pkg.create_employee’ — execution_file_name
, ” — subroutine_name
, ” — icon_name
, ‘US’ — language_code
, ”); 

END;
/

For Registering the Concurrent program for the Executable file created.

PROMPT Creating Concurrent Program ABC_XYZ_EMPLOYEE …
PROMPT

BEGIN
FND_PROGRAM.register(‘ABC Data migration program for HR-Employee’ — program
, ‘XXM’ — application
, ‘Y’ — enabled
, ‘ABC_XYZ_EMPLOYEE’ — short_name
, ‘Data Migration Program for Migrating HR-Employee’ — description
, ‘ABC_XYZ_EMPLOYEE’ — executable_short_name
, ‘XYZ’ — executable_application
, ” — execution_options
, ” — priority
, ‘Y’ — save_output
, ‘Y’ — print
, ” — cols
, ” — rows
, ” — style
, ‘N’ — style_required
, ” — printer
, ” — request_type
, ” — request_type_application
, ‘Y’ — use_in_srs
, ‘N’ — allow_disabled_values
, ‘N’ — run_alone
, ‘TEXT’ — output_type
, ‘N’ — enable_trace
, ‘Y’ — restart
, ‘Y’ — nls_compliant
, ” — icon_name
, ‘US’); — language_code
END;
/

For attaching the concurrent program to the request group.

PROMPT Adding Concurrent program to request group ‘XYZ Group’
PROMPT

BEGIN
FND_PROGRAM.add_to_group(‘XYZ_ABC_EMPLOYEE’ — program_short_name
, ‘XYZ’ — application
, ‘XYZ Group’ — Report Group Name
, ‘XYZ’); — Report Group Application

END;
/

Submit the Concurrent Program from Backend.
Submit Program is the Function. where. we need to pass the Application name, Program, stage etc.

function FND_SUBMIT.SUBMIT_PROGRAM
(application IN varchar2,
program IN varchar2,
stage IN varchar2,
argument1,…argument100)
return boolean;

PROMPT Submitting the Concurrent Program from backend.
PROMPT

BEGIN
–fnd_global.apps_initialize( user_id => , resp_id => , resp_appl_id => );

l_success := fnd_submit.set_request_set(‘‘, ‘‘);

IF l_success = TRUE
THEN

l_success := fnd_submit.submit_program(
‘‘ –Application
,’‘ — program
,’‘ –Stage
,’NEW’ — Arument1
,’10’ -Arument2
,’N’
,’N’
,’N’);

— If fail then for the log record.

IF (NOT l_success)
THEN
fnd_file.put_line(
fnd_file.LOG
,’Request submission of stage STAGE FAILED’);
ELSE
fnd_file.put_line(
fnd_file.LOG
,’Request submission of stage STAGE10 SUCCESSFUL’);
END IF;
l_req_id := fnd_submit.submit_set(NULL, FALSE );

IF (l_req_id <= 0) THEN fnd_file.put_line( fnd_file.LOG ,’REQUEST SET SUBMISSION FAILED’); ELSE fnd_file.put_line( fnd_file.LOG ,’REQUEST SET SUBMITTED SUCCESSFULLY: ‘ || l_req_id); END IF; END IF; END;

I hope the above document is helpful to all of you for understanding the backend process and API’s to register the program and submit from the backend.

/by