Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- find part refill, no shortage
- SELECT a.* FROM [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] a
- INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] b ON a.PART_NO = b.PART_NO
- WHERE LEFT(b.RACK_ADDRESS_CD, 1)='M'
- AND a.PART_NO NOT IN (
- SELECT a.PART_NO FROM [SPEX_DB].[spex].[TB_R_REPORT_PROBLEM] a
- INNER JOIN [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] b ON a.[PART_NO] = b.[PART_NO]
- INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] c ON a.[PART_NO] = c.[PART_NO]
- WHERE a.[TYPE_PROBLEM] = 2
- AND LEFT(c.RACK_ADDRESS_CD, 1)='M'
- AND ISNULL(a.REFILL_QTY, 0) < ISNULL(a.QTY, 0)
- )
- -- find part refill, shortage
- SELECT a.* FROM [SPEX_DB].[spex].[TB_R_REPORT_PROBLEM] a
- INNER JOIN [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] b ON a.[PART_NO] = b.[PART_NO]
- INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] c ON a.[PART_NO] = c.[PART_NO]
- WHERE a.[TYPE_PROBLEM] = 2
- AND LEFT(c.RACK_ADDRESS_CD, 1)='Y'
- AND ISNULL(a.REFILL_QTY, 0) < ISNULL(a.QTY, 0)
- -- find part on rack reserve
- SELECT a.* FROM [spex].[TB_R_BINNING] a
- INNER JOIN [SPEX_DB].[spex].[TB_R_PART_STOCK_INFO] b ON a.PART_NO = b.PART_NO
- LEFT JOIN [spex].[TB_R_BUCKET_TRANSFER_H] c ON a.TRANSFER_NO = c.TRANSFER_NO AND a.BT_NO = c.BT_NO
- WHERE
- a.[PART_NO] = '12306BZ210'
- AND (a.[SOURCE] = 'NON-DIRECT')
- SELECT a.*, b.MAX_STOCK FROM spex.TB_R_PART_STOCK_INFO a
- INNER JOIN [SPEX_DB].[spex].[TB_M_PART_STOCK] b ON a.[PART_NO] = b.[PART_NO]
- WHERE a.PART_NO IN ('121010C010', '12101BZ070', '12306BZ210')
- --UPDATE [spex].[TB_M_PART_STOCK] SET MAX_STOCK=500
- --WHERE
- --PART_NO IN ('04005081BZ', '04111BZ025', '04111BZ135', '04112BZ105', '04112BZ127', '04112BZ680', '04112BZ690', '04112BZ700', '044120K220', '04426BZ060')
- ---- Zone M
- ---- Part No, SOH, SOR, SODR, SONDR, MAX_STOCK on [spex].[TB_R_PART_STOCK_INFO]
- ---- 04005081BZ, 222, NULL, NULL, NULL, 1 (Change) => 372, NULL, NULL, 150, 500
- ---- 04111BZ025, 3, NULL, NULL, NULL, 1 (Change) => 53, NULL, NULL, 50, 500
- ---- 04111BZ135, 2, NULL, NULL, NULL, 1 (Change) => 52, NULL, NULL, 50, 500
- ---- 04112BZ105, 4, NULL, NULL, NULL, 1 (Change) => 14, NULL, NULL, 10, 500
- ---- 04112BZ127, 10, NULL, NULL, NULL, 1 (Change) => 20, NULL, NULL, 10, 500
- ---- 04112BZ680, 3, NULL, NULL, NULL, 1 (Change) => 8, NULL, NULL, 5, 500
- ---- 04112BZ690, 3, NULL, NULL, NULL, 1 (Change) => 23, NULL, NULL, 20, 500
- ---- 04112BZ700, 4, NULL, NULL, NULL, 1 (Change) => 104, NULL, NULL, 100, 500
- ---- 044120K220, 5, NULL, NULL, NULL, 1 (Change) => 15, NULL, NULL, 10, 500
- ---- 04426BZ060, 2, NULL, NULL, NULL, 1 (Change) => 102, NULL, NULL, 100, 500
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=150 WHERE PART_NO='04005081BZ'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=50 WHERE PART_NO='04111BZ025'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=50 WHERE PART_NO='04111BZ135'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=10 WHERE PART_NO='04112BZ105'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=10 WHERE PART_NO='04112BZ127'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=5 WHERE PART_NO='04112BZ680'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=20 WHERE PART_NO='04112BZ690'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=100 WHERE PART_NO='04112BZ700'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=10 WHERE PART_NO='044120K220'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=100 WHERE PART_NO='04426BZ060'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='04005081BZ'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='04111BZ025'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='04111BZ135'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='04112BZ105'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='04112BZ127'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='04112BZ680'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='04112BZ690'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='04112BZ700'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='044120K220'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='04426BZ060'
- ---- Part No : 04005081BZ
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-UHAMMAD-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-UHAMMAD-002')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-UHAMMAD-003')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-UHAMMAD-004')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-UHAMMAD-005')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-UHAMMAD-006')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-UHAMMAD-007')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-UHAMMAD-008')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 8, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-ADRID-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 2, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-USSA-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 2, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-ADARA-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 8, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-ADARA-002')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-ADARA-003')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-ADARA-004')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 40, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-ADARA-005')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04005081BZ', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-ADARA-006')
- ---- Part No : 04111BZ025
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04111BZ025', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-U-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04111BZ025', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-U-002')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04111BZ025', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-C-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04111BZ025', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-C-002')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04111BZ025', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-C-003')
- ---- Part No : 04111BZ135
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04111BZ135', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-A-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04111BZ135', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-A-002')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04111BZ135', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-A-003')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04111BZ135', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-A-004')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04111BZ135', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-A-005')
- ---- Part No : 04112BZ105
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04112BZ105', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-BA-001')
- ---- Part No : 04112BZ127
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04112BZ127', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-BA-002')
- ---- Part No : 04112BZ680
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04112BZ680', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-BA-003')
- ---- Part No : 04112BZ690
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04112BZ690', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-BA-004')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04112BZ690', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-BA-005')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04112BZ690', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-BA-006')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04112BZ690', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-BA-007')
- ---- Part No : 04112BZ700
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04112BZ700', 50, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-UKBANG-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04112BZ700', 50, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-UKBANG-002')
- ---- Part No : 044120K220
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '044120K220', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-UKBANG-003')
- ---- Part No : 04426BZ060
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04426BZ060', 100, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'M-ASTERCHEF-001')
- ---- Zone Y
- ---- Part No, SOH, SOR, SODR, SONDR, MAX_STOCK on [spex].[TB_R_PART_STOCK_INFO]
- ---- 121010C010, 4, NULL, NULL, NULL, 1 (Change) => 24, NULL, NULL, 20, 500
- ---- 12101BZ070, 2, NULL, NULL, NULL, 1 (Change) => 12, NULL, NULL, 10, 500
- ---- 12306BZ210, 2, NULL, NULL, NULL, 1 (Change) => 17, NULL, NULL, 15, 500
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=20 WHERE PART_NO='121010C010'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=10 WHERE PART_NO='12101BZ070'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=15 WHERE PART_NO='12306BZ210'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='121010C010'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='12101BZ070'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='12306BZ210'
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '121010C010', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Y-AMANAKA-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '121010C010', 8, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Y-AMANAKA-002')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '121010C010', 2, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Y-AKUZA-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '121010C010', 2, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Y-AKUZA-002')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '121010C010', 3, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Y-AKUZA-003')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '12101BZ070', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Y-ONEX-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '12306BZ210', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Y-AMYAM-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '12306BZ210', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Y-AMYAM-002')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '12306BZ210', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Y-AMYAM-003')
- ---- Zone S
- ---- Part No, SOH, SOR, SODR, SONDR, MAX_STOCK on [spex].[TB_R_PART_STOCK_INFO]
- ---- 04009111B0, 154, NULL, NULL, NULL, 1 (Change) => 204, NULL, NULL, 50, 500
- ---- 04371BZ010, 4, NULL, NULL, NULL, 1 (Change) => 7, NULL, NULL, 3, 500
- ---- 04371BZ030, 3, NULL, NULL, NULL, 1 (Change) => 5, NULL, NULL, 2, 500
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=50 WHERE PART_NO='04009111B0'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=3 WHERE PART_NO='04371BZ010'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SONDR=2 WHERE PART_NO='04371BZ030'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='04009111B0'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='04371BZ010'
- --UPDATE spex.TB_R_PART_STOCK_INFO SET SOH = ISNULL(SOH, 0) + ISNULL(SOR, 0) + ISNULL(SODR, 0) + ISNULL(SONDR, 0) WHERE PART_NO='04371BZ030'
- ---- insert binning zone 'S'
- ---- Part No : 04009111B0
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04009111B0', 20, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'S-UPERMAN-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04009111B0', 15, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'S-UPERMAN-002')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04009111B0', 10, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'S-PIDERMAN-001')
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04009111B0', 5, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'S-PIDERMAN-002')
- ---- Part No : 04371BZ010
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04371BZ010', 3, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'S-EJUJURNYA-001')
- ---- Part No : 04371BZ030
- --INSERT INTO spex.TB_R_BINNING (TRANSFER_NO, BT_NO, PART_NO, QTY, SOURCE, CREATED_BY, CREATED_DT, START_DATE, RACK_ADDRESS) VALUES ('TR2020012900125', 'SOBI-M-2801200000', '04371BZ030', 2, 'NON-DIRECT', 'AMANAH.Yusup', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'S-ASUKEUCHIHA-001')
- ---- query
- --SELECT * FROM spex.TB_R_PART_STOCK_INFO WHERE CHANGED_BY='sahrulgun1' OR CHANGED_BY='sahrulgun2' ORDER BY CHANGED_DT DESC
- --SELECT * FROM spex.TB_M_BUCKET_TRANSFER WHERE SOURCE='Reserve' AND TARGET='Binning'
- --SELECT * FROM spex.TB_R_BUCKET_TRANSFER_H ORDER BY CREATED_DT DESC
- --INSERT INTO spex.TB_R_BUCKET_TRANSFER_H
- --(TRANSFER_NO, BT_NO, OPEN_BY, OPEN_DT, CLOSE_BY, CLOSE_DT, STATUS, CREATED_BY, CREATED_DT, CHANGED_BY, CHANGED_DT)
- --VALUES
- --('TR2020012900125', 'SOBI-M-2801200000', 'AMANAH.Yusup', CURRENT_TIMESTAMP, 'AMANAH.Yusup', CURRENT_TIMESTAMP, 1, 'AMANAH.Yusup', CURRENT_TIMESTAMP, 'AMANAH.Yusup', CURRENT_TIMESTAMP)
- -- RUN SP
- EXECUTE spex.SP_AMANAH_RE_FILL_3_ZONE
- EXECUTE spex.SP_AMANAH_RE_FILL_3_LIST 'M'
- EXECUTE spex.SP_AMANAH_RE_FILL_3_SEARCH '04009111B0 F', 'S';
- EXECUTE spex.SP_AMANAH_RE_FILL_4_DETAIL '04009111B0', 'S', 'LIST';
- EXECUTE spex.SP_AMANAH_RE_FILL_4_RESERVE_LIST '04009111B0', 'S';
- EXECUTE spex.SP_AMANAH_RE_FILL_4_RESERVE_DETAIL '04009111B0', 'S';
- --EXECUTE spex.SP_AMANAH_RE_FILL_5_SCAN_M_RESERVE_LOCATION '04009111B0', 'S-UPERMAN-001';
- EXECUTE spex.SP_AMANAH_RE_FILL_5_SCAN_BT 'S-UPERMAN-001', 'REBI-S-0106200001', 'AMANAH.Yusup';
- EXECUTE spex.SP_AMANAH_RE_FILL_5_DO_RE_FILL '04009111B0', 'S0406-2-B', 'S-UPERMAN-001,S-UPERMAN-001', 2, 0, 500, 500, 20, 'NON-DIRECT', 'REBI-S-0106200001', 0, 'S', 'AMANAH.Yusup';
- --EXECUTE spex.SP_AMANAH_RE_FILL_5_CLOSE_BT 'S-UPERMAN-001', 'REBI-S-0106200001', 'AMANAH.Yusup'
- --EXECUTE spex.SP_AMANAH_RE_FILL_13_PARTNO_VALIDATE '04009111B0', '04009111B0 X', 'AMANAH.Yusup';
- EXECUTE spex.SP_AMANAH_RE_FILL_ROLLBACK 'S','04009111B0', 'REBI-S-0106200001', 'S-UPERMAN-001', 'AMANAH.Yusup'
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement