Dynamically pass the date or Year or month in pl/sql code in oracle

Code to Dynamically pass the date or Year or month in pl/sql code in oracle

Declare
 v_prev_year           VARCHAR2(10);
   v_prevdate            DATE;

    v_year                VARCHAR2 (2);

begin

SELECT TO_CHAR(ADD_MONTHS (SYSDATE, -12),'DD-MON-YY')--15-DEC-14
     INTO v_prevdate
     FROM DUAL;
   
  SELECT TO_CHAR(TO_DATE(v_prevdate,'DD-MON-YY'),'YY')
     INTO v_prev_year
     FROM DUAL;

SELECT TO_CHAR (v_prevdate, 'RR')
     INTO v_prev_year
     FROM DUAL;
   
  SELECT TO_CHAR (SYSDATE, 'RR')
     INTO v_year
     FROM DUAL;

SELECT column1,column2
    INTO ly2
    FROM table_name
    WHERE
TRUNC (creation_date) BETWEEN '01-FEB-' || v_prev_year AND '28-FEB-' || v_prev_year;
/*in this it dynamically passes the previous_year*/

SELECT column1,column2
    INTO ly2
    FROM table_name
    WHERE
TRUNC (creation_date) BETWEEN '01-FEB-' || v_yearAND '28-FEB-' || v_year;
/*in this it dynamically passes the year*/
end;

Add Printer from backend in oracle apps R12

we can add printer from backend in procedure,trigger or anonymous block in oracle.



lc_boolean := fnd_submit.set_print_options (printer      => 'Printer_name'
                                   ,style        => 'Landscape'
                                   ,copies       => 1
                                   );

lc_boolean1 :=fnd_request.add_printer (
                    printer => 'Printer_name',
                    copies  => 1);

Query for location of rtf template in bursting control file

select 'xdo://'||
       xtb.application_short_name||'.'||
         xtb.template_code ||'.'||
         xtb.default_language ||'.'||
         xtb.default_territory
  from   apps.xdo_templates_b xtb
 where   xtb.template_code ='template_name';

xml bursting using pl/sql stored procedure

create or replace procedure data_xml(errbuf out varchar2,retcode out number)
as
result boolean;
  --
  --Cursor to fetch the data
  --
  CURSOR data_cur
  IS
    --
    select * from emp;
  --
 -- output_row data_cur%rowtype;
BEGIN
  --
  --
  dbms_output.put_line('<?xml version="1.0" encoding="US-ASCII" standalone="no"?>');
  fnd_file.put_line(fnd_file.output,'<?xml version="1.0" encoding="US-ASCII" standalone="no"?>');
  dbms_output.put_line('<OUTPUT>');
  fnd_file.put_line(fnd_file.output,'<OUTPUT>');
  --
 for i in data_cur
 loop
    --
    if   i.Minutes > i.max_running_time then
    --
    dbms_output.put_line('<ROW>');
    fnd_file.put_line(fnd_file.output,'<ROW>');
    --
    dbms_output.put_line('<ename>'||dbms_xmlgen.CONVERT(i.job)||'</ename>');
    fnd_file.put_line(fnd_file.output,'<ename>'||dbms_xmlgen.CONVERT(i.job)||'</ename>');
    --
    dbms_output.put_line('<empno>'||dbms_xmlgen.CONVERT(i.Parent_Req_ID )||'</empno>');
    fnd_file.put_line(fnd_file.output,'<empno>'||dbms_xmlgen.CONVERT(i.job)||'</empno>');
    --
    dbms_output.put_line('<job>'||dbms_xmlgen.CONVERT(i.Req_ID )||'</job>');
    fnd_file.put_line(fnd_file.output,'<job>'||dbms_xmlgen.CONVERT(i.job)||'</job>');
    --
        --
        --
    dbms_output.put_line('</ROW>');
    fnd_file.put_line(fnd_file.output,'</ROW>');
    --
    end if;
  END LOOP;
  
  --
  dbms_output.put_line('</OUTPUT>');
  fnd_file.put_line(fnd_file.output,'</OUTPUT>');
  --
  result:=BURSTING_CP_SUB_PKG.AfterReport();
  
  exception
  when others then
  dbms_output.put_line('error program');
  fnd_file.put_line(fnd_file.output,'error program');
END;

Link Between PO and WIP Tables in oracle apps

SELECT DISTINCT pv.vendor_name
    FROM po.po_requisition_headers_all prha
       , po.po_requisition_lines_all prla
       , po_req_distributions_all prd
       , applsys.fnd_user fu
       , wip.wip_entities we
       , po_headers_all poh
       , po_lines_all pol
       , po_line_locations_all pll
       , po_distributions_all pod
       , po_vendors pv
   WHERE prha.requisition_header_id = prla.requisition_header_id
     AND prha.created_by = fu.user_id
     AND prla.wip_entity_id = :WIP_ENTITY_ID
     AND prha.creation_date > '25-JUL-2013'
     AND prd.requisition_line_id = prla.requisition_line_id
     AND prd.distribution_id = pod.req_distribution_id
     AND pod.po_header_id = pll.po_header_id
     AND pod.po_line_id = pll.po_line_id
     AND pod.line_location_id = pll.line_location_id
     AND pll.po_line_id = pol.po_line_id
     AND pll.po_header_id = pll.po_header_id
     AND pol.po_header_id = poh.po_header_id
     AND poh.vendor_id = pv.vendor_id;


SELECT DISTINCT poh.segment1 into V_PO
    FROM po.po_requisition_headers_all prha
       , po.po_requisition_lines_all prla
       , po_req_distributions_all prd
       , applsys.fnd_user fu
       , wip.wip_entities we
       , po_headers_all poh
       , po_lines_all pol
       , po_line_locations_all pll
       , po_distributions_all pod
       , po_vendors pv
   WHERE prha.requisition_header_id = prla.requisition_header_id
     AND prha.created_by = fu.user_id
     AND prla.wip_entity_id = :WIP_ENTITY_ID
    -- AND prha.creation_date > '25-JUL-2013'
     AND prd.requisition_line_id = prla.requisition_line_id
     AND prd.distribution_id = pod.req_distribution_id
     AND pod.po_header_id = pll.po_header_id
     AND pod.po_line_id = pll.po_line_id
     AND pod.line_location_id = pll.line_location_id
     AND pll.po_line_id = pol.po_line_id
     AND pll.po_header_id = pll.po_header_id
     AND pol.po_header_id = poh.po_header_id
     AND poh.vendor_id = pv.vendor_id;

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;

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

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';

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

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;

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.

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);

ware house locator change

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

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

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)

    IS

       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

       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,

                        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;

       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;

/