2011年7月12日星期二

Script to control sequence assignment // Script pour vérifier les affectations des séquences dans Oracle applications

SELECT distinct fds.name,fds.creation_date, fu.user_name, fdsc.name "Category",fda.method_code
FROM FND_DOC_SEQUENCE_ASSIGNMENTS FDA
,FND_DOCUMENT_SEQUENCES FDS
, fnd_doc_sequence_categories fdsc
, FND_USER FU
WHERE
FDA.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID
and fda.category_code = fdsc.code
AND FDS.created_by= fu.user_id
AND FDA.start_date = to_date('01/'||to_char(sysdate-30, 'MM/YY')) -- début période N-1
AND FDA.end_date = last_day(trunc(sysdate-30))-- Fin période N-1
AND not exists (select 1
from FND_DOCUMENT_SEQUENCES FDS2,
FND_DOC_SEQUENCE_ASSIGNMENTS FDA2
WHERE 1=1
AND FDA2.DOC_SEQUENCE_ID = FDS2.DOC_SEQUENCE_ID
AND fda.method_code = fda2.method_code
AND fda.category_code= fda2.category_code
AND fda.set_of_books_id = fda2.set_of_books_id
-- AND length(fds.initial_value) = length(fds2.initial_value)
AND FDA2.start_date = to_date('01/'||to_char(sysdate, 'MM/YY'))-- Début Période N
)
order by fds.name;

2011年5月17日星期二

PO workflow error "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" in Wf_Notification.runFuncOnBody(XXXXXX, text/html)

The PO encountered error in workflow while generate notifiation PO_REQ_APPROVE :

[
WF_ERROR] ERROR_MESSAGE=3835: Erreur "-20002 - ORA-20002: 2018: Impossible de générer la notification au format XML. Cause : 2020: Erreur d'extraction du contenu de la notification. Cause : ORA-06502: PL/SQL: numeric or value error: character string


The wfstatus output for the PO shows the following error :

[WF_ERROR] ERROR_MESSAGE=3835: Erreur "-20002 - ORA-20002: 2018: Impossible de générer la notification au format XML. Cause : 2020: Erreur d'extraction du contenu de la notification. Cause : ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Wf_Notification.runFuncOnBody(409499, text/html)
Wf_Notification.GetBody(409499, text/html)
WF_NOTIFICATION.GetFullBody(nid => 409499, disptype => text/html)
WF_MAIL.GetLOBMessage3(nid => 409499, r_ntf_pref => MAILHTML)" survenue lors de l'exécution de la fonction de génération "WF_XML.Generate" pour l'événement "oracle.apps.wf.notification.send". ERROR_STACK=
WF_MAIL.GetLOBMessage3(409499, WFMAIL, 2020: Erreur d'extraction du contenu de la notification. Cause : ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Wf_Notification.runFuncOnBody(409499, text/html)
Wf_Notification.GetBody(409499, text/html)
WF_NOTIFICATION.GetFullBody(nid => 409499, disptype => text/html)
WF_MAIL.GetLOBMessage3(nid => 409499, r_ntf_pref => MAILHTML), Step -> Getting text/html body)
WF_XML.GenerateDoc(oracle.apps.wf.notification.send, 409499)
WF_XML.Generate(oracle.apps.wf.notification.send, 409499)
WF_XML.Generate(oracle.apps.wf.notification.send, 409499)
Wf_Event.setMessage(oracle.apps.wf.notification.send, 409499, WF_XML.Generate)
Wf_Event.dispatch_internal()


After search in Oracle metalink, we found that a patch has been delivred to fix this issue : ID 579729.1 :

The issue can be reproced at will with the following steps :
1. Create a PO with more than 20 lines
2. Submit the PO for approval
3. Log in as approver
4. Request for more information from the "Standard Purchase Order 1234 for USD 999,999.99 requires your approval" notification
5. Log in as the buyer
6. Open the "More Information Requested: Standard Purchase Order 1234 for USD 999,999.99 requires your approval" notification.

Cause
When the line details exceed the available limit of the notification it gives the ORA-06512. This is happening because of the 32K size limit.

This is explained in the following bug:
Bug 6932794 MORE INFO REQUESTED NOTIFICATIONS ERROR WITH ORA-06502: PL/SQL: NUMERIC OR VALUE

Solution

To implement the solution, execute the following steps:
For Release 11.5.10:

1. Download and review the readme and pre-requisites for Patch 7370646.

2. Ensure that you have taken a backup of your system before applying
the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:
POXWPA1B.pls 115.206.11510.62
POXWPA7S.pls 115.6.11510.2
POXWPA7B.pls 115.38.11510.23

Use the following sql:
select text
from dba_source
where line = 2
and name in ('PO_REQAPPROVAL_INIT1','PO_WF_PO_NOTIFICATION');

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

For Release 12:

1. Download and review the readme and pre-requisites for Patch 7218243 - Procurement R12.0 Update July 2008 or higher
or
One-off Patch 7612490 which indirectly includes the fix for this issue.

2. Ensure that you have taken a backup of your system before applying the recommended patch.

3. Apply the patch in a test environment.

4. Confirm the following file versions:
POXWPA1B.pls-120.46.12000000.13 or higher
POXWPA7S.pls-120.0.12000000.2 or higher
POXWPA7B.pls-120.8.12000000.3 or higher

You can use the commands like the following:
strings -a $PO_TOP/patch/115/sql/POXWPA1B.pls | grep -i '$Header:'
strings -a $PO_TOP/patch/115/sql/POXWPA7S.pls | grep -i '$Header:'
strings -a $PO_TOP/patch/115/sql/POXWPA7B.pls | grep -i '$Header:'

5. Retest the issue.

6. Migrate the solution as appropriate to other environments.

2011年5月6日星期五

France Chine Business Consultig

Le meilleur site de conseil en France Chine Business Consulting ;-) :

http://chine-consulting.fr

Leur domaines d'activités :

France Chine Business Consulting, Implantation en Chine, Offshore en Chine, Import-Export, Voyage en Chine, Traduction en chinois

2011年3月31日星期四

How to resolve SQL error : SQL*Loader-510

The issue happend when we tried to import a csv data file via sql loader.

The loader finished in error with following message in log file :

- US : SQL*Loader-510: Physical record in data file (XXX/XXXXXX/XX.csv) is longer than the maximum
- F : SQL*Loader-510: Enreg. physique dans fichier de données (XXX/XXXXXX/XX.csv) plus long que max

The CSV file has been uploaded correctly on the server. And there is none line in CSV file which seems very long.

In our case here, the problem comes from the CSV file generation format in Excel. In fact, two CSV saving format are proposed by Excel, we choosed the format CSV macintosh. If we choose another one format CSV DOS, the error would not be occured.

2011年3月29日星期二

API to import ASLs (approved supplier list) in Oracle from blanket agreements or quotations // Import des ASLs à partir des commandes ouverts ou prix

Since Nov 2008(patch ID 262093.1), Oracle provides the possibility to create local Approved Supplier List (ASL) through the PO Open Interface via the concurrent program "Generate Sourcing Rules and ASLs from Blanket Agreement".

With this concurrent program, we can create the ASLs from the blankets already imported into Oracle PO. It's useful in the case of we use blanket agreements. If we don't use blanket but only use quotation, it's impossible for us to mass create ASLs.

So we duplicated the standard concurrent program "Generate Sourcing Rules and ASLs from Blanket Agreement" and created another new specific concurrent program "XX : Generate Sourcing Rules and ASLs from Quotations".

The executable file is always the same : POXSRASL.sql
This program requires 12 parameters that 5 need to be modified :

Parameter 1 - Supplier
LOV 1 - PO_ASL_VENDOR

Parameter 2 - Blanket Agreement Number
LOV 2 - SE_PO_ASL_BLANKET_NUMBER
Define LOV -
Table : PO_HEADERS_ALL POH,PO_LOOKUP_CODES PLC
Valeur : POH.segment1
ID : POH.po_header_id
WHERE : POH.vendor_id=:$FLEX$.PO_ASL_VENDOR
and NVL(POH.closed_code,'OPEN') not in ('CLOSED','FINALLY CLOSED')
and POH.type_lookup_code='QUOTATION'
and
(
poh.org_id IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS)
OR
(
POH.global_agreement_flag='Y' and
EXISTS (
select pgoa.purchasing_org_id
from po_ga_org_assignments pgoa
where pgoa.po_header_id=poh.po_header_id and
pgoa.purchasing_org_id
IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS)
)
)
)
AND PLC.LOOKUP_TYPE='YES/NO'
AND PLC.LOOKUP_CODE=NVL(POH.GLOBAL_AGREEMENT_FLAG,'N')
ORDER BY : PLC.DISPLAYED_FIELD "Global Agreement"

Parameter 3 - Global Agreement Flag
LOV 3 - PO_ASL_ENABLE_FLAG
Default val - select decode(nvl(poh.global_agreement_flag,'N'),'N',null,'Y') from po_headers_all poh where poh.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER

Parameter 4 - Purchasing Organization
LOV 4 - SE_PO_ASL_PURCHASING_ORG
Default val - SELECT name FROM hr_all_organization_units_tl houtl WHERE houtl.organization_id= decode(nvl(:$FLEX$.PO_ASL_ENABLE_FLAG:NULL,'N'),'Y',decode ( ( SELECT count(*) FROM po_ga_org_assignments pgoa WHERE pgoa.purchasing_org_id=:$PROFILES$.ORG_ID AND pgoa.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER ) , 1, :$PROFILES$.ORG_ID, null ) ,null) AND language=USERENV('LANG')
Define LOV - Table : PO_HEADERS_ALL POH,PO_GA_ORG_ASSIGNMENTS PGOA, HR_ALL_ORGANIZATION_UNITS HOU,HR_ALL_ORGANIZATION_UNITS HOU1, HR_ALL_ORGANIZATION_UNITS HOU2
Where : POH.global_agreement_flag=:$FLEX$.PO_ASL_ENABLE_FLAG
and POH.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
and PGOA.po_header_id = POH.po_header_id
and ((POH.org_id IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS))
or (PGOA.purchasing_org_id IN (SELECT ORG_ID FROM PO_SYSTEM_PARAMETERS)))
and HOU.organization_id=PGOA.purchasing_org_id
and HOU1.organization_id=POH.org_id
and HOU2.organization_id=PGOA.organization_id
Order by : hou1.name as "Owning Organization",
hou2.name as "Requesting Organization"


Parameter 5 - Supplier Site
LOV 5 - SE_PO_ASL_VENDOR_SITE
Default val - SELECT pvs.vendor_site_code FROM po_vendor_sites pvs, po_headers_all poh WHERE pvs.vendor_site_id = decode( nvl(poh.global_agreement_flag,'N'),'N',poh.vendor_site_id, decode ( ( SELECT count(DISTINCT(pgoa.vendor_site_id)) FROM po_ga_org_assignments pgoa WHERE pgoa.purchasing_org_id=nvl(:$FLEX$.SE_PO_ASL_PURCHASING_ORG:NULL ,null) AND pgoa.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER ) , 1, ( SELECT DISTINCT (pgoa.vendor_site_id) FROM po_ga_org_assignments pgoa WHERE pgoa.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER AND pgoa.purchasing_org_id=nvl(:$FLEX$.SE_PO_ASL_PURCHASING_ORG:NULL ,null) ) , poh.vendor_site_id ) ) AND poh.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
Define LOV - Table : PO_VENDOR_SITES_ALL POV,PO_GA_ORG_ASSIGNMENTS PGOA,PO_HEADERS_ALL POH
Where : PGOA.po_header_id(+)=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
AND POH.po_header_id=:$FLEX$.SE_PO_ASL_BLANKET_NUMBER
and PGOA.purchasing_org_id(+)=
decode(nvl(POH.global_agreement_flag,'N'),'N',-999,
nvl(:$FLEX$.PO_ASL_PURCHASING_ORG:NULL,(:$PROFILES$.ORG_ID)))
and POV.vendor_site_id=decode(nvl(POH.global_agreement_flag,'N'),'N',
POH.vendor_site_id,
PGOA.vendor_site_id)

Parameter 6 - Select Agreement Lines
LOV 6 - PO_ASL_LINES_SELECTION

Parameter 7 - Assignment Set
LOV 7 - PO_ASL_ASSIGNMENT_SET
Default val - select assignment_set_name from mrp_assignment_sets where assignment_set_id=:$PROFILES$.MRP_DEFAULT_ASSIGNMENT_SET

Parameter 8 - Sourcing Level
LOV 8 - PO_ASL_SOURCING_LEVEL

Parameter 9 - Inv Org Enable
LOV 9 - PO_ASL_ENABLE_FLAG
Default val - select decode(:$FLEX$.PO_ASL_SOURCING_LEVEL,'ITEM',NULL,'Y') from dual

Parameter 10 - Inventory Organization
LOV 10 - PO_ASL_INV_ORG

Parameter 11 - Sourcing Rule Name
LOV 11 - SE_PO_ASL_SR_RULE_NAME
Define LOV - Table : MRP_SOURCING_RULES MSR , org_organization_definitions oog
Where : oog.organization_id = msr.organization_id
AND EXISTS
(
SELECT
1
FROM MRP_SR_RECEIPT_ORG MSRO,
MRP_SR_SOURCE_ORG MSSO
WHERE MSR.SOURCING_RULE_ID=MSRO.SOURCING_RULE_ID
AND MSRO.SR_RECEIPT_ID=msso.SR_RECEIPT_ID
AND msso.vendor_id=:$FLEX$.PO_ASL_VENDOR
AND msso.vendor_site_id=:$FLEX$.SE_PO_ASL_VENDOR_SITE
)
and oog.organization_id=:$FLEX$.PO_ASL_INV_ORG
Order By : oog.organization_name "Organization"


Parameter 12 - Release Generation Method
LOV 12 - PO_PDOI_REL_GEN_METHOD

This concurrent program allows users to create ASLs from one specific or more quotations. We can also customize this program by adding a new parameter request_id so that only quotations with this request_id (probably the request_id of program Import of price catalog) will be treated.

2011年3月2日星期三

Start or stop Concurrent Manager by using unix script / Relance GTS avec shell Unix

After modication of workflow(pkg, wft or other workflow files), we have to restart the concurrent manager, otherwise the workflow will not work.

The following steps allows us to restart the concurrent manager in Unix :

cd $COMMON_TOP/admin/scripts/$CONTEXT_NAME

Execute the script : adcmctl.sh stop apps/apps

Wait that all process FNDLIBR are killed, to check : ps -fu $USER
Sometimes this step could take several minutes.

To restart the concurrent manager : adcmctl.sh start apps/apps

2011年1月25日星期二

Customization of Oracle Workflow (AP Remittance advice workflow APPEWF)

The first thing before any action is amend Oracle workflow access in Workflow builder in order to amend the standard workflow APPEWF.wft :

Menu : Help -> About Oracle Workflow Builder XXXXXX

In the opening window, modify the access level to 20 and enable the Allow modifications of customized objects case.

Then we can modify the standard workflow file APPEWF.wft

Example of modification : add cc list for workflow email(AP remittance advice email).

Step 1 - Add a new global attribute in workflow : XX_CC_NAME

Step 2 - Create a new attribute #WFM_CC in message level, and attached to the global attirbute created previously

Step 3 - Initialize the global attribute XX_CC_NAME in PL/SQL package, in our case here, in AP_PAYMENT_EVENT_WF_PKG.get_check_info, add the following lines :

BEGIN
SELECT NVL(fpov.profile_option_value, '')
INTO l_se_cc
FROM fnd_profile_options fpo, fnd_profile_option_values fpov
WHERE fpo.application_id =20003
AND fpo.profile_option_name = 'XXXXXXX' -- XXXXXX is the profil option's name
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.level_id = 10006
AND fpov.level_value = l_org_id;
EXCEPTION
WHEN OTHERS THEN
l_se_cc := '';
END;
wf_engine.setitemattrtext(p_item_type,
p_item_key,
'XX_CC_NAME',
l_se_cc);

Step 4 - Upload the new workflow, restart the GTS.