Oracle User Defined Aggregate Function Script

The WM_CONCAT function and LISTAGG function if not supported then  you can create your own user defined aggregate function as below:
 
-----------------------
CREATE OR REPLACE TYPE xxtex_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  xxtex_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  xxtex_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   xxtex_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  xxtex_string_agg,
                                     ctx2  IN      xxtex_string_agg)
    RETURN NUMBER
);



------------------------------
CREATE OR REPLACE TYPE BODY xxtex_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  xxtex_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := xxtex_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  xxtex_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   xxtex_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  xxtex_string_agg,
                                     ctx2  IN      xxtex_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;


---------------------------------------
CREATE OR REPLACE FUNCTION xxtex_string_agg_f (p_input VARCHAR2)
   RETURN VARCHAR2 PARALLEL_ENABLE AGGREGATE
   USING xxtex_string_agg;


------------------------------using in select statement -----------------
SELECT  xxtex_string_agg_f(distinct pa.name) AS project_name
FROM po_distributions_all po_dist,pa_projects_all pa
     WHERE po_dist.po_header_id= :po_header_id
           AND po_dist.project_id=pa.project_id;

Return 91234,92345,93456,94567,95678

Purchasing

PO

SELECT   fu.user_name po_created_by,pha.org_id ,
         (SELECT NAME || ' ( ' || short_code || ' )'
            FROM hr_operating_units
           WHERE organization_id = pha.org_id) ou_name,
         pha.segment1 po_num,pha.revision_num, pha.approved_date,SUM (pla.quantity) po_qty, SUM (pla.unit_price) po_price,
        --SUM(pla.quantity*pla.unit_price) po_value,
         pha.authorization_status, pha.creation_date po_creation_date,
         wn.to_user, wn.subject, wn.original_recipient, wn.begin_date,
         TRUNC (SYSDATE - wn.begin_date) no_days_pending
        ,d.name approval_path,wn.from_role approve_by,pha.po_header_id,wn.notification_id,pha.agent_id
    FROM wf_notifications wn,
         po_headers_all pha,
         fnd_user fu,
         po_lines_all pla
         ,po_action_history a,per_position_structures_v d
   WHERE 1=1
     AND pha.org_id = pla.org_id
     AND pha.authorization_status = 'APPROVED'
     AND notification_id =(Select MAX(notification_id) FROM wf_notifications wn Where pha.segment1 = user_key )
     AND a.SEQUENCE_NUM = (Select max(aa.sequence_num) FROM po_action_history aa where  aa.object_id = a.object_id and ACTION_CODE='SUBMIT' )
--     AND MESSAGE_TYPE = 'POAPPRV'
--     AND message_name = 'PO_PO_APPROVE_PDF'
      AND a.object_id = pha.po_header_id
      AND a.approval_path_id = d.position_structure_id
     --AND wn.end_date IS NULL
     AND pha.segment1 = user_key
     AND pha.created_by = fu.user_id
     AND pha.po_header_id = pla.po_header_id
     AND NVL (pha.cancel_flag, 'N') <> 'Y'
     AND pha.org_id = NVL(:P_Org_id,pha.org_id)
     AND pha.agent_id = NVL((select employee_id from fnd_user where user_id=:p_buyer_id),pha.agent_id)
     --AND trunc(a.action_date) >= '08-JAN-2019'
     ---AND trunc(pha.approved_date) between trunc(sysdate-1) and trunc(sysdate)
     ----AND trunc(wn.begin_date) between trunc(:P_from_date) and trunc(:P_to_date)
      --AND NOT EXISTS (SELECT 1 FROM po_action_history pah, per_position_structures_v pps WHERE pps.name LIKE '%Emergency%'
        AND a.SEQUENCE_NUM=(Select max(aa.sequence_num) FROM po_action_history aa where  aa.object_id = a.object_id and ACTION_CODE='SUBMIT')
--        AND pah.approval_path_id = pps.position_structure_id AND pha.po_header_id = pah.object_id
GROUP BY pha.authorization_status,
         pha.creation_date,pha.approved_date,
         pha.org_id,
         wn.to_user,
         wn.subject,
         wn.original_recipient,
         fu.user_name,
         wn.begin_date,d.name,pha.agent_id,
         pha.segment1,pha.revision_num,wn.from_role, pha.po_header_id, wn.notification_id     
ORDER BY 1, 2

Oracle GST Tax for AR Receipts Query

SELECT   ara.receivable_application_id, ara.cash_receipt_id,
         jtl.tax_rate_id, jtl.tax_regime_id
         ,jtdf.det_factor_id det_factor_id
         ,jtl.tax_line_id tax_line_id
         ,(Select trx_number from ra_customer_trx_all where customer_trx_id=ara.applied_customer_trx_id)invoice_number
         ,trim(acr.receipt_number)receipt_number
         ,acr.RECEIPT_DATE
         ,ara.gl_date
         ,ara.line_applied line_applied_amt
         ,jtdf.line_amt trx_amount
         ,jtl.rounded_tax_amt_trx_curr tax_amt_trx_curr
         ,jtl.rounded_tax_amt_fun_curr tax_amt_fun_curr
         ,xal.*
    FROM ar_receivable_applications_all ara,
         ar_cash_receipts_all acr,
         jai_tax_det_factors jtdf,
         jai_tax_lines jtl
         ,xla.xla_transaction_entities te
         ,xla_ae_headers xah
         ,xla_ae_lines xal
WHERE    trim(acr.receipt_number) = NVL(:ar_receipt_number,trim(acr.receipt_number))
     AND ara.cash_receipt_id = acr.cash_receipt_id
     AND ara.status = 'APP'
     AND ara.display = 'Y'
     AND ara.set_of_books_id = :p_ledger_id
     AND ara.org_id = :p_org_id
     AND ara.gl_date BETWEEN :p_start_date AND :p_end_date
     AND ara.cash_receipt_id = jtdf.trx_id
     AND jtdf.application_id = 222
     AND jtdf.entity_code = 'RECEIPTS'
     AND jtdf.event_class_code = 'RECEIPT'
     AND jtdf.det_factor_id = jtl.det_factor_id
     and nvl (te.source_id_int_1, -99) = acr.cash_receipt_id
   and te.application_id = 222
   and te.entity_code = 'RECEIPTS'
   and xah.entity_id = te.entity_id
   and xah.application_id = te.application_id
   and xah.event_type_code ='RECP_CREATE'-- 'MANUAL'
   and xah.ae_header_id = xal.ae_header_id
   and xal.accounting_class_code='RECEIVABLE'

Getting Error in R12 FNDLOAD: command not found

uploading or downloading FNDLOADS we may get following error

FNDLOAD: command not found

Solution: Apply . Env file as shown below
After apply .env file the error will be resoled FNDLOAD: command not found issue 

Run command:

cd /prod/oracle/apps/apps_st/appl/

. APPSGST_testr12.env

Oracle Purchasing- PO Match Approval Level column store

In Oracle Purchasing Match options column store in INSPECTION_REQUIRED_FLAG and RECEIPT_REQUIRED_FLAG fields of table PO_LINE_LOCATIONS_ALL.

2-way matching 
--------------------
INSPECTION_REQUIRED_FLAG = 'N' and RECEIPT_REQUIRED_FLAG = 'N'
3-way matching 
---------------------
INSPECTION_REQUIRED_FLAG = 'N' and RECEIPT_REQUIRED_FLAG = 'Y' 

4-way matching 
---------------------
INSPECTION_REQUIRED_FLAG = 'Y' and RECEIPT_REQUIRED_FLAG = 'Y' 



MATCH_OPTION fields
---------------------------------

P Match to Purchase Order 
R Match to Receipt 
 

Oracle R12 Vendor Details Query

----Oracle R12 Supplier name and Address  
SELECT     (Select Name from hr_operating_units where organization_id=aps.ORG_ID) OU_NAME,
           ap.segment1 vendor_code,
           ap.vendor_name,
           aps.vendor_site_code vendor_Site_Code,
           ap.VENDOR_TYPE_LOOKUP_CODE Type_Code,
           aps.address_line1,
           aps.address_line2,
           aps.address_line3,
           aps.city,
           aps.state,
           aps.zip,
           aps.Country,
           apc.AREA_CODE,
           apc.PHONE,
           apc.FAX_AREA_CODE,
           apc.FAX,
           apc.EMAIL_ADDRESS
    FROM   ap_suppliers ap,
           ap_supplier_sites_all aps,
           ap_supplier_contacts apc
--           hz_party_sites hps,
--           zx_party_tax_profile zp
--           zx_registrations zr
   WHERE       ap.vendor_id = aps.vendor_id
           AND apc.VENDOR_CONTACT_ID(+) = ap.VENDOR_ID
           AND NVL(ap.VENDOR_TYPE_LOOKUP_CODE,'NA') <>  'EMPLOYEE'
           AND aps.vendor_site_code NOT IN 'OFFICE'
           AND ap.enabled_flag ='Y'
--           AND hps.PARTY_SITE_ID = aps.PARTY_SITE_ID
--           AND hps.party_id = zp.party_id
--           AND zp.PARTY_TYPE_CODE = 'THIRD_PARTY'
--           AND PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
--           AND zr.party_tax_profile_id(+)= zp.party_tax_profile_id
--           AND ap.segment1='2406'
ORDER BY   ap.segment1, ap.vendor_name

Run the Workflow backend or PL/SQL Code

The below script to run workflow from PL/SQL code-


DECLARE
   v_itemtype   VARCHAR2 (50);
   v_itemkey    VARCHAR2 (50);
   v_process    VARCHAR2 (50);
   v_userkey    VARCHAR2 (50);
BEGIN
   v_itemtype := 'TEXPO_W1';
   v_itemkey := '123';
   v_userkey := '123';
   v_process := 'TEX_PO_P';
   wf_engine.threshold := -1;
   wf_engine.createprocess (v_itemtype, v_itemkey, v_process);
   wf_engine.setitemuserkey (v_itemtype, v_itemkey, v_userkey);
   wf_engine.setitemowner (v_itemtype, v_itemkey, 'SYSADMIN');
   wf_engine.startprocess (v_itemtype, v_itemkey);
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

Query of Calculate Balance Owed Oracle Payables

SELECT   (SELECT NAME
            FROM hr_operating_units
           WHERE organization_id = aia.org_id) operating_unit_name,
         (SELECT segment1
            FROM ap_suppliers
           WHERE vendor_id = aia.vendor_id) vendor_code,
         (SELECT vendor_name
            FROM ap_suppliers
           WHERE vendor_id = aia.vendor_id) vendor_name,
         SUM (NVL (ps.gross_amount, 0)) balance,
         SUM (NVL (ps.amount_remaining, 0)) unpaid_amount,
         COUNT (aia.invoice_num) number_of_invoices
    FROM ap_invoices_all aia,
         ap_payment_schedules_all ps,
         ap_supplier_sites_all vs
   WHERE 1 = 1
     AND ps.amount_remaining != 0
     AND ps.invoice_id = aia.invoice_id
     AND NVL (aia.payment_status_flag, 'N') != 'Y'
     AND aia.cancelled_date IS NULL
     AND vs.vendor_site_id = aia.vendor_site_id
     AND NOT (    aia.invoice_type_lookup_code = 'PREPAYMENT'
              AND NVL (aia.payment_status_flag, 'N') = 'N'
             )
     AND aia.org_id = NVL (:p_org_id, aia.org_id)
     AND aia.vendor_id = NVL (:p_vendor_id, aia.vendor_id)
     AND aia.vendor_site_id = NVL (:p_vendor_site_id, aia.vendor_site_id)
GROUP BY aia.org_id, aia.vendor_id
ORDER BY vendor_code

Oracle GL Main Query for Reconciliation

Step 1:
SELECT  gjh.je_source, gjh.je_category,
       sum(NVL(gjl.accounted_dr,0)) accounted_dr, sum(NVL(gjl.accounted_cr,0)) accounted_cr,
       sum(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0) ) net_balance
  FROM gl_je_lines gjl,
       gl_je_headers gjh,
       gl_code_combinations gcc
 WHERE gjl.je_header_id = gjh.je_header_id

   AND gjl.period_name NOT LIKE 'ADJ%' -- Adjustment Period Excluded
   AND EXISTS (
               SELECT 1
                 FROM gl_code_combinations gc
                WHERE gc.segment1 = :P_COMPANY
                  AND gc.segment4 = :P_ACCT_CD AND gc.code_combination_id= gcc.code_combination_id)
   AND gjl.code_combination_id = gcc.code_combination_id
   AND gjl.status = 'P'
   AND TRUNC (gjl.effective_date) <= :p_end_date
Group By  gjh.je_source, gjh.je_category 
Order By 1

Step 2:
  
SELECT  gjh.je_source, gjh.je_category, gjb.NAME AS je_batch_name, gjh.NAME AS je_name,
       gjh.period_name, --TRUNC (gjl.effective_date) AS gl_date,
--     TO_CHAR (gjh.doc_sequence_value) AS doc_number,
--     gjh.currency_code AS gl_currency_code,
--     gcc.concatenated_segments, gjl.description AS naration,
       sum(NVL(gjl.accounted_dr,0)) accounted_dr, sum(NVL(gjl.accounted_cr,0)) accounted_cr,
       sum(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0) ) net_balance
  FROM gl_je_lines gjl,
       gl_je_headers gjh,
       gl_code_combinations_kfv gcc,
       gl_je_batches gjb
 WHERE gjl.je_header_id = gjh.je_header_id
   AND gjl.period_name NOT LIKE 'ADJ%' -- Adjustment Period Excluded  
   AND EXISTS (
               SELECT 1
                 FROM gl_code_combinations gc
                WHERE gc.segment1 = :P_COMPANY
                  AND gc.segment4 = :P_ACCT_CD AND    gc.code_combination_id= gcc.code_combination_id)
   AND gjl.code_combination_id = gcc.code_combination_id
   AND gjh.je_batch_id = gjb.je_batch_id
   AND gjl.status = 'P'
   AND TRUNC (gjl.effective_date) <= :p_end_date
Group By  gjh.je_source, gjh.je_category,gjb.NAME, gjh.NAME,gjh.period_name 
Order By 1 

Find the Concurrent Program Execution Method

Spawned : Your concurrent program is a stand-alone program in C or Pro*C.

Host : Your concurrent program is written in a script for your operating system.

Immediate : Your concurrent program is a subroutine written in C or Pro*C. Immediate programs are linked in with your concurrent manage and must be included in the manager's program library.

Oracle Reports :
Your concurrent program is an Oracle Reports script.

PL/SQL Stored Procedure : Your concurrent program is a stored procedure written in PL/SQL.

Java Stored Procedure :
Your concurrent program is a Java stored procedure.

Java Concurrent Program :
Your concurrent program is a program written in Java.

Multi Language Function : A multi-language support function (MLS function) is a function that supports running concurrent programs in multiple languages (as well as territories and numeric character settings). You should not choose a multi-language function in the Executable: Name field. If you have an MLS function for your program (in addition to an appropriate concurrent program executable), you specify it in the MLS Function field.

SQL*Loader : Your concurrent program is a SQL*Loader program.

SQL*Plus : Your concurrent program is a SQL*Plus or PL/SQL script.






















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 the Supplier extraction with account script for EBS Tax

SELECT     ap.segment1 vendor_code,
           ap.vendor_name Suppliers_Name,
           aps.vendor_site_code Supplier_Site_Code,
           ap.VENDOR_TYPE_LOOKUP_CODE Type_Code,
           aps.address_line1,
           aps.address_line2,
           aps.address_line3,
           aps.city,
           aps.state,
           aps.zip,
           aps.Country,
           apc.AREA_CODE,
           apc.PHONE,
           apc.FAX_AREA_CODE,
           apc.FAX,
           apc.EMAIL_ADDRESS,
           ap.PAYMENT_CURRENCY_CODE CURRENCY_CODE,
           (Select Name from hr_operating_units where organization_id=aps.ORG_ID) OU_NAME,
           aps.vat_registration_num,
           aps.auto_tax_calc_flag,
           ap.offset_tax_flag offset_tax_flag_header,
           aps.offset_tax_flag offset_tax_flag_line,
           aps.attribute2 free_field,
           (Select LOCATION_CODE from hr_locations where location_id= aps.ship_to_location_id) ship_location,
           (Select LOCATION_CODE from hr_locations where location_id= aps.bill_to_location_id) bill_location,
           (SELECT terms.NAME
               From ap_terms_tl terms
               WHERE terms.term_id = ap.terms_id
               AND terms.LANGUAGE(+) = USERENV ('LANG')
               AND terms.enabled_flag(+) = 'Y')AS terms_header,
           (SELECT terms.NAME
               From ap_terms_tl terms
               WHERE terms.term_id = aps.terms_id
               AND terms.LANGUAGE(+) = USERENV ('LANG')
               AND terms.enabled_flag(+) = 'Y')AS terms_line,
           ap.attribute9 supplier_category,
            (SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id=aps.ACCTS_PAY_CODE_COMBINATION_ID) ACCTS_AC,
       (SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id=aps.PREPAY_CODE_COMBINATION_ID) PRPAY_AC
    FROM   ap_suppliers ap,
           ap_supplier_sites_all aps,
           ap_supplier_contacts apc
   WHERE       ap.vendor_id = aps.vendor_id
           AND apc.VENDOR_CONTACT_ID(+) = ap.VENDOR_ID
           AND ap.VENDOR_TYPE_LOOKUP_CODE <>  'EMPLOYEE'
           AND aps.vendor_site_code NOT IN 'OFFICE'
           AND ap.enabled_flag ='Y'
           --AND ap.segment1='19173'
ORDER BY   ap.vendor_name,ap.segment1