Oracle Applications R12
Hardware Recommendations
For
City-42
Table of Contents
1.
Production Environment-Oracle Applications R12
* Hardware specs for Oracle E Business Suite have been
suggested based on 20 total users and approx 10 concurrent users. The
architecture i.e. RAM, number of processors e.t.c. should be expandable to
accommodate future requirements.
Single-Node Environment (RHEL 6.4+ 64-Bit)
Processors
|
2
* Six Core Processors (Expandable)
|
RAM
|
32
GB (Expandable)
|
Post
Raid Oracle DB
|
500
GB (RAID 5) (Expandable)
|
Peripherals
|
Mouse,
Keyboard, Monitor, High Speed Ethernet, DVDROM
|
Operating
System
|
Linux
v6.4+ (64-bit) or Above
|
2.
Test Environment-Oracle Applications R12
Single-Node Environment (RHEL 6.4+ 64-Bit)
Processors
|
2
* Quad Core Processors (Expandable)
|
RAM
|
24
GB (Expandable)
|
Post
Raid Oracle DB
|
500
GB (RAID 5) (Expandable)
|
Peripherals
|
Mouse,
Keyboard, Monitor, High Speed Ethernet, DVDROM
|
Operating
System
|
Linux
v6.4+ (64-bit) or Above
|
R12.2.X Cloning
R12.2.4 Installation Gide Help
How to get Run File
system full path?
find . -name
TEST_testapps.xml
/opt/oracle/TEST/fs1/inst/apps/TEST_testapps/appl/admin/TEST_testapps.xml
I have resolved issue
fmw failed to execute prerequisite check
Go to this path
cd
/opt/oracle/TEST/fs1/EBSapps/comn/clone/prereq/webtier/Scripts/prereq/linux64/
vi refhost.xml
only change linux 6
to 6.6
edition value oracle to redhat
I have resolved issue
ora-01031 insufficient previliges
orapwd
file=/opt/oracle/TEST/db/tech_st/11.2.0/dbs/orapwTEST password=tanveer
entries=5
ls -l sqlplus
sqlplus sys as sysdba
lsnrctl start TEST
echo $PATH
echo $ORACLE_SID
echo $ORACLE_HOME
sqlplus "/ as
sysdba"
ls –l
ora-00020 maximum no
of process 200 exceeded
change file
/opt/oracle/TEST/11.2.0/dbs/initTEST.ora
Processes 200 to 400
Session 400 to 800
Then restart the
server
Select db environment
file
Connect sqlplus /
sysdba
Create spfile from
pfile;
Exit
Start the services
Issue has been
resolved
Restric user oracle
canot set user resource temperorily unavailable
vi
/etc/security/limits.d/90-nproc.conf
·
Process 1024
change to 16384
Limits.conf soft nproc 16384
Reboot the system and resolve issue successfully
CREATE OR REPLACE function APPS.get_acc_desc_RSML (CCID in VARCHAR2) return varchar2
is
v_SEG3 VARCHAR2(25);
v_SEG4 VARCHAR2(25);
v_SEG5 VARCHAR2(25);
v_SEG7 VARCHAR2(25);
v_SEG3_DESC VARCHAR2(240);
v_SEG4_DESC VARCHAR2(240);
v_SEG5_DESC VARCHAR2(240);
v_SEG7_DESC VARCHAR2(240);
V_ACC_DESC VARCHAR2(200);
begin
SELECT GLC.SEGMENT3,GLC.SEGMENT4,GLC.SEGMENT5,GLC.SEGMENT6
INTO v_SEG3,v_SEG4,v_SEG5,v_SEG7
FROM GL_CODE_COMBINATIONS GLC
WHERE GLC.CODE_COMBINATION_ID = CCID ;
/*SELECT FV.DESCRIPTION
INTO v_SEG3_DESC
FROM FND_FLEX_VALUES_VL FV
WHERE FLEX_VALUE = v_SEG3
AND FLEX_VALUE_SET_ID=1016209;
SELECT FV.DESCRIPTION
INTO v_SEG4_DESC
FROM apps.FND_FLEX_VALUES_VL FV
WHERE FLEX_VALUE = v_SEG4
AND FLEX_VALUE_SET_ID =1016210; */
SELECT FND_FLEX_VALUES_VL.DESCRIPTION
INTO v_SEG5_DESC
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE = v_SEG5
AND FLEX_VALUE_SET_ID=1016211; --Int
Unit
/*SELECT FV.DESCRIPTION
INTO v_SEG7_DESC
FROM FND_FLEX_VALUES_VL FV
WHERE FLEX_VALUE = v_SEG7
AND FLEX_VALUE_SET_ID=1016210; */
--SELECT
v_SEG3_DESC||'-'||v_SEG4_DESC||'-'||v_SEG7_DESC INTO V_ACC_DESC FROM DUAL ;
RETURN v_SEG5_DESC;
exception
WHEN too_many_rows then
return 'Too rows';
WHEN no_data_found then
return 'No data';
end get_acc_desc_RSML================;
/
CREATE OR REPLACE PROCEDURE APPS.CUST_AP_AGING_CITY (P_ORG_ID NUMBER,P_DATE_AS_ON VARCHAR2)
AS
BEGIN
DELETE FROM CUST_AGING_POPULATE;
COMMIT;
INSERT INTO CUST_AGING_POPULATE
select ai.INVOICE_NUM ,
ai.INVOICE_ID ,
pv.vendor_name vendor_name
, pv.SEGMENT1 vendor_number
, (select sum(ail.AMOUNT)
from AP_INVOICE_lines_all ail
where ail.INVOICE_ID = ai.INVOICE_ID
and ail.ORG_ID = ai.ORG_ID
and ail.LINE_TYPE_LOOKUP_CODE in ('ITEM'))INVOICE_AMOUNT
, ai.AMOUNT_PAID
, (select sum(ail.AMOUNT)
from AP_INVOICE_lines_all ail
where ail.INVOICE_ID = ai.INVOICE_ID
and ail.LINE_TYPE_LOOKUP_CODE = 'AWT' ) WHTAX
, (select sum(ail.AMOUNT)
from AP_INVOICE_lines_all ail
where ail.INVOICE_ID = ai.INVOICE_ID
and ail.LINE_TYPE_LOOKUP_CODE = 'TAX' ) TAX
, ai.GL_DATE , P_DATE_AS_ON as as_on_date , (TO_DATE(P_DATE_AS_ON)-(TO_DATE(ai.INVOICE_DATE)+AT1.DUE_DAYS)) as days_between
, AT1.DUE_DAYS DAYS
, (Select ATT.NAME from AP_TERMS_TL ATT WHERE ATT.TERM_ID=AI.TERMS_ID AND ROWNUM=1 ) days_name ,
ai.PAY_GROUP_LOOKUP_CODE
, ap_invoices_pkg.get_approval_status
(ai.invoice_id,
ai.invoice_amount,
ai.payment_status_flag,
ai.invoice_type_lookup_code
) status,
ai.INVOICE_DATE
from po_vendors pv,
Po_vendor_sites_all pvsa,
AP_INVOICES_all ai
, AP_TERMS_LINES AT1
where pv.vendor_id = ai.vendor_id
AND PVSA.VENDOR_ID = PV.VENDOR_ID
AND PVSA.VENDOR_SITE_ID = AI.VENDOR_SITE_ID
AND PVSA.ORG_ID = AI.ORG_ID
AND AI.ORG_ID = P_ORG_ID
AND ai.terms_id = AT1.term_id
AND TRUNC(AI.GL_DATE ) <= P_DATE_AS_ON
AND AI.CANCELLED_DATE IS NULL ;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
NULL;
END;
/
DROP TABLE APPS.CUST_AGING_POPULATE CASCADE CONSTRAINTS;
CREATE TABLE APPS.CUST_AGING_POPULATE
(
INVOICE_NUM VARCHAR2(50 BYTE),
INVOICE_ID NUMBER(15),
VENDOR_NAME VARCHAR2(360 BYTE),
VENDOR_NUMBER VARCHAR2(30 BYTE),
INVOICE_AMOUNT NUMBER,
AMOUNT_PAID NUMBER,
WHTAX NUMBER,
TAX NUMBER,
GL_DATE DATE,
AS_ON_DATE DATE,
DAYS_BETWEEN NUMBER,
DAYS NUMBER,
DAYS_NAME VARCHAR2(50 BYTE),
PAY_GROUP_LOOKUP_CODE VARCHAR2(25 BYTE),
STATUS VARCHAR2(50 BYTE),
INVOICE_DATE DATE
)
TABLESPACE APPS_TS_TX_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE OR REPLACE FUNCTION APPS.ap_get_amountcr_cust (
p_ids NUMBER,
p_lookup_code VARCHAR,
p_amount NUMBER,
p_exchange_rate NUMBER,
p_PARENT_REVERSAL_ID number,
p_REVERSAL_FLAG VARCHAR
)
RETURN NUMBER
IS
RESULT NUMBER;
BEGIN
IF (nvl(p_REVERSAL_FLAG,'X') = 'Y' and p_PARENT_REVERSAL_ID is not null) then
IF p_lookup_code IN ('STANDARD', 'AWT','RETAINAGE RELEASE')
THEN
RESULT := p_amount * NVL (round(p_exchange_rate,7), 1);
ELSIF p_lookup_code = 'MIXED' AND p_amount > 0
THEN
RESULT := -1 * ABS (p_amount * NVL (round(p_exchange_rate,7), 1));
--RESULT := (p_amount * NVL (round(p_exchange_rate,7),
1));
ELSE
RESULT := 0;
END IF;
ELSE
IF p_lookup_code IN ('STANDARD', 'AWT','RETAINAGE RELEASE')
THEN
RESULT := p_amount * NVL (round(p_exchange_rate,7), 1);
ELSIF p_lookup_code = 'MIXED' AND p_amount > 0
THEN
RESULT := ABS (p_amount * NVL (round(p_exchange_rate,7), 1));
--RESULT := (p_amount * NVL (round(p_exchange_rate,7),
1));
ELSE
RESULT := 0;
END IF;
END IF;
RETURN RESULT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 0;
END;
/
CREATE OR REPLACE FUNCTION APPS.ap_get_amountdr_cust (
p_ids NUMBER,
p_lookup_code VARCHAR,
p_amount NUMBER,
p_exchange_rate NUMBER,
p_PARENT_REVERSAL_ID number,
p_REVERSAL_FLAG VARCHAR
)
RETURN NUMBER
IS
RESULT NUMBER;
BEGIN
IF (nvl(p_REVERSAL_FLAG,'X') = 'Y' and p_PARENT_REVERSAL_ID is not null) then
IF p_lookup_code = 'DEBIT'
OR p_lookup_code = 'CREDIT'
OR p_lookup_code = 'PREPAYMENT'
THEN
RESULT := -1 * (p_amount * NVL (round(p_exchange_rate,7), 1));
ELSIF p_lookup_code = 'MIXED' AND p_amount < 0
THEN
RESULT := -1 * ABS (p_amount * NVL (round(p_exchange_rate,7), 1));
--RESULT := (p_amount * NVL
(round(p_exchange_rate,7), 1));
ELSE
RESULT := 0;
END IF;
ELSE
IF p_lookup_code = 'DEBIT'
OR p_lookup_code = 'CREDIT'
OR p_lookup_code = 'PREPAYMENT'
THEN
RESULT := -1 * (p_amount * NVL (round(p_exchange_rate,7), 1));
ELSIF p_lookup_code = 'MIXED' AND p_amount < 0
THEN
RESULT := ABS (p_amount * NVL (round(p_exchange_rate,7), 1));
ELSE
RESULT := 0;
END IF;
END IF;
RETURN RESULT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
RETURN 0;
END;
/
CREATE OR REPLACE FUNCTION APPS.SFML_AP_INV_WHT_DESC(P_INVOICE_ID number)
return Varchar is
v_desc varchar2(2000);
cursor c1 is
SELECT distinct g.NAME description
FROM ap_invoices_all aia, ap_invoice_lines_all aila,AP_AWT_GROUPS g
WHERE aia.invoice_id = aila.invoice_id
and nvl(aila.PAY_AWT_GROUP_ID,aila.AWT_GROUP_ID) = g.group_id
AND aila.line_type_lookup_code = 'AWT'
AND aia.invoice_id = P_INVOICE_ID;
begin
for r1 in c1 loop
if v_desc is null then
v_desc :=r1.description;
else
v_desc:=v_desc||',
'||r1.description;
end if;
end loop;
return v_desc;
exception when others then return null;
end;
/
CREATE OR REPLACE FUNCTION APPS.SFML_AP_INV_REC_NUM(P_CHECK_ID number)
return Varchar is
v_rec varchar2(2000);
v_inv varchar2(2000);
cursor c1 is
SELECT DISTINCT RSH.RECEIPT_NUM
FROM ap_invoice_payments_all aip,
ap_checks_all aca,
ap_invoices_all aia ,ap_invoice_lines_all aila,
rcv_shipment_headers rsh ,rcv_shipment_lines rsl
WHERE aip.check_id = aca.check_id
AND aia.invoice_id = aip.invoice_id
and aca.STATUS_LOOKUP_CODE <> 'VOIDED'
and aia.invoice_id = aila.invoice_id
and aila.invoice_id = aip.invoice_id
and aila.RCV_SHIPMENT_LINE_ID=rsl.SHIPMENT_LINE_ID
and rsh.shipment_header_id = rsl.shipment_header_id
AND ACA.CHECK_ID = P_CHECK_ID;
cursor c2 is
SELECT distinct INVOICE_NUM from
(
SELECT DISTINCT aia.INVOICE_NUM
FROM ap_invoice_payments_all aip,
ap_checks_all aca,
ap_invoices_all aia ,ap_invoice_lines_all aila,
rcv_shipment_headers rsh ,rcv_shipment_lines rsl
WHERE aip.check_id = aca.check_id
AND aia.invoice_id = aip.invoice_id
and aca.STATUS_LOOKUP_CODE <> 'VOIDED'
and aia.invoice_id = aila.invoice_id
and aila.invoice_id = aip.invoice_id
and aila.RCV_SHIPMENT_LINE_ID=rsl.SHIPMENT_LINE_ID
and rsh.shipment_header_id = rsl.shipment_header_id
AND ACA.CHECK_ID = P_CHECK_ID
union all
SELECT DISTINCT aia.INVOICE_NUM
FROM ap_invoice_payments_all aip,
ap_checks_all aca,
ap_invoices_all aia ,ap_invoice_lines_all aila
WHERE aip.check_id = aca.check_id
AND aia.invoice_id = aip.invoice_id
and aca.STATUS_LOOKUP_CODE <> 'VOIDED'
and aia.invoice_id = aila.invoice_id
and aila.invoice_id = aip.invoice_id
AND ACA.CHECK_ID = P_CHECK_ID
);
begin
for r1 in c1 loop
if v_rec is null then
v_rec :=r1.RECEIPT_NUM;
else
v_rec:=v_rec||',
'||r1.RECEIPT_NUM;
end if;
end loop;
for r1 in c2 loop
if v_inv is null then
v_inv :=r1.INVOICE_NUM;
else
v_inv:=v_inv||',
'||r1.INVOICE_NUM;
end if;
end loop;
return 'GRN(s)#'||v_rec||'
, Inv(s)#'||v_inv;
exception when others then return null;
end;
/
DROP VIEW APPS.CUST_SFML_ACTIVE_SUPPLIERS;
/* Formatted on 2016/03/01 12:23 (Formatter Plus
v4.8.8) */
CREATE OR REPLACE FORCE VIEW apps.cust_sfml_active_suppliers (NAME,
site,
site_id,
org_id
)
AS
SELECT DISTINCT asa.vendor_name NAME, assa.vendor_site_code site,
assa.vendor_site_id site_id, org_id
FROM ap_suppliers asa, ap_supplier_sites_all assa
WHERE asa.vendor_id = assa.vendor_id
AND enabled_flag = 'Y'
AND inactive_date IS NULL
--AND ORG_ID = 81
ORDER BY 1;
CREATE OR REPLACE FUNCTION APPS.AR_AP_DESCRIPTION_UP(P_COMBINATION NUMBER,P_je_header_id NUMBER,P_CATEGORY CHAR,P_JE_LINE_NUM NUMBER)
RETURN CHAR
IS
P_DESC VARCHAR2(5000);
BEGIN
IF P_CATEGORY IN ('Receipts','Misc Receipts')
THEN
select DISTINCT AR.comments
INTO P_DESC
FROM gl_je_lines gjl,
gl_je_headers gjh,
gl_code_combinations gc,
gl_je_batches gjb ,
gl_je_categories gjc
,xla_ae_lines xl
,xla_ae_headers xh
,GL_IMPORT_REFERENCES gir
,ar_cash_receipts_all ar
WHERE gjh.je_header_id = gjl.je_header_id
AND gc.code_combination_id = gjl.code_combination_id
and xh.ae_header_id = xl.ae_header_id
and gjb.je_batch_id = gjh.je_batch_id
and gjh.JE_CATEGORY = gjc.JE_CATEGORY_NAME
and gir.GL_SL_LINK_ID=xl.GL_SL_LINK_ID(+)
and gir.GL_SL_LINK_ID=gjl.GL_SL_LINK_ID(+)
and xh.DOC_SEQUENCE_ID (+)= ar.DOC_SEQUENCE_Id
and xh.DOC_SEQUENCE_VALUE (+)= ar.DOC_SEQUENCE_VALUE
AND gjh.actual_flag = 'A'
AND gjh.ledger_id = 2021
AND ar.ORG_ID = 81
and xh.JE_CATEGORY_NAME in ('Receipts','Misc Receipts')
and gjh.je_header_id = P_je_header_id --1503006826
AND GJL.CODE_COMBINATION_ID = P_COMBINATION --47149
and GJL.JE_LINE_NUM = P_JE_LINE_NUM;
RETURN P_DESC;
ELSIF P_CATEGORY IN ('Payments','Reconciled Payments')
then
select DISTINCT ap.DESCRIPTION
INTO P_DESC
FROM gl_je_lines gjl,
gl_je_headers gjh,
gl_code_combinations gc ,
gl_je_batches gjb ,
gl_je_categories gjc
,xla_ae_lines xl
,xla_ae_headers xh
,GL_IMPORT_REFERENCES gir
,po_vendors pv
,ap_checks_all ap
WHERE gjh.je_header_id = gjl.je_header_id
AND gc.code_combination_id = gjl.code_combination_id
and xh.ae_header_id = xl.ae_header_id
and gjb.je_batch_id = gjh.je_batch_id
and gjh.JE_CATEGORY = gjc.JE_CATEGORY_NAME
and gir.GL_SL_LINK_ID=xl.GL_SL_LINK_ID(+)
and gir.GL_SL_LINK_ID=gjl.GL_SL_LINK_ID(+)
and pv.VENDOR_ID=xl.PARTY_ID
AND gjh.actual_flag = 'A'
AND gjh.ledger_id = 2021
AND AP.ORG_ID = 81
and ap.vendor_id = pv.vendor_id
and xh.DOC_SEQUENCE_VALUE (+)= ap.DOC_SEQUENCE_VALUE
and xh.JE_CATEGORY_NAME in ('Payments','Reconciled Payments')
and gjh.je_header_id = P_je_header_id --1503008318
AND GJL.CODE_COMBINATION_ID = P_COMBINATION --2056
and GJL.JE_LINE_NUM = P_JE_LINE_NUM;
RETURN P_DESC;
ELSIF P_CATEGORY IN ('Debit Memos') THEN
select DISTINCT min(RCTLA.DESCRIPTION)
INTO P_DESC
FROM gl_je_lines gjl,
gl_je_headers gjh,
gl_code_combinations gc,
gl_je_batches gjb ,
gl_je_categories gjc
,xla_ae_lines xl
,xla_ae_headers xh
,GL_IMPORT_REFERENCES gir ,
RA_CUSTOMER_TRX_all rcta,
ra_customer_trx_lines_all rctla
WHERE gjh.je_header_id = gjl.je_header_id
AND gc.code_combination_id = gjl.code_combination_id
and xh.ae_header_id = xl.ae_header_id
and gjb.je_batch_id = gjh.je_batch_id
and gjh.JE_CATEGORY = gjc.JE_CATEGORY_NAME
and gir.GL_SL_LINK_ID=xl.GL_SL_LINK_ID(+)
and gir.GL_SL_LINK_ID=gjl.GL_SL_LINK_ID(+)
AND rcta.customer_trx_id = rctla.customer_trx_id
and xh.DOC_SEQUENCE_ID = rcta.DOC_SEQUENCE_Id
and xh.DOC_SEQUENCE_VALUE = rcta.DOC_SEQUENCE_value
AND gjh.actual_flag = 'A'
AND gjh.ledger_id = 2021
AND rcta.ORG_ID = 81
and xh.JE_CATEGORY_NAME in ('Debit Memos')
and gjh.je_header_id = P_je_header_id --1503006826
AND GJL.CODE_COMBINATION_ID = P_COMBINATION --47149
and GJL.JE_LINE_NUM = P_JE_LINE_NUM;
RETURN P_DESC;
ELSE
RETURN '-' ;
END IF;
END;
/
No comments:
Post a Comment