Home » SQL & PL/SQL » SQL & PL/SQL » Break line as per required attached output. (Oracle 11i)
Break line as per required attached output. [message #678299] |
Sat, 23 November 2019 11:08 |
|
nitesh.erp@gmail.com
Messages: 24 Registered: June 2016
|
Junior Member |
|
|
I need output like attached file. please find below table 2 table script and insert statement.please help me and revert.
--############## FIRST TABLE SCRIPT ####################################
CREATE TABLE TRANS_TBL1
(
TRANSACTION_ID NUMBER,
INVENTORY_ITEM_ID NUMBER,
QUANTITY NUMBER,
LOT_NUMBER VARCHAR2(15),
SUBINVENTORY_CODE VARCHAR2(15),
TRANSFER_SUBINVENTORY VARCHAR2(15),
TRANSACTION_TYPE_ID NUMBER,
TRANSACTION_TYPE_NAME VARCHAR2(100),
TRANSACTION_DATE DATE
)
---------------------------------------------------------------------------
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(83932874, 8683, 400, '696705', 'OSP_STORE',
44, 'WIP Completion', TO_DATE('09/13/2019 18:11:39', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSFER_SUBINVENTORY, TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84007611, 8683, -286, '696705', 'OSP_STORE',
'BJSHOP_FLR', 64, 'Move Order Transfer', TO_DATE('09/16/2019 09:52:54', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSFER_SUBINVENTORY, TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84007612, 8683, 286, '696705', 'BJSHOP_FLR',
'OSP_STORE', 64, 'Move Order Transfer', TO_DATE('09/16/2019 09:52:54', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSFER_SUBINVENTORY, TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84007617, 8683, -114, '696705', 'OSP_STORE',
'BJSHOP_FLR', 64, 'Move Order Transfer', TO_DATE('09/16/2019 09:53:01', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSFER_SUBINVENTORY, TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84007618, 8683, 114, '696705', 'BJSHOP_FLR',
'OSP_STORE', 64, 'Move Order Transfer', TO_DATE('09/16/2019 09:53:01', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84050204, 8683, -106, '696705', 'BJSHOP_FLR',
35, 'WIP Issue', TO_DATE('09/18/2019 08:57:56', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84053133, 8683, -3, '696705', 'BJSHOP_FLR',
35, 'WIP Issue', TO_DATE('09/18/2019 10:05:27', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84058082, 8683, 459, '696705', 'OSP_STORE',
44, 'WIP Completion', TO_DATE('09/18/2019 13:41:24', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84077879, 8683, -191, '696705', 'BJSHOP_FLR',
35, 'WIP Issue', TO_DATE('09/18/2019 22:59:23', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84078060, 8683, -77, '696705', 'BJSHOP_FLR',
35, 'WIP Issue', TO_DATE('09/18/2019 23:04:24', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSFER_SUBINVENTORY, TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84172235, 8683, -459, '696705', 'OSP_STORE',
'BJSHOP_FLR', 64, 'Move Order Transfer', TO_DATE('09/21/2019 08:38:07', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSFER_SUBINVENTORY, TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84172236, 8683, 459, '696705', 'BJSHOP_FLR',
'OSP_STORE', 64, 'Move Order Transfer', TO_DATE('09/21/2019 08:38:07', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84412157, 8683, 122, '696705', 'OSP_STORE',
44, 'WIP Completion', TO_DATE('09/30/2019 08:51:51', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84415156, 8683, -4, '696705', 'BJSHOP_FLR',
35, 'WIP Issue', TO_DATE('09/30/2019 10:05:52', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSFER_SUBINVENTORY, TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84418436, 8683, -4, '696705', 'BJSHOP_FLR',
'BJ_MTS_REJ', 64, 'Move Order Transfer', TO_DATE('09/30/2019 11:36:10', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSFER_SUBINVENTORY, TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84418437, 8683, 4, '696705', 'BJ_MTS_REJ',
'BJSHOP_FLR', 64, 'Move Order Transfer', TO_DATE('09/30/2019 11:36:10', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84454661, 8683, -4, '696705', 'BJ_MTS_REJ',
63, 'Move Order Issue', TO_DATE('10/01/2019 11:31:05', 'MM/DD/YYYY HH24:MI:SS'));
Insert into TRANS_TBL1
(TRANSACTION_ID, INVENTORY_ITEM_ID, QUANTITY, LOT_NUMBER, SUBINVENTORY_CODE,
TRANSACTION_TYPE_ID, TRANSACTION_TYPE_NAME, TRANSACTION_DATE)
Values
(84727446, 8683, -2, '696705', 'BJSHOP_FLR',
35, 'WIP Issue', TO_DATE('10/10/2019 09:58:07', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
--------------------SHEET 1 -------------------
SELECT*FROM TRANS_TBL1 ORDER BY transaction_date, transaction_id
---------------------------------------
--############## SECOND TABLE SCRIPT ####################################
CREATE TABLE PROCESS_TBL22
(
SRNO NUMBER,
ORGANIZATION_ID NUMBER,
INVENTORY_ITEM_ID NUMBER,
LOT_NUMBER VARCHAR2(15),
SUBINVENTORY_CODE VARCHAR2(15),
QUANTITY NUMBER,
TRANSACTION_DATE DATE
)
Insert into PROCESS_TBL22
(SRNO, ORGANIZATION_ID, INVENTORY_ITEM_ID, LOT_NUMBER, SUBINVENTORY_CODE,
QUANTITY, TRANSACTION_DATE)
Values
(1, 85, 8683, '696705', 'OSP_STORE',
0, TO_DATE('09/13/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into PROCESS_TBL22
(SRNO, ORGANIZATION_ID, INVENTORY_ITEM_ID, LOT_NUMBER, SUBINVENTORY_CODE,
QUANTITY, TRANSACTION_DATE)
Values
(2, 85, 8683, '696705', 'BJSHOP_FLR',
472, TO_DATE('09/13/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into PROCESS_TBL22
(SRNO, ORGANIZATION_ID, INVENTORY_ITEM_ID, LOT_NUMBER, SUBINVENTORY_CODE,
QUANTITY, TRANSACTION_DATE)
Values
(3, 85, 8683, '696705', 'OSP_STORE',
0, TO_DATE('09/18/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into PROCESS_TBL22
(SRNO, ORGANIZATION_ID, INVENTORY_ITEM_ID, LOT_NUMBER, SUBINVENTORY_CODE,
QUANTITY, TRANSACTION_DATE)
Values
(4, 85, 8683, '696705', 'OSP_STORE',
122, TO_DATE('09/30/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into PROCESS_TBL22
(SRNO, ORGANIZATION_ID, INVENTORY_ITEM_ID, LOT_NUMBER, SUBINVENTORY_CODE,
QUANTITY, TRANSACTION_DATE)
Values
(5, 85, 8683, '696705', 'BJ_MTS_REJ',
0, TO_DATE('09/30/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
--------------------------------------------------------------------
SELECT*FROM PROCESS_TBL22 ORDER BY 1
-----------------------------------------------------------------
Regards,
Nitesh
|
|
|
|
|
|
|
Re: Break line as per required attached output. [message #678308 is a reply to message #678301] |
Sat, 23 November 2019 20:42 |
|
nitesh.erp@gmail.com
Messages: 24 Registered: June 2016
|
Junior Member |
|
|
Sir,
My transactions data stored in Table - TRANS_TBL1, after process some program it's stored in Table- PROCESS_TBL22, now I required data of PROCESS_TBL22 in split into following way
FINAL OUTPUT
SRNO ORGANIZATION_ID INVENTORY_ITEM_ID LOT_NUMBER SUBINVENTORY_CODE QUANTITY TRANSACTION_DATE
1 85 8683 696705 OSP_STORE 122 30/09/2019
2 85 8683 696705 BJSHOP_FLR 13 13/09/2019
3 85 8683 696705 BJSHOP_FLR 459 18/09/2019
Both table composite key (INVENTORY_ITEM_ID,LOT_NUMBER,SUBINVENTORY_CODE)
when first data is (WIP Completion) comes, that will transfer to another sub_inventory, but the date should remain when it comes from (WIP Completion) and running remaining value on basis of FIFO, it should break and insert into another table as per above scenario.
LOGIC
13/09/2019 OSP_STORE 0 (400-286-114)
13/09/2019 BJSHOP_FLR 13 (286+114-106-3-191-77-4-4-2)
18/09/2019 OSP_STORE 0 (459-459)
18/09/2019 BJSHOP_FLR 459 (459)
30/09/2019 OSP_STORE 122 (122)
[Edit MC: add code tags]
[Updated on: Sun, 24 November 2019 04:05] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 15 17:00:12 CDT 2024
|