create or replace PACKAGE BODY pwr_rev_ins_pkg IS
----------------------------------------------------------
--- Main Process called from the Concurrent program
----------------------------------------------------------
PROCEDURE pwr_main_process( x_ret_code 	   								 OUT NUMBER,
                            x_errmsg   	   								 OUT VARCHAR2,
                            p_organization_selection       IN  VARCHAR2,  -- Added for HD 21283
 			    p_dummy                        IN  NUMBER,     -- Added for HD 21283
 			    p_org_id			   IN  NUMBER,
    			    p_item_cat_id		   IN  NUMBER,
 			    p_ins_rev  			   IN  VARCHAR2,
			    p_eff_dt    	 	   IN  DATE,						    		 			    p_inc_desc_diff                IN  VARCHAR2,   -- Added for HD 21283
			    p_upd_desc_diff                IN  VARCHAR2    -- Added for HD 21283
						  ) IS
--Local Variables
BEGIN
----------------------------------------------------------
-- Print the details of Parameters in Log from Concurrent
-- program to stored program
     g_stmt_num := 1;
	 pwr_exception_pkg.print_blank_line(g_log,1);
	 pwr_exception_pkg.print_msg('Log -> Details of Parameter :',g_log,1);
	 pwr_exception_pkg.print_msg('-----------------------------',g_log,1);
	 pwr_exception_pkg.print_msg('p_org_id		->'||p_org_id,g_log,1);
	 pwr_exception_pkg.print_msg('p_item_cat_id	->'||p_item_cat_id,g_log,1);
	 pwr_exception_pkg.print_msg('p_ins_rev		->'||p_ins_rev,g_log,1);
	 pwr_exception_pkg.print_msg('p_eff_dt 		->'||p_eff_dt ,g_log,1);
	 pwr_exception_pkg.print_blank_line(g_log,1);
----------------------------------------------------------
-- Assign the values to global var
	 g_request_id  := fnd_global.conc_request_id;
     g_user_id     := fnd_global.user_id;
     g_login_id    := fnd_global.login_id;
	 g_eff_dt	   := p_eff_dt;
----------------------------------------------------------
--  Procedure will populate data to Interface table
   	 g_stmt_num := 2;
   -- Modified for HD 21283
	 --pwr_populate_interface(p_org_id,p_item_cat_id);
	 pwr_populate_interface(NVL(p_org_id,1),p_item_cat_id,p_inc_desc_diff,p_upd_desc_diff,p_ins_rev);
	 IF p_ins_rev = 1 THEN  -- Call the Import
	 ----------------------------------------------------------
	 --  Procedure Call the Item import pgm
   	 g_stmt_num := 6;
	 --pwr_launch_item_import(NVL(p_org_id,1));
	 pwr_launch_item_import(1);
	 END IF;
	 ---------------------------------------------------------
	 --Delete all the records, which we inserted in interface
	 --related to this process
	  g_stmt_num := 7;
	  pwr_del_processed_record;
	  pwr_exception_pkg.print_blank_line(g_log,2);
	  pwr_exception_pkg.print_msg('Log (Import Activity) ',g_log,1);
	  pwr_exception_pkg.print_msg('----------------------',g_log,1);
	  IF p_ins_rev =1 THEN
	  	  pwr_exception_pkg.print_msg('Import Item Fired');
	  ELSE
	       pwr_exception_pkg.print_msg('Import Item -> Not fired, Since Insert Latest Revision=No ');
	  END IF;
	  pwr_exception_pkg.print_blank_line(g_log,2);
g_stmt_num := 1000;
EXCEPTION  WHEN OTHERS THEN
		  pwr_exception_pkg.print_blank_line(g_log,2);
		  pwr_exception_pkg.print_msg('Log -> Unexpected Error in Main Program:',g_log,1);
		  pwr_exception_pkg.print_msg('----------------------------------------',g_log,1);
		  pwr_exception_pkg.print_msg('Exception     : Others');
		  pwr_exception_pkg.print_msg('Package       : pwr_rev_ins_pkg.pwr_main_process',g_log,1);
		  pwr_exception_pkg.print_msg('Error Msg     :'||substrb(sqlerrm,1,500),g_log,1);
		  pwr_exception_pkg.print_msg('Error Code    :'||sqlcode,g_log,1);
		  pwr_exception_pkg.print_msg('Statement num :' ||g_stmt_num,g_log,1);
		  pwr_exception_pkg.print_blank_line(g_log,2);
END pwr_main_process;
PROCEDURE pwr_populate_interface( p_org_id 	        IN 	NUMBER,
                                  p_item_cat_id		IN  NUMBER,
  							      p_inc_desc_diff   IN VARCHAR2, -- Added by HCL for HD 21283
    						      p_upd_desc_diff   IN VARCHAR2, -- Added by HCL for HD 21283
								  p_ins_rev			IN VARCHAR2  -- Added by HCL for HD 21283
)  IS
  l_organization_code varchar2(200);
----------------------------------------------------------------------------------------------
-- Below cursor will fatch data only for the slno having "cursor fetch data -> yes"
----------------------------------------------------------------------------------------------
-- Slno.   Revision      |Condition|  Effectivity dt    | Cursor     | Action
--                       |         |                    | Fetch data |
------------------------------------------------------------------------------------------------
--  1     GLO > Oth Org  |   AND   |  GLO > Oth org     | Yes        |Implement through interface
--  2     GLO > Oth Org  |   AND   |  GLO < Oth org     | Yes        |Implement through interface,
--                                                                    Handled before the interface
--  3     GLO = Oth Org  |   AND   |  Anything          | No         |No action recomended
--  4     GLO < Oth Org  |   AND   |  Anything          | No         |No action recomended,
--                                                                    may be onetime data, needs
--                                                                    manual update
-------------------------------------------------------------------------------------------------
Cursor c1_data(c_org_id NUMBER) IS
  SELECT mir.inventory_item_id,
  		max(decode(mir.organization_id,86,mir.effectivity_date,null)) glo_effdt,
		max(decode(mir.organization_id,86,mir.implementation_date,null)) glo_impldt,
  		max(decode(mir.organization_id,86,null,mir.effectivity_date)) oth_effdt,
		max(decode(mir.organization_id,86,null,mir.implementation_date)) oth_impldt,
  		max(decode(mir.organization_id,86,mir.revision,null)) glo_rev,
                max(decode(mir.organization_id,86,null,mir.revision)) oth_rev,
	        max(description) description
    FROM        mtl_item_revisions  mir,
		mtl_item_categories mic
   WHERE
	        mir.organization_id in (86,c_org_id)
		 AND mir.inventory_item_id = mic.inventory_item_id   --Link between mir and mic
		 AND mir.organization_id   = mic.organization_id     --Link between mir and mic
	         AND mic.category_set_id   = DECODE(p_item_cat_id,NULL,mic.category_set_id,p_item_cat_id) --Category set id from Concurrent pgm
		 AND mir.implementation_date IS NOT NULL
	--      AND NVL(LTRIM(RTRIM(mir.revision)),'0') <> '0'
        	AND TRUNC(NVL(mir.implementation_date,mir.effectivity_date)) =
		       (SELECT TRUNC(MAX(NVL(mir_1.implementation_date,mir_1.effectivity_date)))
		        FROM mtl_item_revisions mir_1
		        WHERE mir_1.inventory_item_id  = mir.inventory_item_id
		        AND mir_1.organization_id = mir.organization_id
		        AND mir_1.implementation_date IS NOT NULL
		        AND NVL(LTRIM(RTRIM(mir_1.revision)),'0') <> '0' )
		        AND mir.effectivity_date =  (SELECT MAX(mir2.effectivity_date)
                        FROM mtl_item_revisions mir2
                        WHERE mir2.inventory_item_id = mir.inventory_item_id
                        AND mir2.organization_id = mir.organization_id
                        AND mir2.implementation_date IS NOT NULL
                        AND NVL(LTRIM(RTRIM(mir2.revision)),'0') <> '0' )
		AND  mir.effectivity_date > NVL(g_eff_dt,TO_DATE('01-JAN-99','DD-MON-RR'))--records effectdt > Parameter dt
		        group by mir.inventory_item_id
		        having max(decode(mir.organization_id,86,mir.revision,null))  >
		        max(decode(mir.organization_id,86,null,mir.revision))
		       ;
   ----Added By HCL for HD 21283------------------------------------------------------------------------------------
   CURSOR c2_data(c_org_id NUMBER,p_item_cat_id NUMBER)
   IS
     SELECT  master.segment1 Item,
             substr(master.itemdescription,1,50) itemdescription,
             (master.revision) Revision,
	     child.revision crevision,
	     master.description masterdesc,
             child.description     childesc,
             ood.organization_code organization,
	     master.inventory_item_id,
	     child.organization_id c_organization_id
     FROM    PWR_REVDESC_DIFF_VIEW master,
             PWR_REVDESC_DIFF_VIEW child,
   	     org_organization_definitions ood
     WHERE   master.organization_id = 86
     AND     child.organization_id like  decode(c_org_id,1,'%',c_org_id || '%')
     AND     master.inventory_item_id = child.inventory_item_id
     AND     child.organization_id <> 86
     AND     ood.organization_id = child.organization_id
     AND     (rtrim(ltrim(master.revision))) = (rtrim(ltrim(child.revision)))
     AND     (rtrim(ltrim(NVL(master.description,'#')))) <> (rtrim(ltrim(NVL(child.description,'#'))))
     AND     master.category_set_id = child.category_set_id
     AND     child.category_set_id  = NVL(p_item_cat_id,master.category_set_id)
     ORDER BY ood.organization_code
   ;
------------------------------------------------------------------------------------------------------------------
-----------------------------------Added By HCLT for HD 21283 for implentation date updation----------------------
 CURSOR  c3_data
 IS
 SELECT  distinct child.inventory_item_id inventory_item_id,
   	 child.revision revision,
	 child.organization_id organization_id
 FROM    PWR_UPDIMP_VIEW master,
         PWR_UPDIMP_VIEW child,
         org_organization_definitions ood
 WHERE  master.organization_id = 86
 AND    master.inventory_item_id = child.inventory_item_id
 AND    child.organization_id <> 86
 AND    ood.organization_id = child.organization_id
 AND    (rtrim(ltrim(master.revision))) = (rtrim(ltrim(child.revision)))
 AND    child.implementation_date is null
 AND    master.implementation_date is not null
 ;
--------------------------------------------------------------------------------------------------------------------
l_item_number	 VARCHAR2(40) := NULL;
l_item_desc	 VARCHAR2(240):= NULL;
l_effdt		 DATE;
l_impldt	 DATE;
l_flag_insert    NUMBER := 0;
l_org_id         NUMBER;
BEGIN
----------------------------------------------------------
--- Generate Process id for the Transactions
    g_stmt_num := 1001;
BEGIN   --------ADDED BY HCLT FOR HD21283
 FOR i IN c3_data
 LOOP
       UPDATE mtl_item_revisions
       SET    implementation_date = SYSDATE
       WHERE  inventory_item_id = i.inventory_item_id
       AND    organization_id   = i.organization_id
       AND    revision          = i.revision;
 END LOOP;
COMMIT;
END;
BEGIN
      SELECT mtl_system_items_intf_sets_s.NEXTVAL
      INTO   g_set_process_id
      FROM   DUAL;
END;
pwr_print_heading('Revision Number Details for Inventory Items as compared to GLOBAL Org.',220);
pwr_heading_trn_iface;
----------------------------------------------------------
--- Loop to process data
FOR j in ( SELECT  organization_id
           FROM    org_organization_definitions
           WHERE   organization_id like  '%')--  decode(p_org_id,1,'%',p_org_id || '%')
LOOP   ------- Added By HCL for HD 21283 ---------------------------------------
  fnd_file.put_line(FND_FILE.LOG, 'organization_id ' || j.organization_id);
   fnd_file.put_line(FND_FILE.LOG, 'entering'  );
FOR i IN c1_data(j.organization_id)
LOOP
   fnd_file.put_line(FND_FILE.LOG, 'U are in the C1_Data Loop'  );
	  --Fetch the Item Number
	  BEGIN
	  	  SELECT msi.segment1,SUBSTR(msi.description,1,50)
         	  INTO   l_item_number,l_item_desc
		  FROM   mtl_system_items msi
		  WHERE  msi.inventory_item_id = i.inventory_item_id
		  AND    msi.organization_id = 86;
		  EXCEPTION WHEN NO_DATA_FOUND THEN
		       l_item_number := NULL;
		   WHEN OTHERS THEN
		      l_item_number := NULL;
   	  END;
     BEGIN
	      SELECT organization_code
	      INTO   l_organization_code
	      FROM   org_organization_definitions
	      WHERE  organization_id = j.organization_id;
	      EXCEPTION
	      WHEN OTHERS THEN
                  l_organization_code := NULL;
      	      END;
	          l_effdt 	:= i.glo_effdt;
         	  l_impldt  := i.glo_impldt;
	  ------------------------------------------------------------------------------------
	  --- Below portion will handle the condition for Effectivity and impl dt
	  ---
	  IF i.glo_effdt < sysdate AND  i.oth_effdt < sysdate  THEN
	  		  l_effdt 	:= SYSDATE;
	   		  l_impldt := SYSDATE;
	  ELSIF i.glo_effdt > sysdate AND i.oth_effdt > sysdate THEN
	  	   	 l_effdt 	:= to_date(i.oth_effdt,'DD-MON-YYYY HH24:MI:SS') + 1;
	  		 l_impldt := SYSDATE;
      ELSIF i.glo_effdt < sysdate AND i.oth_effdt > sysdate THEN
	  	   	 l_effdt 	:= to_date(i.oth_effdt,'DD-MON-YYYY HH24:MI:SS') + 1;
	  		 l_impldt := SYSDATE;
	  END IF;
	  ------------------------------------------------------------------------------------
	  -- Added by HCL for HD 21283
	  IF p_ins_rev = 1 AND (i.glo_rev <> i.oth_rev ) THEN
	  BEGIN
	--  FOR k in (SELECT organization_id
	  --          FROM   mtl_system_items_b
		--		WHERE  inventory_item_id = i.inventory_item_id) -- Added by HCL for HD 21283
	 -- LOOP
	  	  fnd_file.put_line(FND_FILE.LOG, 'inserting into  interface table' || g_user_id || ' '|| i.inventory_item_id );
	 BEGIN
	  INSERT INTO mtl_item_revisions_interface (
                  created_by, creation_date, effectivity_date,
                  implementation_date, inventory_item_id, last_update_date,
                  last_update_login, last_updated_by, organization_id,
                  process_flag, revision, transaction_type,
	          set_process_id,request_id,description)
           VALUES (g_user_id, SYSDATE, l_effdt,
                  -- l_impldt, i.inventory_item_id, SYSDATE, Substitued Sysdate for l_impldt
                    SYSDATE,i.inventory_item_id, SYSDATE,
                   g_login_id, g_user_id
		   -- For HD 21283
         	   --,p_org_id,
 	    	    ,j.organization_id,
                     1,i.glo_rev,'CREATE',
		     g_set_process_id,g_request_id,
		   -- 'Created through Request id->'||g_request_id||' PWR Revision Insert Program'
		    i.description
                  );
   commit;
   EXCEPTION
         	 WHEN OTHERS THEN
				fnd_file.put_line(FND_FILE.LOG,'Exception while inserting ' || SQLERRM);
          	END;
	 	--  END LOOP;
	       END;
  END IF;	-- Added by HCL for HD 21283
		fnd_file.put_line(FND_FILE.LOG, 'porgid ' || p_org_id || 'j.orga ' || j.organization_id);
 IF (p_org_id = j.organization_id) or ( p_org_id = 1) THEN
	  pwr_exception_pkg.print_msg(RPAD(g_slno,5)
						||' '||RPAD(l_item_number,25)
						||' '||RPAD(l_item_desc,50)
						||' '||RPAD(i.glo_rev,10)
						||' '||RPAD(i.glo_effdt,15)
						||' '||RPAD(i.glo_impldt,15)
						||' '||RPAD(i.oth_rev,10)
						||' '||RPAD(i.oth_effdt,15)
						||' '||RPAD(i.oth_impldt,15)
						||' '||'Interfaced Effdt->'
						||RPAD(l_effdt,15)||'Impl dt->'||RPAD(l_impldt,15)
						||' '||LPAD(l_organization_code,15)
						,g_out,1);
  END IF;
g_slno := g_slno + 1;
END LOOP;
g_slno := 1;
END LOOP; -- Added By HCL for HD 21283
IF p_ins_rev = 1  THEN   -- Added By HCL for HD 21283
   commit;
END IF;
g_stmt_num := 1049;
pwr_print_eos(260);--220  -- Added for HD 21283
g_stmt_num := 1050;
----------------------------Logic to show the Revisions Description Details----------------------------------------
 pwr_exception_pkg.print_msg('p_inc_desc_diff		->'||p_inc_desc_diff,g_log,1);
 IF p_inc_desc_diff = 'Y' THEN
      pwr_exception_pkg.print_blank_line(g_log,2);
      pwr_exception_pkg.print_blank_line(g_log,2);
      pwr_exception_pkg.print_blank_line(g_log,2);
      g_stmt_num := 1082;
      pwr_exception_pkg.print_blank_line(g_out,2);
      pwr_exception_pkg.print_line(' ',220 ,'Revision Description Difference as compared to GLO for the same Revisions','C',g_out);
      pwr_exception_pkg.print_blank_line(g_out,2);
      pwr_exception_pkg.print_line('-',220+40,'-','C',g_out);
      g_stmt_num := 1090;
      pwr_exception_pkg.print_msg(RPAD('Srno',5)
		  ||''||RPAD(' Item  ',20)
		  ||''||RPAD('  Item Description',33)
		  ||''||RPAD('                          Revision',40)
		  ||''||RPAD('            Rev Desc in GLO',34)
		  ||''||LPAD('Rev Desc in Child Org',50)
		  ||''||LPAD('Child Org',50)
		    ,g_out,1);
      pwr_exception_pkg.print_line('-',220+40,'-','C',g_out);
      g_slno := 1 ;
   FOR i in ( SELECT  organization_id
              FROM    org_organization_definitions
              WHERE   organization_id like decode(p_org_id,1,'%',p_org_id || '%'))
   LOOP
--   FOR J IN c2_data(p_org_id,p_item_cat_id)
      FOR J IN c2_data(i.organization_id,p_item_cat_id)
   LOOP
    	pwr_exception_pkg.print_msg(RPAD(g_slno,5)
	 	  				||' '||RPAD(J.Item,20)
			   			||' '||RPAD(J.itemdescription,53)
				  		||'    '||RPAD(J.Revision,25)
					  	||' '||RPAD(J.masterdesc,40)
				  		||'           '||RPAD(J.childesc,40)
						||'                       '||RPAD(J.organization,40)
					    	,g_out,1);
     g_slno := g_slno + 1;
   END LOOP;
   END LOOP;
   g_stmt_num := 1049;
   pwr_print_eos(260) ;--(220);
   g_stmt_num := 1050;
END IF;
----------------------------Logic Revisions Description Details Ends----------- ----------------------------------------
----------------------------Logic Revision Description Details updation-------------------------------------------------
 pwr_exception_pkg.print_msg('p_upd_desc_diff 		->'||p_upd_desc_diff ,g_log,1);
 IF p_upd_desc_diff = 'Y' THEN
   FOR i in ( SELECT  organization_id
              FROM    org_organization_definitions
              WHERE   organization_id like decode(p_org_id,1,'%',p_org_id || '%'))
   LOOP
   -- FOR J IN c2_data(p_org_id,p_item_cat_id)
    FOR J IN c2_data(i.organization_id,p_item_cat_id)
    LOOP
 	    UPDATE mtl_item_revisions
 	    SET    description 		 		   =  (RTRIM(LTRIM(J.masterdesc)))
 	    WHERE  inventory_item_id 			   =  J.inventory_item_id
 	    AND    (LTRIM(RTRIM(revision)))           =  (LTRIM(RTRIM(J.Revision)))
 	    AND    organization_id                         =  J.c_organization_id
 	    ;
 	   END LOOP;
	END LOOP;
	commit;
   END IF;
-----------------------------------Revision Description Details updation ends-------------------------------------------
EXCEPTION  WHEN OTHERS THEN
		  pwr_exception_pkg.print_blank_line(g_log,2);
		  pwr_exception_pkg.print_msg('Log ->  Error :',g_log,1);
		  pwr_exception_pkg.print_msg('--------------',g_log,1);
		  pwr_exception_pkg.print_msg('Exception     : Others');
		  pwr_exception_pkg.print_msg('Package       : pwr_rev_ins_pkg.pwr_populate_interface',g_log,1);
		  pwr_exception_pkg.print_msg('Error Msg     :'||substrb(sqlerrm,1,500),g_log,1);
		  pwr_exception_pkg.print_msg('Error Code    :'||sqlcode,g_log,1);
		  pwr_exception_pkg.print_msg('Statement num :' ||g_stmt_num,g_log,1);
		  pwr_exception_pkg.print_blank_line(g_log,2);
END pwr_populate_interface;
PROCEDURE pwr_launch_item_import( p_org_id 	IN 	NUMBER ) IS
--
l_request_id 		NUMBER := 0;
l_request_status	BOOLEAN;
l_phase      		VARCHAR2(25) := NULL;
l_status     		VARCHAR2(25) := NULL;
l_dev_phase  		VARCHAR2(25) := NULL;
l_dev_status 		VARCHAR2(25) := NULL;
l_message    		VARCHAR2(500):= NULL;
BEGIN
g_stmt_num := 1051;
-----------------------------------------------------------------
--This program run the Concurrent program -> Import Items
--
-----------------------------------------------------------------
--Parameter Details 	| Default Values passed  | Values
-----------------------------------------------------------------
--Organization_id 		| MFG org from Concurrent pgm
--All Organization 		| Yo
--Validate Items 		| Yes
--Process Items		| Yes
--Delete Processed Rows	| No
--Process Set            | Process Set id	    |
--Create OR Update		|  1				    | 1` -Created, 2 - Update
-----------------------------------------------------------------
FOR i in ( SELECT  organization_id
           FROM    org_organization_definitions
     --     where organization_id = 465    )
    WHERE   organization_id like decode(p_org_id,1,'%',p_org_id || '%'))
LOOP
BEGIN
l_request_id:=
         FND_REQUEST.SUBMIT_REQUEST (application      => 'INV',     --application short name
                                     program          => 'INCOIN',  --program short name
                                     description      => 'Import item by PWR Revision Insert Program',
                                     start_time       => SYSDATE,   --default as sysdate
                                     sub_request      => FALSE,
                                --   argument1        => p_org_id,  --MFG org id
    				     argument1        => i.organization_id,  --MFG org id
                                     argument2        => 2,  --All org         = NO
                                     argument3        => 1,  --Validate items  = Yes
                                     argument4        => 1,  --Process Items   = yes
                                     argument5        => 2,  --delete processed rows = No
								     argument6        => g_set_process_id, -- Process id passed to interface
								     argument7	    => 1   --Create or Update = Created
								  );
--
COMMIT;
g_stmt_num := 1053;
--  Wait for the Request to Complete
l_request_status := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request_id, -- Request Id Submited
				 									10, -- Interval in seconds default is 60
		  											0, -- MAX_WAIT  DEFAULT 0
		  											l_phase,
		  											l_status,
		  											l_dev_phase,
		  											l_dev_status,
		  											l_message);
g_stmt_num := 1054;
pwr_interface_results;
g_stmt_num := 1060;
EXCEPTION
 WHEN OTHERS THEN
	BEGIN
		  pwr_exception_pkg.print_blank_line(g_log,2);
		  pwr_exception_pkg.print_msg('Log ->  Error :',g_log,1);
		  pwr_exception_pkg.print_msg('--------------',g_log,1);
		  pwr_exception_pkg.print_msg('Exception     : Others');
		  pwr_exception_pkg.print_msg('Package       : pwr_rev_ins_pkg.pwr_launch_item_import',g_log,1);
		  pwr_exception_pkg.print_msg('Error Msg     :'||substrb(sqlerrm,1,500),g_log,1);
		  pwr_exception_pkg.print_msg('Error Code    :'||sqlcode,g_log,1);
		  pwr_exception_pkg.print_msg('Statement num :' ||g_stmt_num,g_log,1);
		  pwr_exception_pkg.print_blank_line(g_log,2);
	END;
END;
END LOOP;
END pwr_launch_item_import;
PROCEDURE pwr_interface_results IS
--
v_i 	 NUMBER :=0;
-------------------------------------------------------
--cursor select the details/Status  from interface
--after processed completed
CURSOR c_iresult IS
SELECT miri.organization_id,
       oods.organization_code,
       substr(oods.organization_name,1,20) organization_name,
       miri.inventory_item_id ,
       substr(msi.segment1,1,50) item_number,
       msi.description item_desc,
       decode(miri.process_flag,1,'Pending',
	  		  	  2,'Assigned Succeeded',
			  	  3,'Assign/Validation Failed',
			 	  4,'Validation Succeeded',
				  7,'Import Succeeded') status,
       miri.transaction_id,
       err.message_name,
       err.error_message
  FROM mtl_interface_errors err,
       mtl_item_revisions_interface  miri,
       mtl_system_items_b msi,
	 ( SELECT ood.organization_id,
	  	  ood.organization_code,
		  ood.organization_name
	   FROM   Org_organization_definitions  ood ) oods
           WHERE  miri.transaction_id    = err.transaction_id(+)  --OUT JOIN USED SINCE ERROR MAY OR MAY NOT OCCUR
           AND    miri.request_id		= g_request_id
           AND    miri.inventory_item_id = msi.inventory_item_id
           AND    miri.organization_id   = oods.organization_id -- ADDED HERE TO ONLY TO GET THE ORGANIZATION_NAME OF miri
           AND    msi.organization_id 	= 86;
--
BEGIN
--
g_stmt_num := 1061;
--pwr_exception_pkg.print_blank_line(g_out,2);
pwr_print_heading('Status of Processed Records',150);
pwr_heading_interface;
g_stmt_num := 1062;
g_slno := 1;  -- Init the Slno only for printing
FOR v_i IN c_iresult
LOOP
g_stmt_num := 1063;
pwr_exception_pkg.print_msg(RPAD(g_slno,5)
		  ||' '||RPAD(v_i.organization_code,15)
           ||' '||RPAD(v_i.organization_name,20)
		  ||' '||RPAD(v_i.item_number,20)
           ||' '||RPAD(v_i.item_desc,50)
           ||' '||RPAD(v_i.status,25)
		  ||' '||RPAD(v_i.error_message,240),g_out,1);
g_slno := g_slno + 1;
END LOOP;
pwr_print_eos(150);
g_stmt_num := 1070;
EXCEPTION  WHEN OTHERS THEN
		  pwr_exception_pkg.print_blank_line(g_log,2);
		  pwr_exception_pkg.print_msg('Log ->  Error :',g_log,1);
		  pwr_exception_pkg.print_msg('--------------',g_log,1);
		  pwr_exception_pkg.print_msg('Exception     : Others');
		  pwr_exception_pkg.print_msg('Package       : pwr_rev_ins_pkg.pwr_interface_results',g_log,1);
		  pwr_exception_pkg.print_msg('Error Msg     :'||substrb(sqlerrm,1,500),g_log,1);
		  pwr_exception_pkg.print_msg('Error Code    :'||sqlcode,g_log,1);
		  pwr_exception_pkg.print_msg('Statement num :' ||g_stmt_num,g_log,1);
		  pwr_exception_pkg.print_blank_line(g_log,2);
END pwr_interface_results;
PROCEDURE pwr_del_processed_record IS
--
BEGIN
g_stmt_num := 1071;
--------------------------------------------------------------------
--process flag = 7 means records sucessfully imported
--we manually delete record which is similar to delete_processed_row
DELETE FROM mtl_item_revisions_interface
 WHERE
   --process_flag = 7
   --AND
   request_id  = g_request_id;
--
COMMIT;  --save the changes
--
g_stmt_num := 1080;
EXCEPTION  WHEN OTHERS THEN
		  pwr_exception_pkg.print_blank_line(g_log,2);
		  pwr_exception_pkg.print_msg('Log ->  Error :',g_log,1);
		  pwr_exception_pkg.print_msg('--------------',g_log,1);
		  pwr_exception_pkg.print_msg('Exception     : Others');
		  pwr_exception_pkg.print_msg('Package       : pwr_rev_ins_pkg.pwr_del_processed_record',g_log,1);
		  pwr_exception_pkg.print_msg('Error Msg     :'||substrb(sqlerrm,1,500),g_log,1);
		  pwr_exception_pkg.print_msg('Error Code    :'||sqlcode,g_log,1);
		  pwr_exception_pkg.print_msg('Statement num :' ||g_stmt_num,g_log,1);
		  pwr_exception_pkg.print_blank_line(g_log,2);
END pwr_del_processed_record;
PROCEDURE pwr_print_heading(p_heading_msg IN VARCHAR2,
		 				   p_rep_length  IN NUMBER) IS
e_head_len_great_rep_len EXCEPTION;
BEGIN
g_stmt_num := 1081;
IF LENGTH(p_heading_msg) > p_rep_length THEN
   RAISE e_head_len_great_rep_len;
END IF;
g_stmt_num := 1082;
pwr_exception_pkg.print_blank_line(g_out,2);
pwr_exception_pkg.print_line(' ',p_rep_length,p_heading_msg,'C',g_out);
pwr_exception_pkg.print_blank_line(g_out,2);
pwr_exception_pkg.print_msg('Report Run on :'||sysdate,g_out,1);
pwr_exception_pkg.print_line('-',p_rep_length+40,'-','C',g_out);
g_stmt_num := 1090;
EXCEPTION  WHEN e_head_len_great_rep_len THEN
		  pwr_exception_pkg.print_blank_line(g_log,2);
		  pwr_exception_pkg.print_msg('Log ->  Error :',g_log,1);
		  pwr_exception_pkg.print_msg('--------------',g_log,1);
		  pwr_exception_pkg.print_msg('Exception     : e_head_len_great_rep_len');
		  pwr_exception_pkg.print_msg('Package       : pwr_rev_ins_pkg.pwr_print_heading',g_log,1);
		  pwr_exception_pkg.print_msg('Error Msg     : Heading Length is Greater than Rep Length',g_log,1);
		  pwr_exception_pkg.print_msg('Error Code    :'||sqlcode,g_log,1);
		  pwr_exception_pkg.print_msg('Statement num :' ||g_stmt_num,g_log,1);
		  pwr_exception_pkg.print_blank_line(g_log,2);
		  WHEN OTHERS THEN
		  pwr_exception_pkg.print_blank_line(g_log,2);
		  pwr_exception_pkg.print_msg('Log ->  Error :',g_log,1);
		  pwr_exception_pkg.print_msg('--------------',g_log,1);
		  pwr_exception_pkg.print_msg('Exception     : Others');
		  pwr_exception_pkg.print_msg('Package       : pwr_rev_ins_pkg.pwr_print_heading',g_log,1);
		  pwr_exception_pkg.print_msg('Error Msg     :'||substrb(sqlerrm,1,500),g_log,1);
		  pwr_exception_pkg.print_msg('Error Code    :'||sqlcode,g_log,1);
		  pwr_exception_pkg.print_msg('Statement num :' ||g_stmt_num,g_log,1);
		  pwr_exception_pkg.print_blank_line(g_log,2);
END pwr_print_heading;
PROCEDURE pwr_print_eos( p_rep_length  IN NUMBER) IS
BEGIN
g_stmt_num := 1091;
pwr_exception_pkg.print_line('-',p_rep_length,'End of statement','C',g_out);
g_stmt_num := 1100;
EXCEPTION  WHEN OTHERS THEN
		  pwr_exception_pkg.print_blank_line(g_log,2);
		  pwr_exception_pkg.print_msg('Log ->  Error :',g_log,1);
		  pwr_exception_pkg.print_msg('--------------',g_log,1);
		  pwr_exception_pkg.print_msg('Exception     : Others');
		  pwr_exception_pkg.print_msg('Package       : pwr_rev_ins_pkg.pwr_print_eos',g_log,1);
		  pwr_exception_pkg.print_msg('Error Msg     :'||substrb(sqlerrm,1,500),g_log,1);
		  pwr_exception_pkg.print_msg('Error Code    :'||sqlcode,g_log,1);
		  pwr_exception_pkg.print_msg('Statement num :' ||g_stmt_num,g_log,1);
		  pwr_exception_pkg.print_blank_line(g_log,2);
END pwr_print_eos;
PROCEDURE pwr_heading_interface IS
BEGIN
g_stmt_num := 1101;
pwr_exception_pkg.print_msg(RPAD('Slno',5)
		  ||' '||RPAD('INV Org Code',15)
           ||' '||RPAD('Organization Name',20)
		  ||' '||RPAD('Item Number',20)
		  ||' '||RPAD('Item desc',50)
           ||' '||RPAD('Status',25)
		  ||' '||RPAD('Error Msg ',50),g_out,1);
pwr_exception_pkg.print_line('-',150,'-','C',g_out);
g_stmt_num := 1110;
EXCEPTION  WHEN OTHERS THEN
		  pwr_exception_pkg.print_blank_line(g_log,2);
		  pwr_exception_pkg.print_msg('Log ->  Error :',g_log,1);
		  pwr_exception_pkg.print_msg('--------------',g_log,1);
		  pwr_exception_pkg.print_msg('Exception     : Others');
		  pwr_exception_pkg.print_msg('Package       : pwr_rev_ins_pkg.pwr_heading_interface',g_log,1);
		  pwr_exception_pkg.print_msg('Error Msg     :'||substrb(sqlerrm,1,500),g_log,1);
		  pwr_exception_pkg.print_msg('Error Code    :'||sqlcode,g_log,1);
		  pwr_exception_pkg.print_msg('Statement num :' ||g_stmt_num,g_log,1);
		  pwr_exception_pkg.print_blank_line(g_log,2);
END pwr_heading_interface;
PROCEDURE pwr_heading_trn_iface IS
BEGIN
g_stmt_num := 1111;
pwr_exception_pkg.print_msg(RPAD('Srno',5)
		  ||' '||RPAD('Inventory Item Number',25)
		  ||' '||RPAD('Item Description',48)
		  ||'|'||RPAD('Details of GLOBAL Org',42)
		  ||'|'||RPAD('Details of Selected Inv.Org',42)
		  ||'|'||LPAD('Status',30)
		  ||'|                      '||LPAD('Organization Code',30)
		    ,g_out,1);
pwr_exception_pkg.print_msg(RPAD(' ',80)
           ||'|'||RPAD('Latest Rev',10)
		  ||'|'||RPAD('Latest Eff dt',15)
		  ||'|'||RPAD('Latest Impl dt',15)
           ||'|'||RPAD('Latest Rev',10)
		  ||'|'||RPAD('Latest Eff dt',15)
		  ||'|'||RPAD('Latest Impl dt',15)
		  ||'|',g_out,1);
pwr_exception_pkg.print_line('-',260,'-','C',g_out);
g_stmt_num := 1120;
EXCEPTION  WHEN OTHERS THEN
		  pwr_exception_pkg.print_blank_line(g_log,2);
		  pwr_exception_pkg.print_msg('Log ->  Error :',g_log,1);
		  pwr_exception_pkg.print_msg('--------------',g_log,1);
		  pwr_exception_pkg.print_msg('Exception     : Others');
		  pwr_exception_pkg.print_msg('Package       : pwr_rev_ins_pkg.pwr_heading_trn_iface',g_log,1);
		  pwr_exception_pkg.print_msg('Error Msg     :'||substrb(sqlerrm,1,500),g_log,1);
		  pwr_exception_pkg.print_msg('Error Code    :'||sqlcode,g_log,1);
		  pwr_exception_pkg.print_msg('Statement num :' ||g_stmt_num,g_log,1);
		  pwr_exception_pkg.print_blank_line(g_log,2);
END pwr_heading_trn_iface;
END  pwr_rev_ins_pkg;