Organization Structure

SELECT hou.NAME operating_unit_name,
hou.short_code,
hou.organization_id operating_unit_id,
hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code,
ood.organization_id Inv_organization_id,
ood.chart_of_accounts_id
FROM hr_operating_units hou,
org_organization_definitions ood
WHERE 1 = 1
AND hou.organization_id = ood.operating_unit
ORDER BY hou.organization_id ASC

LDT Creation and Upload

Login 8004 instance.

CONNECT PUTTY -

cd $FND_TOP

cd patch              

cd 115

cd import

pwd

CREATE DATA DEFINITION LDT

FNDLOAD apps/app8004s0tst O Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXESRETROREPORT.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME="XXES" DATA_SOURCE_CODE="XXESRETROREPORT" TMPL_APP_SHORT_NAME="XXES" TEMPLATE_CODE="XXESRETROREPORT"

Crate Responsibility ldt

Create ldt in 8004 instance

FNDLOAD apps/app8004s0tst O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct "ACCOUNTING PERIOD_HO.ldt" FND_RESPONSIBILITY RESP_KEY="ACCOUNTING PERIOD_HO"

FNDLOAD apps/apps80040tst 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ESCORTS_FSN_RED.ldt FND_RESPONSIBILITY RESP_KEY="ESCORTS FSN ITEM APPROVAL RED"

Copy this ldt into your location

Upload ldt in 8003 instance

Paste all the ldt into PWD location in 8003 instance

CONNECT PUTTY -

cd $FND_TOP

cd patch

cd 115

cd import

pwd

            

FNDLOAD apps/apps80030dev 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct ESCORTS_EXPORT_DOCUMENTS.ldt

To Upload Concurrent Program

FNDLOAD apps/app8005s0appdev 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXES_ECE_ITEM_WISE_BILLING.ldt

Check through the Backend

              

               SELECT * FROM FND_RESPONSIBILITY

               WHERE RESPONSIBILITY_KEY='ESCORTS_EXPORT_DOCUMENTS'

Craete Lookup ldt in 8004

              

               FNDLOAD apps/apps80040tst 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXES_EXP_LCTYPE.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXES_EXP_LCTYPE" LOOKUP_TYPE="XXES_EXP_LCTYPE" 

              

FNDLOAD apps/apps80040tst 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XXES_EXP_LCSTATUS.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXES_EXP_LCSTATUS" LOOKUP_TYPE="XXES_EXP_LCSTATUS"

Upload ldt in 8003 instance         

FNDLOAD apps/apps80030dev 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXES_EXP_LCTYPE.ldt

               

 FNDLOAD apps/apps80030dev 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXES_EXP_LCSTATUS.ldt

               

                FNDLOAD apps/apps80030dev 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XXES_VENDORMAIL_NOTIFICATION.ldt

MENU

FNDLOAD apps/apps80040tst 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct SPD_WMS_NAVIGATE.ldt MENU MENU_NAME="WMS_NAVIGATE"

FNDLOAD apps/apps80030dev 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXES_IRISO_KITS.ldt MENU MENU_NAME="ESCORTS_PO_NEW"

menu--

FNDLOAD apps/app8002s0tst 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXES_DEALER_SERVICE.ldt MENU MENU_NAME="XXES_DEALER_SERVICE"

RG_group--

FNDLOAD apps/app8002s0tst 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct EAM_Dealer_Readiness.ldt REQUEST_GROUP REQUEST_GROUP_NAME="EAM_Dealer_Readiness" APPLICATION_SHORT_NAME="XXES"

responsiblity--

FNDLOAD apps/app8002s0tst 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct EAM_Dealer_Readiness_Service.ldt FND_RESPONSIBILITY RESP_KEY="EAM Dealer Readiness Service"

Program-

FNDLOAD apps/app8005s0appdev O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXES_ECE_ORDER_BOOKING_STATUS.ldt PROGRAM APPLICATION_SHORT_NAME="XXES" CONCURRENT_PROGRAM_NAME="XXES_ECE_ORDER_BOOKING_STATUS"

Form Function

FNDLOAD apps/app8002s0tst 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ESCORTS_UPDATE_WRT_DEFECT.ldt FUNCTION FUNCTION_NAME="ESCORTS UPDATE WRT DEFECT"

                                                                           New Codes

1. Lookups
-- -------------
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="XXCUST" LOOKUP_TYPE="XX_LOOKUP_TYPE"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct XX_CUSTOM_LKP.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

2. Concurrent Program
-- -----------------------------
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XX_CONCURRENT_PROGRAM"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_CP.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

3. Profile
-- ---------
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt PROFILE PROFILE_NAME="XX_PROFILE_NAME" APPLICATION_SHORT_NAME="XXCUST"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XX_CUSTOM_PRF.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

4. Request Set and Link
-- ------------------------------
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_CUSTOM_RS.ldt REQ_SET REQUEST_SET_NAME='REQUEST_SET_NAME'

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct  XX_CUSTOM_RS.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

5. FND Message
-- ---------------------
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XXCUST" MESSAGE_NAME="MESSAGE_NAME%"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_CUSTOM_MESG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

6. D2K FORMS
-- ------------------
$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt FORM FORM_NAME="FORM_NAME"
      

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FRM.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

7. Form Function
-- ---------------------
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt FUNCTION FUNCTION_NAME="FORM_FUNCTION_NAME"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUSTOM_FUNC.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

8. Alerts
-- ---------
FNDLOAD apps/apps 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt ALR_ALERTS APPLICATION_SHORT_NAME=XXCUST ALERT_NAME="XX - Alert Name"

FNDLOAD apps/apps 0 Y UPLOAD $ALR_TOP/patch/115/import/alr.lct XX_CUSTOM_ALR.ldt CUSTOM_MODE=FORCE

9. Value Set
-- --------------
$FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt VALUE_SET FLEX_VALUE_SET_NAME="XX Value Set Name"

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_CUSTOM_VS.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

10. Data Definition and Associated Template
--- ----------------------------------------------------------
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD  $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME='XXCUST' DATA_SOURCE_CODE='XX_SOURCE_CODE' TMPL_APP_SHORT_NAME='XXCUST' TEMPLATE_CODE='XX_SOURCE_CODE'

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XX_CUSTOM_DD.ldt

11. DATA_TEMPLATE (Data Source .xml file)
--- ----------------------------------------------------------
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

java oracle.apps.xdo.oa.util.XDOLoader UPLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE DATA_TEMPLATE -LOB_CODE XX_TEMPLATE -XDO_FILE_TYPE XML -FILE_NAME $DATA_FILE_PATH/$DATA_FILE_NAME.xml -APPS_SHORT_NAME XXCUST -NLS_LANG en -TERRITORY US -LOG_FILE $LOG_FILE_NAME

12. RTF TEMPLATE (Report Layout .rtf file)
--- -------------------------------------------------------
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD -DB_USERNAME apps -DB_PASSWORD apps -JDBC_CONNECTION '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XX_HOST_NAME)(PORT=XX_PORT_NUMBER))(CONNECT_DATA=(SERVICE_NAME=XX_SERVICE_NAME)))' -LOB_TYPE TEMPLATE -LOB_CODE XX_TEMPLATE -APPS_SHORT_NAME XXCUST -LANGUAGE en -TERRITORY US -lct_FILE $XDO_TOP/patch/115/import/xdotmpl.lct -LOG_FILE $LOG_FILE_NAME

LAST TIME REPORT RUN RESPONSIBILITY

/* Formatted on 2019/11/28 11:43 (Formatter Plus v4.8.8) */
SELECT frt.responsibility_name                                                  --fcr.REQUEST_DATE
  FROM fnd_concurrent_requests fcr, fnd_concurrent_programs_tl fcpt,fnd_responsibility_tl frt
 WHERE fcr.concurrent_program_id = fcpt.concurrent_program_id
   AND fcr.program_application_id = fcpt.application_id
   AND frt.responsibility_id = FCR.responsibility_id
   AND fcr.actual_start_date > SYSDATE - :days_to_check
   AND fcpt.user_concurrent_program_name LIKE '%' || :program_name || '%'

Check Scheduled Program Resp

select responsibility_id from
fnd_concurrent_requests
where
REQUEST_ID in (174977047,174979126)


select distinct r.responsibility_name
from fnd_responsibility_vl r, fnd_concurrent_requests c
where
 c.responsibility_application_id = r.application_id
and c.responsibility_id = r.responsibility_id
and c.responsibility_id in (50641,50642)

ASSIGN RESPONSIBILITY FROM BACKEND

/* Formatted on 2020/02/11 11:33 (Formatter Plus v4.8.8) */
SELECT responsibility_id, responsibility_name
  FROM fnd_responsibility_vl
 WHERE responsibility_name LIKE 'System Administrator';





 DECLARE

  v_user_name  VARCHAR2(30) := '94533';
  Enter_Resp_Id  VARCHAR2(100) ;
  v_resp_appl  VARCHAR2(100);
  v_resp_key   VARCHAR2(100);
  v_appl_id    VARCHAR2(30);

BEGIN
select APPLICATION_ID ,RESPONSIBILITY_KEY
  into v_appl_id ,v_resp_key
  from FND_RESPONSIBILITY_VL
where RESPONSIBILITY_ID=('20420');

select APPLICATION_SHORT_NAME
  into v_resp_appl
  from FND_APPLICATION_VL 
where upper(APPLICATION_ID) = v_appl_id;

  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => v_resp_appl
                      ,resp_key       => v_resp_key
                      ,security_group => 'STANDARD'
                      ,description    => 'Auto Assignment'
                      ,start_date     => SYSDATE
                      ,end_date       => SYSDATE + 1000);
END;
commit;
/

XML Based Report Format

<?xml version="1.0" encoding="UTF-8"?>
<dataTemplate name="XXES_ECAT_ORDER_EXCEPTION" version="1.0">
  <properties> <property name="include_null_Element" value="True"/>
               <property name="xml_tag_case" value="upper"/>
               <property name="scalable_mode" value="on"/>
  </properties>
  <parameters>                  <parameter name="p_organization_id" include_in_output="TRUE" dataType="NUMBER"/>
<parameter name="p_parent_item" include_in_output="TRUE" dataType="VARCHAR2"/>
                             
  </parameters>
  <dataQuery>
         
     <sqlStatement name="Q_1">
     <![CDATA[SELECT msi.segment1 Parent_Item,
       msi.description Parent_Item_Description,
       msi1.segment1 Child_Item,
       msi1.description Child_Item_Description,
       BIC.component_quantity CHILD_ITEM_QUANTITY,
       bic.supply_subinventory,
        MIL.segment1
         || '.'
         || MIL.segment2
         || '.'
         || MIL.segment3
        ChildLocator
  FROM mtl_system_items_b msi,
       bom_bill_of_materials bom,
       bom_inventory_components bic,
       mtl_system_items_b msi1,
       MTL_ITEM_LOCATIONS mil
WHERE     1 = 1
       AND msi.organization_id = :p_organization_id
       AND msi.inventory_item_id = bom.assembly_item_id
       AND msi.organization_id = bom.organization_id
       AND bom.organization_id = :p_organization_id
       AND bom.alternate_bom_designator IS NULL
       AND bom.bill_sequence_id = bic.bill_sequence_id
       AND bic.disable_date IS NULL
       AND bic.component_item_id = msi1.inventory_item_id
       AND mil.inventory_location_id(+) = bic.supply_locator_id
       AND msi1.organization_id = :p_organization_id
    AND msi.segment1 = nvl(:p_parent_item,msi.segment1)   
    order by 1,3
    ]]>
            </sqlStatement>

  </dataQuery>
   <dataStructure> 
      <group name="G_PO_TYPE" source="Q_1">
                 <element name="Parent_Item" value="Parent_Item"/>
  <element name="Parent_Item_Description" value="Parent_Item_Description"/>
  <element name="Child_Item" value="Child_Item"/>
  <element name="Child_Item_Description" value="Child_Item_Description"/>
          <element name="CHILD_ITEM_QUANTITY" value="CHILD_ITEM_QUANTITY"/>
                  <element name="supply_subinventory" value="supply_subinventory"/>
  <element name="ChildLocator" value="ChildLocator"/>
     </group>
  </dataStructure>
</dataTemplate>

Burst File Program For Email Sending

<?xml version="1.0" encoding="UTF-8"?>
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi" type="bursting">
  <xapi:request select="/XXES_GST_AR_INVOICE_PDF/LIST_G_CUSTOMER_NUMBER/G_CUSTOMER_NUMBER">
   <xapi:delivery>
<xapi:email id="123" server="150.100.1.85" port="25" from="no.reply@escorts.co.in" reply-to="no.reply@escorts.co.in">
<xapi:message id="123" to="" cc="" attachment="true" content-type="text/html" subject="GST Invoice No.- ${STA_NO} Dated- ${STA_DATE}">


 <![CDATA[
                    <html>
                       
                    <body>
Dear Sir,
<br/><br/>

Find attached PDF copy of Invoice ${STA_NO}-${DESCRIPTION}.
<br/><br/>

Please call to respective buyer for clarification, If require.
<br/>
<br/>

<i>*************This is a system generated email, do not reply to this email id.Req. ID-${CF_REQ_ID}*************</i>
<br/>
<br/>

Regards,
<br/>
Purchase Department


</body></html>]]>
</xapi:message>
</xapi:email>
</xapi:delivery>
<xapi:document delivery="123" output-type="pdf" output="Invoice_${ORDER_NUMBER}.pdf" >
<xapi:template type="rtf" location="/usr/tmp/XXES_GST_AR_INVOICE_PDF.rtf">
</xapi:template>
</xapi:document>
</xapi:request>
</xapi:requestset>


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

Need To Write It In Trigeer-----






function BeforeReport return boolean is
begin
    SRW.USER_EXIT('FND SRWINIT');
  return (TRUE);
end;
--------------------------------------------------------------------------------------------------------------------------


function AfterReport return boolean is
 l_req_id    NUMBER;

begin

IF :P_EMAIL ='Y' AND :P_SERVICE_INV_NO = 'Y'  then

srw.message(1,  'enter');

l_req_id    := fnd_request.submit_request (
application => 'XDO' ,
program => 'XDOBURSTREP'
,description => 'Bursting output'
,start_time => ''
,sub_request => FALSE
,argument1 => 'Y'
,argument2 => fnd_global.conc_request_id
,argument3 => 'Y');
END IF;
  SRW.USER_EXIT('FND SRWEXIT');
  return (TRUE);
end;