Tuesday, 8 September 2015

query to get the code of package, procedure,function

SELECT replace(replace(text,CHR(13),''),CHR(10),' ') FROM all_source where name like 'JM_MAKE856_NEW' order by line;

Monday, 31 August 2015

CREATE THE DATA DEFINITION TEMPLATE IN XML PUBLISHER

Create the data definition template

<?xml version="1.0" encoding="WINDOWS-1252" ?><dataTemplate name="ABINVDATA" description="JM Inventory Data" defaultPackage="AB_BURSTING_CP_SUB_PKG" Version="1.0">

<dataQuery>

<sqlStatement name="Q_AB_INV_DET">
<![CDATA[

select CREATION_DATE,SEGMENT1,SEGMENT2,DESCRIPTION from mtl_system_items where attribute15='900' and creation_date>='01-MAY-13' and segment2!='PB'and inventory_item_id not in (select inventory_item_id from mtl_system_items where attribute15='900' and creation_date>='01-MAY-13' and segment2!='PB' and organization_id<>5) and inventory_item_status_code='Active' order by creation_date,segment2,segment1

]]>
</sqlStatement>
</dataQuery>
<dataStructure>

<group name="G_AB_INV_DET" source="Q_AB_INV_DET">
<element name="CREATION_DATE" value="CREATION_DATE" />
<element name="SEGMENT1" value="SEGMENT1" />
<element name="SEGMENT2" value="SEGMENT2" />
<element name="DESCRIPTION" value="DESCRIPTION" />
</group>
</dataStructure>
<dataTrigger name="afterReport" source="AB_BURSTING_CP_SUB_PKG.AfterReport()"/>

</dataTemplate>

Save the file with name ABINVDATA.xml

-------------------------------------------------------------------------------------------------------

Create data definition in xml publisher and attach the data definition template to the data definition

Navigation: XML Publisher Administrator -> Data Definitions -> Create Data Definition
---------------------------------------------------------------------------------------------------------
Create Concurrent Program to generate the Data XML output.
Navigation: System Administrator-> Concurrent-> Program->Define

AND THEIR EXECUTABLE NAME AS "XDODTEXE"

Assignment of concurrent program to responsibility

Navigation: System Administrator-> Security-> Responsibility->Request

Creation of RTF from msword.
attach the RTF to the program from "data template"

Creation of Control file for bursting.
<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">
<xapi:globalData location="stream" />
<xapi:request select="/ABINVDATA/LIST_G_AB_BOM_RWO">
<xapi:delivery>
<xapi:email id="123" server="mail3.abc.com" port="25" from="abc.123@gmail.com" reply-to="abc.123@gmail.com">
<xapi:message id="123" to="xx.aa@yahoo.com,yy.aa@yahoo.com" cc="xx.aa@yahoo.com,yy.aa@yahoo.com"
attachment="true" content-type="html/text" subject="control file of inv data">

Hello,

Please find the attached latest “control file of inv data” spread sheet.

Thanks,
xyz.

</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document output="ABINVDATA.xls" output-type="excel" delivery="123">
<xapi:template type="rtf" location="/usr/tmp/ABINVDATA.rtf" />
</xapi:document>
</xapi:request>

</xapi:requestset>

save the file as control.xml

Monday, 24 August 2015

JOINS BETWEEN mtl_system_items,mtl_item_categories, mtl_categories_b, Ra_customer_trx_all, AND Ra_customer_trx_lines_all

SELECT DISTINCT MSI.SEGMENT1,RCTL.CREATION_DATE,MCB.SEGMENT4
FROM mtl_system_items MSI,
mtl_item_categories MIC,
mtl_categories_b MCB,
Ra_customer_trx_all RCTA,
Ra_customer_trx_lines_all RCTL
WHERE MSI.INVENTORY_ITEM_ID=MIC.INVENTORY_ITEM_ID
AND MIC.CATEGORY_ID=MCB.CATEGORY_ID
AND RCTA.CUSTOMER_TRX_ID=RCTL.CUSTOMER_TRX_ID
AND RCTL.INVENTORY_ITEM_ID=MSI.INVENTORY_ITEM_ID
AND MCB.SEGMENT4 LIKE 'ABC%'
AND RCTL.CREATION_DATE LIKE '%15';

Thursday, 20 August 2015

XML BURSTING

XML BURSTING

<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">
<xapi:globalData location="stream" />
<xapi:request select="/XXREP/LIST_G_CUST_NUMBER">
<xapi:delivery>
<xapi:email id="123" server="mail.oracleappsql.com" port="25" from="abc@oracleappsql.com" reply-to="abc@oracleappsql.com">
<xapi:message id="123" to="xyz@oracleappsql.com,def@oracleappsql.com" cc="abcd@oracleappsql.com,support@oracleappsql.com"
attachment="true" content-type="html/text" subject="customer numbers">
Hello xxx,

Please find the attached customer numbers.

Thanks
ORACLEAPPSQL TEAM.
</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document output="customer numbers.pdf" output-type="pdf" delivery="123">
<xapi:template type="rtf" location="/usr/tmp/XXREP.rtf" />
</xapi:document>
</xapi:request>
</xapi:requestset>

SAVE THE FILE AS .XML FORMAT

HERE LOCATION IS DEFINED IN
NAVIGATION :XML PUBLISHER ADMINISTRATION -> HOME -> ADMINISTRATION

HERE XXREP IS THE REPORT NAME THE REPORT RTF IS STORED IN THE LOCATION FROM ABOVE NAVIGATION .

HZ TABLES AND THEIR JOININGS IN ORACLE APPS

ORACLE APPS TABLES

Wednesday, 19 August 2015

CONCURRENT REQUEST USING TRIGGER IN ORACLE APPS

CREATE OR REPLACE TRIGGER XX_TRIGGER
AFTER UPDATE
ON TABLE_NAME
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
NEW.ATTRIBUTE!=old.ATTRIBUTE and
NEW.VALUE!=OLD.VALUE)

DECLARE
l_appl_id NUMBER;
l_appl_name VARCHAR2 (100) := 'WIP';
l_user_id NUMBER;
l_user_name VARCHAR2 (100) := 'USER_NAME;
l_responsibility_id NUMBER;
l_resp_name VARCHAR2 (200) := 'WORK IN PROCESS';
ln_request_id NUMBER;

PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

-- To get the Application ID of given Application. ---- 401
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 and language='US';

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

l_layout:= FND_REQUEST.add_layout(
template_appl_name => 'OM',
template_code => 'TEMPLATE_CODE',
template_language => 'en',
template_territory => '00',
output_format => 'PDF');

ln_request_id :=
fnd_request.submit_request ('WIP',--application_short_name
'XXABVCS',--conc prog short name
'XX WIP CONC PROGRAM',--description of conc program
NULL,
FALSE,
--parameter1
--parameter2
1150583
)
;
COMMIT;

/*IF ln_request_id = 0
THEN
DBMS_OUTPUT.PUT_LINE ('Concurrent Request Failed to Submit.');
ELSE
DBMS_OUTPUT.PUT_LINE('Successfully Submitted the Concurrent Request. Request Id: '||ln_request_id);
END IF;*/

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||SQLERRM);
NULL;
END;

how to raise concurrent request using trigger in oracle apps

CREATE OR REPLACE TRIGGER XX_TRIGGER
AFTER UPDATE
ON TABLE_NAME
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
NEW.ATTRIBUTE!=old.ATTRIBUTE and
NEW.VALUE!=OLD.VALUE)

DECLARE
l_appl_id NUMBER;
l_appl_name VARCHAR2 (100) := 'WIP';
l_user_id NUMBER;
l_user_name VARCHAR2 (100) := 'USER_NAME;
l_responsibility_id NUMBER;
l_resp_name VARCHAR2 (200) := 'WORK IN PROCESS';
ln_request_id NUMBER;

PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

-- To get the Application ID of given Application. ---- 401
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 and language='US';

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

ln_request_id :=
fnd_request.submit_request ('WIP',--application_short_name
'XXABVCS',--conc prog short name
'XX WIP CONC PROGRAM',--description of conc program
NULL,
FALSE,
--parameter1
--parameter2
1150583
)
;
COMMIT;

/*IF ln_request_id = 0
THEN
DBMS_OUTPUT.PUT_LINE ('Concurrent Request Failed to Submit.');
ELSE
DBMS_OUTPUT.PUT_LINE('Successfully Submitted the Concurrent Request. Request Id: '||ln_request_id);
END IF;*/

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||SQLERRM);
NULL;
END;

Thursday, 13 August 2015

How to create dff for subinventories in oracle apps

DEFINING SUBINVENTORY

GO TO INVENTORY SUPER USER RESPONSIBILITY

SETUP->ORGANIZATION->SUBINVENTORIES

NOW CLICK ON NEW

NAME : TYPE THE NAME THAT SHOULD BE UNIQUE

Description: Short description of the subineventory

Status: By default it takes as Active

Default Cost Group: Attach the Cost group here.

Type: Storage or Receiving, as per the requirement.

WE CAN ALSO ADD DFF IN THIS FORM FROM THE NAVIGATION

GO TO INVENTORY SUPER USER RESPONSIBILITY

SETUP->FLEXFIELDS->DESCRIPTIVE ->SEGMENTS

Query by using

APPLICATION :- Inventory

Title :- Subinventories

now click on segments.

DEFINING SUBINVENTORY IN ORACLE APPS

DEFINING SUBINVENTORY

GO TO INVENTORY SUPER USER RESPONSIBILITY

SETUP->ORGANIZATION->SUBINVENTORIES

NOW CLICK ON NEW

NAME : TYPE THE NAME THAT SHOULD BE UNIQUE

Description: Short description of the subineventory

Status: By default it takes as Active

Default Cost Group: Attach the Cost group here.

Type: Storage or Receiving, as per the requirement.

WE CAN ALSO ADD DFF IN THIS FORM FROM THE NAVIGATION

GO TO INVENTORY SUPER USER RESPONSIBILITY

SETUP->FLEXFIELDS->DESCRIPTIVE ->SEGMENTS

Query by using

APPLICATION :- Inventory

Title :- Subinventories

now click on segments.

Wednesday, 12 August 2015

How to check Trigger is fired or not

How to check Trigger is fired or not ?

create table temp(n varchar2(1000), n1 date);

After that create the following procedure:--

CREATE OR REPLACE PROCEDURE trace_error(Msg varchar2) IS
pragma autonomous_transaction;
BEGIN
insert into temp Values(Msg, SYSDATE);
commit;
end;
/

Then call this procedure in u r trigger like the following..

trace_error('Executing trigger');

we can call whole trigger at different places like
trace_error('Executing trigger1');
trace_error('Executing trigger2');
.... and so on

then check till where the code is executing in temp table by using
select * from temp;

wip_move_transaction NAVIGATION FOR JOB

Wip is performed when one process completes and moves to the next stage is termed as wip move transaction.

NAVIGATION-> WORK IN PROCESS ->MOVE TRANSACTIONS ->MOVE TRANSACTIONS

NEXT ENTER THE JOB NAME

ENTER THE SEQUENCE NUMBER AND OTHER DETAILS

CLICK ON SAVE
NOW THE DATA IS TRANSFERRED IN TO WIP MOVE TRANSACTION TABLE.

WE CAN CHECK WITH THE QUERY

select * from wip_move_transactions where to_char(CREATION_DATE,'DD-MON-RR')=trunc(sysdate);

WIP MOVE TRANSACTION

Wip is performed when one process completes and moves to the next stage is termed as wip move transaction.

NAVIGATION-> WORK IN PROCESS ->MOVE TRANSACTIONS ->MOVE TRANSACTIONS

NEXT ENTER THE JOB NAME

ENTER THE SEQUENCE NUMBER AND OTHER DETAILS

CLICK ON SAVE
NOW THE DATA IS TRANSFERRED IN TO WIP MOVE TRANSACTION TABLE.

WE CAN CHECK WITH THE QUERY

select * from wip_move_transactions where to_char(CREATION_DATE,'DD-MON-RR')=trunc(sysdate);

Friday, 7 August 2015

ware house locator change

declare
X_RETURN_STATUS varchar2(1000);
X_MSG_COUNT number;
X_MSG_DATA VARCHAR2(1000);
CURSOR a1 IS
SELECT MSI.SEGMENT1
||'.'
||MSI.SEGMENT2 STYLE,
MSI.INVENTORY_ITEM_ID,
MIL.SEGMENT1
||'.'
||MIL.SEGMENT2
||'.'
||MIL.SEGMENT3
||'.'
||MIL.SEGMENT4
||'.'
||MIL.SEGMENT5 LOC,
SUBSTR(MOH.SUBINVENTORY_CODE, 1,20) SUBINVENTORY_CODE,
MOH.LOCATOR_ID,
msi.primary_uom_code,
SUM(MOH.PRIMARY_TRANSACTION_QUANTITY) PRIMARY_TRANSACTION_QUANTITY,
SUM(MOH.TRANSACTION_QUANTITY) TRANSACTION_QUANTITY
FROM MTL_ONHAND_QUANTITIES_DETAIL MOH,
MTL_SYSTEM_ITEMS MSI,
MTL_ITEM_LOCATIONS MIL
WHERE MOH.SUBINVENTORY_CODE ='WAREHOUSE' and MIL.SEGMENT1='WAREHOUSE'
AND MSI.INVENTORY_ITEM_ID =MOH.INVENTORY_ITEM_ID
AND msi.segmENT1='abc' and msi.SEGMENT2='xx'
AND MOH.ORGANIZATION_ID =MSI.ORGANIZATION_ID
AND MSI.ORGANIZATION_ID =40
AND MIL.INVENTORY_LOCATION_ID=MOH.LOCATOR_ID
AND MIL.SUBINVENTORY_CODE =MOH.SUBINVENTORY_CODE
AND MOH.ORGANIZATION_ID =MIL.ORGANIZATION_ID
AND MIL.ENABLED_FLAG ='Y'
GROUP BY MSI.SEGMENT1,
MSI.SEGMENT2,
MSI.INVENTORY_ITEM_ID,
MIL.SEGMENT1
||'.'
||MIL.SEGMENT2
||'.'
||MIL.SEGMENT3
||'.'
||MIL.SEGMENT4
||'.'
||MIL.SEGMENT5,
MOH.SUBINVENTORY_CODE,
MOH.LOCATOR_ID,
msi.primary_uom_code
ORDER BY 1,2;

BEGIN
FOR S IN a1 LOOP
BEGIN
SUBINVENTORY_TRANSFER(S.INVENTORY_ITEM_ID, 4, S.SUBINVENTORY_CODE, S.LOC, NULL,'PC', S.PRIMARY_TRANSACTION_QUANTITY,'Subinventory Transfer',
S.SUBINVENTORY_CODE,'PK.G.2.3.WH', X_RETURN_STATUS, X_MSG_COUNT, X_MSG_DATA,NULL);
END;
end LOOP;
end;

--commit;

QUERY TO FIND REQUEST GROUP

SELECT cpt.user_concurrent_program_name "Concurrent Program Name",
DECODE(rgu.request_unit_type,
'P', 'Program',
'S', 'Set',
rgu.request_unit_type) "Unit Type",
cp.concurrent_program_name "Concurrent Program Short Name",
rg.application_id "Application ID",
rg.request_group_name "Request Group Name",
fat.application_name "Application Name",
fa.application_short_name "Application Short Name",
fa.basepath "Basepath"
FROM fnd_request_groups rg,
fnd_request_group_units rgu,
fnd_concurrent_programs cp,
fnd_concurrent_programs_tl cpt,
fnd_application fa,
fnd_application_tl fat
WHERE rg.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND rg.application_id = fat.application_id
AND fa.application_id = fat.application_id
AND cpt.language = USERENV('LANG')
AND fat.language = USERENV('LANG')
AND cpt.user_concurrent_program_name = 'ABC'; -- Here You need to pass the concurrent program name

BOM QUERY ,BOM TABLE JOINS

BOM QUERY

SELECT
MSI.SEGMENT1||'.'||MSI.SEGMENT2 STYLE,MSI.SEGMENT2,MSI.CREATION_DATE,BOR.ASSEMBLY_ITEM_ID,MSI.INVENTORY_ITEM_ID ITEMID,BOS.DEPARTMENT_ID,BD.DEPARTMENT_CODE
,BR.RESOURCE_SEQ_NUM,BRS.RESOURCE_CODE,BR.USAGE_RATE_OR_AMOUNT,
BR.USAGE_RATE_OR_AMOUNT_INVERSE,
BOR.routing_sequence_id , BOS.operation_seq_num,-- BR.resource_seq_num,
--BRS.resource_code ,
BR.assigned_units, BR.basis_type,BR.schedule_flag,to_char(BOS.effectivity_date,'DD-MON-YYYY HH24:MI:SS') edate--BOS.effectivity_date--,BR.USAGE_RATE_OR_AMOUNT_INVERSE

FROM BOM_OPERATIONAL_ROUTINGS BOR,
BOM_OPERATION_SEQUENCES BOS,
BOM_DEPARTMENTS BD,
BOM_OPERATION_RESOURCES BR,
BOM_RESOURCES BRS,
BOM_STANDARD_OPERATIONS BSO,
BOM_BILL_OF_MATERIALS BOM,
BOM_INVENTORY_COMPONENTS BIC

WHERE BOM.ASSEMBLY_ITEM_ID=22445
AND BOR.ORGANIZATION_ID=42
AND BOR.ASSEMBLY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
AND BOM.ORGANIZATION_ID = BOR.ORGANIZATION_ID
AND BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
and bos.ROUTING_SEQUENCE_ID = bor.ROUTING_SEQUENCE_ID
AND BOS.STANDARD_OPERATION_ID=BSO.STANDARD_OPERATION_ID
AND BOS.DISABLE_DATE is null
AND BD.DEPARTMENT_ID = BOS.DEPARTMENT_ID
AND BRS.ORGANIZATION_ID=BOR.ORGANIZATION_ID
AND BRS.ORGANIZATION_ID=BOM.ORGANIZATION_ID
AND BD.ORGANIZATION_ID = BRS.ORGANIZATION_ID
AND BOS.OPERATION_SEQUENCE_ID = BR.OPERATION_SEQUENCE_ID
AND BR.RESOURCE_ID = BRS.RESOURCE_ID;

SUB_INVENTORY_TRANSFER PROCEDURE

create or replace procedure SUBINVENTORY_TRANSFER (P_ITEM_ID in varchar2, --'985877'

P_ORGANIZATION_ID in number, -- 4

P_FROM_SUBINV in varchar2

P_FROM_LOC in varchar2,

P_LOT_NO in varchar2,

P_UOM_CODE in varchar2,

P_QUANTITY in number, --6 this should un reserved quantity then only sub inventory transfer will success

P_TRANSACTION_TYPE in varchar2, --'Subinventory Transfer'

P_TO_SUBINV in varchar2,

p_to_loc IN VARCHAR2,

x_return_status OUT VARCHAR2,

x_msg_count OUT NUMBER,

x_msg_data OUT VARCHAR2,

p_lpn_id IN NUMBER)

l_transaction_id NUMBER;

l_loc_id NUMBER;

l_from_loc_id NUMBER;

l_to_loc_id NUMBER;

l_trn_type_id NUMBER;

l_segment1 VARCHAR2 (150);

l_segment2 VARCHAR2 (150);

l_segment3 VARCHAR2 (150);

l_segment4 VARCHAR2 (150);

l_segment5 VARCHAR2 (150);

l_segment6 VARCHAR2 (150);

l_segment7 VARCHAR2 (150);

l_segment8 VARCHAR2 (150);

l_segment9 VARCHAR2 (150);

l_segment10 VARCHAR2 (150);

l_expiration_date DATE;

l_trans_count NUMBER;

lc_result NUMBER;

BEGIN

SELECT mtl_material_transactions_s.NEXTVAL

INTO l_transaction_id

FROM DUAL;

EXCEPTION

WHEN OTHERS

THEN

l_transaction_id := NULL;

END;

BEGIN

SELECT transaction_type_id

INTO l_trn_type_id

FROM mtl_transaction_types

WHERE transaction_type_name = p_transaction_type;

EXCEPTION

WHEN OTHERS

THEN

l_trn_type_id := NULL;

END;

BEGIN

SELECT gcc.segment1,

gcc.segment2,

gcc.segment3,

gcc.segment4,

gcc.segment5,

gcc.segment6,

gcc.segment7,

gcc.segment8,

gcc.segment9,

gcc.segment10

INTO l_segment1,

l_segment2,

l_segment3,

l_segment4,

l_segment5,

l_segment6,

l_segment7,

l_segment8,

l_segment9,

l_segment10

FROM gl_code_combinations_kfv gcck, gl_code_combinations gcc

where GCC.CODE_COMBINATION_ID= 1669

--gcck.concatenated_segments = '01-00-12106-000000-00000-00000'

AND gcc.code_combination_id = gcck.code_combination_id;

EXCEPTION

WHEN OTHERS

THEN

l_segment1 := NULL;

l_segment2 := NULL;

l_segment3 := NULL;

l_segment4 := NULL;

l_segment5 := NULL;

l_segment6 := NULL;

l_segment7 := NULL;

l_segment8 := NULL;

l_segment9 := NULL;

l_segment10 := NULL;

END;

BEGIN

SELECT inventory_location_id

INTO l_from_loc_id

FROM mtl_item_locations_kfv

WHERE concatenated_segments = p_from_loc

AND organization_id = p_organization_id;

EXCEPTION

WHEN OTHERS

THEN

l_from_loc_id := NULL;

END;

BEGIN

SELECT inventory_location_id

INTO l_to_loc_id

FROM mtl_item_locations_kfv

WHERE concatenated_segments = p_to_loc

AND organization_id = p_organization_id;

EXCEPTION

WHEN OTHERS

THEN

l_to_loc_id := NULL;

END;

dbms_output.put_line ('l_to_loc_id: ' || l_to_loc_id); --p_log

dbms_output.put_line ('p_to_subinv: ' || p_to_subinv);

BEGIN

-- Insert MTI for inv open transactions.

INSERT INTO mtl_transactions_interface (transaction_uom,

transaction_date,

source_code,

source_line_id,

source_header_id,

process_flag,

transaction_mode,

lock_flag,

locator_id,

last_update_date,

last_updated_by,

creation_date,

created_by,

inventory_item_id,

subinventory_code,

organization_id,

transaction_quantity,

primary_quantity,

transaction_type_id,

dst_segment1,

dst_segment2,

dst_segment3,

dst_segment4,

dst_segment5,

dst_segment9,

dst_segment6,

dst_segment7,

dst_segment8,

dst_segment10,

transfer_subinventory,

transfer_locator,

transaction_interface_id,

transaction_header_id,

content_lpn_id)

VALUES (p_uom_code, --transaction uom

SYSDATE, --transaction date

p_transaction_type, --source code

l_transaction_id, --source line id

l_transaction_id, --source header id

1, --process flag

3, --transaction mode

2, --lock flag

l_from_loc_id, --locator id

sysdate, --last update date

fnd_global.user_id, --last updated by

SYSDATE, --creation date

fnd_global.user_id, --created by

p_item_id, --inventory item id

p_from_subinv, --From subinventory code

p_organization_id, --organization id

p_quantity, --transaction quantity

p_quantity, --Primary quantity

L_TRN_TYPE_ID, --transaction type id

l_segment1, --segment1 account combination

l_segment2, --segment2 account combination

l_segment3, --segment3 account combination

l_segment4, --segment4 account combination

l_segment5, --segment5 account combination

l_segment6, --segment5 account combination

l_segment7, --segment5 account combination

l_segment8, --segment5 account combination

l_segment9, --segment5 account combination

L_SEGMENT10, --segment5 account combination

p_to_subinv,

l_to_loc_id,

l_transaction_id, --transaction interface id

l_transaction_id,

p_lpn_id);

dbms_output.put_line ('Inserted a record' || l_transaction_id);

x_return_status := fnd_api.g_ret_sts_success;

EXCEPTION

WHEN OTHERS

THEN

fnd_file.put_line (

fnd_file.output,

'in exception ---- inserting data into MTL_TRANSACTIONS_INTERFACE : '

|| SQLERRM);

dbms_output.put_line ('Error' || SQLERRM);

x_return_status := fnd_api.g_ret_sts_error;

x_msg_count := x_msg_count + 1;

x_msg_data := 'Error While Inserting data to MTL_TRANSACTIONS_INTERFACE :' || SQLERRM;

END;

IF x_return_status != fnd_api.g_ret_sts_error

THEN --Successful return of first insert

IF p_lot_no IS NOT NULL

THEN

--Derive lot expiration date for the lot number

BEGIN

SELECT expiration_date

INTO l_expiration_date

FROM MTL_LOT_NUMBERS

WHERE lot_number = p_lot_no

AND inventory_item_id = p_item_id

AND organization_id = p_organization_id;

EXCEPTION

WHEN OTHERS

THEN

l_expiration_date := NULL;

END;

BEGIN

--Insert lot number interface if the item is lot controlled.

INSERT

INTO MTL_TRANSACTION_LOTS_INTERFACE (

transaction_interface_id,

source_code,

source_line_id,

last_update_date,

last_updated_by,

creation_date,

created_by,

last_update_login,

lot_number,

lot_expiration_date,

transaction_quantity,

primary_quantity,

serial_transaction_temp_id)

VALUES (l_transaction_id,

p_transaction_type,

l_transaction_id,

SYSDATE,

FND_GLOBAL.USER_ID,

SYSDATE,

FND_GLOBAL.USER_ID,

FND_GLOBAL.LOGIN_ID,

p_lot_no,

l_expiration_date,

p_quantity,

l_transaction_id);

EXCEPTION

WHEN OTHERS

THEN

x_return_status := fnd_api.g_ret_sts_error;

x_msg_count := x_msg_count + 1;

x_msg_data := 'Error While Inserting data to MTL_TRANSACTION_LOTS_INTERFACE :' || SQLERRM;

END;

END IF;

COMMIT;

--call Inventory open transaction api to perform transfer of material from source locator to destination locator.

x_return_status := NULL;

x_msg_count := 0;

x_msg_data := NULL;

dbms_output.put_line ('Before Calling process_transactions' || l_transaction_id);

lc_result :=

inv_txn_manager_pub.process_transactions (

p_api_version => 1.0,

p_init_msg_list => fnd_api.g_false,

p_commit => fnd_api.g_true,

p_validation_level => fnd_api.g_valid_level_full,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data,

x_trans_count => l_trans_count,

p_table => 1,

p_header_id => l_transaction_id);

dbms_output.put_line ('Return Status' || x_return_status);

IF NVL (x_return_status, 'N') != FND_API.g_ret_sts_success

THEN

dbms_output.put_line ('Return Status' || x_return_status);

COMMIT;

END IF;

EXCEPTION

WHEN OTHERS

THEN

fnd_msg_pub.count_and_get (p_count => x_msg_count,

p_data => x_msg_data,

p_encoded => fnd_api.g_false);

end SUBINVENTORY_TRANSFER;