E9.2 Remove cancelled PO messages from Purchase order Approval screen P43081

[Jetski60]

Hi,
trying to write an orchestration to remove messages from P43081 for purchase order lines with status 980|999.

I know there is a processing options as in screen shot attached, will this po option be sufficient to hide cancelled lines.

or go re-invent the wheel and do an orchestration.??

Many Thanks

 

[Jetski60]

after some search n P43081 and F4209 Held Orders table.
am i correct t F4209 is the place that holds the messages foor Purchase orders pending approval r queued for approval?

Thanks

 

[BetoJDE]

Hi Jetski60, you are correct, the holds messages store on F4209, on that table you find the holds from sales and purshases orders

 

[alfredorz]

Yeah, you need to do a orchestrator to purge F4209 records.
Oracle Support Document 2790784.1 (E1: 43: Batch Process to Remove Purchase Order Approval Hold Code and Purchase Orders) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2790784.1
Enhancement Bug 33098479 has been requested for a future release

 

[Jetski60]

Yeah, you need to do a orchestrator to purge F4209 records.
Oracle Support Document 2790784.1 (E1: 43: Batch Process to Remove Purchase Order Approval Hold Code and Purchase Orders) can be found at: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2790784.1
Enhancement Bug 33098479 has been requested for a future release

Thank you alfredorz,
I did a SQL for SEELCT
******
SELECT * FROM UAT1DTA.F4209
JOIN UAT1DTA.F4311 ON F4209.HODOCO = F4311.PDDOCO
WHERE F4311.PDLTTR = '980' AND
F4311.PDNXTR = '999'AND
F4209.HOASTS = '2N';
****
But struggling to transslate this to an orchestration
once I'm successful with SELECT, then will do a DEELTE or UPDATE Statements

 

[DaveWagoner]

Thank you alfredorz,
I did a SQL for SEELCT
******
SELECT * FROM UAT1DTA.F4209
JOIN UAT1DTA.F4311 ON F4209.HODOCO = F4311.PDDOCO
WHERE F4311.PDLTTR = '980' AND
F4311.PDNXTR = '999'AND
F4209.HOASTS = '2N';
****
But struggling to transslate this to an orchestration
once I'm successful with SELECT, then will do a DEELTE or UPDATE Statements

Which tools release are you on? If you're on R23 at least, you could:

1) Run this SQL as a db connector component, returning dataset containing only essential fields (fields for keying, fields for update/delete logic)
2) Bring dataset into logic extension component, taking care to set up the input array to match db output from step #1
3) Within logic extension, loop over the array and do record-by-record logic for update or delete. Very very similar to doing event rules programming from E1.

I've found this to be more performant than using similar logic within rule and form components to handle the update/delete.

 

[markdcci]

Jetski,
I concur with what Dave said.

But in addition based on looking at your select statement, you will want to add the full key to the F4311 so that you don't inadvertently delete or update the wrong records. The full key on F4311 is PDDOCO, PDDCTO, PDKCOO, PDSFXO,PDLNID.

As Beto said, this table has several uses in procurement and sales so you will want to also limit your selection to records with HOHCOD = '**'.

Also, I believe order processing for procurement using the F4209 is at the header level. Therefore, although the F4209 has LNID, it should be blank so you would want to drop the LNID join.

Try this select statement to see if it gets you what you need.

SELECT * FROM UAT1DTA.F4209
JOIN UAT1DTA.F4311 ON F4209.HODOCO = F4311.PDDOCO
AND F4209.HODCTO = F4311.PDDCTO
AND F4209.HOKCOO = F4311.PDKCOO
AND F4209.HOSFXO = F4311.PDSFXO
WHERE F4311.PDLTTR = '980' AND
F4311.PDNXTR = '999' AND
F4209.HOASTS = '2N' AND
F4209.HOHCOD='**';

Even if it doesn't make a difference today as you use different features in the future you may end up adding records to the F4209 that could cause them to get inadvertently selected in the delete/update.

 

[Jetski60]

Which tools release are you on? If you're on R23 at least, you could:

1) Run this SQL as a db connector component, returning dataset containing only essential fields (fields for keying, fields for update/delete logic)
2) Bring dataset into logic extension component, taking care to set up the input array to match db output from step #1
3) Within logic extension, loop over the array and do record-by-record logic for update or delete. Very very similar to doing event rules programming from E1.

I've found this to be more performant than using similar logic within rule and form components to handle the update/delete.

Thanks Dave,
We are on 9.2.4.3
I was Thinking of a custom request, possibly Groovy

 

[Jetski60]

Thanks Dave,
We are on 9.2.4.3