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 Go to next message
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 #678301 is a reply to message #678299] Sat, 23 November 2019 12:05 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator


As I told you in your previous topic: tell us with words what you want.
Also post the result you want for the data you gave.
What are the primary key of the tables?
What are the relationship between the two tables?

In addition, Please read How to use [code] tags and make your code easier to read.

Re: Break line as per required attached output. [message #678302 is a reply to message #678299] Sat, 23 November 2019 12:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also I'm still waiting the following from your previous topic:

nitesh.erp@gmail.com wrote on Thu, 23 June 2016 16:28
Great Sir, will reply tomorrow after reach office. Thanks a lot

Or didn't you reach your office yet? Very Happy

[Updated on: Sat, 23 November 2019 12:10]

Report message to a moderator

Re: Break line as per required attached output. [message #678305 is a reply to message #678299] Sat, 23 November 2019 15:09 Go to previous messageGo to next message
EdStevens
Messages: 1250
Registered: September 2013
Senior Member
I doubt if anyone here is going to open an attachment from a stranger on the internet. Certainly no one with any appreciation for cyber security. You need to inlclude all information directly in your messages, not as attachments.
Re: Break line as per required attached output. [message #678307 is a reply to message #678302] Sat, 23 November 2019 20:18 Go to previous messageGo to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
Thanks a lot sir, You don't know how much you helped me, I am sorry for that, I think my thanks statement could not posted successfully. But I told numbers of friends about your skills that you done my required query very quickly. Again Thanks a lot.
Re: Break line as per required attached output. [message #678308 is a reply to message #678301] Sat, 23 November 2019 20:42 Go to previous messageGo to next message
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

Re: Break line as per required attached output. [message #678309 is a reply to message #678308] Sat, 23 November 2019 20:44 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Break line as per required attached output. [message #678310 is a reply to message #678309] Sun, 24 November 2019 02:41 Go to previous messageGo to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
Sir, sent you reply, please see and revert.
Re: Break line as per required attached output. [message #678311 is a reply to message #678310] Sun, 24 November 2019 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How from that:
SQL> SELECT*FROM PROCESS_TBL22 ORDER BY 1;
      SRNO ORGANIZATION_ID INVENTORY_ITEM_ID LOT_NUMBER      SUBINVENTORY_CO   QUANTITY TRANSACTION_DATE
---------- --------------- ----------------- --------------- --------------- ---------- -------------------
         1              85              8683 696705          OSP_STORE                0 13/09/2019 00:00:00
         2              85              8683 696705          BJSHOP_FLR             472 13/09/2019 00:00:00
         3              85              8683 696705          OSP_STORE                0 18/09/2019 00:00:00
         4              85              8683 696705          OSP_STORE              122 30/09/2019 00:00:00
         5              85              8683 696705          BJ_MTS_REJ               0 30/09/2019 00:00:00
Do you get that:
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
Question
Re: Break line as per required attached output. [message #678312 is a reply to message #678311] Sun, 24 November 2019 06:01 Go to previous messageGo to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
Yes sir, I need that.
Re: Break line as per required attached output. [message #678313 is a reply to message #678312] Sun, 24 November 2019 06:13 Go to previous messageGo to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
On the basis of first table TRANS_TBL1 transactions, we get PROCESS_TBL22 table data, but now I need

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

one basis of below Logic of first table TRANS_TBL1, I need above output,


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)

Re: Break line as per required attached output. [message #678314 is a reply to message #678313] Sun, 24 November 2019 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

1/ Format your posts.
2/ Repeat is not explain.

Re: Break line as per required attached output. [message #678315 is a reply to message #678314] Sun, 24 November 2019 07:24 Go to previous messageGo to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
When I do that, after post it's shrink, how can I do as you did it in table manner, I am unable to do so.

second point--
I need my required output from table TRANS_TBL1 as per logic given.
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 LOGIC. You may use required output from table TRANS_TBL1 only or (use both table TRANS_TBL1 and PROCESS_TBL22).
Re: Break line as per required attached output. [message #678323 is a reply to message #678315] Sun, 24 November 2019 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67370
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
When I do that, after post it's shrink, how can I do as you did it in table manner, I am unable to do so.

Read the link BlackSwan and I gave you.

Re: Break line as per required attached output. [message #678324 is a reply to message #678323] Sun, 24 November 2019 11:09 Go to previous messageGo to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
one the basis of below Logic of first table TRANS_TBL1, I need required output,


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)
Here balance quantity of the subinventory after issue on the basis of transfer quantity need to show but the Date comes as per originally stock received.
Re: Break line as per required attached output. [message #678327 is a reply to message #678324] Mon, 25 November 2019 00:07 Go to previous messageGo to next message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
Sir, sent you reply, please see and revert.
Re: Break line as per required attached output. [message #678337 is a reply to message #678327] Mon, 25 November 2019 05:39 Go to previous message
nitesh.erp@gmail.com
Messages: 24
Registered: June 2016
Junior Member
Dear Michel Cadot Sir,
I am waiting your response sir. Thanks.
Previous Topic: question for reverse
Next Topic: bulk collect problem through dblink
Goto Forum:
  


Current Time: Tue Sep 29 00:42:16 CDT 2020