Material Transactions Type wise organization and SubInventory Data

 --- Organization and Sub Inventory and Transactions Type wise Material Transactions data 

SELECT ood.organization_code,

       ood.organization_name,

       mmt.subinventory_code,

       TO_CHAR (transaction_date, 'YYYY') trans_years,

       mtt.transaction_type_name,

       mtt.description transaction_description,

       msi.segment1 item_code,

       mmt.primary_quantity,

       transaction_date,

       transaction_uom,

       transaction_quantity

  FROM mtl_material_transactions mmt,

       mtl_system_items_b msi,

       mtl_transaction_types mtt,

       org_organization_definitions ood

 WHERE     msi.inventory_item_id = mmt.inventory_item_id

       AND mmt.organization_id = msi.organization_id

       AND ood.organization_id = mmt.organization_id

       AND mmt.transaction_type_id = mtt.transaction_type_id

       AND TO_CHAR (transaction_date, 'YYYY') >= 2021 ---2021 to tail data

Order by  mtt.transaction_type_name;

Oracle Responsibility wise User Name Find Script

---- Responsibility wise User Name Search Script 

SELECT fu.user_name                "User Name",

       frt.responsibility_name     "Responsibility Name",

       fat.APPLICATION_NAME        "Application Name",

       furg.start_date             "Start Date",

       furg.end_date               "End Date",   

       fr.responsibility_key       "Responsibility Key", 

       fa.application_short_name   "Application Short Name"

  FROM fnd_user_resp_groups_direct        furg

      ,fnd_user                   fu

      ,fnd_responsibility_tl      frt

      ,fnd_responsibility         fr

      ,fnd_application_tl         fat

      ,fnd_application            fa

 WHERE furg.user_id             =  fu.user_id

   AND furg.responsibility_id   =  frt.responsibility_id

   AND upper(frt.responsibility_name) =  UPPER(:P_Respo_Name) --'Yard Manager')

   AND fr.responsibility_id     =  frt.responsibility_id

   AND fr.application_id        =  fat.application_id

   AND frt.language             =  USERENV('LANG')

   AND fa.application_id        =  fat.application_id

   AND (furg.end_date IS NULL OR furg.end_date >= TRUNC(SYSDATE))

 ORDER BY fu.user_name;

Oracle Invalid Synonym are valid script

---Invalid Synonym are valid script with Editionable

---DB object

DECLARE

   l_DDL   VARCHAR2 (5000);

   v_DDL   VARCHAR2 (5000);

   CURSOR Cur

   IS

      SELECT EDITION_NAME,

             DBMS_METADATA.get_ddl (object_type   => 'SYNONYM',

                                    name          => db.object_name,

                                    schema        => 'PUBLIC')

                DDL,

             als.synonym_name,

             REPLACE (

                DBMS_METADATA.get_ddl (object_type   => 'SYNONYM',

                                       name          => db.object_name,

                                       schema        => 'PUBLIC'),

                'NONEDITIONABLE',

                'EDITIONABLE')

                new_ddl

        FROM all_synonyms als, dba_objects db

       WHERE     als.owner = 'PUBLIC'

             AND als.table_owner NOT IN ('SYS')

             AND als.synonym_name = db.object_name

             AND db.status = 'INVALID'

             AND db.OWNER = 'PUBLIC'

             AND object_type = 'SYNONYM'

             AND OBJECT_NAME = :OBJECT_NAME         --- comment for all record

                                           ;

BEGIN

   DBMS_OUTPUT.put_line ('>>> DDL >>> ');

   FOR i IN Cur

   LOOP

      DBMS_OUTPUT.put_line ('>>> DDL Name: ' || i.synonym_name);

      COMMIT;

      -------1-----------

      BEGIN

         EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM  ' || i.synonym_name;

         DBMS_OUTPUT.put_line (

            '>>>Successfuly Drop Public Synonym: ' || i.synonym_name);

         COMMIT;

      END;

      ------2----------

      BEGIN

         EXECUTE IMMEDIATE REPLACE (i.new_ddl, CHR (10), '');

         DBMS_OUTPUT.put_line (

               '>>>Created Editionable Public Synonym : '

            || REPLACE (i.new_ddl, CHR (10), ''));

         COMMIT;

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('>>> Error : ' || SQLERRM);

END;

Oracle Write a Blob to file

 CREATE OR REPLACE PROCEDURE APPS.write_blob_to_file (
   --p_entity_name   IN   VARCHAR2,
   p_pk1_value     IN   VARCHAR2
)
AS
   v_lob_loc       BLOB;
   v_buffer        RAW (32767);
   v_buffer_size   BINARY_INTEGER;
   v_amount        BINARY_INTEGER;
   v_offset        NUMBER (38)        := 1;
   v_chunksize     INTEGER;
   v_out_file      UTL_FILE.file_type;
   v_file_name     VARCHAR2 (1000);
   
   Cursor Cur IS
     SELECT  fd.media_id, flb.file_data v_lob_loc, flb.file_name v_file_name
     FROM     fnd_lobs flb, 
             fnd_attached_documents fad,
             fnd_documents fd
     WHERE 1=1
     AND fad.entity_name = 'RA_CUSTOMER_TRX'
     AND fad.pk1_value = p_pk1_value
     AND fad.document_id = fd.document_id
     AND fd.media_id = flb.file_id;
BEGIN
-- +-------------------------------------------------------------+
-- | SELECT THE LOB LOCATOR                                      |
-- +-------------------------------------------------------------+
  
DBMS_OUTPUT.put_line('File Location'||p_pk1_value);
-- +-------------------------------------------------------------+
 -- | FIND OUT THE CHUNKSIZE FOR THIS LOB COLUMN                  |
 -- +-------------------------------------------------------------+
 FOR c1 in Cur
    LOOP
   v_chunksize := DBMS_LOB.getchunksize (c1.v_lob_loc);
   IF (v_chunksize < 32767)
   THEN
      v_buffer_size := v_chunksize;
   ELSE
      v_buffer_size := 32767;
   END IF;
   v_amount := v_buffer_size;
-- +-------------------------------------------------------------+
-- | OPENING THE LOB IS OPTIONAL                                 |
-- +-------------------------------------------------------------+
   DBMS_LOB.OPEN (c1.v_lob_loc, DBMS_LOB.lob_readonly);
-- +-------------------------------------------------------------+
-- | WRITE CONTENTS OF THE LOB TO A FILE                         |
-- +-------------------------------------------------------------+
   v_out_file :=
      UTL_FILE.fopen
         (LOCATION          => '/global/erpgrp/cfs02/EBSPROD_cmlogs/conc/upload',
          filename          => c1.v_file_name,
          open_mode         => 'wb',
          max_linesize      => 32767
         );
   WHILE v_amount >= v_buffer_size
   LOOP
      DBMS_LOB.READ (lob_loc      => c1.v_lob_loc,
                     amount       => v_amount,
                     offset       => v_offset,
                     buffer       => v_buffer
                    );
      v_offset := v_offset + v_amount;
      UTL_FILE.put_raw (FILE           => v_out_file,
                        buffer         => v_buffer,
                        autoflush      => TRUE
                       );
      UTL_FILE.fflush (FILE => v_out_file);
   END LOOP;
   UTL_FILE.fflush (FILE => v_out_file);
   UTL_FILE.fclose (v_out_file);
-- +-------------------------------------------------------------+
-- | CLOSING THE LOB IS MANDATORY IF YOU HAVE OPENED IT          |
-- +-------------------------------------------------------------+
   DBMS_LOB.CLOSE (c1.v_lob_loc);
   END LOOP;
   
EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line('No file found with Given Entity Name and PK1_Value'||SQLERRM);   
   
END write_blob_to_file;
/

SLA : Account Derivation Rule Script

 /* account derivation rule  */

SELECT xsr.user_sequence Priority,xsr.VALUE_TYPE_CODE,xsr.VALUE_CODE_COMBINATION_ID,xc.user_sequence seq
,xc.SOURCE_TYPE_CODE,xc.SOURCE_CODE,xc.LINE_OPERATOR_CODE,xc.VALUE_TYPE_CODE VLAUE_TYPE,xc.VALUE_CONSTANT
  FROM xla_seg_rule_details xsr,
       xla_conditions xc
 WHERE segment_rule_code = 'XX_RCA_COMBINATION1'
   AND xsr.SEGMENT_RULE_DETAIL_ID = xc.SEGMENT_RULE_DETAIL_ID
   AND xsr.application_id = xc.application_id
   
--   AND xsr.user_sequence > 600
--   AND xsr.user_sequence <= 1020

Oracle Apps R12: Check if the patch applied or not

--Check if the patch applied or not

This table includes the defined bugs on the system:

SELECT    bug_number

       || '.'

       || aru_release_name

       || '.'

       || UPPER (trackable_entity_abbr)

       || '.'

       || baseline_name

          Patch

FROM     apps.ad_bugs
WHERE   bug_number LIKE '%' ||3594604 || '%';

This table includes patches applied on the system:

SELECT patch_name

FROM   apps.ad_applied_patches
WHERE patch_name LIKE '%' ||3594604 || '%'

OM: Order Header and Order Line base Advance Query

BEGIN

   mo_global.init('ONT');

END;

BEGIN

   mo_global.init('ONT');

END;

SELECT   ac.customer_name, ac.customer_number, ooh.order_number,

         ooh.cust_po_number, ooh.ordered_date,

         ooh.flow_status_code status_code, ooh.order_firmed_date,

         ood.organization_code warehouse, ooh.fob_point_code fob,

         ooh.freight_terms_code, ooh.shipping_method_code,

         ooh.shipment_priority_code, ooh.transactional_curr_code curr_code,

         ot.NAME order_type,

         (SELECT s.NAME

            FROM jtf.jtf_rs_salesreps s

           WHERE s.org_id = ood.operating_unit

             AND s.salesrep_id = ooh.salesrep_id) sales_parson,

         (SELECT qpl.NAME

            FROM qp_list_headers_vl qpl

           WHERE list_header_id = ooh.price_list_id) price_list,

         (SELECT rat.NAME

            FROM ra_terms rat

           WHERE rat.term_id = ooh.payment_term_id) payment_term_name, 

         ooh.orig_sys_document_ref,

         ooh.attribute1 header_ddf1, ooh.attribute2 header_ddf2,

         ooh.attribute3 header_ddf3, ooh.attribute4 header_ddf4,

         ooh.attribute5 header_ddf5, ooh.attribute6 header_ddf6,

----------------Line Info below-----------

         ool.line_number, ool.ordered_item, ool.ordered_quantity,

         ool.shipped_quantity, ool.schedule_ship_date,

         ool.order_quantity_uom uom, ool.request_date,

         ool.flow_status_code line_status_code,

         ool.tax_value,

        (SELECT transaction_type_code FROM ont.oe_transaction_types_all where transaction_type_id =ool.LINE_TYPE_ID)LINE_TYPE,

        (SELECT process_name FROM oe_workflow_assignments WHERE order_type_id = ooh.order_type_id and line_type_id= ool.line_type_id) WF_PROCESS_NAME,

         (SELECT NAME

            FROM pa_projects_all

           WHERE project_id = ool.project_id) project_name,

         (SELECT task_name

            FROM pa_tasks

           WHERE task_id = ool.task_id) task_name, ool.return_reason_code,

         ool.reference_type, ool.unit_selling_price unit_price, ool.tax_code,

         ool.tax_rate, ool.tax_value, ship_to.address1 ship_to_address1,

         ship_to.address2 ship_to_address2, ship_to.address3 ship_to_address3,

         ship_to.city ship_to_city, ship_to.state ship_to_state,

         ship_to.nls_territory bill_to_territory,

         ship_to.postal_code ship_to_postal_code,

         ship_to.province ship_to_pin, bill_to.address1 bill_to_address1,

         bill_to.address2 bill_to_address2, bill_to.address3 bill_to_address3,

         bill_to.city bill_to_city, bill_to.state bill_to_state,

         bill_to.nls_territory bill_to_territory,

         bill_to.postal_code bill_to_postal_code,

         bill_to.province bill_to_pin, ool.attribute1 line_ddf1,

         ool.attribute2 line_ddf2, ool.attribute3 line_ddf3,

         ool.attribute4 line_ddf4,ool.attribute5 line_ddf5,

         ool.attribute6 line_ddf6,

(select lookup_code from apps.oe_lookups where upper (lookup_type) = 'FOB' and enabled_flag = 'Y' and upper (lookup_code) = upper (ooh.fob_point_code) )FOB,

(select lookup_code from apps.oe_lookups where upper (lookup_type) = 'FREIGHT_TERMS' and enabled_flag = 'Y' and upper (lookup_code) = upper (ooh.freight_terms_code) )Freight_Terms,

(Select CHARGE_AMOUNT from OE_CHARGE_LINES_V  ocl where ocl.HEADER_ID=ool.HEADER_ID and ocl.LINE_ID=ool.LINE_ID) FREIGHT_CHARGE,

    ool.line_category_code,

    (SELECT order_number FROM oe_order_headers_all WHERE header_id = ool.return_attribute1) RMA_SALES_ORDER,

    (SELECT line_number  FROM oe_order_lines_all WHERE header_id = ool.return_attribute1 AND line_id = ool.return_attribute2) RMA_SO_Line_No

    FROM oe_order_headers_all ooh,

         oe_order_lines_all ool,

         oe_transaction_types_tl ot,

         ar_customers ac

       , org_organization_definitions ood

----------------------Bill To Customer-------------------------------

         ,(SELECT hp.party_name, hp.party_number, hca.account_number,

                 hca.cust_account_id, hp.party_id, hps.party_site_id,

                 hcsu.cust_acct_site_id, hps.location_id, hl.address1,

                 hl.address2, hl.address3, hl.city, hl.state,

                 ter.nls_territory, hl.postal_code, hl.province,

                 hcsu.site_use_code, hcsu.site_use_id, hcsa.bill_to_flag

            FROM hz_parties hp,

                 hz_party_sites hps,

                 hz_locations hl,

                 hz_cust_accounts_all hca,

                 hz_cust_acct_sites_all hcsa,

                 hz_cust_site_uses_all hcsu,

                 fnd_territories ter

           WHERE hp.party_id = hps.party_id

             AND hps.location_id = hl.location_id

             AND hp.party_id = hca.party_id

             AND hcsa.party_site_id = hps.party_site_id

             AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id

             AND hca.cust_account_id = hcsa.cust_account_id

             AND hl.country = ter.territory_code

             AND hcsu.site_use_code = 'BILL_TO') bill_to

----------------------Ship To Customer-------------------------------

         ,(SELECT hp.party_name, hp.party_number, hca.account_number,

                 hca.cust_account_id, hp.party_id, hps.party_site_id,

                 hcsu.cust_acct_site_id, hps.location_id, hl.address1,

                 hl.address2, hl.address3, hl.city, hl.state,

                 ter.nls_territory, hl.postal_code, hl.province,

                 hcsu.site_use_code, hcsu.site_use_id, hcsa.bill_to_flag

            FROM hz_parties hp,

                 hz_party_sites hps,

                 hz_locations hl,

                 hz_cust_accounts_all hca,

                 hz_cust_acct_sites_all hcsa,

                 hz_cust_site_uses_all hcsu,

                 fnd_territories ter

           WHERE hp.party_id = hps.party_id

             AND hps.location_id = hl.location_id

             AND hp.party_id = hca.party_id

             AND hcsa.party_site_id = hps.party_site_id

             AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id

             AND hca.cust_account_id = hcsa.cust_account_id

             AND hl.country = ter.territory_code

             AND hcsu.site_use_code = 'SHIP_TO') ship_to

   WHERE ooh.header_id = ool.header_id

     AND ooh.order_type_id = ot.transaction_type_id

     AND ac.customer_id = ooh.sold_to_org_id

--     AND ooh.flow_status_code = 'BOOKED'

     AND ood.organization_id = ooh.ship_from_org_id

     AND ood.organization_code = NVL (:p_inv_org_code, ood.organization_code)

     AND ooh.order_number = NVL (:p_sales_order_no, ooh.order_number)

     AND ship_to.cust_account_id = ooh.sold_to_org_id

     AND ship_to.site_use_id = ooh.ship_to_org_id

     AND bill_to.cust_account_id = ooh.sold_to_org_id

     AND bill_to.site_use_id = ooh.invoice_to_org_id

ORDER BY order_number

/

OM: Order header and line base query

BEGIN

mo_global.init('ONT');

END;

SELECT

     AC.CUSTOMER_NAME, AC.CUSTOMER_NUMBER

    ,OOH.ORDER_NUMBER,OOH.CUST_PO_NUMBER

    ,OOH.ORDERED_DATE, OOH.FLOW_STATUS_CODE STATUS_CODE

    ,OOH.ORDER_FIRMED_DATE

    ,OOD.ORGANIZATION_CODE WAREHOUSE

    ,OOH.FOB_POINT_CODE FOB

    ,OOH.FREIGHT_TERMS_CODE

    ,OOH.SHIPPING_METHOD_CODE

    ,OOH.SHIPMENT_PRIORITY_CODE

    ,OOH.TRANSACTIONAL_CURR_CODE CURR_CODE

    ,OOH.ATTRIBUTE1 HEADER_DDF1,OOH.ATTRIBUTE2 HEADER_DDF2,OOH.ATTRIBUTE3 HEADER_DDF3,OOH.ATTRIBUTE4 HEADER_DDF4

    ,OT.NAME ORDER_TYPE

    ,( select s.name  from JTF.jtf_rs_salesreps s where S.ORG_ID=OOD.OPERATING_UNIT AND s.salesrep_id = ooh.salesrep_id) SALES_PARSON

    ,(SELECT NAME FROM qp_list_HEADERS_VL qpl WHERE LIST_HEADER_ID = OOH.PRICE_LIST_ID) PRICE_LIST

    ,(SELECT RAT.NAME FROM RA_TERMS RAT WHERE RAT.TERM_ID=OOH.PAYMENT_TERM_ID) PAYMENT_TERM_NAME

    ,OOL.LINE_NUMBER

    ,OOL.ORDERED_ITEM

    ,OOL.ORDERED_QUANTITY

    ,OOL.SHIPPED_QUANTITY

    ,OOL.SCHEDULE_SHIP_DATE

    ,OOL.ORDER_QUANTITY_UOM UOM

    ,OOL.REQUEST_DATE

    ,OOl.FLOW_STATUS_CODE LINE_STATUS_CODE

    ,(SELECT NAME  FROM pa_projects_all WHERE project_id = OOL.PROJECT_ID) PROJECT_NAME

    ,(SELECT TASK_NAME FROM pa_tasks WHERE task_id = OOL.TASK_ID)TASK_NAME

    ,OOL.RETURN_REASON_CODE

    ,OOL.REFERENCE_TYPE

    ,OOL.UNIT_SELLING_PRICE UNIT_PRICE

    ,OOL.TAX_CODE,OOL.TAX_RATE,OOL.TAX_VALUE

    ,SHIP_TO.address1 SHIP_TO_ADDRESS1,SHIP_TO.address2 SHIP_TO_ADDRESS2,SHIP_TO.address3 SHIP_TO_ADDRESS3,SHIP_TO.city SHIP_TO_CITY

    ,SHIP_TO.state SHIP_TO_STATE,SHIP_TO.nls_territory BILL_TO_TERRITORY,SHIP_TO.postal_code SHIP_TO_POSTAL_CODE,SHIP_TO.province SHIP_TO_PIN

    ,BILL_TO.address1 BILL_TO_ADDRESS1,BILL_TO.address2 BILL_TO_ADDRESS2,BILL_TO.address3 BILL_TO_ADDRESS3,BILL_TO.city BILL_TO_CITY

    ,BILL_TO.state BILL_TO_STATE,BILL_TO.nls_territory BILL_TO_TERRITORY,BILL_TO.postal_code BILL_TO_POSTAL_CODE,BILL_TO.province BILL_TO_PIN

    ,OOL.ATTRIBUTE1 LINE_DDF1,OOH.ATTRIBUTE2 LINE_DDF2,OOH.ATTRIBUTE3 LINE_DDF3,OOH.ATTRIBUTE4 LINE_DDF4

FROM

     OE_ORDER_HEADERS_ALL OOH

    ,OE_ORDER_LINES_ALL OOL

    ,OE_TRANSACTION_TYPES_TL OT

    ,AR_CUSTOMERS AC

--    ,MTL_PARAMETERS MP

   ,ORG_ORGANIZATION_DEFINITIONS OOD

        ----------------------Bill To Customer-------------------------------

,(SELECT hp.party_name,

       hp.party_number,

       hca.account_number,

       hca.cust_account_id,

       hp.party_id,

       hps.party_site_id,

       hcsu.cust_acct_site_id,

       hps.location_id,

       hl.address1,

       hl.address2,

       hl.address3,

       hl.city,

       hl.state,

       ter.nls_territory,

       hl.postal_code,

       hl.province,

       hcsu.site_use_code,

       hcsu.site_use_id,

       hcsa.bill_to_flag

FROM hz_parties hp,

     hz_party_sites hps,

     hz_locations hl,

     hz_cust_accounts_all hca,

     hz_cust_acct_sites_all hcsa,

     hz_cust_site_uses_all hcsu,

     fnd_territories ter

WHERE     hp.party_id = hps.party_id

      AND hps.location_id = hl.location_id

      AND hp.party_id = hca.party_id

      AND hcsa.party_site_id = hps.party_site_id

      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id

      AND hca.cust_account_id = hcsa.cust_account_id

      AND hl.country = ter.territory_code

      AND hcsu.site_use_code = 'BILL_TO')BILL_TO

----------------------Ship To Customer-------------------------------

,(SELECT hp.party_name,

       hp.party_number,

       hca.account_number,

       hca.cust_account_id,

       hp.party_id,

       hps.party_site_id,

       hcsu.cust_acct_site_id,

       hps.location_id,

       hl.address1,

       hl.address2,

       hl.address3,

       hl.city,

       hl.state,

       ter.nls_territory,

       hl.postal_code,

       hl.province,

       hcsu.site_use_code,

       hcsu.site_use_id,

       hcsa.bill_to_flag

FROM hz_parties hp,

     hz_party_sites hps,

     hz_locations hl,

     hz_cust_accounts_all hca,

     hz_cust_acct_sites_all hcsa,

     hz_cust_site_uses_all hcsu,

     fnd_territories ter

WHERE     hp.party_id = hps.party_id

      AND hps.location_id = hl.location_id

      AND hp.party_id = hca.party_id

      AND hcsa.party_site_id = hps.party_site_id

      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id

      AND hca.cust_account_id = hcsa.cust_account_id

      AND hl.country = ter.territory_code

      AND hcsu.site_use_code = 'SHIP_TO')SHIP_TO

WHERE

     OOH.HEADER_ID=OOL.HEADER_ID

AND  OOH.ORDER_TYPE_ID=OT.TRANSACTION_TYPE_ID

AND  AC.CUSTOMER_ID=OOH.SOLD_TO_ORG_ID

AND OOH.FLOW_STATUS_CODE ='BOOKED'

AND OOD.ORGANIZATION_ID = OOH.SHIP_FROM_ORG_ID

AND OOD.ORGANIZATION_CODE = NVL(:P_INV_ORG_CODE,OOD.ORGANIZATION_CODE) 

AND OOH.ORDER_NUMBER = NVL(:P_SALES_ORDER_NO, OOH.ORDER_NUMBER)

AND SHIP_TO.cust_account_id = OOH.SOLD_TO_ORG_ID 

AND SHIP_TO.site_use_id = OOH.SHIP_TO_ORG_ID  

AND BILL_TO.cust_account_id = OOH.SOLD_TO_ORG_ID 

AND BILL_TO.site_use_id = OOH.INVOICE_TO_ORG_ID 

Order By ORDER_NUMBER

Oracle Concurrent Programs wise Request Group and Responsibility Name Script

SELECT   frg.request_group_name, frg.description request_group_description,

         fat.application_name, frv.responsibility_name, frv.description,

         frv.responsibility_key, fcp.concurrent_program_name,

         fcp.user_concurrent_program_name, fcp.description cp_description

    FROM fnd_request_group_units frgu,

         fnd_request_groups frg,

         fnd_concurrent_programs_vl fcp,

         fnd_application_tl fat,

         fnd_responsibility_vl frv

   WHERE frg.request_group_id = frgu.request_group_id

     AND frg.application_id = frgu.application_id

     AND frgu.request_unit_id = fcp.concurrent_program_id

     AND frg.application_id = fat.application_id

     AND fat.application_id = frv.application_id

     AND frg.request_group_id = frv.request_group_id

     AND fcp.concurrent_program_name = :p_program_short_name

ORDER BY responsibility_name;

Email us at JGTech

Projectwise PR to Purchase Order Details Script

Projectwise PR to PO Details Report Query

Select MA.*,TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS') SYS_DATE FROM

(SELECT MAIN.project_no,

       MAIN.Project_name,

       MAIN.PR_Number,

       MAIN.PR_Creation_date,

       MAIN.Item_Code,

       MAIN.ITEM_DESCRIPTION,

       MAIN.ORG_ID,       

       organization_id,

       MAIN.PO_HEADER_ID,

       MAIN.PO_line_ID,

       MAIN.PO_Number,

       MAIN.PO_Date,

       MAIN.PO_Status, DECODE(MAIN.VENDOR_NAME,NULL,'ALL',MAIN.VENDOR_NAME,MAIN.VENDOR_NAME) VN,

       MAIN.Vendor_Code,

       MAIN.Vendor_name,

       MAIN.UNIT_MEAS_LOOKUP_CODE,

       MAIN.QUANTITY PR_QTY,

       MAIN.PO_Quantity,

       MAIN.QUANTITY_RECEIVED,

       MAIN.QUANTITY_ACCEPTED,

       MAIN.QUANTITY_REJECTED,

       MAIN.QUANTITY_BILLED,

       MAIN.Balance_Quantity,

       MAIN.invoice_id,

       MAIN.INVOICE_NUMBER,

       MAIN.invoice_date,

       MAIN.invoice_amount,

       MAIN.INVOICE_DOC,

       MAIN.INV_QTY,

       MAIN.DOC_NO,

       MAIN.payment_date,

       MAIN.payment_amount

FROM       

(

Select

(select SEGMENT1 from pa_projects_all where project_id = prd.project_id) Project_no,

(select NAME from pa_projects_all where project_id = prd.project_id) Project_name,

prh.segment1 pr_number,

prh.creation_date pr_creation_date,

(select REPLACE(REPLACE(REPLACE(REPLACE(msi.segment1,CHR(13),NULL),CHR(10),NULL),CHR(9),NULL),'"',null)       

   from mtl_system_items_b msi

  where msi.inventory_item_id = prl.item_id AND msi.organization_id = poll.ship_to_organization_id) Item_Code,

prl.item_description,

prl.quantity,

poh.org_id,

poll.ship_to_organization_id organization_id,

poh.po_header_id,

pol.po_line_id,

poh.segment1 po_number,

poh.creation_date po_date,

DECODE(poh.authorization_status,NULL,'IN COMPLETE',poh.authorization_status) po_status,

(select  segment1 from    ap_suppliers where   vendor_id=poh.vendor_id) Vendor_Code ,

(select  vendor_name from    ap_suppliers where   vendor_id = poh.vendor_id) vendor_name,

pol.unit_meas_lookup_code,

poll.quantity po_quantity,

poll.quantity_received,

poll.quantity_accepted,

poll.quantity_rejected,

poll.quantity_billed,

--(poll.quantity - poll.QUANTITY_ACCEPTED) Balance_Quantity, comment by JG 

(poll.quantity - decode(poll.quantity_accepted,0,poll.QUANTITY_BILLED,poll.quantity_accepted)) Balance_Quantity,

aia.invoice_id,

aia.invoice_num   invoice_number,

aia.invoice_date  invoice_date,

aia.invoice_amount invoice_amount,

aia.doc_sequence_value invoice_doc,

--(Select sum(quantity_invoiced) FROM ap_invoice_lines_all WHERE line_type_lookup_code ='ITEM' 

--                AND org_id=aia.org_id AND invoice_id=aia.invoice_id AND PO_LINE_LOCATION_ID = poll.LINE_LOCATION_ID) INV_QTY,

invoice_qty INV_QTY,                

(Select ac.doc_sequence_value FROM apps.ap_checks_all ac Where aip.check_id = ac.check_id

        AND aip.org_id = ac.org_id) doc_no,

(Select ac.check_date FROM apps.ap_checks_all ac Where aip.check_id = ac.check_id

        AND aip.org_id = ac.org_id)  payment_date,

(Select ac.amount FROM apps.ap_checks_all ac Where aip.check_id = ac.check_id

        AND aip.org_id = ac.org_id) payment_amount             

FROM

         po_requisition_headers_all prh

        ,po_requisition_lines_all prl

        ,po_req_distributions_all prd

        ,po_headers_all poh

        ,po_lines_all pol

        ,po_distributions_all pod

        ,po_line_locations_All poll

        ,(Select org_id,invoice_id,NVL(po_header_id,quick_po_header_id)po_header_id,invoice_num,invoice_date,invoice_amount,doc_sequence_value From ap_invoices_all) aia

,(Select org_id,invoice_id,po_header_id,po_line_id,sum(quantity_invoiced)invoice_qty From ap_invoice_lines_all Group by org_id,invoice_id,po_header_id,po_line_id) aila        

        ,(Select org_id,invoice_id,check_id From apps.ap_invoice_payments_all) aip

WHERE

        prh.requisition_header_id = prl.requisition_header_id

        AND prl.requisition_line_id = prd.requisition_line_id

        and prd.distribution_id = pod.req_distribution_id

        and poh.po_header_id = pod.po_header_id

        and poh.po_header_id = pol.po_header_id

        and poh.PO_HEADER_ID = poll.PO_HEADER_ID

        and pol.PO_LINE_ID   = pod.PO_LINE_ID

        and pod.line_location_id = poll.line_location_id

        and prh.authorization_status IN ('APPROVED','PRE-APPROVED')

        AND aia.po_header_id(+) = poh.po_header_id

        AND aia.org_id(+) = poh.org_id

        AND aia.invoice_id = aila.invoice_id

        AND aia.org_id = aila.org_id

        AND pol.po_line_id   = aila.po_line_id(+)

        AND aia.invoice_id = aip.invoice_id

        AND aia.org_id = aip.org_id

        AND prd.project_id = NVL(:p_project_name,prd.project_id) 

        AND prh.creation_date BETWEEN NVL(:P_START_DATE,prh.creation_date) AND NVL(:P_END_DATE,prh.creation_date)   

        AND prh.segment1 = NVL(:P_REQ_NUM,prh.segment1)  

        AND prl.item_id = NVL(:P_ITEM,prl.item_id)  

UNION------------------------

SELECT

(select segment1 from pa_projects_all where project_id = prd.project_id) project_no,

(select name from pa_projects_all where project_id = prd.project_id) project_name,

prh.segment1 pr_number,

prh.creation_date pr_creation_date,

(select replace(replace(replace(replace(msi.segment1,chr(13),null),chr(10),null),chr(9),null),'"',null)       

   from mtl_system_items_b msi

  where msi.inventory_item_id = prl.item_id and msi.organization_id = prl.destination_organization_id 

  ) item_code,

prl.item_description,

prl.quantity,

poh.org_id,

poll.ship_to_organization_id organization_id,

poh.po_header_id,

pol.po_line_id,

poh.segment1 po_number,

poh.creation_date po_date,

poh.authorization_status po_status,

(select  segment1 from    ap_suppliers where   vendor_id=prl.vendor_id) vendor_code ,

(select  vendor_name  from    ap_suppliers where   vendor_id=prl.vendor_id) vendor_name,

pol.unit_meas_lookup_code,

poll.quantity po_quantity,

poll.quantity_received,

poll.quantity_accepted,

poll.quantity_rejected,

poll.quantity_billed,

(poll.quantity - poll.quantity_accepted) balance_quantity,

null invoice_id,

null invoice_number,

null invoice_date,

null invoice_amount,

null invoice_doc,

null inv_qty,

null doc_no,

null payment_date,

null payment_amount

FROM   po_requisition_headers_all prh,

       po_requisition_lines_all prl,

       po_req_distributions_all prd,

       po_line_locations_all poll,

       po_lines_all pol,

       po_headers_all poh

WHERE prh.requisition_header_id = prl.requisition_header_id

AND prl.requisition_line_id = prd.requisition_line_id

AND poll.line_location_id(+) = prl.line_location_id

AND poll.po_header_id = poh.po_header_id(+)

AND poll.po_line_id = pol.po_line_id(+)

AND prh.authorization_status = 'APPROVED'

AND poll.line_location_id IS NULL

AND prl.closed_code IS NULL

AND NVL (prl.cancel_flag, 'N') <> 'Y'

AND prd.project_id = NVL(:p_project_name,prd.project_id) 

AND prh.creation_date BETWEEN NVL(:P_START_DATE,prh.creation_date) AND NVL(:P_END_DATE,prh.creation_date)

AND prh.segment1 = NVL(:P_REQ_NUM,prh.segment1)

AND prl.item_id = NVL(:P_ITEM,prl.item_id)

)MAIN

WHERE 1=1--MAIN.PROJECT_NAME = DECODE(:P_PROJECT_NAME,NULL,PROJECT_NAME,:P_PROJECT_NAME)

--AND   MAIN.PR_CREATION_DATE BETWEEN NVL(:P_START_DATE,PR_CREATION_DATE) AND NVL(:P_END_DATE,PR_CREATION_DATE)

--AND   MAIN.PR_NUMBER = NVL(:P_REQ_NUM,MAIN.PR_NUMBER)

--AND   MAIN.Item_Code = NVL(:P_ITEM,MAIN.Item_Code)

ORDER BY 1, 3,9,10) MA

WHERE MA.VN =NVL(:P_VENDOR_NAME,MA.VN)

Pocket Hanuman Chalisa (Hindi)

Oracle Forms Personlization list with Condition

SELECT DISTINCT a.form_name, a.enabled, c.user_form_name, d.application_name,
                a.trigger_event, a.trigger_object, a.condition
           FROM fnd_form_custom_rules a,
                fnd_form b,
                fnd_form_tl c,
                fnd_application_tl d
          WHERE enabled = 'Y'
            AND a.form_name = b.form_name
            AND b.form_id = c.form_id
            AND b.application_id = d.application_id

PO and OM Joining

Select * 
FROM rcv_transactions_interface rti,
          rcv_shipment_lines rsl,
          rcv_shipment_headers rsh,
          po_requisition_lines prl,
          mtl_system_items msi,
          mtl_units_of_measure mum1,
          mtl_units_of_measure mum2,
          mtl_units_of_measure mum3,
          mtl_units_of_measure mum4,
          mtl_units_of_measure mum5,
          po_lookup_codes plc1,
          po_lookup_codes plc2,
          po_lookup_codes plc3,
          po_lookup_codes plc4,
          po_lookup_codes plc5,
          po_lookup_codes plc6,
          oe_sold_to_orgs_v oec,
          oe_order_lines_all oel,
          oe_order_headers_all oeh,
          oe_transaction_types_tl oetl,
          oe_transaction_types_all oet,
          po_headers_trx_v ph,
          po_lines_trx_v pl,
          po_line_locations_trx_v pll,
          po_releases_all pr,
          po_vendors pov,
          hr_locations_all_tl hrl,
          mtl_transaction_reasons mtr,
          rcv_routing_headers rrh,
          per_all_people_f hre,
          org_organization_definitions ood,
          fnd_currencies_vl fcv,
          per_jobs pj,
          po_job_associations pja
    WHERE (    rti.transaction_type = plc1.lookup_code
           AND plc1.lookup_type = 'RCV TRANSACTION TYPE'
          )
      AND (rti.shipment_line_id = rsl.shipment_line_id(+))
      AND (rti.requisition_line_id = prl.requisition_line_id(+))
      AND (rti.shipment_header_id = rsh.shipment_header_id(+))
      AND (    msi.inventory_item_id(+) = rti.item_id
           AND NVL (msi.organization_id, rti.to_organization_id) =
                                                        rti.to_organization_id
           AND msi.primary_unit_of_measure = mum1.unit_of_measure(+)
          )
      AND (rti.unit_of_measure = mum2.unit_of_measure(+))
      AND (plc2.lookup_type(+) = 'INSPECTION STATUS'
           AND plc2.lookup_code(+) = rti.inspection_status_code)
      AND oel.line_id(+) = rti.oe_order_line_id
      AND oeh.header_id(+) = rti.oe_order_header_id
      AND oec.customer_id(+) = rsh.customer_id
      AND oet.transaction_type_id(+) = oeh.order_type_id
      AND oet.transaction_type_id = oetl.transaction_type_id(+)
      AND oetl.LANGUAGE(+) = USERENV ('LANG')
      AND oet.transaction_type_code(+) = 'ORDER'
      AND ph.po_header_id(+) = rti.po_header_id
      AND (pl.po_line_id(+) = rti.po_line_id)
      AND (pll.line_location_id(+) = rti.po_line_location_id)
      AND (pr.po_release_id(+) = rti.po_release_id)
      AND pov.vendor_id(+) = rsh.vendor_id
      AND plc3.lookup_type(+) = 'RCV DESTINATION TYPE'
      AND plc3.lookup_code(+) = rti.destination_type_code
      AND hrl.location_id(+) =
                             NVL (rti.deliver_to_location_id, rti.location_id)
      AND hrl.LANGUAGE(+) = USERENV ('LANG')
      AND rrh.routing_header_id(+) = rti.routing_header_id
      AND mum3.unit_of_measure(+) = pl.unit_meas_lookup_code
      AND mum4.unit_of_measure(+) = pll.secondary_unit_of_measure
      AND mum5.uom_code(+) = oel.ordered_quantity_uom2
      AND mtr.reason_id(+) = rti.reason_id
      AND hre.person_id(+) = rti.deliver_to_person_id
      AND TRUNC (SYSDATE) BETWEEN hre.effective_start_date(+) AND hre.effective_end_date(+)
      AND DECODE (hr_security.view_all,
                  'Y', 'TRUE',
                  hr_security.show_person (hre.person_id(+), hre.current_applicant_flag(+),
                                           hre.current_employee_flag(+), hre.current_npw_flag(+),
                                           hre.employee_number(+), hre.applicant_number(+),
                                           hre.npw_number(+))
                 ) = 'TRUE'
      AND DECODE (hr_general.get_xbg_profile,
                  'Y', hre.business_group_id(+),
                  hr_general.get_business_group_id
                 ) = hre.business_group_id(+)
      AND ood.organization_id(+) = rti.from_organization_id
      AND DECODE (rti.source_document_code,
                  'PO', 'PO TYPE',
                  'SHIPMENT SOURCE TYPE'
                 ) = plc4.lookup_type
      AND DECODE (rti.transaction_type,
                  'UNORDERED', 'STANDARD',
                  DECODE (rti.source_document_code,
                          'PO', ph.type_lookup_code,
                          rsh.receipt_source_code
                         )
                 ) = plc4.lookup_code
      AND plc5.lookup_type = 'TRANSACTION STATUS'
      AND plc5.lookup_code = rti.transaction_status_code
      AND plc6.lookup_type = 'RCV PROCESSING MODE'
      AND plc6.lookup_code = rti.processing_mode_code
      AND fcv.currency_code(+) = rti.currency_code
      AND pj.job_id(+) = rti.job_id
      AND pja.job_id(+) = rti.job_id
      AND NVL (ph.org_id, -99) = NVL (pll.org_id, -99);

Calculate Average Completed Time on Concurrent Program

/*Calculate Average Oracle Concurrent Program Completed Time*/

select avg(fr1.actual_completion_date - fr1.actual_start_date) avgCPtime

  from fnd_concurrent_requests fr1

         , fnd_concurrent_requests fr2

  where fr2.request_id = NVL(:P_request_ID, fr2.request_id)

  and fr1.concurrent_program_id = fr2.concurrent_program_id

  and fr1.program_application_id = fr2.program_application_id

  and fr1.actual_start_date is not null

  and fr1.actual_completion_date is not null;

Oracle Apps R12 Legal Entity details

--Oracle R12 Legal Entity details Query

SELECT   xep.legal_entity_id, xep.party_id, xep.legal_entity_identifier,

         xep.name legal_entity, hp.party_name legal_entity_name

    FROM xle_entity_profiles xep, hz_parties hp

   WHERE xep.party_id = hp.party_id

ORDER BY hp.party_name

FNDLOAD Script for AOL and WebADI objects.

Before run FNDLOAD check the Set environment variables (.env)

. $APPL_TOP/custom< CONTEXT_NAME >.env (e.g. APPSICEBSDEV.env)

Following are the usage list of FNDLOAD to download and upload various AOL objects.

Concurrent Program

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXCUST_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXCUST" CONCURRENT_PROGRAM_NAME="XXCUST_CP"

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

Data Definition and Data Template

FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct XXCUST_DD.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXCUST DATA_SOURCE_CODE=XXCUST_DS

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

Lookups

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

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

Descriptive Flexfield

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXPO_MY_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="FND_COMMON_LOOKUPS" DESCRIPTIVE_FLEX_CONTEXT_CODE="XXJG"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct  XXJG.ldt UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Message

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XXCUST_MESG.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME="XX_CUST" MESSAGE_NAME="XX_CUST_MESG%"

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

Request Set and Link

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_RS.ldt REQ_SET REQUEST_SET_NAME='XX_CUST_RS'

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XXCUST_LNK.ldt REQ_SET_LINKS REQUEST_SET_NAME='XX_CUST_LNK'


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

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

Form Function

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_FUNC.ldt FUNCTION FUNC_APP_SHORT_NAME='XX_CUST' FUNCTION_NAME='XX_CUST_FUNC'

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XX_CUST_FUNC.ldt

Profile

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct XXCUST_PROF.ldt PROFILE PROFILE_NAME="XXCUST_PROFILE" APPLICATION_SHORT_NAME="XXCUST"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct XXCUST_FUNC.ldt

Menu

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt MENU MENU_NAME="XXCUST_MENU"

FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct XXCUST_MENU.ldt


-------------------------
Following are the usage list of FNDLOAD to download and upload various WebADI objects.
------------------------

• Integrators
• Contents
• Layouts
• Mappings
• Parameter List
• Components

Integrator
Command to Download Integrator

FNDLOAD apps/apps 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XX_INTEGRATOR_LDT_FILE.ldt BNE_INTEGRATORS INTEGRATOR_ASN=”XX_CUST” INTEGRATOR_CODE=”XX_INTEGRATOR_CODE”

Command to Upload Integrator

FNDLOAD apps/apps 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct XX_INTEGRATOR_LDT_FILE.ldt

Contents
Download
FNDLOAD apps/apps 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecont.lct XX_CONTENT_LDT_FILE.ldt BNE_CONTENTS CONTENT_ASN=”XX_CUST” CONTENT_CODE=”XX_CONTENT_CODE”

Upload
FNDLOAD apps/apps 0 Y UPLOAD $BNE_TOP/patch/115/import/bnecont.lct XX_CONTENT_LDT_FILE.ldt

Layout:
Download
FNDLOAD apps/apps 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnelay.lct XX_LAYOUT_LDT_FILE.ldt BNE_LAYOUTS LAYOUT_ASN=”XX_CUST” LAYOUT_CODE=”XX_LAYOUT_CODE”

Upload
FNDLOAD apps/apps 0 Y UPLOAD $BNE_TOP/patch/115/import/bnelay.lct XX_LAYOUT_LDT_FILE.ldt

Mappings
Download
FNDLOAD apps/apps 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnemap.lct XX_MAPPINGS_LDT_FILE.ldt BNE_MAPPINGS MAPPING_ASN=”XX_CUST” MAPPING_CODE=”XX_MAPPINGS_CODE”

Upload
FNDLOAD apps/apps 0 Y UPLOAD $BNE_TOP/patch/115/import/bnemap.lct XX_MAPPINGS_LDT_FILE.ldt

Parameter Lists
Download
FNDLOAD apps/apps 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneparamlist.lct XX_PARAM_LIST_LDT_FILE.ldt BNE_PARAM_LISTS PARAM_LIST_ASN=”XX_CUST” PARAM_LIST_CODE=”XX_PARAM_LIST_CODE”

Upload
FNDLOAD apps/apps 0 Y UPLOAD $BNE_TOP/patch/115/import/bneparamlist.lct XX_PARAM_LIST_LDT_FILE.ldt

Components
Download
FNDLOAD apps/apps 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecomp.lct XX_COMPONENT_LDT_FILE.ldt BNE_COMPONENTS COMPONENT_ASN=”XXOM” COMPONENT_CODE=”XX_COMPONENT_CODE”

Upload
FNDLOAD apps/apps 0 Y UPLOAD $BNE_TOP/patch/115/import/bnecomp.lct XX_COMPONENT_LDT_FILE.ldt


Workflow
Download
WFLOAD apps/<<Apps Password>> 0 Y DOWNLOAD JGAPINVW.wft JGAPINVW

Upload
WFLOAD apps/<<Apps Password>> 0 Y UPLOAD JGAPINVW.wft JGAPINVW

Upgrade

WFLOAD apps/<<Apps Password>> 0 Y UPGRADE JGAPINVW.wft JGAPINVW

SoftLinkx

ln -s $FND_TOP/bin/fndcpesr $XXJG_TOP/bin/XXJG_TEST_P

File transfer from Windows to Linux

---CSV file move

The PuTTY Secure Copy client(PSCP), is a tool for transferring files securely between computers using an SSH connection:

First of  Install from https://www.putty.org/
 
Putty installer least version. other wise Set PATH in environment variable
C:\Program Files\PuTTY\;

Open Command Prompt or CMD.

Run the below command :

pscp C:\Users\Documents\AR_INVOICE_UPLOAD.csv appldev@192.99.999.99:/u01/DEV/apps/apps_st/appl/xxcus/12.0.0/bin/AR_INVOICE_UPLOAD.csv

Then csv file transfer from windows to server .

Find the Operating Unit with AR and AP and Company Code and Location

---Find the Operating Unit with AR and AP and Company code and Location

SELECT DISTINCT hrl.country,
                hroutl_bg.NAME bg,
                hroutl_bg.organization_id,
                lep.legal_entity_id,
                lep.NAME legal_entity,
                hroutl_ou.NAME ou_name,
                hroutl_ou.organization_id org_id,
                hrl.location_id,
                hrl.location_code,
                glev.flex_segment_value company_code,
                (SELECT NAME FROM hr_operating_units
                              WHERE organization_id = arsp.org_id) ar_ou,
               (SELECT NAME FROM hr_operating_units
                              WHERE organization_id = apsp.org_id) ap_ou              
  FROM xle_entity_profiles          lep,
       xle_registrations            reg,
       hr_locations_all             hrl,
       hz_parties                   hzp,
       fnd_territories_vl           ter,
       hr_operating_units           hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units        gloperatingunitseo,
       gl_legal_entities_bsvs       glev,
       ar_system_parameters_all     arsp,
       ap_system_parameters_all     apsp
 WHERE lep.transacting_entity_flag        = 'Y'
   AND lep.party_id                       = hzp.party_id
   AND lep.legal_entity_id                = reg.source_id
   AND reg.source_table                   = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id                    = reg.location_id
   AND reg.identifying_flag               = 'Y'
   AND ter.territory_code                 = hrl.country
   AND lep.legal_entity_id                = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id          = hro.business_group_id
   AND hroutl_ou.organization_id          = hro.organization_id
   AND glev.legal_entity_id               = lep.legal_entity_id
   AND hro.set_of_books_id = arsp.set_of_books_id(+)
   AND hro.organization_id = arsp.org_id(+)
   AND hro.set_of_books_id = apsp.set_of_books_id(+)
   AND hro.organization_id = apsp.org_id(+)
Order by company_code  

How to Find Currency enabled in Oracle R12

SELECT currency_code, name, description, issuing_territory_code
  FROM fnd_currencies_vl
  WHERE enabled_flag ='Y'

How To Recover Hidden Files and Folders Due to Virus

Flow the step:
 

1.Open administrative command prompt/ CMD.
2.Go to your pen-drive drive letter. Suppose your USB disk is D:.
       Type D: and press Enter
3.Now type the below command to remove system file, hidden read-only attribute
      attrib -s -h -r /s /d *.*
4.Delete Autorun.inf from USB – Disk.
5.This makes all files visible and you are now able to delete them.
      el *.lnk
6.Now to remove the shortcuts from pen-drive , Check the size of any One or Few Shortcuts, all be same.
7.Delete suspicious files, on errror check task manager for process with same name or any suspicious process and end it.
8.Open Explorer and right Click on Pen-drive ,
      Click Open.
9.Search with Advance Search for all files of same size in Pen-drive.
10.Select All and press Delete.

How to find concurrent program or request will be Completed and Terminated?

SELECT r.request_id, u.user_name requestor, pt.user_concurrent_program_name,
       r.phase_code, r.status_code, r.priority_request_id, r.priority,
       r.request_date, r.requested_by, r.requested_start_date, r.hold_flag,
       r.has_sub_request, r.is_sub_request, r.update_protected,
       r.queue_method_code, r.responsibility_application_id,
       r.responsibility_id, r.save_output_flag, r.last_update_date,
       r.last_updated_by, r.last_update_login, r.printer, r.print_style,
       r.parent_request_id, r.controlling_manager, r.actual_start_date,
       r.actual_completion_date, r.completion_text, r.argument_text,
       r.implicit_code, r.request_type, r.program_application_id,
       r.concurrent_program_id, pb.concurrent_program_name program_short_name,
       pb.execution_method_code, pb.enabled_flag enabled,
       DECODE (r.description,
               NULL, pt.user_concurrent_program_name,
               r.description || ' (' || pt.user_concurrent_program_name || ')'
              ) program_name,
       pb.printer_name fcp_printer, pb.output_print_style fcp_print_style,
       pb.required_style fcp_required_style,
       s.user_printer_style_name user_print_style, r.description
  FROM applsys.fnd_concurrent_programs_tl pt,
       applsys.fnd_concurrent_programs pb,
       applsys.fnd_user u,
       applsys.fnd_printer_styles_tl s,
       applsys.fnd_concurrent_requests r
 WHERE pb.application_id = r.program_application_id
   AND pb.concurrent_program_id = r.concurrent_program_id
   AND pb.application_id = pt.application_id
   AND pb.concurrent_program_id = pt.concurrent_program_id
   AND pt.LANGUAGE = USERENV ('LANG')
   AND u.user_id = r.requested_by
   AND s.printer_style_name(+) = r.print_style
   AND s.LANGUAGE(+) = USERENV ('LANG')
   AND u.user_name = NVL(:p_requestor,u.user_name)
   AND phase_code = 'C' -- Completed
   AND status_code = 'C'
   UNION
SELECT r.request_id, u.user_name requestor, pt.user_concurrent_program_name,
       r.phase_code, r.status_code, r.priority_request_id, r.priority,
       r.request_date, r.requested_by, r.requested_start_date, r.hold_flag,
       r.has_sub_request, r.is_sub_request, r.update_protected,
       r.queue_method_code, r.responsibility_application_id,
       r.responsibility_id, r.save_output_flag, r.last_update_date,
       r.last_updated_by, r.last_update_login, r.printer, r.print_style,
       r.parent_request_id, r.controlling_manager, r.actual_start_date,
       r.actual_completion_date, r.completion_text, r.argument_text,
       r.implicit_code, r.request_type, r.program_application_id,
       r.concurrent_program_id, pb.concurrent_program_name program_short_name,
       pb.execution_method_code, pb.enabled_flag enabled,
       DECODE (r.description,
               NULL, pt.user_concurrent_program_name,
               r.description || ' (' || pt.user_concurrent_program_name || ')'
              ) program_name,
       pb.printer_name fcp_printer, pb.output_print_style fcp_print_style,
       pb.required_style fcp_required_style,
       s.user_printer_style_name user_print_style, r.description
  FROM applsys.fnd_concurrent_programs_tl pt,
       applsys.fnd_concurrent_programs pb,
       applsys.fnd_user u,
       applsys.fnd_printer_styles_tl s,
       applsys.fnd_concurrent_requests r
 WHERE pb.application_id = r.program_application_id
   AND pb.concurrent_program_id = r.concurrent_program_id
   AND pb.application_id = pt.application_id
   AND pb.concurrent_program_id = pt.concurrent_program_id
   AND pt.LANGUAGE = USERENV ('LANG')
   AND u.user_id = r.requested_by
   AND s.printer_style_name(+) = r.print_style
   AND s.LANGUAGE(+) = USERENV ('LANG')
   AND u.user_name = NVL(:p_requestor,u.user_name)
   AND status_code = 'X'