Step 1 :
CREATE TABLE ORDER_INFO
(
ID NUMBER,
ORDER_NO VARCHAR2 (25),
ORDER_STATUS NUMBER,
ORDER_DATE DATE,
CUSTOMER VARCHAR2 (25),
C_PHONE VARCHAR2 (25),
CONFIRM_STATUS NUMBER,
CONFIRM_DATE DATE,
CONFIRM_BY NUMBER,
DELEVERY_STATUS NUMBER,
DELEVERY_AGENT VARCHAR2 (25),
D_PHONE VARCHAR2 (25),
PUR_RCV_STATUS NUMBER,
PUR_RCV_DATE DATE,
PUR_RCV_AMOUNT NUMBER,
CREATE_BY NUMBER,
CREATE_DATE DATE,
CONSTRAINT PK_ORDER_INFO PRIMARY KEY (ID)
);
Create from Process :
SELECT NVL(MAX(ID),100)+1 INTO :P39_ID FROM ORDER_INFO;
:P39_ORDER_STATUS := 1;
:P39_CREATE_BY := :guser_id;
:P39_CREATE_DATE := sysdate;
When Button Pressed : Create
Step 2 :
Create Model Dialog For Traking Update (as Like my from "Order Confirm")
Create from Process :
update ORDER_INFO set
CONFIRM_STATUS = 1,
CONFIRM_BY = :guser_id,
CONFIRM_DATE = sysdate,
where ID = :P40_ID;
When Button Pressed : Save / Apply Change
Step 3 :
Create Interactive Report for Tracking Status View
SQL Query :
select ID,
ORDER_NO,
ORDER_STATUS,
ORDER_DATE,
CUSTOMER,
C_PHONE,
CONFIRM_STATUS,
CONFIRM_DATE,
CONFIRM_BY,
DELEVERY_STATUS,
DELEVERY_AGENT,
D_PHONE,
PUR_RCV_STATUS,
PUR_RCV_DATE,
PUR_RCV_AMOUNT,
CREATE_BY,
CREATE_DATE,
'<div style="height:150px; width:125px; overflow: auto;" >
<p>'
||'<br> Order No : '|| ORDER_NO
||'<br> Order Date : ' ||ORDER_DATE
||'<br> Customer : ' ||CUSTOMER
||'<br> Phone : ' ||C_PHONE
||'</p> </div>' ||
' <div style="margin-bottom: 10px; text-align: center" >
<a href="'||apex_util.prepare_url('f?p='||:APP_ID||':'||000||':'||:APP_SESSION||'::::P000_ID:'||id) || '"
style="color:white;
background: '||case when ORDER_STATUS is not null then 'Gray' else 'Green' end||';
margin:15px;
border:2px solid;
border-radius: 25px;
padding-top: 5px;
padding-right: 15px;
padding-bottom: 5px;
padding-left: 15px;
'||case when ORDER_STATUS is not null then 'pointer-events: none' else null end ||'
">'||case when ORDER_STATUS is not null then 'Customer Ordered' else 'Pending' end ||'</a>
<a href="'||apex_util.prepare_url('f?p='||:APP_ID||':'||00||':'||:APP_SESSION) || '">
</div>'
as Order_info ,
'<div style="height:150px; width:125px; overflow: auto;" >
<p>'
||'<br> CONFIRM DATE : '|| CONFIRM_DATE
||'<br> CONFIRM BY : ' || CONFIRM_BY
||'</p> </div>' ||
' <div style="margin-bottom: 10px; text-align: center" >
<a href="'||apex_util.prepare_url('f?p='||:APP_ID||':'||40||':'||:APP_SESSION||'::::P40_ID:'||id) || '"
style="color:white;
background: '||case when CONFIRM_STATUS is not null then 'Gray' else 'Green' end||';
margin:15px;
border:2px solid;
border-radius: 25px;
padding-top: 5px;
padding-right: 15px;
padding-bottom: 5px;
padding-left: 15px;
'||case when CONFIRM_STATUS is not null then 'pointer-events: none' else null end ||'
">'||case when CONFIRM_STATUS is not null then 'Confirmed' else 'Pending' end ||'</a>
<a href="'||apex_util.prepare_url('f?p='||:APP_ID||':'||40||':'||:APP_SESSION) || '">
</div>'
as Order_Confirm,
'<div style="height:150px; width:125px; overflow: auto;" >
<p>'
||'<br> DELEVERY AGENT : '|| DELEVERY_AGENT
||'<br> PHONE : ' ||D_PHONE
||'</p> </div>' ||
' <div style="margin-bottom: 10px; text-align: center" >
<a href="'||apex_util.prepare_url('f?p='||:APP_ID||':'||40||':'||:APP_SESSION||'::::P40_ID:'||id) || '"
style="color:white;
background: '||case when DELEVERY_STATUS is not null then 'Gray' else 'Green' end||';
margin:15px;
border:2px solid;
border-radius: 25px;
padding-top: 5px;
padding-right: 15px;
padding-bottom: 5px;
padding-left: 15px;
'||case when DELEVERY_STATUS is not null then 'pointer-events: none' else null end ||'
">'||case when DELEVERY_STATUS is not null then 'Delevered' else 'Pending' end ||'</a>
<a href="'||apex_util.prepare_url('f?p='||:APP_ID||':'||40||':'||:APP_SESSION) || '">
</div>'
as DELEVERY_INFO,
'<div style="height:150px; width:125px; overflow: auto;" >
<p>'
||'<br> PURCHASE RCV : '|| DECODE (PUR_RCV_STATUS,1,'YES','NO')
||'<br> RCV DATE : '|| PUR_RCV_DATE
||'<br> AMOUNT : ' ||PUR_RCV_AMOUNT
||'</p> </div>' ||
' <div style="margin-bottom: 10px; text-align: center" >
<a href="'||apex_util.prepare_url('f?p='||:APP_ID||':'||40||':'||:APP_SESSION||'::::P40_ID:'||id) || '"
style="color:white;
background: '||case when PUR_RCV_STATUS is not null then 'Gray' else 'Green' end||';
margin:15px;
border:2px solid;
border-radius: 25px;
padding-top: 5px;
padding-right: 15px;
padding-bottom: 5px;
padding-left: 15px;
'||case when PUR_RCV_STATUS is not null then 'pointer-events: none' else null end ||'
">'||case when PUR_RCV_STATUS is not null then 'Delevered' else 'Pending' end ||'</a>
<a href="'||apex_util.prepare_url('f?p='||:APP_ID||':'||40||':'||:APP_SESSION) || '">
</div>'
as PURCHASE_RCV
From ORDER_INFO
--> Escape special characters : OFF
===============================================
'<div style="height:80px; width:150px; overflow: auto;" >
<p style="background-color:lightGray;">
'||SUBSTR (get_party_name_add('N',BUYER_ID),1,20) ||'
</p>
<p>
'|| UPPER(SUBSTR (get_airport_DTL ('A',AIRPORT_DESTI),1,20)) ||'
</p>
<p style="background-color:lightGray;">
'||TO_CHAR(EDD_DATE,'DD-MON-YYYY HH:MI:SS AM') ||' <br>
</p>
</div>' ||
'<div style="margin-bottom: 10px; text-align: center" >
<a href="'||apex_util.prepare_url('f?p='||:APP_ID||':'||7||':'||:APP_SESSION) || '"
style="color:white;
background:'||case when REGISTERED is not null and BOOKED is null then 'Gray' else 'Green' end||';
margin:3px;
border:2px solid;
border-radius: 25px;
padding-top: 5px;
padding-right: 15px;
padding-bottom: 5px;
padding-left: 15px;
'||case when REGISTERED is null or BOOKED = 1 then 'pointer-events: none' else null end ||'
">'||case when BOOKED is null then 'Book' else 'Booked' end ||'</a> </div>'
as Booked_status,
Page Inline :-
p {
margin: 0 0 0;
}
-----------------------------------------------------------------------------------
Edit Lock-Unlock :
select ID,
TYPE,
NAME,
decode(STATUS,1,'Active',0, 'Deactive')STATUS,
get_company_dtl('N',COM_ID) COMPANY,
CREATE_BY,
CREATE_DATE,
UPDATE_BY,
UPDATE_DATE,
CODE,
case when COM_ID is not null then
'<div style="margin-bottom: 10px; text-align: center" >
<a href="'||apex_util.prepare_url('f?p='||:APP_ID||':'||10||':'||:APP_SESSION||'::::P10_ID:'||id) || '"
style="color:green;
background:white;
margin: 3px;
-- border: 2px solid;
-- border-radius: 15px;
padding-top: 5px;
padding-right: 25px;
padding-bottom: 5px;
padding-left: 15px;
"> <span aria-label="Edit"><span class="fa fa-edit" aria-hidden="true" title="Edit"></span></span> </a> </div>'
else '<span class="fa fa-check" aria-hidden="true"></span>' end Edit
from LOOKUP_DATA
WHERE COM_ID=:GCOM_ID
AND TYPE ='Air Booking Package Type'
OR ID IN (SELECT ID FROM LOOKUP_DATA WHERE TYPE ='Air Booking Package Type' and COM_ID IS NULL)