Home » SQL & PL/SQL » SQL & PL/SQL » Script to categorize the DJ aging (Apps: EBS 11.5.10.2, DB: 9.2.0.6)
icon5.gif  Script to categorize the DJ aging [message #346630] Tue, 09 September 2008 03:28 Go to next message
jasmine.hue
Messages: 17
Registered: August 2008
Location: Penang, Malaysia
Junior Member
Hi experts,

I had some asistant at my SQL scripts, I am calculating the DJ againg. However, I had different aging acceptance level different inventory org. For example:

Org A: the DJ aging acceptance level is 10days
Org B: the DJ aging acceptance level is 21days
Org C: the DJ aging acceptance level is 15days
Other orgs: the DJ aging acceptance level is 5days

Below is my scripts:
select
wdj.ORGANIZATION_ID AS "ORG",
WE.WIP_ENTITY_NAME "JOB",
(ASSY.SEGMENT1||'-'||ASSY.SEGMENT2||'-'||ASSY.SEGMENT3) AS "ASSEMBLY",
ASSY.DESCRIPTION AS "ASSEMBLY DESC",
paa.NAME||'.'||pat.TASK_NUMBER as "PROJECT",
ML1.MEANING STATUS,
WDJ.DATE_RELEASED,
WE.CREATION_DATE,
ROUND(SYSDATE - WE.CREATION_DATE) AS AGING,
ROUND(SYSDATE - WE.CREATION_DATE) AS AGING2,
WDJ.ATTRIBUTE1 AS "REMARKS",
WDJ.ATTRIBUTE2 AS "SO NUM",
WDJ.ATTRIBUTE3 AS "S0 LN"
from
WIP_ENTITIES WE,
MFG_LOOKUPS ML1,
MTL_SYSTEM_ITEMS ASSY,
WIP_DISCRETE_JOBS WDJ,
PA_PROJECTS_ALL PAA,
PA_TASKS PAT
Where ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
and we.ORGANIZATION_ID = wdj.ORGANIZATION_ID
and we.WIP_ENTITY_ID = wdj.WIP_ENTITY_ID
AND ML1.LOOKUP_CODE = WDJ.STATUS_TYPE
AND WE.PRIMARY_ITEM_ID = ASSY.INVENTORY_ITEM_ID
AND WE.ORGANIZATION_ID = ASSY.ORGANIZATION_ID
and wdj.PROJECT_ID = paa.PROJECT_ID
and wdj.TASK_ID = pat.TASK_ID
and wdj.ORGANIZATION_ID not in (124,125,126,127,101,128,106,105,135,109,108,110,102,103,111,112)
and WDJ.STATUS_TYPE not in (4,5,7,12,14,15)
group by
WE.WIP_ENTITY_NAME,
(ASSY.SEGMENT1||'-'||ASSY.SEGMENT2||'-'||ASSY.SEGMENT3),
ASSY.DESCRIPTION,
ML1.MEANING,
WDJ.DATE_RELEASED,
WE.CREATION_DATE,
ROUND(SYSDATE - WE.CREATION_DATE),
wdj.ORGANIZATION_ID,
WDJ.ATTRIBUTE1,
WDJ.ATTRIBUTE2,
WDJ.ATTRIBUTE3,
paa.NAME,
pat.TASK_NUMBER
order by wdj.ORGANIZATION_ID asc, (ASSY.SEGMENT1||'-'||ASSY.SEGMENT2||'-'||ASSY.SEGMENT3) asc;/*--============ table fields listing===============
--
--1= unreleased
--3= released
--4= complete
--5= complete- No Charges
--6= On hold
--7= cancelled
--8= pending Bill Load
--9= failed bill load
--10=Pending routing load
--11=Failed routing load
--12=Closed
--13=Pending-Mass Loaded
--14=Pending Close
--15=Failed Close
--16=Pending Scheduling
--================================================================


Thanks,
Jasmine

[mod-edit] removed bold, italic and color to help readability.

[Updated on: Wed, 10 September 2008 07:57] by Moderator

Report message to a moderator

Re: Script to categorize the DJ aging [message #346632 is a reply to message #346630] Tue, 09 September 2008 03:31 Go to previous messageGo to next message
jasmine.hue
Messages: 17
Registered: August 2008
Location: Penang, Malaysia
Junior Member
Embarassed

Hi my report is publishing at Oracle Discoverer


Thanks,
Jasmine

Report message to a moderator

Re: Script to categorize the DJ aging [message #346650 is a reply to message #346630] Tue, 09 September 2008 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Post a test case: create table and insert statements along with the result you want with these data.

Sorry but I can't read red lines.

Regards
Michel

[Updated on: Tue, 09 September 2008 04:26]

Report message to a moderator

icon6.gif  Re: Script to categorize the DJ aging [message #346846 is a reply to message #346650] Tue, 09 September 2008 19:28 Go to previous messageGo to next message
jasmine.hue
Messages: 17
Registered: August 2008
Location: Penang, Malaysia
Junior Member
hi Michel,

Sorry, as this is my first time to log an request here.

I had attached a file of the coding.

Regards,
Jasmine Embarassed
  • Attachment: DJ.sql
    (Size: 1.86KB, Downloaded 925 times)

Report message to a moderator

Re: Script to categorize the DJ aging [message #346860 is a reply to message #346846] Tue, 09 September 2008 22:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It is still not formatted (see formatter above).

I don't understand what you want. I don't understand the point of your "table fields listing". I don't understand what is DJ aging. I don't understand what is "aging acceptance level".
Simplify your problem, post a test case, and the result you want with it.

Regards
Michel

Report message to a moderator

Re: Script to categorize the DJ aging [message #346862 is a reply to message #346860] Tue, 09 September 2008 23:22 Go to previous messageGo to next message
jasmine.hue
Messages: 17
Registered: August 2008
Location: Penang, Malaysia
Junior Member
Hi Michel,

DJ aging means the number of days that the DJ being active after is has been released. The objective of the DJ aging is to control the DJ closed on time.
What requestor need from this aging report is calculate number of days the dj still active for the report generated date.

Scenario:
DJ from inv org A was created and released to production on 1st-Sept-08. Until today, if the DJ have not been closed, so the aging of this DJ is 9 days.

We have more than 1 inventory org, every inventory org having diff ecceptance level. Certain inv org, the DJ cannot aged for more than 10days. SO this report is to show management the DJ status.

I had calculate the DJ aging by using formula (sysdate-DJ created date). But I don't know how to put the status of "Delayed", "Acceptable" in my report. For example:

Org A: the DJ aging acceptance level is 10days
if aging > 10
then "Delayed"
else "Acceptable"
Org B: the DJ aging acceptance level is 21days
if aging > 21
then "Delayed"
else "Acceptable"
Org C: the DJ aging acceptance level is 15days
if aging > 15
then "Delayed"
else "Acceptable"
Other orgs: the DJ aging acceptance level is 5days
if aging > 5
then "Delayed"
else "Acceptable"
Therefore for my DJ created above, the status is "Acceptable"

Hope this explain well my requirements.
Besides email, you can contact me through:
Yahoo: lyhue802002
Skype: jasminehue

Report message to a moderator

Re: Script to categorize the DJ aging [message #346868 is a reply to message #346862] Tue, 09 September 2008 23:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
the DJ being active after is has been released

Which columns can give you this information?
...
Many things are obvious for you that knows the model and meaning of the columns and tables you posted are not for us.

Anyway, it seems that CASE will do the trick.

Regards
Michel

Report message to a moderator

Re: Script to categorize the DJ aging [message #346872 is a reply to message #346868] Tue, 09 September 2008 23:47 Go to previous messageGo to next message
jasmine.hue
Messages: 17
Registered: August 2008
Location: Penang, Malaysia
Junior Member
Hi Michel

Thanks for your prompt reply.

If you refer to my codes, the field: ML1.MEANING STATUS will tell that the DJ is still active.

Thanks,
Jasmine

Report message to a moderator

Re: Script to categorize the DJ aging [message #346892 is a reply to message #346872] Wed, 10 September 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
As I said, I think CASE will do the trick, just express what you said in your post in terms of CASE expression.

Regards
Michel

Report message to a moderator

Re: Script to categorize the DJ aging [message #346909 is a reply to message #346868] Wed, 10 September 2008 01:22 Go to previous messageGo to next message
jasmine.hue
Messages: 17
Registered: August 2008
Location: Penang, Malaysia
Junior Member
Hi Michel,

I am not familiar the CASE syntax, can you provide some sample codes?

Thanks,
Jasmine Smile

Report message to a moderator

Re: Script to categorize the DJ aging [message #346911 is a reply to message #346892] Wed, 10 September 2008 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What about reading the link I posted? There are examples.
CASE WHEN THEN ELSE is just like IF THEN ELSE

Regards
Michel

Report message to a moderator

icon14.gif  Re: Script to categorize the DJ aging [message #346916 is a reply to message #346911] Wed, 10 September 2008 01:39 Go to previous messageGo to next message
jasmine.hue
Messages: 17
Registered: August 2008
Location: Penang, Malaysia
Junior Member
Hi Michel,

I am able to view the link.

Thanks.

Jasmine

Report message to a moderator

icon10.gif  Re: Script to categorize the DJ aging [message #346959 is a reply to message #346916] Wed, 10 September 2008 04:11 Go to previous messageGo to next message
jasmine.hue
Messages: 17
Registered: August 2008
Location: Penang, Malaysia
Junior Member
Laughing Thanks Michel,

My codes is done!!!!!! Laughing


Really thanks for your help.

Jasmine

Report message to a moderator

Re: Script to categorize the DJ aging [message #346963 is a reply to message #346959] Wed, 10 September 2008 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Nice to know you got it.
Please post it for others (just the part that calculate the field)

Regards
Michel

Report message to a moderator

Re: Script to categorize the DJ aging [message #347026 is a reply to message #346963] Wed, 10 September 2008 08:12 Go to previous messageGo to next message
jasmine.hue
Messages: 17
Registered: August 2008
Location: Penang, Malaysia
Junior Member
Hi Michel,

Below is the code I write:

Case wdj.ORGANIZATION_ID 
   when 134 then (case when (ROUND(SYSDATE - WE.CREATION_DATE)) > 56 then 'Delayed' else 'Acceptable' end)   
   when 133 then (case when (ROUND(SYSDATE - WE.CREATION_DATE)) > 21 then 'Delayed' else 'Acceptable' end)
   when 129 then (case when (ROUND(SYSDATE - WE.CREATION_DATE)) > 70 then 'Delayed' else 'Acceptable' end)
   when 136 then (case when (ROUND(SYSDATE - WE.CREATION_DATE)) > 56 then 'Delayed' else 'Acceptable' end)
else 'No Control'
end


Jasmine Razz

Report message to a moderator

Re: Script to categorize the DJ aging [message #347033 is a reply to message #347026] Wed, 10 September 2008 08:29 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks. Smile
Michel

Report message to a moderator

Previous Topic: Deleting large number of rows Quickly in Oracle Db
Next Topic: SQL question
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Dec 06 15:07:10 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.