How to find the concurrent program and concurrent program short name using the query

select fcpt.user_concurrent_program_name, fcp.concurrent_program_name from
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_programs fcp
where
fcp.CONCURRENT_PROGRAM_ID= fcpt.CONCURRENT_PROGRAM_ID
and
fcpt.created_by =1706



Profile Value in oracle apps


If you want the query to display the data from different org_id's dont use org_id hardcoded in your query. Use Profile value for this.


SELECT  sum(AIA.invoice_amount)                       INVOICE_AMT
             ,sum(NVL(AIPA.amount,APSA.amount_remaining))   PAYMENT_AMT
            FROM
                   ap_invoices_all AIA
                  ,ap_checks_all ACA
                  ,ap_invoice_payments_all AIPA
                  ,ap_payment_schedules_all APSA
                  ,pa_projects_all PPA
             WHERE AIA.invoice_id = AIPA.invoice_id (+)
             AND APSA.invoice_id  = AIA.invoice_id
             AND AIA.vendor_id    = NVL(:P_SUPPLIER_NAME,AIA.vendor_id)
             AND AIA.vendor_site_id = NVL(:P_SUPPLIER_SITE_NAME, AIA.vendor_site_id)
             AND NVL(ACA.check_date,SYSDATE) >= NVL(:P_FROM_DATE,NVL(ACA.check_date,SYSDATE))
             AND NVL(ACA.check_date,SYSDATE) <= NVL(:P_TO_DATE,NVL(ACA.check_date,SYSDATE))
             AND AIPA.check_id = ACA.check_id (+)
             AND AIA.project_id = PPA.project_id (+)
             AND AIA.invoice_type_lookup_code = 'STANDARD'
           AND AIA.org_id = FND_PROFILE.value('ORG_ID')
             GROUP BY AIA.vendor_id, AIA.vendor_site_id,aia.org_id
             

Combination of replace and regexp_replace

The Combination of using replace and regexp_replace

SELECT    
         clause CLAUSE, regexp_replace(REPLACE(replace(replace(replace(replace(replace(tc_description,'<br>',chr(10)),'<br/>',chr(10)),'<br />',chr(10)),
          '</br>',chr(10)),'</ br>',chr(10)),'P_OP_MARGIN',NVL(:CP_OPERATING_MARGIN,'8')),'&', '\&'||'amp;') TC_DESCRIPTION
         FROM
         BRITANNIA_TERM_CONDTION_MSTR NTCM
         ,britannia_term_clause_mstr  NTCLM
         WHERE
         NTCM.status                 = 1
         AND NTCLM.status            = 1
         AND TC_TYPE                 = 15
         AND TC_NAME = 127
           AND NTCM.term_condition_id  = NTCLM.TERM_CONDITION_ID
         ORDER BY CLAUSE;

Items Uploading to MTL_SYSTEM_ITEMS_B

Items Uploading

1.     Prepare the Flex Data

2.     Prepare the Item Data

3.     Move the csv’s into the server.

4.     Go to BRITANNIA Inventory Super User Responsibility and run the concurrent program BRITANNIA ITEM FLEX LOADER

And BRITANNIA Item Flex Conversion.

5.     Check the Flex has been created or not using the query

select 

attribute1,attribute2,attribute3,description_attribute3,attribute4,description ,status

 from xxBRITANNIA_flex_item_stg

where trunc(creation_date)=trunc(sysdate)

and attribute1 ='99'

and status='PROCESSED';

6.     Run the BRITANNIA ITEM LOADER Concurrent Program and provide the path of the file in the Parameter.

7.     Run the BRITANNIA ITEM CONVERSION Concurrent Program and after that Run the IMPORT ITEMS Concurrent Program.

This has to be run in the Master Organization using Change Organization and the items have been loaded. Check using the Query.

select * from mtl_system_items_b

where trunc(creation_date)=trunc(sysdate)

and organization_id=84

8.     After that run the concurrent Program Assign Items to the Child Organization Concurrent Program and specify the Organization Id for uploading the items.

9.     After that Apply Item Category Assignment using the program.

*********************Item Category Assignment****************

DECLARE

x NUMBER := 0;

CURSOR lcu_items

IS

Select INVENTORY_ITEM_ID, category_id

from MTL_SYSTEM_ITEMS_B MSIB

,MTL_CATEGORIES MC

WHERE TRUNC(MSIB.CREATION_DATE) = TRUNC(SYSDATE)

AND ORGANIZATION_ID = 84

AND MC.segment1 = MSIB.segment1

AND MC.segment2 = MSIB.segment2;

BEGIN

FOR i IN lcu_items LOOP

x := x+1;

UPDATE inv.mtl_item_categories

set category_id = i.category_id

where inventory_item_id = i.inventory_item_id;

end loop;

dbms_output.put_line('Total Update: '||x);

--COMMIT;

END;

How to replace & with & amp; in oracle

I have a requirement to display the text in HTML Output. In the item description I was encountering an issue with &, whenever this symbol is present the concurrent program is completing with Warning.

To overcome this issue, we need to change the & symbol with &amp;

Solution:-

select regexp_replace(tc_description,'&', '\&'||'amp;') from xx_table;

The output of the query without replacing the description is:
Installation & Training (Small Library)

Now after changing the query, the output is

Installation &amp; Training (Small Library).


For my requirement the above query has given the result.