My Table
======
CREATE TABLE PR_DAILY_PRODUCTION
( ID NUMBER,
PRODUCTION_DATE DATE,
ITEM_CD VARCHAR2(20),
QTY NUMBER,
ENTRY_DATE DATE DEFAULT SYSDATE,
ENTRY_BY VARCHAR2(30),
UPDATE_DATE DATE,
UPDATE_BY VARCHAR2(30),
CONSTRAINT PR_DAILY_PRODUCTION_PK PRIMARY KEY ( ID ) ENABLE
) ;
========================
Step:1
=====
Create a Region "Date Setup"
Two Item Create
- P26_PRODUCTION_DATE
- P26_PREVIOUS_PRODUCTION_UPDATE
Step:2
=====
=====
For insert:
Create a Region "Daily Production Qty."
Type: Classic Report
Type: Classic Report
SQL Query:
SELECT ROWNUM SL,Item_Code, ITEM_CD, ITEM_NM, Quantity
FROM
(
SELECT
APEX_ITEM.TEXT(2, A.ITEM_CD, p_attributes=> 'class="varchar2"
readonly style="text-align: center; max-width:
200px; min-width: 50px; pointer-events: none; user-select: none;
border:none; font-weight: bold;"') as Item_Code,
A.ITEM_CD,
A.ITEM_NM,
APEX_ITEM.text( p_idx=> 1, p_value=>'' , p_attributes=> 'class="number_fieldonly balqty" style="text-align: right; max-width: 100px;"') as Quantity
FROM ITEM A
WHERE
ITM_SUBGRCD = 'F101'
AND STATUS = 'Y'
ORDER BY ITEM_SEQ)
Step:3
=====
=====
For Update:
Create a Region "Update Daily Production Qty."
Type: Classic Report
Type: Classic Report
SQL Query:
SELECT ROWNUM SL,PRODUCTION_DATE, Item_Code,ITEM_CD, ITEM_NM, Quantity, QTY
FROM
(
SELECT
b.PRODUCTION_DATE,
APEX_ITEM.TEXT(11, A.ITEM_CD, p_attributes=> 'class="varchar2"
readonly style="text-align: center; max-width:
200px; min-width: 50px; pointer-events: none; user-select: none;
border:none; font-weight: bold;"') as Item_Code,
A.ITEM_CD,
A.ITEM_NM,
apex_item.hidden(13,B.ID)||APEX_ITEM.text(12,B.QTY , p_attributes=> 'class="number_fieldonly balqty" style="text-align: right; max-width: 100px;"') as Quantity,
B.QTY
FROM ITEM A , PR_DAILY_PRODUCTION B
WHERE A.ITEM_CD=B.ITEM_CD
AND ITM_SUBGRCD = 'F101'
AND STATUS = 'Y'
AND LTRIM(RTRIM(TO_CHAR(PRODUCTION_DATE,'MONTH')))||'-'||LTRIM(RTRIM(TO_CHAR(PRODUCTION_DATE,'YYYY')))= :P26_PREVIOUS_PRODUCTION_UPDATE
ORDER BY ITEM_SEQ
)
ORDER BY ROWNUM
--> Page Items to Submit --> :P26_PREVIOUS_PRODUCTION_UPDATE
Step:4
=====
=====
Show-Hide Dynamic action on --> :P26_PREVIOUS_PRODUCTION_UPDATE
Step:5
=====
=====
Processes:
For Save:
declare
PK_ID number;
begin
FOR i IN 1 .. apex_application.g_f01.COUNT LOOP
select nvl(max(ID),0)+1 into PK_ID from PR_DAILY_PRODUCTION;
IF apex_application.g_f01(i) > 0 and :P26_PRODUCTION_DATE IS NOT NULL THEN
INSERT INTO PR_DAILY_PRODUCTION ( ID, PRODUCTION_DATE, ITEM_CD, QTY, ENTRY_DATE, ENTRY_BY)
VALUES ( PK_ID, :P26_PRODUCTION_DATE, apex_application.g_f02(i),apex_application.g_f01(i),sysdate , :APP_USER);
elsif
:P26_PRODUCTION_DATE IS NULL THEN
raise_application_error(-20010, 'Please Production date set');
end if;
COMMIT;
end loop;
END;
For Update:
declare
v_date date;
begin
FOR i IN 1 .. apex_application.g_f12.COUNT LOOP
select PRODUCTION_DATE
into v_date
from PR_DAILY_PRODUCTION
where ID = apex_application.g_f13(i);
if v_date = (TRUNC(sysdate)-1) then
update PR_DAILY_PRODUCTION set
QTY = apex_application.g_f12(i),
UPDATE_DATE = SYSDATE,
UPDATE_BY = :APP_USER
where ID = apex_application.g_f13(i);
else
raise_application_error(-20010, 'Do not update Production Qty. Becouse Production Date is Over. ');
END IF;
end loop;
commit;
end;
========================================
Note : Any Apex Hidden item can not insert/update this value. so you should not Hidden. You can do Hidden by HTML use in Inline CSS.
Example :
[headers='ID'],#ID{ display:none;}
==========================================
For Select List.
APEX_ITEM.SELECT_LIST_FROM_QUERY(5,1,'SELECT MUNIT_NM A, MUNIT_CD B FROM MUNIT WHERE MUNIT_CD IN (1,3)') as UNIT ,
Tags:
Oracle Apex (SAK)