| How to stop the process (merged) [message #608553] |
Fri, 21 February 2014 00:42  |
 |
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi ,
I have 3 alerts and if 3 conditions are satisfied then run the next process , otherwise stop the Process
Thank You
[MERGED #1 by CM]
[MERGED #2, #3, #4, #5 and #6 by LF]
[Updated on: Wed, 05 March 2014 05:28] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: How to stop the process [message #608573 is a reply to message #608572] |
Fri, 21 February 2014 02:47  |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
I want to show the messages 'Are you sure,you want to move data into Brooklyn Warehouse?'; after all the 3 conditions satisfied.
I want to stop the next process where any one of the 3 conditions failed, other wise go to the next process and show the 'Are you sure,you want to move data into Brooklyn Warehouse?
Getting stopped the process , it was not showing me the messages when the 3 conditions satisfied.
DECLARE
alert_button NUMBER;
v_alert NUMBER;
v_message VARCHAR2(1000);
v_count1 NUMBER; -->Added on 14-Feb-2014
v_count2 NUMBER; -->Added on 14-Feb-2014
v_count NUMBER := 0;
BEGIN
BEGIN
--IF :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
Go_block('RHCLANDEDCOSTDET');
first_record;
LOOP
--IF :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
SELECT Count(1)
INTO v_count1
FROM mtl_system_items_kfv a
WHERE inventory_item_id IN(SELECT inventory_item_id
FROM regal.regal_inv_landed_cost_tab b
WHERE item_number = :RHCLANDEDCOSTDET.item_number
AND file_number = :RHCLANDEDCOSTHEADER.file_number)
--AND selected_flag = 'Y')
AND organization_id = '82';
IF v_count1 = 0 and :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
fnd_message.Set_string('This' ||:RHCLANDEDCOSTDET.item_number ||' Number is not assigned into the BRW'); --testing
fnd_message.show;
RAISE form_trigger_failure;
ELSIF :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
SELECT Count(1)
INTO v_count2
FROM mtl_system_items_kfv a
WHERE inventory_item_id IN(SELECT inventory_item_id
FROM regal.regal_inv_landed_cost_tab b
WHERE item_number = :RHCLANDEDCOSTDET.item_number
AND file_number = :RHCLANDEDCOSTHEADER.file_number)
--AND a.inventory_item_id=b.inventory_item_id
--AND selected_flag = 'Y')
AND organization_id IN( '81', '82' )
AND Nvl(inventory_item_status_code, 'Active') = 'Active';
IF v_count2 = 0 and :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
fnd_message.Set_string('The' ||' ' ||:RHCLANDEDCOSTDET.item_number ||' ' || 'is in the Inactive Mode Please Active ') ; --testing
fnd_message.show;
RAISE form_trigger_failure;
ELSIF :RHCLANDEDCOSTDET.ONHANDQUANTITY =0 and :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
--IF :RHCLANDEDCOSTDET.ONHANDQUANTITY =0 and :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
fnd_message.Set_string('This '||:RHCLANDEDCOSTDET.ITEM_NUMBER||' Does not have ONHANDQUANTITY in RHC');
fnd_message.show;
RAISE form_trigger_failure;
END IF;
next_record;
end if;
Exit WHEN :System.last_record = 'TRUE';
--END IF;
--END IF;
END LOOP;
first_record;
--RAISE form_trigger_failure;
--END IF;
--end if;
END;
BEGIN
v_message := 'Are you sure,you want to move data into Brooklyn Warehouse?';
Set_alert_property('BRW', alert_message_text, v_message);
alert_button := Show_alert('BRW');
IF alert_button = alert_button1 THEN
commit_form;
BEGIN
SELECT Count(1)
INTO v_count
FROM regal.regal_inv_landed_cost_tab
WHERE file_number = :RHCLANDEDCOSTHEADER.file_number
AND selected_flag = 'Y'
AND brw_processed = 'Y'
AND orig_quantity_received = quantity_received;
END;
IF v_count != 0 THEN
fnd_message.Set_string('You are selecting Processed Records. Please Check '); --testing
fnd_message.show;
ELSE
submit_inv_request;
END IF;
END IF;
END;
END;
I am very confusing please help
|
|
|
|
|
|
|
|
| Re: How to stop the process [message #608579 is a reply to message #608578] |
Fri, 21 February 2014 03:06 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
mist598 wrote on Fri, 21 February 2014 08:56I want to show if the 3 conditions are satisfied that is Are you sure,you want to move data into Brooklyn Warehouse?
If the 3 conditions are satisfied for all records or just some of them?
mist598 wrote on Fri, 21 February 2014 08:56
Is this code is correct?
Haven't you tested it already? Cause if you have you should know the answer to that.
|
|
|
|
| Re: How to stop the process [message #608584 is a reply to message #608579] |
Fri, 21 February 2014 03:19 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Means, if the any one the 3 alerts shows then stop the process, if not then show this Alert " Are you sure,you want to move data into Brooklyn Warehouse?"
1) This item_number is not assigned into the BRW'
2)'is in the Inactive Mode Please Active '
3)' Does not have ONHANDQUANTITY in RHC'
In the above 3 alerts ,if i get any one of the 3, then don't want to show the " Are you sure,you want to move data into Brooklyn Warehouse?", means stop the process after i get the any one of the 3 Alerts ,Otherwise go to the next process.that is " Are you sure,you want to move data into Brooklyn Warehouse?"
|
|
|
|
|
|
|
|
| Re: How to stop the process [message #608597 is a reply to message #608584] |
Fri, 21 February 2014 04:45 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
mist598 wrote on Fri, 21 February 2014 09:19Means, if the any one the 3 alerts shows then stop the process, if not then show this Alert " Are you sure,you want to move data into Brooklyn Warehouse?"
1) This item_number is not assigned into the BRW'
2)'is in the Inactive Mode Please Active '
3)' Does not have ONHANDQUANTITY in RHC'
In the above 3 alerts ,if i get any one of the 3, then don't want to show the " Are you sure,you want to move data into Brooklyn Warehouse?", means stop the process after i get the any one of the 3 Alerts ,Otherwise go to the next process.that is " Are you sure,you want to move data into Brooklyn Warehouse?"
That doesn't actually answer this at any point
cookiemonster wrote on Fri, 21 February 2014 09:06mist598 wrote on Fri, 21 February 2014 08:56I want to show if the 3 conditions are satisfied that is Are you sure,you want to move data into Brooklyn Warehouse?
If the 3 conditions are satisfied for all records or just some of them?
I assume the answer is that the process should run only if all the selected rows satisfy all the conditions. But I shouldn't have to assume.
If I'm right then I've got to ask - have you actually tested it?
Because at a glance it appears to do what you want.
LF has given the basic code structure and it appears to be what you have.
|
|
|
|
| Re: How to stop the process [message #608604 is a reply to message #608597] |
Fri, 21 February 2014 05:43 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi,
1) This item_number is not assigned into the BRW'
2)'is in the Inactive Mode Please Active '
3)' Does not have ONHANDQUANTITY in RHC'
I have 3 Alerts ,i want an Alerts when the above 3 conditions are check one by one , means suppose if i select the 3 records from the multi-records , then validate the 3 records and shows Alerts if the conditions are not satified.
My Requirement is if any one of the Alert is display then stop the process and if the Any one of the Alerts not show then go to the next process, means i want to show "Are you sure,you want to move data into Brooklyn Warehouse?"(This is the Alert)
I want to check the above 3 Alerts , if not display any Alerts then got to the "Are you sure,you want to move data into Brooklyn Warehouse?"
I tried with the code where Littlefoot were updated...But not shows any Alerts.
DECLARE
alert_button NUMBER;
v_alert NUMBER;
v_message VARCHAR2(1000);
v_count1 NUMBER; -->Added on 14-Feb-2014
v_count2 NUMBER; -->Added on 14-Feb-2014
v_count NUMBER := 0;
V_ONHANDQUANTITY NUMBER;
BEGIN
if :RHCLANDEDCOSTDET.selected_flag = 'Y' then -->(1)
Go_block('RHCLANDEDCOSTDET');
first_record;
LOOP
SELECT Count(1)
INTO v_count1
FROM mtl_system_items_kfv a
WHERE inventory_item_id IN(SELECT inventory_item_id
FROM regal.regal_inv_landed_cost_tab b
WHERE item_number = :RHCLANDEDCOSTDET.item_number
AND file_number = :RHCLANDEDCOSTHEADER.file_number)
AND organization_id = '82';
IF v_count1 = 0 THEN
fnd_message.Set_string('This' ||:RHCLANDEDCOSTDET.item_number ||' Number is not assigned into the BRW'); --testing
fnd_message.show;
END IF;
raise form_trigger_failure;
next_record;
Exit WHEN :System.last_record = 'TRUE';
END LOOP;
elsif :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
Go_block('RHCLANDEDCOSTDET');
first_record;
LOOP
SELECT Count(1)
INTO v_count2
FROM mtl_system_items_kfv a
WHERE inventory_item_id IN(SELECT inventory_item_id
FROM regal.regal_inv_landed_cost_tab b
WHERE item_number = :RHCLANDEDCOSTDET.item_number
AND file_number = :RHCLANDEDCOSTHEADER.file_number)
AND organization_id IN( '81', '82' )
AND Nvl(inventory_item_status_code, 'Active') = 'Active';
IF v_count2 = 0 THEN
fnd_message.Set_string('The' ||' ' ||:RHCLANDEDCOSTDET.item_number ||' ' || 'is in the Inactive Mode Please Active ') ; --testing
fnd_message.show;
END IF;
RAISE form_trigger_failure;
next_record;
Exit WHEN :System.last_record = 'TRUE';
END LOOP;
ELSIF :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
Go_block('RHCLANDEDCOSTDET');
first_record;
LOOP
select sum(transaction_quantity) ONHANDQUANTITY
INTO V_ONHANDQUANTITY
from mtl_onhand_quantities
where inventory_item_id = :RHCLANDEDCOSTDET.inventory_item_id
and organization_id = 81
and subinventory_code = 'Vessel';
IF V_ONHANDQUANTITY =0 THEN
fnd_message.Set_string('This '||:RHCLANDEDCOSTDET.ITEM_NUMBER||' Does not have ONHANDQUANTITY in RHC');
fnd_message.show;
END IF;
RAISE form_trigger_failure;
next_record;
Exit WHEN :System.last_record = 'TRUE';
END LOOP;
ELSIF v_count1>0 AND v_count2>0 AND V_ONHANDQUANTITY>0 THEN
v_message := 'Are you sure,you want to move data into Brooklyn Warehouse?';
Set_alert_property('BRW', alert_message_text, v_message);
alert_button := Show_alert('BRW');
IF alert_button = alert_button1 THEN
commit_form;
BEGIN
SELECT Count(1)
INTO v_count
FROM regal.regal_inv_landed_cost_tab
WHERE file_number = :RHCLANDEDCOSTHEADER.file_number
AND selected_flag = 'Y'
AND brw_processed = 'Y'
AND orig_quantity_received = quantity_received;
END;
IF v_count != 0 THEN
fnd_message.Set_string('You are selecting Processed Records. Please Check '); --testing
fnd_message.show;
ELSE
submit_inv_request;
END IF;
END IF;
END if;-->(1)
end;
Thank you
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to stop the process [message #608612 is a reply to message #608610] |
Fri, 21 February 2014 06:41 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
1) learn to indent your code properly, it's hard to see what if's match to what.
2) Why are you checking :RHCLANDEDCOSTDET.selected_flag before going to the first record in the block?
3) You appear to have this structure:
if :RHCLANDEDCOSTDET.selected_flag = 'Y' then
<do a check>
elsif :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
<do a another check>
ELSIF :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
<do yet a another check>
ELSIF v_count1>0 AND v_count2>0 AND V_ONHANDQUANTITY>0 THEN
<ask if the user wants to move data>
END IF;
I have no idea how you dreamed that up.
When the code reaches an if/elsif combo it will only ever execute the first if/elsif that evaluates to true.
So it will never, ever execute the 1st and 2nd elsif. The above is equivalent to:
if :RHCLANDEDCOSTDET.selected_flag = 'Y' then
<do a check>
ELSIF v_count1>0 AND v_count2>0 AND V_ONHANDQUANTITY>0 THEN
<ask if the user wants to move data>
END IF;
And those variables are only set inside the other IF's so that elsif is never true, leaving you with:
if :RHCLANDEDCOSTDET.selected_flag = 'Y' then
<do a check>
END IF;
The conditions you're checking there are not what LF meant at all.
Again, your original code appears to do what you want, are you sure it doesn't work?
|
|
|
|
|
|
| Re: How to stop the process [message #608616 is a reply to message #608614] |
Fri, 21 February 2014 07:05 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
You've got a block of records.
You need to loop through the block of records.
For each record that's selected you need to do 3 checks.
If any of the 3 checks fails you need to stop the process by raising form_trigger_failure.
Otherwise, after looping through all the records you need to display the alert asking them if they want to move the data.
As far as I can tell (which is difficult since a) it's badly formatted and b) I can't run it) your original code does that.
It's certainly far closer to what you need than your latest attempt.
I presume the orginal code doesn't quite work. That doesn't mean that the basic structure is wrong, just that there's a bug somewhere.
So do this:
1) Format it properly, check that the if/elsif/end if line up correctly and that the correct code is inside the correct if.
2) Go through in your head what it does. It's not complicated, you only have three checks. Ask yourself what will happen at each stage.
If you still can't spot where you've gone wrong then you need to debug it.
So put messages in the code to display the values being checked at each point. Ensure it does all three checks for each selected record.
You will then be able to easily spot where it's going wrong and fix it.
|
|
|
|
|
|
|
|
| Re: Error [message #608686 is a reply to message #608683] |
Sat, 22 February 2014 02:04 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Hi,
the problem is in the nesting of LOOP and IF statements. Your code flow looks like this:
IF (condition) THEN
...
LOOP
...
ELSE
...
EXIT WHEN (condition);
...
END LOOP
...
END IF;
Does not make much sense, does it? Just imagine that you should interpret this code.
What would you do when reaching ELSE inside the LOOP? Skip to END IF? But, you will miss END LOOP then. Follow to the ELSE clause? But, the condition is not filled to enter that branch.
Or, another possible code flow, when running the ELSE branch from the beginning and reaching END LOOP there (there was no start of LOOP)?
|
|
|
|
| Re: How to stop the process [message #608693 is a reply to message #608616] |
Sat, 22 February 2014 04:30 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I've merged your two threads, do not start a new thread for the same problem, as just confuses matters.
As I pointed out earlier checking selected_flag before going to the first record in the block is just pointless.
Furthermore I've already described the code structure:
cookiemonster wrote on Fri, 21 February 2014 13:05You've got a block of records.
You need to loop through the block of records.
For each record that's selected you need to do 3 checks.
If any of the 3 checks fails you need to stop the process by raising form_trigger_failure.
Otherwise, after looping through all the records you need to display the alert asking them if they want to move the data.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: not in query [message #608818 is a reply to message #608817] |
Tue, 25 February 2014 03:48 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
AID101.JCP.5495.BE" IS in the " 81 in the regal_inv_landed_cost_tab",
AID101.JCP.5495.BE" IS in the " 81 & 82 in the mtl_system_items_kfv table",
AID101.JCP.5495.BE" IS Exists in the " 81 in the regal_inv_landed_cost_tab & AID101.JCP.5495.BE" IS Not Exists in the " 82 in the mtl_system_items_kfv table
Means, exists in the 81 only in the mtl_system_items_kfv table
81 & 82 are the Org_id's
I want check thei AID101.JCP.5495.BE(Item from regal_inv_landed_cost_tab) not exists in the 82 in the mtl_system_items_kfv table
[Updated on: Tue, 25 February 2014 03:52] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| How to validates the records using Loop [message #608853 is a reply to message #608553] |
Tue, 25 February 2014 08:56 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi all,
I selected file-number and clicked on the QUERY Button and it displayed records(Multiple Records means 10 records)at the Database Block(RHCLANDEDCOSTDET) , in the Database block i have selected_flag,file_number,item_number . I check the Selected_flag and click on the MOVE Button...
In the below code , i don't want to add " AND item_number = :RHCLANDEDCOSTDET.item_number" this line in the code , but how can i validate records and in the table selected_flag='N', how can i validate and process this Alert..
If i pass the file_number it shows me the count, if count=0 then show me the Alert..
But i don't want to add item_number....Please Help
/****Checking for Item Number is Active or Not*******/
BEGIN
Go_block('RHCLANDEDCOSTDET');
v_itemnumber:=NULL;
first_record;
LOOP
IF :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
SELECT Count(1)
INTO v_count2
FROM regal_inv_landed_cost_tab a1
WHERE organization_id = 81
AND inventory_item_id IN (SELECT inventory_item_id
FROM mtl_system_items_kfv a2
WHERE a1.item_number = a2.concatenated_segments
AND a2.organization_id = 82
AND Nvl(a2.inventory_item_status_code, 'Active' ) = 'Active')
AND file_number = :RHCLANDEDCOSTDET.file_number
AND item_number = :RHCLANDEDCOSTDET.item_number;
IF v_count2 = 0 THEN v_incrval := 1;
v_itemnumber := v_itemnumber ||:RHCLANDEDCOSTDET.item_number ||',';
END IF;
END IF;
next_record;
EXIT WHEN :SYSTEM.last_record = 'TRUE';
END LOOP;
first_record;
IF v_incrval = 1 THEN
fnd_message.Set_string('This' ||Rtrim(v_itemnumber) ||' is in the Inactive Mode Please Active '); --testing
fnd_message.show;
END IF;
end;
|
|
|
|
|
|
| Messages [message #608905 is a reply to message #608553] |
Wed, 26 February 2014 09:23 |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi all,
I have a custom table & i want to check the records of custom table in the another table.Means i have only 81 Org_id data and in the Standard table i am having 81&82 , means both the means custom tale data also.
And i have a selected_flag in the database block this column is a Database Column and in the table it is in the "N"
(By Default)
But below code is shows the current positioned record only (in the message).How can i show the records where i selected , suppose i selected 3 records like 1,2,3...i want to show in the Messages like "1,2,3 are not Active or something..."
I am getting only one that is current positioned record message.But in my table Selected_flag='N'.
Declare
v_count1 Number;
v_file_number varchar2(60):=:RHCLANDEDCOSTHEADER.file_number;
v_item_list varchar2(4000);
alert_button NUMBER;
v_alert NUMBER;
v_message VARCHAR2(1000);
v_count NUMBER := 0;
cursor c_get_notassigned(c_file_number in varchar2) is
SELECT a1.item_number
FROM regal.regal_inv_landed_cost_tab a1
WHERE organization_id = 81
AND exists(SELECT a2.inventory_item_id
FROM mtl_system_items_kfv a2
where a1.inventory_item_id =a2.inventory_item_id and a2.organization_id = 82)
and item_number=:RHCLANDEDCOSTDET.item_number
AND file_number = c_file_number;
Begin
SELECT Count(1)
INTO v_count1
FROM regal.regal_inv_landed_cost_tab a1
--WHERE organization_id = 81
where exists(SELECT a2.inventory_item_id
FROM mtl_system_items_kfv a2
where a1.inventory_item_id =a2.inventory_item_id and a2.organization_id = 82)
AND file_number =v_file_number;
If v_count1 > 0 then
fnd_message.Set_string('The following Items are not assigned to BRW Org'); --testing
fnd_message.show;
v_item_list := null;
for c_get_notassigned_rec in c_get_notassigned(v_file_number) loop
v_item_list := v_item_list||' , '||c_get_notassigned_rec.item_number;
--v_item_list := v_item_list ||c_get_notassigned_rec.item_number ||',';
End Loop;
fnd_message.Set_string('This' ||Rtrim(v_item_list) ||' is in not assigned to BRW Org');
fnd_message.show;
END if;
end;
I tried with this code, it is working fine, but i don't want to take -- AND item_number = :RHCLANDEDCOSTDET.item_number; Line & Display the items as in the one line , "1,2,3 are inactive" in the message, if i remove item_number line then it is not worked for me and in the table, selected_flag=N, then how to validate the records if i am not use both the item_number and selected_flag, if i use selected_flag then how can i use?, i don't want to update this column,it updated as Y at the last stage...
EGIN
Go_block('RHCLANDEDCOSTDET');
first_record;
LOOP
IF :RHCLANDEDCOSTDET.selected_flag = 'Y' THEN
SELECT Count(1)
INTO v_count
FROM regal.regal_inv_landed_cost_tab a1
WHERE organization_id = 81
AND inventory_item_id IN (SELECT inventory_item_id
FROM mtl_system_items_kfv a2
where a2.organization_id = 82)
AND file_number = :RHCLANDEDCOSTDET.file_number
-- AND item_number = :RHCLANDEDCOSTDET.item_number;
IF v_count = 0 THEN v_incrval := 1;
v_itemnumber := v_itemnumber ||:RHCLANDEDCOSTDET.item_number ||',';
END IF;
END IF;
next_record;
EXIT WHEN :SYSTEM.last_record = 'TRUE';
END LOOP;
first_record;
IF v_incrval = 1 THEN
fnd_message.Set_string('This' ||Rtrim(v_itemnumber) ||' Number is not assigned into the BRW'); --testing
fnd_message.show;
END IF;
END;
Thank You
[Updated on: Wed, 26 February 2014 09:29] Report message to a moderator |
|
|
|
| Re: Messages [message #608907 is a reply to message #608905] |
Wed, 26 February 2014 09:29 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
What does this bit of the cursor where clause do:
and item_number=:RHCLANDEDCOSTDET.item_number
|
|
|
|
|
|
| Re: Messages [message #608909 is a reply to message #608908] |
Wed, 26 February 2014 09:38 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
As per usual:
We don't know anything about your tables or how they relate to each other or how they relate to the records in the form.
We can't know this unless you tell us.
So we're not really in a position to tell you what the code should be unless you supply more information.
However, what was wrong with your original approach of looping through the records in the datablock?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
