/* Creation of batch using the ‘RECIPE’ creation mode */

alter session set nls_language=american;
set serveroutput on size 1000000;

DECLARE  		
   CURSOR ic_adjs_jnl_vw_cursor  IS
   SELECT ORGN_CODE,JOURNAL_NO,POSTED_IND,DOC_DATE,DOC_LINE,REASON_CODE,REASON_DESC1,ITEM_NO,ITEM_DESC1,LOT_NO,LOT_DESC,SUBLOT_NO,WHSE_CODE,WHSE_NAME,LOCATION,QTY,ITEM_UM,QTY2,TO_WHSE_CODE,TO_LOCATION,TO_QTY,TO_ITEM_UM,TO_QTY2,TO_LOT_STATUS,TO_QC_GRADE,JOURNAL_COMMENT,ACCTG_UNIT_NO,ACCT_NO,LOCT_DESC,LOT_STATUS,STATUS_DESC,QC_GRADE,QC_GRADE_DESC,DELETE_MARK,ASSIGNMENT_TYPE,TO_WHSE_NAME,TO_ITEM_UM2,TO_STATUS_DESC,TO_QC_GRADE_DESC,TO_DOC_LINE,TO_LINE_ID,TO_WHSE_LOCT_CTL,TRANS_TYPE,TRANS_FLAG,DOC_ID,COMPLETED_IND,ITEM_ID,LOT_ID,ITEM_UM2,LINE_ID,CO_CODE,NO_INV,NO_TRANS,ITEM_LOCT_CTL,LOT_CTL,SUBLOT_CTL,GRADE_CTL,STATUS_CTL,WHSE_LOCT_CTL,DUALUM_IND,DEFAULT_LOT_STATUS,DEFAULT_QC_GRADE,LOT_INDIVISIBLE,REASON_TYPE,LINE_TYPE,TO_LINE_TYPE,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,TRANS_CNT,TEXT_CODE,JOURNAL_ID,POSTING_ID,PRINT_CNT,IN_USE,LAST_UPDATED_BY,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,REQUEST_ID,LAST_UPDATE_LOGIN,ACCTG_UNIT_DESC,ACCTG_UNIT_ID,ACCT_ID,ACCT_NO_DESC,SEGMENT_DELIM,SOBNAME,ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,ATTRIBUTE21,ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25,ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,ATTRIBUTE29,ATTRIBUTE30,WHSE_CO_CODE  
   FROM   ic_adjs_jnl_vw 
   WHERE  REASON_CODE ='TNFR' 
	  and ITEM_NO = 'I08233'
	  and LOT_NO = 'PPP1'
	  and SUBLOT_NO = 16
	  and posted_ind = 0;    
  
   jnl_vw_row  		ic_adjs_jnl_vw_cursor%ROWTYPE; -- cursor row type	
   
   GEM5TRANSID_SEQ NUMBER;  

BEGIN 
	  OPEN ic_adjs_jnl_vw_cursor;
	  FETCH ic_adjs_jnl_vw_cursor INTO jnl_vw_row;


 dbms_output.put_line('jnl_vw_row.item_id = '|| jnl_vw_row.item_id);
 dbms_output.put_line('jnl_vw_row.lot_id = '|| jnl_vw_row.lot_id);
 dbms_output.put_line('jnl_vw_row.whse_code = '|| jnl_vw_row.whse_code);
 dbms_output.put_line('jnl_vw_row.location = '|| jnl_vw_row.location);
  dbms_output.put_line('jnl_vw_row.last_updated_by = '|| jnl_vw_row.last_updated_by);
dbms_output.put_line('jnl_vw_row.qty = '|| jnl_vw_row.qty);
 	  

--LOCT_ONHAND := LOCT_ONHAND +  MOVE JOURNAL QUANTITY ?? LOCT_ONHAND2 := LOCT_ONHAND2 
UPDATE IC_LOCT_INV 
SET    LOCT_ONHAND = LOCT_ONHAND + jnl_vw_row.qty, 
	   LOCT_ONHAND2 = jnl_vw_row.qty2,
 	   LAST_UPDATE_DATE = SYSDATE, 
	   LAST_UPDATED_BY = jnl_vw_row.last_updated_by 
WHERE  ITEM_ID = jnl_vw_row.item_id 
	   AND LOT_ID = jnl_vw_row.lot_id 
	   AND WHSE_CODE = jnl_vw_row.whse_code 
	   AND LOCATION = jnl_vw_row.location;
	   
commit;	   
   
-- LOCT_ONHAND need to re check that this is ok 	   
INSERT INTO IC_LOCT_INV ( ITEM_ID, WHSE_CODE, LOT_ID, LOCATION, LOCT_ONHAND, 
  	   		LOCT_ONHAND2, LOT_STATUS, QCHOLD_RES_CODE, DELETE_MARK, TEXT_CODE, 
  			CREATED_BY, CREATION_DATE, LAST_UPDATE_DATE, LAST_UPDATED_BY ) 
VALUES		(jnl_vw_row.item_id, jnl_vw_row.whse_code, jnl_vw_row.lot_id, jnl_vw_row.location, jnl_vw_row.qty , 
			jnl_vw_row.qty2, jnl_vw_row.lot_status , null , jnl_vw_row.delete_mark , jnl_vw_row.text_code , 
			jnl_vw_row.created_by , sysdate , sysdate , jnl_vw_row.created_by);	   

commit;	
			
 SELECT GEM5_TRANS_ID_S.NEXTVAL
 INTO 	GEM5TRANSID_SEQ
 FROM	DUAL;

-- HARDCODING GL_POSTED_IND as 0
-- EVENT_ID is always 0 select unique ... = 0
INSERT INTO IC_TRAN_CMP ( TRANS_ID , ITEM_ID , LINE_ID , CO_CODE , ORGN_CODE ,
  WHSE_CODE , LOT_ID , LOCATION , DOC_ID , DOC_TYPE , DOC_LINE , LINE_TYPE , 
  REASON_CODE , CREATION_DATE , TRANS_DATE , TRANS_QTY , TRANS_QTY2 , 
  QC_GRADE , LOT_STATUS , TRANS_STAT , TRANS_UM , TRANS_UM2 , OP_CODE , 
  GL_POSTED_IND , EVENT_ID , TEXT_CODE , LAST_UPDATE_DATE , CREATED_BY , 
  LAST_UPDATED_BY , LINE_DETAIL_ID , INTORDER_POSTED_IND ) 
VALUES
 (  GEM5TRANSID_SEQ, jnl_vw_row.item_id , jnl_vw_row.line_id , jnl_vw_row.co_code , jnl_vw_row.orgn_code, 
    jnl_vw_row.whse_code , jnl_vw_row.lot_id , jnl_vw_row.location , jnl_vw_row.doc_id , jnl_vw_row.trans_type , jnl_vw_row.doc_line, jnl_vw_row.line_type,
	jnl_vw_row.reason_code , sysdate , jnl_vw_row.creation_date , jnl_vw_row.qty , jnl_vw_row.qty2 ,
	jnl_vw_row.qc_grade , jnl_vw_row.lot_status , null , jnl_vw_row.item_um, jnl_vw_row.item_um2, jnl_vw_row.created_by, 
    0 , 0, null  , sysdate , jnl_vw_row.created_by, 
	jnl_vw_row.last_updated_by, null , 0);  
	
commit;		

-- Update the already created entry from the pending move journal creation 
UPDATE IC_ADJS_JNL 
	   SET COMPLETED_IND=1,	  
	   LAST_UPDATE_DATE=SYSDATE,
  	   LAST_UPDATED_BY=jnl_vw_row.last_updated_by
WHERE  JOURNAL_ID = jnl_vw_row.journal_id;

COMMIT;

END;