WITH TMP_ACT AS												
(												
SELECT * FROM SPETLB21.TMP_ACTIVITY A )												
												
	SELECT             											
							 e.Company_Id AS Company_Id					
                            , Pol_Num												
                            , Proposal_Num												
                            , c.Holding_Id      AS Holding_Id												
                            , e.Policy_Id       AS Policy_Id												
							, e.orig_eff_dt		AS Original_Eff_Dt			
							, d.coverage_id     AS Coverage_Id					
                            , d.cov_num    AS Coverage_Num  --added as per Des to investigate												
							, d.rider_num  AS rider_num   					
							, f.life_participant_id AS life_participant_id					
							, f.party_seq_num as Life_num					
							, f.Party_ID as PARTY_ID					
							, f.Vitality_Status	as Vitality_Ind				
                            , q.LOCAL_LOOKUP_CD AS coverage_cd												
                            , j.LOCAL_LOOKUP_CD AS STATCODE												
                            , u.LOCAL_LOOKUP_CD AS PREM_STAT_CD												
                            , l.LOCAL_LOOKUP_CD AS CNTTYPE												
                            , m.LOCAL_LOOKUP_CD AS Payment_Mode												
                            , n.LOCAL_LOOKUP_CD AS Payment_Method												
                            , Total_Prem_Amt												
                            , Single_Prem_Amt												
                            , r.LOCAL_LOOKUP_CD        AS Source_of_Business												
                            , t.LOCAL_LOOKUP_LONG_DESC AS LONGDESC												
                            , o.LOCAL_LOOKUP_CD        AS Currency_Cd												
							, 'null'				       AS Report_Code 	
							, 'null'					   AS Report_Desc 
                            ,												
                              (												
                              CASE												
                                        WHEN k.LOCAL_LOOKUP_CD='2'												
                                        THEN 'SGP'												
                                        ELSE 'BRN'												
                              END												
                              )												
                              AS COUNTRY_Cd												
                            , i.Start_Dt as Ptr_Eff_Dt												
							, i.transaction_dt as Status_Dt /*Added Status Change Date from PTRNPF for tracking latest change in Penders*/					
							, i.Activity_Cd as Tran_Num					
							, i.batch_tran_cd as batch_tran_cd					
                            , h.Curr_From												
                            , AGNTNUM												
                            , AGENCYNUM												
							, CONTRACT_START_DATE					
                            , agnt_agncy_rel.District_cd												
                            , Area_Cd												
                            , clusterNm												
                            , SDOA												
                            , Subchannel1												
                            , Subchannel2												
                            , Subchannel3 												
												
                    FROM												
												
							SPETLB21.TMP_HOLDING	c				
												
                              INNER JOIN SPETLB21.TMP_POLICY_CURR e												
								  ON				
									(			
									  c.Holding_id    =e.Holding_id			
									  /* AND c.company_id=e.company_id */			
									)			
												
                              INNER JOIN SPETLB21.TMP_COVERAGE_CURR d												
								  ON				
									(			
									  d.Policy_Id     =e.Policy_Id			
									 /*  AND d.company_id=e.company_id */			
									)			
												
                              INNER JOIN SPETLB21.TMP_LIFEP_CURR f												
								  ON				
									(			
									 d.coverage_id=f.coverage_id			
									)			
												
                              INNER JOIN SPETLB21.TMP_POLSTATUS_CURR h												
								  ON				
									(			
									 e.policy_id=h.policy_id			
									)			
							  INNER JOIN 					
							  (SELECT A.* 					
							  FROM SPETLB21.TMP_ACT A 					
							  INNER JOIN 					
								(       				
								SELECT				
									LOCAL_LOOKUP_CD			
								FROM				
									SPETLB21.MV_SalesMI_Adam_Grp_Code_Rltn			
								WHERE				
									(MASTER_LKP_TYPE_DESC)='BUSDPF' and substr(LOCAL_LOOKUP_CD,1,4) <= to_char(sysdate,'yyyy')			
								) c				
							ON 					
							(					
								(				
								SUBSTR(c.LOCAL_LOOKUP_CD,5,2)       ='12'				
								AND TO_CHAR(a.TRANSACTION_DT,'YYYYMM') >= SUBSTR(c.LOCAL_LOOKUP_CD,1,4)||'11' /*use transaction date as discussed */				
								)				
								OR				
								(				
								SUBSTR(c.LOCAL_LOOKUP_CD,5,2)      <>'12'				
								AND TO_CHAR(a.TRANSACTION_DT,'YYYYMM') >=CAST(SUBSTR(c.LOCAL_LOOKUP_CD,1,4) AS SMALLINT)-1 ||'11'/*use transaction date as discussed */				
								) 				
							)    					
							where  					
								(batch_tran_cd  in ('T607','T71A','T46A','T642','T646','TN32'))				
												
							UNION ALL					
							SELECT * 					
							FROM SPETLB21.TMP_ACT A 					
							where   					
							(batch_tran_cd in ('T1JA','T6A0') or (batch_tran_cd='T600' and activity_cd=1)) /*Extract all Outstanding Penders and Submission for reinstatement*/   					
							  ) i ON					
									(			
										e.policy_id=i.policy_id		
									)			
												
                              LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn j												
								  ON				
									(			
									  j.CODE_RELATIONSHIP_ID    =h.policy_status			
									  AND (j.MASTER_LKP_TYPE_DESC)='CONTRACT RISK STATUS'			
									)			
                              LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn l												
									ON			
                                        (												
                                                  l.CODE_RELATIONSHIP_ID =e.product_cd												
                                                  /* AND (l.MASTER_LKP_TYPE_DESC)='CONTRACT PROCESSING RULES' */												
                                        )												
                              LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn k												
                              ON												
                                        (												
                                                  k.CODE_RELATIONSHIP_ID    =e.Company_Id												
                                                  AND (k.MASTER_LKP_TYPE_DESC)='COMPANY CODE'												
                                        )												
                              LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn m												
                              ON												
                                        (												
                                                  m.CODE_RELATIONSHIP_ID    =e.Payment_Mode												
                                                  --AND m.MASTER_LKP_TYPE_DESC='Payment Mode' --Vamsi: Condition changed in ETL Spec.												
												  AND (m.MASTER_LKP_TYPE_DESC)='FREQUENCIES'
                                        )												
                              LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn n												
                              ON												
                                        (												
                                                  n.CODE_RELATIONSHIP_ID    =e.Payment_Method												
                                                 -- AND n.MASTER_LKP_TYPE_DESC='Payment Method' --Vamsi: Condition changed in ETL Spec.												
												  AND (n.MASTER_LKP_TYPE_DESC)='BILLING CHANNELS/METHOD OF PAY'
                                        ) 												
												
                             LEFT OUTER  JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn o ON												
                                       (												
                                                  o.CODE_RELATIONSHIP_ID=c.Currency_Cd												
                                                  --AND o.MASTER_LKP_TYPE_DESC='Currency' --Vamsi: Condition changed in ETL Spec.												
												  AND (o.MASTER_LKP_TYPE_DESC)='CURRENCY CODE DETAILS'
                                       )												
                            												
							LEFT OUTER  JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn q ON					
							(					
									  q.CODE_RELATIONSHIP_ID=f.Coverage_Cd			
									  AND			
									  (q.MASTER_LKP_TYPE_DESC)='GENERAL COVERAGE/RIDER DETAILS'			
							) --Map Coverage_Cd column from Adam Life Participant once the Agreement changes from Adam Coverage to Adam Life Participant is  deployed					
							LEFT OUTER  JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn r ON					
							(					
									  --r.CODE_RELATIONSHIP_ID=f.Source_of_Business-- Vamsi : Condition Changed			
									  r.CODE_RELATIONSHIP_ID=e.Source_of_Business			
									  AND			
									  (r.MASTER_LKP_TYPE_DESC)='SOURCES OF BUSINESS'			
							) 			
							LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn t ON					
							(					
									  t.local_lookup_cd=r.local_lookup_cd			
									  AND			
									  (t.MASTER_LKP_TYPE_DESC)='DETAIL CHANNEL SALES MI'			
									  and k.LOCAL_LOOKUP_CD = t.local_lookup_company_id			
							)					
												
                              LEFT OUTER JOIN spetlb21.MV_SalesMI_Adam_Grp_Code_Rltn u												
								  ON				
									(			
									  u.CODE_RELATIONSHIP_ID    =h.premium_status			
									  AND (u.MASTER_LKP_TYPE_DESC)='COVERAGE/RIDER PREMIUM STATUS'			
									)			
							LEFT OUTER JOIN spetlb21.MV_SalesMI_Sub_Channel agnt_agncy_rel ON agnt_agncy_rel.holding_id=c.holding_id