Oracle E-Business Suite号航班

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

2011-07-12T06:53:00.000-07:00

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;

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

2011-05-17T03:09:00.000-07:00

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.

France Chine Business Consultig

2011-05-06T00:49:00.000-07:00

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

How to resolve SQL error : SQL*Loader-510

2011-03-31T06:24:00.000-07:00

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.

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

2011-03-29T02:15:00.000-07:00

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.

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

2011-03-02T02:42:00.000-08:00

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

Customization of Oracle Workflow (AP Remittance advice workflow APPEWF)

2011-01-25T06:03:00.000-08:00

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.

Oracle - API to get AP invoice validation status via SQL Query

2010-12-17T07:31:00.000-08:00

The AP invoice validation status is determinated in AP invoice Forms. There is only one API to get invoice approval status, but no API available to get the validation status.

We developped the following function to return the AP invoice validation status.

SQL:

FUNCTION get_invoice_validation_status(l_invoice_id IN NUMBER) RETURN VARCHAR2
IS
l_status VARCHAR2(50);
l_validation_status VARCHAR2(50);
l_inv_sel_count NUMBER;
l_step VARCHAR2(10);

l_NLS_TRANSLATION varchar2(15) := 'NLS TRANSLATION';
l_QUICKPRO varchar2(8) := 'QUICKPRO';
l_POSTING_STATUS varchar2(14) := 'POSTING STATUS';
l_P varchar2(1) := 'P';
l_S varchar2(1) := 'S';
l_ANY_MULTIPLE varchar2(12) := 'ANY MULTIPLE';
l_UNMATCHED varchar2(9) := 'UNMATCHED';
l_SYSTEM varchar2(6) := 'SYSTEM';
l_APPROVED varchar2(8) := 'APPROVED';
l_CANCELLED varchar2(9) := 'CANCELLED';
l_NEEDS_REAPPROVAL varchar2(16) := 'NEEDS REAPPROVAL';
l_NEVER_APPROVED varchar2(14) := 'NEVER APPROVED';
l_INVOICE_DISTRIBUTION_TYPE varchar2(25) := 'INVOICE DISTRIBUTION TYPE';
l_ITEM varchar2(4) := 'ITEM';
l_INVOICE_TYPE varchar2(12) := 'INVOICE TYPE';
l_STANDARD varchar2(8) := 'STANDARD';
l_MISC_PHRASES varchar2(12) := 'MISC PHRASES';
l_B varchar2(1) := 'B';
l_SELECTED_FOR_PAYMENT varchar2(20) := 'SELECTED FOR PAYMENT';

l_YES_NO_ALL varchar2(10) := 'YES_NO_ALL';
l_Y varchar2(1) := 'Y';
l_N varchar2(1) := 'N';

l_NLS_REPORT_PARAMETER varchar2(20) := 'NLS REPORT PARAMETER';
l_ALL varchar2(3) := 'ALL';
l_PREPAY_STATUS varchar2(13) := 'PREPAY STATUS';
l_AVAILABLE varchar2(9) := 'AVAILABLE';
l_FULL varchar2(4) := 'FULL';
l_UNAPPROVED varchar2(10) := 'UNAPPROVED';
l_UNPAID varchar2(6) := 'UNPAID';
l_PERMANENT varchar2(9) := 'PERMANENT';

l_AP_WFAPPROVAL_STATUS varchar2(20) := 'AP_WFAPPROVAL_STATUS';
l_NOT_REQUIRED varchar2(12) := 'NOT REQUIRED';
l_REQUIRED varchar2(8) := 'REQUIRED';
l_INITIATED varchar2(9) := 'INITIATED';
l_WFAPPROVED varchar2(10) := 'WFAPPROVED';
l_REJECTED varchar2(8) := 'REJECTED';
l_MANUALLY_APPROVED varchar2(17) := 'MANUALLY APPROVED';
l_NEEDS_WFREAPPROVAL varchar2(18) := 'NEEDS WFREAPPROVAL';

l_dummy fnd_lookup_values.meaning%TYPE;
l_nls_partial fnd_lookup_values.meaning%TYPE;
l_nls_selected fnd_lookup_values.meaning%TYPE;
l_nls_approved fnd_lookup_values.meaning%TYPE;
l_nls_cancelled fnd_lookup_values.meaning%TYPE;
l_nls_needs_reapproval fnd_lookup_values.meaning%TYPE;
l_nls_never_approved fnd_lookup_values.meaning%TYPE;
l_nls_selected_for_payment fnd_lookup_values.meaning%TYPE;
l_nls_all fnd_lookup_values.meaning%TYPE;
l_nls_available fnd_lookup_values.meaning%TYPE;
l_nls_full fnd_lookup_values.meaning%TYPE;
l_nls_unapproved fnd_lookup_values.meaning%TYPE;
l_nls_unpaid fnd_lookup_values.meaning%TYPE;
l_nls_permanent fnd_lookup_values.meaning%TYPE;
l_nls_not_required fnd_lookup_values.meaning%TYPE;
l_nls_required fnd_lookup_values.meaning%TYPE;
l_nls_initiated fnd_lookup_values.meaning%TYPE;
l_nls_wfapproved fnd_lookup_values.meaning%TYPE;
l_nls_rejected fnd_lookup_values.meaning%TYPE;
l_nls_manually_approved fnd_lookup_values.meaning%TYPE;
l_nls_wf_needs_reapproval fnd_lookup_values.meaning%TYPE;

CURSOR c_invoices is
SELECT i.invoice_id
,i.invoice_amount
,i.payment_status_flag
,i.invoice_type_lookup_code, i.invoice_num
FROM ap_invoices_all i
WHERE invoice_id = l_invoice_id;

BEGIN
l_step := '1';
SELECT l1.displayed_field,
l2.displayed_field,
l3.displayed_field,
l4.displayed_field,
l5.displayed_field,
l6.displayed_field,
l7.displayed_field,
l13.displayed_field,
l8.displayed_field,
l9.displayed_field,
l10.displayed_field,
l11.displayed_field,
l12.displayed_field,
l14.displayed_field
INTO l_dummy, -- l_quickpro_prefix,
l_nls_partial,
l_nls_selected,
l_dummy, -- l_nls_multiple,
l_dummy, -- l_nls_unmatched,
l_dummy, -- l_nls_system,
l_nls_approved,
l_nls_cancelled,
l_nls_needs_reapproval,
l_nls_never_approved,
l_dummy, -- l_nls_item,
l_dummy, -- l_nls_standard,
l_dummy, -- l_nls_not_applicable,
l_nls_selected_for_payment
FROM ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3,
ap_lookup_codes l4,
ap_lookup_codes l5,
ap_lookup_codes l6,
ap_lookup_codes l7,
ap_lookup_codes l8,
ap_lookup_codes l9,
ap_lookup_codes l10,
ap_lookup_codes l11,
ap_lookup_codes l12,
ap_lookup_codes l13,
ap_lookup_codes l14
WHERE l1.lookup_type = l_NLS_TRANSLATION
AND l1.lookup_code = l_QUICKPRO
AND l2.lookup_type = l_POSTING_STATUS
AND l2.lookup_code = l_P
AND l3.lookup_type = l_POSTING_STATUS
AND l3.lookup_code = l_S
AND l4.lookup_type = l_NLS_TRANSLATION
AND l4.lookup_code = l_ANY_MULTIPLE
AND l5.lookup_type = l_NLS_TRANSLATION
AND l5.lookup_code = l_UNMATCHED
AND l6.lookup_type = l_NLS_TRANSLATION
AND l6.lookup_code = l_SYSTEM
AND l7.lookup_type = l_NLS_TRANSLATION
AND l7.lookup_code = l_APPROVED
AND l13.lookup_type = l_NLS_TRANSLATION
AND l13.lookup_code = l_CANCELLED
AND l8.lookup_type = l_NLS_TRANSLATION
AND l8.lookup_code = l_NEEDS_REAPPROVAL
AND l9.lookup_type = l_NLS_TRANSLATION
AND l9.lookup_code = l_NEVER_APPROVED
AND l10.lookup_type = l_INVOICE_DISTRIBUTION_TYPE
AND l10.lookup_code = l_ITEM
AND l11.lookup_type = l_INVOICE_TYPE
AND l11.lookup_code = l_STANDARD
AND l12.lookup_type = l_MISC_PHRASES
AND l12.lookup_code = l_B
AND l14.lookup_type = l_NLS_TRANSLATION
AND l14.lookup_code = l_SELECTED_FOR_PAYMENT;
l_step := '2';
SELECT l1.displayed_field,
l2.displayed_field,
l3.displayed_field,
l4.displayed_field,
l5.displayed_field,
l6.displayed_field
INTO l_nls_all,
l_nls_available,
l_nls_full,
l_nls_unapproved,
l_nls_unpaid,
l_nls_permanent
FROM ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3,
ap_lookup_codes l4,
ap_lookup_codes l5,
ap_lookup_codes l6
WHERE l1.lookup_type = l_NLS_REPORT_PARAMETER
AND l1.lookup_code = l_ALL
AND l2.lookup_type = l_PREPAY_STATUS
AND l2.lookup_code = l_AVAILABLE
AND l3.lookup_type = l_PREPAY_STATUS
AND l3.lookup_code = l_FULL
AND l4.lookup_type = l_PREPAY_STATUS
AND l4.lookup_code = l_UNAPPROVED
AND l5.lookup_type = l_PREPAY_STATUS
AND l5.lookup_code = l_UNPAID
AND l6.lookup_type = l_PREPAY_STATUS
AND l6.lookup_code = l_PERMANENT;

l_step := '3';
SELECT l1.displayed_field,
l2.displayed_field,
l3.displayed_field,
l4.displayed_field,
l5.displayed_field,
l6.displayed_field,
l7.displayed_field
INTO l_nls_not_required,
l_nls_required,
l_nls_initiated,
l_nls_wfapproved,
l_nls_rejected,
l_nls_manually_approved,
l_nls_wf_needs_reapproval
FROM ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3,
ap_lookup_codes l4,
ap_lookup_codes l5,
ap_lookup_codes l6,
ap_lookup_codes l7
WHERE l1.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l1.lookup_code = l_NOT_REQUIRED
AND l2.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l2.lookup_code = l_REQUIRED
AND l3.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l3.lookup_code = l_INITIATED
AND l4.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l4.lookup_code = l_WFAPPROVED
AND l5.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l5.lookup_code = l_REJECTED
AND l6.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l6.lookup_code = l_MANUALLY_APPROVED
AND l7.lookup_type = l_AP_WFAPPROVAL_STATUS
AND l7.lookup_code = l_NEEDS_WFREAPPROVAL;

FOR r in c_invoices loop
l_step := '4';
l_status := AP_INVOICES_PKG.GET_APPROVAL_STATUS(r.INVOICE_ID
,r.INVOICE_AMOUNT
,r.PAYMENT_STATUS_FLAG
,r.INVOICE_TYPE_LOOKUP_CODE );
l_step := '5';
--dbms_output.put_line(r.invoice_num || ' - ' || l_status);

if (l_status =
'NEVER APPROVED') then
l_validation_status :=
l_nls_never_approved;
elsif (l_status = 'APPROVED') then
l_validation_status := l_nls_approved;
elsif (l_status = 'CANCELLED') then
l_validation_status := l_nls_cancelled;
elsif (l_status =
'NEEDS REAPPROVAL') then
l_validation_status :=
l_nls_needs_reapproval;
elsif (l_status = 'AVAILABLE') then
l_validation_status := l_nls_available;
elsif (l_status = 'FULL') then
l_validation_status := l_nls_full;
elsif (l_status = 'UNAPPROVED') then
l_validation_status := l_nls_unapproved;
elsif (l_status = 'UNPAID') then
l_validation_status := l_nls_unpaid;
elsif (l_status = 'PERMANENT') then
l_validation_status := l_nls_permanent;
elsif (l_status = 'NOT REQUIRED') then
l_validation_status := l_nls_not_required;
elsif (l_status = 'REQUIRED') then
l_validation_status := l_nls_required;
elsif (l_status = 'INITIATED') then
l_validation_status := l_nls_initiated;
elsif (l_status = 'WFAPPROVED') then
l_validation_status := l_nls_wfapproved;
elsif (l_status = 'REJECTED') then
l_validation_status := l_nls_rejected;
elsif (l_status = 'MANUALLY APPROVED') then
l_validation_status := l_nls_manually_approved;
elsif (l_status = 'NEEDS WFREAPPROVAL') then
l_validation_status := l_nls_wf_needs_reapproval;
end if;

-- Added for Payment Process Enhancements.
if (l_status
IN ('APPROVED','UNPAID')) then
SELECT count(*)
INTO l_inv_sel_count
FROM ap_payment_schedules
WHERE invoice_id = r.invoice_id
AND checkrun_id IS NOT NULL
AND rownum =1; --bug5691219

if (l_inv_sel_count > 0) then
l_validation_status :=
l_nls_selected_for_payment;
end if;
end if;

--dbms_output.put_line(r.invoice_num || ' - ' || l_validation_status);

END LOOP;
RETURN(l_validation_status);

EXCEPTION
WHEN OTHERS THEN
RETURN ('l_step = ' || l_step || ' ' || SQLERRM);
END get_invoice_validation_status;

This function should be used in Oracle application environnement, it will not work in TOAD or SQL Developper even if after contexualization...

Oracle AP Payment API

2010-12-17T06:45:00.000-08:00

There is no public Oracle API for the AP payments. We have developed a custom interface by using the standard procedure : ap_checks_pkg.insert_row.

Suppose that we have the following informations at the beginning :

siaxp2.org_id,
siaxp2.invoice_currency_code,
siaxp2.invoice_amount,
siaxp.reference_id,
siaxp.currency_code,
DECODE(siaxp2.invoice_currency_code,'XOF',siaxp2.invoice_amount,siaxp.payment_amount) payment_amount,
siaxp.base_amount,
siaxp.payment_date,
siaxp.transaction_code,
siaxp.internal_bank_account,
siaxp.vendor_id,
siaxp.vendor_site_id

-- Calculate bank account

SELECT abaa2.bank_account_id,
abaa2.currency_code
INTO n_internal_bank_account_id,
n_currency_code
FROM ap_bank_accounts_all abaa,
ap_bank_accounts_all abaa2
WHERE abaa.attribute1 = r_payment_xrt.internal_bank_account
AND abaa.org_id = abaa2.org_id
AND abaa.bank_account_name = abaa2.bank_account_name
AND abaa.bank_branch_id = abaa2.bank_branch_id
AND abaa2.org_id = r_payment_xrt.org_id;

-- Calculate payment document

SELECT ACS.LAST_DOCUMENT_NUM+1,
acs.check_format_id,
acs.check_stock_id,
apaa.bank_account_num,
apaa.bank_account_name,
apaa.account_type,
acs.doc_category_code,
fdsa.doc_sequence_id
INTO n_check_number,
n_check_format_id,
n_check_stock_id,
n_bank_account_num,
n_bank_account_name,
n_account_type,
v_doc_category_code,
n_doc_sequence_id
FROM ap_check_stocks_all acs,
ap_check_formats acf,
ap_bank_accounts_all apaa,
fnd_doc_sequence_assignments fdsa,
ap_bank_branches abb
WHERE abb.bank_branch_id = apaa.bank_branch_id
AND apaa.bank_account_id = n_internal_bank_account_id
AND apaa.set_of_books_id = r_societe.set_of_books_id
AND apaa.bank_account_id = acs.bank_account_id
AND NVL(acs.inactive_date, SYSDATE+1) > SYSDATE
AND acs.check_format_id = acf.check_format_id
AND acf.payment_method_lookup_code = 'EFT'
AND acs.doc_category_code = fdsa.category_code
AND acs.name = DECODE(r_payment_xrt.currency_code,n_currency_code,'VIREMENT','VIRDEV')
AND fdsa.method_code= 'A'
AND fdsa.set_of_books_id = r_societe.set_of_books_id
AND ROWNUM = 1;

-- Calculate supplier info

SELECT pv.vendor_name,
pvsa.vendor_site_code,
pv.vendor_id,
pvsa.vendor_site_id,
pvsa.address_line1,
pvsa.address_line2,
pvsa.address_line3,
pvsa.city,
pvsa.country
INTO v_vendor_name,
v_vendor_site_code,
n_vendor_id,
n_vendor_site_id,
v_address_line1,
v_address_line2,
v_address_line3,
v_city,
v_country
FROM po_vendor_sites_all pvsa,
po_vendors pv
WHERE pvsa.vendor_site_id = r_payment_xrt.vendor_site_id
AND pv.vendor_id = pvsa.vendor_id;

-- Check id

SELECT ap_checks_s.NEXTVAL
INTO n_check_id
FROM dual;

-- Sequence

SELECT db_sequence_name
INTO v_sequence
FROM fnd_document_sequences
WHERE doc_sequence_id = n_doc_sequence_id;

-- Doc sequence value

v_proc_stmt := 'select ' || v_sequence || '.nextval into :next_val from sys.dual';
EXECUTE IMMEDIATE v_proc_stmt INTO n_doc_sequence_value;

-- Final Insertion

ap_checks_pkg.insert_row (
x_rowid => v_rowid,
x_amount => r_payment_xrt.payment_amount,
x_bank_account_id => n_internal_bank_account_id,
x_bank_account_name => n_bank_account_name,
x_check_date => r_payment_xrt.payment_date,
x_check_id => n_check_id,
x_check_number => n_check_number,
x_currency_code => r_payment_xrt.invoice_currency_code,
x_last_updated_by => l_user_id,
x_last_update_date => SYSDATE,
x_payment_method_lookup_code => 'EFT',
x_payment_type_flag => 'Q',
x_address_line1 => v_address_line1,
x_address_line2 => v_address_line2,
x_address_line3 => v_address_line3,
x_checkrun_name => 'Règlement express : ID='||TO_CHAR(n_check_id),
x_check_format_id => n_check_format_id,
x_check_stock_id => n_check_stock_id,
x_city => v_city,
x_country => v_country,
x_created_by => l_user_id,
x_creation_date => SYSDATE,
x_last_update_login => l_user_id,
x_status_lookup_code => 'NEGOTIABLE',
x_vendor_name => v_vendor_name,
x_vendor_site_code => v_vendor_site_code,
x_external_bank_account_id => n_external_bank_account_id,
x_bank_account_num => n_external_bank_account_num,
x_doc_category_code => v_doc_category_code,
x_doc_sequence_id => n_doc_sequence_id,
x_doc_sequence_value => n_doc_sequence_value,
x_org_id => n_org_id,
x_vendor_id => n_vendor_id,
x_vendor_site_id => n_vendor_site_id,
x_exchange_rate => n_exchange_rate, --r_payment_xrt.base_amount/r_payment_xrt.payment_amount,
x_exchange_date => n_exchange_date, --r_payment_xrt.payment_date,
x_exchange_rate_type => n_exchange_rate_type, --'User',
x_base_amount => n_base_amount, -- r_payment_xrt.base_amount, 1.3
x_calling_sequence => v_sequence
);

ap_pay_invoice_pkg.ap_pay_invoice (
p_invoice_id => r.invoice_id,
p_check_id => n_check_id,
p_payment_num => r.payment_num,
p_invoice_payment_id => n_invoice_payment_id,
p_period_name => n_period_name,
p_accounting_date => r_payment_xrt.payment_date,
p_amount => r.amount_remaining,
p_discount_taken => 0,
p_invoice_base_amount => ROUND(r.amount_remaining * n_exchange_rate, 2), -- 1.3 n_invoice_base_amount, -- A confirmer
p_payment_base_amount => ROUND(r.amount_remaining * n_exchange_rate, 2), -- A confirmer
p_accrual_posted_flag => 'N',
p_cash_posted_flag => 'N',
p_posted_flag => 'N',
p_set_of_books_id => n_sob_id,
p_last_updated_by => l_user_id,
p_last_update_login => l_user_id,
p_currency_code => r_payment_xrt.invoice_currency_code,
p_exchange_rate => n_exchange_rate,
p_exchange_rate_type => n_exchange_rate_type, --'User',
p_exchange_date => n_exchange_date, --r_payment_xrt.payment_date,
p_replace_flag => 'N',
p_bank_account_id => n_external_bank_account_id,
p_bank_account_num => n_external_bank_account_num,
p_payment_mode => 'PAY'
);

Oracle PO API to mass close PO

2010-12-17T05:54:00.000-08:00

The following procedure has to be executed in Oracle environnement, but not in TOAD or SQL Developper with contexualization.

PROCEDURE Mass_close_PO(
PR_po_header_id IN NUMBER,
PR_po_line_id IN NUMBER,
PR_po_shipment_id IN NUMBER,
PR_action IN VARCHAR2,
PR_reason IN VARCHAR2,
PR_result OUT BOOLEAN,
PR_return_code OUT VARCHAR2
) IS
-- =====================================================================
-- P R O C E D U R E Mass_close_PO
-- =====================================================================
-- Description : Mass close or finally close PO
-- =====================================================================
-- I N P U T
-- =====================================================================
-- Nom Description
-- PR_po_header_id po_header_id
-- PR_po_line_id po_line_id
-- PR_po_shipment_id po_shipment_id
-- PR_action po_action
-- PR_reason po_closed_reason
-- =====================================================================
-- O U T P U T
-- =====================================================================
-- Nom Description
-- PR_result TRUE or FALSE
-- PR_return_code Error message
-- =====================================================================

BEGIN
g_step := 'Mass_close_PO - PR_po_header_id = '|| PR_po_header_id || ' PR_po_line_id = ' || PR_po_line_id || ' PR_po_shipment_id = ' || PR_po_shipment_id;
fnd_file.put_line(fnd_file.log, to_char(SYSDATE,'DD-MM-RRRR HH24:MI:SS : ') || g_step);

PR_result := PO_ACTIONS.CLOSE_PO(
P_DOCID => PR_po_header_id,
P_DOCTYP => 'PO',
P_DOCSUBTYP => 'STANDARD',
P_LINEID => PR_po_line_id,
P_SHIPID => PR_po_shipment_id,
P_ACTION => PR_action,
P_REASON => PR_reason,
P_CALLING_MODE => 'PO',
P_CONC_FLAG => 'N',
P_RETURN_CODE => PR_return_code,
P_AUTO_CLOSE => 'N' ,
P_ACTION_DATE => SYSDATE ,
P_ORIGIN_DOC_ID => NULL
);

EXCEPTION
WHEN OTHERS THEN
RAISE;
END Mass_close_PO;

Create a cursor to select eligible shipment lines in po_line_locations_all. Specify the action (CLOSE or FINALLY CLOSE) and the closed reason, call the below procedure to mass close PO.

The standard logic of this API will be the following : after closing the shipment line, the program will control the related po line, if all shipment line are closed or finally closed, api will close this po line. Item for the po header, if all related po lines are closed or finally closed, api will close the po header.

图片搜索时代终于来临了？

2010-05-30T16:32:00.001-07:00

关于图片搜索和声音搜索，我已经期待和思考了很久。今天终于看到国内一家公司推出的图片识别搜索服务，很是兴奋，赶快试用了一下。

淘淘搜网址：http://www.taotaosou.com/ （不禁PS一下，估计是淘宝出资赞助的，连名字都这么淘。。。）

目前这家网站还只能搜索T-Shirt。可搜图片有两种方式，一种是本地上传，一种是网络上的图片。

关于第一种方式，可以理解为哪一天你走在大街上，看到一MM传的衣服特别可爱，也想买一件，但又不知道能到哪儿买。你就可以拿出你的手机，偷拍下这个MM，完之后把照片上传到这个网站，这网站就会到淘宝上的图片库里搜同样样子的T-Shirt。

关于第二种方式，方便了追星族模仿明星的穿着。我们如果对哪个明星的穿着感兴趣的话，就可以把新闻上的照片导过来，开动搜索，查一查淘宝上有没有人翻这件衣服的。

放眼当前的互联网市场，图片搜索功能到现在都没有成功研究出来（最起码是没有一家成功推广的），个中肯定是有原因的。一个可能就是技术问题。我估计Image的后台识别和处理方式不像文字数字那样简单。关于这一方面，可能还没有制定统一的标准。再一个就是商业化的问题。没有商业投资的话，研究人员就不可能继续下去。在这一点上，这家网站就非常聪明。联合淘宝开发，既实现了对图片搜索的研究，又可以将阶段性的成果结合淘宝的庞大图片库来测试，阶段性上线后即可以为淘宝服务，真是淘宝和研究人员的双赢。

再来说说我的测试结果：概念很好，结果很差！

我从电脑里找了一张模特T-Shirt图，传上去，搜了一下。结果搜不到，也算正常吧，这衣服可能国内淘宝上没卖的。在提示我没有找到同样衣服的同时，就跟google一样，其他的T-Shirt在下边建议了出来。好吧，那我就用一件你建议给我的衣服搜，看看能不能搜出同样的出来。我就copy了一件衣服图片的link，用第二种方法开搜。结果同样的衣服没搜出来，到搜出来了10几件八杆子打不着的T-Shirt。。。哭笑不得。。。

这家网站的理念真的不错，但如果没有成熟技术的支持，匆忙上线只能是拔苗助长，摆出来贻笑大方。

图片搜索和声音搜索到现在还没有大范围推广，肯定是理论和技术上还有没过关的地方。希望研究人员多多努力，赶快把这项技术成功的推广开来。

创业者的Plan A和Plan B

2010-05-30T15:23:00.001-07:00

创业是在生与死之间的搏弈，创业之路要经过火焰山，要经过地狱之门，为了确保行程安全，上路时最好准备两手计划：Plan A和Plan B。

Plan A：如果我能搞到VC的钱，就高调出发，过把瘾，好好地大干一场！

Plan B：如果我搞不到VC的钱，就自己掏钱，低调苦干，一定赚进钱来！

Plan A：产品多样化，产业链上下都要抓，企业多元化经营才能不在一棵树上吊死。

Plan B：只做一件事情，把一件事情做好、做透、做到了极致，别的什么也不去做。

Plan A：市场是要靠钱去砸出来的，市场费用的多少，决定了我的市场占有率。

Plan B：创新的产品，过硬的品质，客户的体验，不需自我炒作，口碑传万家。

Plan A：高薪聘请大公司的高管，高标准、高水平、严格按流程管理。

Plan B：用股票期权激励团队，用激情、梦想、结果导向，自我管理。

Plan A：有没有利润、赚不赚钱不要紧，只要速度快，故事动人，马上能IPO上市。

Plan B：不做亏本生意，不去跟风，有现金流、有利润、有增长，就是一桩好生意。

Plan A：防止竞争的最好方法是专利，消灭竞争的最好办法是收购竞争对手。

Plan B：防止竞争的最好方法是迅速商用，消灭竞争的最好方法是不断创新。

Plan A：这商业世界的次序是大鱼吃小鱼，天经地义。

Plan B：这商业世界的次序是快鱼吃慢鱼，天经地义。

Plan A：辞掉了高薪的职位去创业，不成功，实在是亏大了，只准成功、不许失败！

Plan B：辞掉了高薪的职位去创业，是自己的选择，不成功，东山再起、从头再来！

世界上有多少事情是预先想好的？有多少成功是一帆风顺的？有多少创业公司的Plan A能够得以完满兑现？Plan A是理想的，前提是如果有钱、如果做大、如果、如果、如果……Plan B是迫不得已的，没有办法的办法，当创业者走到了陷阱的边缘、生死关头，相信都会清醒过来的，因为你别无选择。

作者简介：

查立：乾龙创投合伙人；网易科技名博；曾在ZiffDavis等公司任管理职务；在英国上市公司MotionPoster其亚洲区任CEO。

Problème compilation Oracle forms 6i - 10g lors de la migration 11.5.9 à 11.5.10

2010-04-27T08:16:00.000-07:00

Contexte :

Après la migration 11.5.10, les spécifiques doivent être ajoutés sur l'écran de facture AR standard (version 11.5.10).

Etape 1 > Prendre l'écran standard 11.5.10 via FTP

Etape 2 > le modifier avec Oracle Forms 6i

Etape 3 > déposer la nouvelle forms sur le serveur dans $AU_TOP/forms/F ou US

Etape 4 > compiler la forms avec f60gen, mettre le fmx dans $AR_TOP/forms/F ou US

Problème :

A l'étape 2, si la forms est déposé dans un répertoire normale
==> impossible de l'ouvrir avec Oracle forms 6i, l'outil plante sans message d'erreur. Il est possible de l'ouvrir avec Oracle forms 10g mais après on aura un problème de compilation à l'étape 4 f60gen : message d'erreur ACCESS DENIED.

Solution :

Mettre le forms standard dans un répertoire local. Avant de l'ouvrir, il faut télécharger toutes les forms standard de $AU_TOP/forms/F ou US et les mettre dans le même répertoire. Une fois toutes les forms téléchargées, on pourra ouvrir cette forms avec 6i puis la modifier. La forms modifiée sera compilée sans problème à l'étape 4 sur le serveur Unix.

VAProxyD.dll 导致的IE内核的浏览器崩溃的解决办法

2010-04-23T07:08:00.000-07:00

今晚RP小爆发，偶的浏览器不幸崩溃了。

好久以前就卸掉了Firefox，只留了IE和Maxthon，两个都是IE内核的，所以这次都给挂了。

花了将近一个小时查毒+调试+卸载，最后终于弄好了。估计在国外看PPLIVE的人不少，so方法发上来，大家备用吧。

现在回头想想，原因应该是今晚卸载的一个UUSEE插件。之后在打开一个视频网页的时候，浏览器就崩溃了。只不过当时是在新标签里打开视频，所以只是新标签崩溃，浏览器中其他非视频网页仍然正常运行。

当浏览器彻底关闭以后，再次打开时，错误信息就弹出来了：

程序无法正常启动。

点击详细信息的话，信息如下：

问题签名:
问题事件名称: APPCRASH
应用程序名: iexplore.exe
应用程序版本: 8.0.6001.18904
应用程序时间戳: 4b835fec
故障模块名称: VAProxyD.dll
故障模块版本: 1.0.0.50
故障模块时间戳: 4bcfe868
异常代码: c0000005
异常偏移量: 0000a8bd
OS 版本: 6.0.6001.2.1.0.768.3
区域设置 ID: 2052
其他信息 1: fd00
其他信息 2: ea6f5fe8924aaa756324d57f87834160
其他信息 3: fd00
其他信息 4: ea6f5fe8924aaa756324d57f87834160

第一感觉就是RP爆发，病毒来了。一边祝福着360的全体人员的老母，一边无奈的打开360查杀（一时半会儿也没别的，唉）。

与此同时，一颗红心，两手准备。我感觉也说不定是IE的哪个插件设置变了，于是右键IE，通过属性进入加载项，看来看去也没找到陌生的插件，只好放弃。

继续等待漫长的查毒，忽然想起了刚才删除的UUSEE的插件。说不定删这个的时候，把它用到的其他的视频软件的插件需要的component也顺带删掉了。。。事已至此，只好闭上眼睛试了（反正我也没装太多的视频插件）。第一个删掉的就是PPLIVE的加速器。删掉之后，重新打开浏览器，BINGO！可以用了！

这次漏洞最不方便的地方就是出问题之后，所有的浏览器（IE内核的）都无法打开了。所以就不能上网找解决方案+下载其他内核浏览器，而且浏览器崩溃的错误信息给的很不明确，让人无从下手。其实这倒也不能怪微软了，如果它这样开放IE内核视频interface的话，势必会造成插件管理和兼容的混乱。对我们用户来说，在浏览器提供更丰富功能的前提下，适当的小崩溃就一笑而过吧。。。

SQL Loader fichier CTL 不可理解的ORA-01722: Nombre non valide

2010-03-29T03:29:00.000-07:00

Loader文件如下：

LOAD DATA
APPEND
INTO TABLE SE_KN_HEADERS
FIELDS TERMINATED BY ';'
(
NOKN,
NOEXP,
MODEXP,
LOCATION,
COUNTRY,
IATA_CODE,
AIRPORT,
AWB_NUMBER,
FLIGHT,
DEPART_DATE "to_date(:DEPART_DATE,'DDMMYYYY')",
DEPART_TIME "to_date(:DEPART_TIME,'HH24MI')",
ARR_DATE "to_date(:ARR_DATE,'DDMMYYYY')",
ARR_TIME "to_date(:ARR_TIME,'HH24MI')",
INVOICE_NUM,
INVOICE_AMOUNT "to_number(:INVOICE_AMOUNT,'9999999999.99')",
CURRENCY,
INVOICE_DATE "to_date(:INVOICE_DATE,'DDMMYYYY')",
POD_DATE "to_date(:POD_DATE,'DDMMYYYY')",
POD_TIME "to_date(:POD_TIME,'HH24MI')",
POD_SIGN,
SHIPMENT_WEIGHT,
NUMLOT "to_number(:NUMLOT,'99999999')"
)

测试的输入文件如下：

“123456;CCH001;ME;Marseille;France;IATA_CCH;MP2;AWB_CCH;F0123;29032010;1157;29032010;1800;CCH-INV-001;90;EUR;29032010;30032010;0900;SIGNE_CCH;SHIPMENT_CCH;100

”

在Oracle中运行后程序以warning结束，错误信息如下：

Enregistrement 1 : Rejeté - Erreur sur table "SE"."SE_KN_HEADERS", colonne NUMLOT. ORA-01722: Nombre non valide

分析：
文本文件中的NUMLOT一列是100，明明是Nombre，为什么会显示Nombre non valide呢？
问题在于csv文件中看不到的最后一个字符：回车。
文本中有两行，一行数据，另外一个是空行。就是这个空行和第一行最后一列里的数据连了起来，导致loader不能正确识别数字。
另外，如果分割符标错了的话（应该用‘；’分割，结果在某一数字列后用了‘，’，于是数字列就和后边的列合并了），一样也会出现这样的错误。

Vérification des réceptions/expéditions pour une commande en PL/SQL dans Oracle applications

2010-03-26T08:32:00.000-07:00

Pour vérifier les réceptions:

SELECT mtl.transaction_date DATE_MVT,
mtl.transaction_quantity QTE_RECUE,
rsh.receipt_num "Receipt",
po_h.segment1 "PO"
FROM rcv_transactions rcv,
po_distributions_all po_pod,
po_lines_all po_l,
po_headers_all po_h,
rcv_shipment_headers rsh,
mtl_material_transactions mtl
WHERE 1=1
AND rcv.po_line_id = po_pod.po_line_id
AND rcv.po_header_id = po_pod.po_header_id
AND po_pod.po_line_id = po_l.po_line_id
AND po_pod.po_header_id = po_l.po_header_id
AND mtl.rcv_transaction_id = rcv.transaction_id
AND po_l.org_id = po_h.org_id
AND po_l.po_header_id = po_h.po_header_id
AND rcv.shipment_header_id = rsh.shipment_header_id
AND po_h.segment1 = '99750'; -- PO to check

Pour vérifier les expéditions (requête spécifique qui n'est utilisable que chez un client ;-)):

SELECT sel.*
FROM se_exped_lines sel, po_lines_all pla, po_headers_all pha
WHERE sel.cmde_line_num = pla.po_line_id
AND pla.po_header_id = pha.po_header_id
AND pha.segment1 = '102575'; -- Numéro de commande

Oracle Applications中经常用到的FNDLOAD指令

2010-03-24T04:08:00.000-07:00

1 - Jeu de valeur / Value Set

Download:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct CHA-ICS-XXXX.ldt VALUE_SET FLEX_VALUE_SET_NAME="CHA-ICS-XXXX"

Upload:
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct CHA-ICS-XXXX.ldt

2 - Champs Utilisateurs Flexibles / Descriptive Flex field

Download:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct SE_DFF_HR_LOCATIONS.ldt DESC_FLEX APPLICATION_SHORT_NAME=PER DESCRIPTIVE_FLEXFIELD_NAME='HR_LOCATIONS'

APPLICATION_SHORT_NAME 和DESCRIPTIVE_FLEXFIELD_NAME都可以在设置界面的Diagnostic->Examin里找到。

Upload:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct SE_DFF_HR_LOCATIONS.ldt

3 - Traitement simultané / Concurrent program

Download:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct SE_XXXX.ldt PROGRAM CONCURRENT_PROGRAM_NAME='SE_XXXX

Upload:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct SE_XXXX.ldt

4 - Jeu de traitements / Request Sets

Download:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct SE_XXXX.ldt REQ_SET REQUEST_SET_NAME='SE_XXXX'

Upload:
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct SE_XXXX.ldt

5 - Lien dans jeu de traitements / Request Sets Link

Download:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct SE_XXXX_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME='SE_XXXX'

Upload:
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct SE_XXXX_LINK.ldt

6- Groupe de traitement / Request Group

Download:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct All_Reports.ldt REQUEST_GROUP REQUEST_GROUP_NAME="All Reports" APPLICATION_SHORT_NAME="PO"

下载之后，所有All Reports Group和Oracle Purchasing Application的concurrent request都会被下载下来。如果希望将所有requests全部传到新的base上的话，直接上传即可。
若希望只上传某一个request，则需要打开ldt文件，将其它的request删除，只保留需要上传的request。

Upload:
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct All_Reports.ldt

ORA-00960: nom de colonne ambigu dans liste de sélection 解决办法

2010-03-05T06:38:00.000-08:00

程序报错：ORA-00960: nom de colonne ambigu dans liste de sélection

原因：

这是因为在source中使用了类似于select A，B，C，A from TEST之类的语句。Oracle不允许在select里出现两次同一列的名称。

在这种情况下，只需要为第二次呼叫的列名添加一个alias就可以了：select A，B，C，A “D” from TEST

Input truncated to * characters 解决办法

2010-03-05T06:17:00.000-08:00

"Input truncated to 5 characters" s'affiche dans le fichier de sortie du concurrent programme.

Cause :
Dans le fichier exécutable sql, à la fin juste après "/" , ajouter un retour chariot, supprimer les espaces inutiles. 在“/”后边如果不是紧接回车的话就会出现这个信息，不是很mechant，但放到结果里很不好看。