Module/Schemia Name : NPL
Module Activity: Bill of Materials (BOM) as the Product Recipe: Structuring Components for Production Efficiency.
Base Tables
- PCAT
- PRODUCT
- RAW_MAT
Transaction Tables
- BOM_MODIFIED_MST (Master Table)
- BOM_MODIFIED (Detail Table);
- HBOM_MODIFIED_MST
- HBOM_MODIFIED
Product Lovs: SELECT A.P_DESC,A.P_CODE,A.PACK_SIZE,A.PACK_SIZE1||' X '||A.PACK_SIZE2 PACK,A.PTYPE_DESC, A.COM_CODE,A.PCAT_CODE FROM PRODUCT_ALL_VW A, (SELECT P_CODE FROM PRODUCT_ALL_VW MINUS SELECT P_CODE FROM BOM_MODIFIED_MST) B WHERE A.P_CODE = B.P_CODE AND PCAT_CODE NOT IN ('STK','TRF','LON') AND NVL(PROD_STAT,'N') IN ('C','I') AND PCAT_CODE=:PCAT.PCAT_CODE ORDER BY A.P_DESC;
Raw Material Lovs: SELECT RAW_CODE, RAW_DESC, RAW_GRADE, RAW_TYPE FROM RAW_MAT WHERE RAW_DESC NOT LIKE '%**%' AND NVL(RAW_STAT,'N')='C' ORDER BY RAW_DESC;
Employee Lovs: SELECT A.EMP_OFFICE_NAME,A.EMP_CODE,B.DEPT_DESC FROM PMIS_EMP A,PMIS.PMIS_DEPARTMENT B WHERE A.DEPT_CODE = B.DEPT_CODE AND NVL(A.DEPT_CODE,'X') IN ('055','080','097','087','093','089','016','002','061','057') AND NVL(A.JOB_CATEGORY,'X')<>'5' ORDER BY 1;
Insert & Update Script
INSERT INTO HBOM_MODIFIED_MST (PCAT_CODE, P_CODE, BATCH_SIZE, BATCH_UNIT, BOM_RATIO, ANNEX_RATIO, BATCH_QNTY, BATCH_QNTY_UNIT, PACK1, PACK2, PACK3, PER_UNIT_WT, PER_UNIT_WT_UNIT, STD_AVG_WT, BMR_NO, VERSION_NO, EFF_DT, PROD_LD_TIME, PROD_DOSAGE_FORM, BOM_CODE, LABEL_RATIO, PER_UNIT_SOLID_UNIT, DML_VALID_UPTO, GRANUL_METHOD, MAX_BT_PER_DAY, MON_SFTY_STOCK, BPR_VERSION_NO, BPR_EFF_DT, BCR_VERSION_NO, BCR_EFF_DT, OPL_P_CODE, BATCH_PER_LARGE_UNIT, BATCH_PER_LARGE_QNTY, NOTE, USER_ID, ENTER_DT, INITIATOR, APRV_BY, APRV_DT, EDIT_BY, EDIT_DT, AUTH_BY, AUTH_DT, SL_NO, HIST_BY, HIST_DT) SELECT PCAT_CODE, P_CODE, BATCH_SIZE, BATCH_UNIT, BOM_RATIO, ANNEX_RATIO, BATCH_QNTY, BATCH_QNTY_UNIT, PACK1, PACK2, PACK3, PER_UNIT_WT, PER_UNIT_WT_UNIT, STD_AVG_WT, BMR_NO, VERSION_NO, EFF_DT, PROD_LD_TIME, PROD_DOSAGE_FORM, BOM_CODE, LABEL_RATIO, PER_UNIT_SOLID_UNIT, DML_VALID_UPTO, GRANUL_METHOD, MAX_BT_PER_DAY, MON_SFTY_STOCK, BPR_VERSION_NO, BPR_EFF_DT, BCR_VERSION_NO, BCR_EFF_DT, OPL_P_CODE, BATCH_PER_LARGE_UNIT, BATCH_PER_LARGE_QNTY, NOTE, USER_ID, ENTER_DT, INITIATOR, APRV_BY, APRV_DT, EDIT_BY, EDIT_DT, AUTH_BY, AUTH_DT, X SL_NO, :GLOBAL.USERID HIST_BY, SYSDATE HIST_DT FROM G_BOM_MODIFIED_MST WHERE P_CODE=:BOM_MODIFIED_MST.P_CODE;
INSERT INTO HBOM_MODIFIED (PCAT_CODE, P_CODE, RAW_CODE, RAW_TYPE, DECLARED_QNTY, DECLARED_UNIT, RATIO_TYPE, RATIO_RA, UNIT_UNIT, UNIT_QNTY, QTY_PER_BATCH, OVERAGE, QTY_PER_BATCH_ISSUE, BATCH_SIZE, BATCH_UNIT, ANNEX_CONT, DEC_VOLM_PER_UNIT, EACH_UNIT_QNTY, QTY_PER_BATCH_UNIT, QTY_PER_BATCH_CONT, QTY_PER_BATCH_UNIT_CONT, FILLER_FLAG, ANNEX, NOT_APPER_FLAG, BATCH_SIZE_QNTY, BATCH_SIZE_QNTY_UNIT, QS_TO_MAKE, EACH_UNIT_RATIO, RAW_GROUP, RAW_CAT, POTENCY_RATIO, DA_GEN_CODE, EACH_UNIT_QNTY_ANNEX, EACH_UNIT_QNTY_UNIT_ANNEX, SEQ, STAT, PROD_QTY, PROD_QTY_UNIT, VERSION_NO, RAW_QNTY, RAW_UNIT, RAW_MOLE_WT, GEN_MOLE_WT, RAW_BY_GEN, REF_BOOK, REF_BOOK_VER, REF_BOOK_PAGE, IS_ANNEX_RAW, OPL_P_CODE, OPL_RAW_CODE, RAW_VERSION_NO, USER_ID, ENTER_DT, SL_NO, HIST_BY, HIST_DT) SELECT PCAT_CODE, P_CODE, RAW_CODE, RAW_TYPE, DECLARED_QNTY, DECLARED_UNIT, RATIO_TYPE, RATIO_RA, UNIT_UNIT, UNIT_QNTY, QTY_PER_BATCH, OVERAGE, QTY_PER_BATCH_ISSUE, BATCH_SIZE, BATCH_UNIT, ANNEX_CONT, DEC_VOLM_PER_UNIT, EACH_UNIT_QNTY, QTY_PER_BATCH_UNIT, QTY_PER_BATCH_CONT, QTY_PER_BATCH_UNIT_CONT, FILLER_FLAG, ANNEX, NOT_APPER_FLAG, BATCH_SIZE_QNTY, BATCH_SIZE_QNTY_UNIT, QS_TO_MAKE, EACH_UNIT_RATIO, RAW_GROUP, RAW_CAT, POTENCY_RATIO, DA_GEN_CODE, EACH_UNIT_QNTY_ANNEX, EACH_UNIT_QNTY_UNIT_ANNEX, SEQ, STAT, PROD_QTY, PROD_QTY_UNIT, VERSION_NO, RAW_QNTY, RAW_UNIT, RAW_MOLE_WT, GEN_MOLE_WT, RAW_BY_GEN, REF_BOOK, REF_BOOK_VER, REF_BOOK_PAGE, IS_ANNEX_RAW, OPL_P_CODE, OPL_RAW_CODE, RAW_VERSION_NO, USER_ID, ENTER_DT, X SL_NO, :GLOBAL.USERID HIST_BY, SYSDATE HIST_DT FROM G_BOM_MODIFIED WHERE P_CODE=:BOM_MODIFIED_MST.P_CODE AND PCAT_CODE=:BOM_MODIFIED_MST.PCAT_CODE;
UPDATE BOM_MODIFIED_MST SET USER_ID=:GLOBAL.USERID,ENTER_DT=SYSDATE,APRV_DT=SYSDATE WHERE P_CODE=:BOM_MODIFIED_MST.P_CODE; UPDATE BOM_MODIFIED SET USER_ID=:GLOBAL.USERID,ENTER_DT=SYSDATE WHERE P_CODE=:BOM_MODIFIED_MST.P_CODE;
UPDATE BOM_MODIFIED_MST SET VERSION_NO=:BOM_MODIFIED_MST.NEW_VERSION_NO, INITIATOR=:BOM_MODIFIED_MST.NEW_INITIATOR,NOTE=:BOM_MODIFIED_MST.NEW_NOTE, USER_ID=:GLOBAL.USERID,ENTER_DT=SYSDATE,APRV_BY=NULL,APRV_DT=NULL WHERE P_CODE=:BOM_MODIFIED_MST.P_CODE; UPDATE BOM_MODIFIED SET VERSION_NO=:BOM_MODIFIED_MST.NEW_VERSION_NO, USER_ID=:GLOBAL.USERID,ENTER_DT=SYSDATE WHERE P_CODE=:BOM_MODIFIED_MST.P_CODE;